EXISTINGBASE Cumulative monthly regular gift payments

PHOTO

Tue Aug 16 2022 11:06:34 GMT+0000 (Coordinated Universal Time)

Saved by @Matt_Stone #gigkpi

select
COUNT_DISTINCT(Opportunity__r.Id) The_Count, sum(Transaction_Allocation__c.Amount__c) amount,  Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
where 

(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND 
((Opportunity__r.RecordTypeId IN ('012w00000006jGy')
And
Opportunity__r.Date_Recurring_Donation_Established__c  > 2022-03-31
)
OR
 
(Opportunity__r.RecordTypeId IN ('0122X000000ie1A') 
AND
Opportunity__r.Direct_Regular_Donor_Start_of_FY__c = False))
 
AND 
Transaction_Allocation__c.GL_Code__c IN ('50030','50090')) 
AND
 Opportunity__r.CiWF_Payment_Method__c IN ('Credit/Debit Card','Direct Debit','Paypal','Agency','Payroll Giving','Standing Order')
 
AND
Opportunity__r.Contact_Earliest_Record_Date__c< 2022-04-01
 
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

Save snippets that work from anywhere online with our extensions

Available in the Chrome Web Store

Get Firefox Add-on

Get VS Code extension

Comments

More like this

Simple GIG KPI

@Matt_Stone

INCOME AND SPEND SUMMARY: Monthly regular gift income (DD, CC, PayPal) INCOME AND SPEND SO AGENCT AND PAYROLL INCOME AND SPEND Non Monthly DD income INCOME AND SPEND SUMMARY Donation (50020) Income and Spend Summary Appeal (50050) INCOME AND SPEND SUMMARY:FAV Donation (50060) INCOME AND SPEND SUMMARY: In Memory Of (IMO) (50170) INCOME AND SPEND SUMMARY: Single Fundraised Sales (51040)+Books(51050) INCOME AND SPEND SUMMARY: Single Fundraised Sales (51040)+Books(51050) INCOME AND SPEND SUMMARY: Single Fundraised Sales (51040)+Books(51050) INCOME AND SPEND SUMMARY: Single Fundraised Sales (51040)+Books(51050) INCOME AND SPEND SUMMARY: Raffle ticket sales INCOME AND SPEND SUMMARY:51060 - Community Fundraising INCOME AND SPEND SUMMARY:50130 - Collecting Tins INCOME AND SPEND SUMMARY:50110 - Gift Aid REGULAR GIVING SUMMARY: Monthly regular gift payments CC/PP/DD SINGLE GIFT SUMMARY: Single gift payments (donations only) NEW SUPPORTER INCOME: Single gift payments NEW SUPPORTER INCOME: Monthly regular gift income NEW SUPPORTER INCOME: New monthly regular gifts established (new supporters only) NEW SUPPORTER INCOME: Single gift income (new supporters only) EXISTING BASE: INCOME FROM EXISTING REGULAR INSTRUCTIONS EXISTING BASE Monthly regular gift payments (CC/PP/DD) EXISTING BASE: Existing Non monthly DD income Existing Base other regular giving income EXISTINGBASE Cumulative monthly regular gift payments EXISTING BASE Monthly regular gift income from new regular donor EXISTING BASE single gift donations income - existing supporters EXISTING BASE Single gift donations EXISTING BASE: Single gift income - high value gifts Existing Base: Workplace giving income EXISTING BASE Volunteer fundraising & other cash income (Community Events, FB fundraiser, Lilo) EXISTING BASE: Gift aid income ES ES Monthly cc/pp/dd Income existing ES ES Monthly cc/pp/dd Payments existing Single Single gift under 500 Total Regular giving Existing WORKPLACE GIVING

Non Monthly DD income

select Transaction_Allocation__c.Close_Date__c,sum(Transaction_Allocation__c.Amount__c) amount,COUNT_DISTINCT(Opportunity__r.Id) The_Count,  Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode 
from Transaction_Allocation__c

where 
Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1 

AND ((Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal')

AND
Opportunity__r.CiWF_Payment_Method__c = 'Direct Debit'
     
AND
Transaction_Allocation__c.GL_Code__c IN ('50030', '50090'))
AND
((Opportunity__r.RecordTypeId ='012w00000006jGy'
AND
Opportunity__r.Recurring_Donation_Installment_Period__c != 'Monthly')
 
OR 
(Opportunity__r.RecordTypeId IN ('0122X000000ie1A')
AND
Opportunity__r.CiWF_Payment_Method__c != 'Paypal')))



Group by Transaction_Allocation__c.Close_Date__c,Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode

ES Monthly cc/pp/dd Payments existing

select Transaction_Allocation__c.Close_Date__c,COUNT_DISTINCT(Opportunity__r.Id) The_Count,sum(Transaction_Allocation__c.Amount__c) amount,  Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode 

from Transaction_Allocation__c

where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 

AND 
((Opportunity__r.RecordTypeId IN ('012w00000006jGy')
And
Opportunity__r.Date_Recurring_Donation_Established__c  < 2022-04-01
AND
Opportunity__r.Recurring_Donation_Installment_Period__c = 'Monthly')
OR

(Opportunity__r.RecordTypeId IN ('0122X000000ie1A') 
AND
Opportunity__r.Direct_Regular_Donor_Start_of_FY__c = True))

AND 
Transaction_Allocation__c.GL_Code__c IN ('50030','50090')) 
AND
 Opportunity__r.CiWF_Payment_Method__c IN ('Credit/Debit Card','Direct Debit','Paypal')
AND
Opportunity__r.CampaignId = '7014J000000MQ63QAG'


Group by Transaction_Allocation__c.Close_Date__c,Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c , Opportunity__r.CurrencyIsoCode

ES Monthly cc/pp/dd Payment existing>0

select Transaction_Allocation__c.Close_Date__c,COUNT_DISTINCT(Opportunity__r.Id) The_Count,sum(Transaction_Allocation__c.Amount__c) amount,  Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode 

from Transaction_Allocation__c

where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
AND
Transaction_Allocation__c.Amount__c <0
AND 
((Opportunity__r.RecordTypeId IN ('012w00000006jGy')
And
Opportunity__r.Date_Recurring_Donation_Established__c  < 2022-04-01
AND
Opportunity__r.Recurring_Donation_Installment_Period__c = 'Monthly')
OR

(Opportunity__r.RecordTypeId IN ('0122X000000ie1A') 
AND
Opportunity__r.Direct_Regular_Donor_Start_of_FY__c = True))

AND 
Transaction_Allocation__c.GL_Code__c IN ('50030','50090')) 
AND
 Opportunity__r.CiWF_Payment_Method__c IN ('Credit/Debit Card','Direct Debit','Paypal')
AND
Opportunity__r.CampaignId = '7014J000000MQ63QAG'


Group by Transaction_Allocation__c.Close_Date__c,Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c , Opportunity__r.CurrencyIsoCode

*Monthly regular gift payments (CC/PP/DD)

select Transaction_Allocation__c.Close_Date__c,
  COUNT_DISTINCT(Opportunity__r.Id) The_Count,
  sum(Transaction_Allocation__c.Amount__c) amount,  
  Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode 
 
from Transaction_Allocation__c
 
where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND 
((Opportunity__r.RecordTypeId IN ('012w00000006jGy')
And
Opportunity__r.Date_Recurring_Donation_Established__c  < 2022-04-01
AND
Opportunity__r.Recurring_Donation_Installment_Period__c = 'Monthly')
OR
 
(Opportunity__r.RecordTypeId IN ('0122X000000ie1A') 
AND
Opportunity__r.Direct_Regular_Donor_Start_of_FY__c = True))
 
AND 
Transaction_Allocation__c.GL_Code__c IN ('50030','50090')) 
AND
 Opportunity__r.CiWF_Payment_Method__c IN ('Credit/Debit Card','Direct Debit','Paypal')
 
 
Group by Transaction_Allocation__c.Close_Date__c,Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c , Opportunity__r.CurrencyIsoCode

*GBP International team

select Opportunity__r.Country_of_Ownership__c,CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c)Months,CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)years,
sum(Transaction_Allocation__c.Amount__c) amount,Opportunity__r.CurrencyIsoCode 

 
from Transaction_Allocation__c
 
where 
Transaction_Allocation__c.GL_Code__c IN ('50140','50180','50181','50270','50260','510141') 
AND
Transaction_Allocation__c.Stage__c IN ('Ask Made', 'Identify','Research','Plan','Engage','Prepare Ask','Pledged','Posted','Refunded','Failed','Refund','Reversal') 
AND
 Opportunity__r.RecordTypeId NOT IN ('012w00000006jGy','0122X000000ie1A')

AND
((Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
AND
Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1 
AND
(Opportunity__r.Pledge_Date__c > 2022-03-31
OR
Opportunity__r.Pledge_Date__c =null))
OR
(Transaction_Allocation__c.Stage__c IN ('Ask Made', 'Identify','Research','Plan','Engage','Prepare Ask','Pledged') 
AND
Opportunity__r.Pledge_Date__c > 2022-03-31))

 
Group by 
Opportunity__r.Country_of_Ownership__c, CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c),CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),Opportunity__r.CurrencyIsoCode

GBP International team 2

select Opportunity__r.Country_of_Ownership__c,CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c)Months,CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)years,
sum(Transaction_Allocation__c.Amount__c) amount,Opportunity__r.CurrencyIsoCode 
 
 
from Transaction_Allocation__c
 
where 
Transaction_Allocation__c.GL_Code__c IN ('50140','50180','50181','50270','50260','50141') 
AND
Transaction_Allocation__c.Stage__c IN ('Ask Made', 'Identify','Research','Plan','Engage','Prepare Ask','Pledged','Posted','Refunded','Failed','Refund','Reversal') 

AND
Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1 

Group by 
Opportunity__r.Country_of_Ownership__c, CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c),CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),Opportunity__r.CurrencyIsoCode

INCOME AND SPEND SUMMARY: Monthly regular gift income (DD, CC, PayPal)

select
sum(Transaction_Allocation__c.Amount__c) amount, count_Distinct(Opportunity__r.Id) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
 
where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
AND Transaction_Allocation__c.GL_Code__c IN ('50030','50090')) 
AND
 
((Opportunity__r.Recurring_Donation_Installment_Period__c = 'Monthly'
 
AND Opportunity__r.RecordTypeId IN ('012w00000006jGy'))
 
OR
 
(Opportunity__r.RecordTypeId IN ('0122X000000ie1A')
 
AND Opportunity__r.CiWF_Payment_Method__c IN ('Paypal')))
 
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

INCOME AND SPEND SO AGENCT AND PAYROLL

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
where
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
AND
Opportunity__r.CiWF_Payment_Method__c IN ('Standing Order','Agency','Payroll Giving'))
AND
Transaction_Allocation__c.GL_Code__c IN ('50030', '50090')
 AND
   
 Opportunity__r.RecordTypeId IN ('012w00000006jGy','0122X000000ie1A')
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

INCOME AND SPEND Non Monthly DD income

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
 
where 
Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1 
 
AND ((Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal')
 
AND
Opportunity__r.CiWF_Payment_Method__c = 'Direct Debit'
     
AND
Transaction_Allocation__c.GL_Code__c IN ('50030', '50090'))
AND
((Opportunity__r.RecordTypeId ='012w00000006jGy'
AND
Opportunity__r.Recurring_Donation_Installment_Period__c != 'Monthly')
 
OR 
(Opportunity__r.RecordTypeId IN ('0122X000000ie1A')
AND
Opportunity__r.CiWF_Payment_Method__c != 'Paypal')))
 
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

INCOME AND SPEND SUMMARY Donation (50020)

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
 where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND Opportunity__r.RecordTypeId IN ('012w0000000MQol','012w0000000hbgn','012w00000006jGo')
 
 
AND
 
Transaction_Allocation__c.TA_GL_Category__c = 'Donation (50020)'
)
 
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

Income and Spend Summary Appeal (50050)

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
 where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND Opportunity__r.RecordTypeId IN ('012w0000000MQol','012w0000000hbgn')
 
 
AND
 
Transaction_Allocation__c.TA_GL_Category__c = 'Appeal (50050)'
)
 
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

INCOME AND SPEND SUMMARY:FAV Donation (50060)

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
 where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND Opportunity__r.RecordTypeId IN ('012w0000000MQol','012w0000000hbgn')
 
 
AND
 
Transaction_Allocation__c.TA_GL_Category__c = 'FAV Donation (50060)'
)
 
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

INCOME AND SPEND SUMMARY: In Memory Of (IMO) (50170)

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
 where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND Opportunity__r.RecordTypeId IN ('012w0000000MQol','012w0000000hbgn','012w00000006jGe','0122X000000ie1F')
  
AND
 
Transaction_Allocation__c.TA_GL_Category__c = 'In Memory Of (IMO) (50170)'
)
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

INCOME AND SPEND SUMMARY: Single Fundraised Sales (51040)+Books(51050)

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND 
 
Opportunity__r.RecordTypeId IN ('012w0000000MQol','012w0000000hbgn','012w00000006jGo')
 
AND
 
Transaction_Allocation__c.TA_GL_Category__c IN ('Fundraised Sales (51040)','Book Sales (51050)'
))
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

INCOME AND SPEND SUMMARY: Single Fundraised Sales (51040)+Books(51050)

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND 
 
Opportunity__r.RecordTypeId IN ('012w0000000MQol','012w0000000hbgn')
 
AND
 
Transaction_Allocation__c.TA_GL_Category__c IN ('Fundraised Sales (51040)','Book Sales (51050)'
))
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

Browse more snippets >>