Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
deniscamh
Creator
Creator

Pivot table totals

Hi all,

I created a set analysis for pivot table to calculate number of donors each moth. The formula displays actual number of donors in completed fiscal month but if a month not completed it displays forecast.

Here is a formula:

If(FiscalMonth < Today_FiscalMonth,

count({<FiscalYear = {'$(=maxstring(FiscalYear))'},[Gift Type] = {[Recurring Gift Pay-Cash]}, DonorCurentSegment = {'New'}>} distinct [Constituent ID]),

sum({<DonortypeBDFC = {'New'}>} Numofdonors_Forecast))

It works fine for each month but as total id displays forecast summary instead of adding year to day actuals and forecast.

Can somebody help me to understand why it is happening?

Thanks

Denis

1 Solution

Accepted Solutions
sunny_talwar

May be you need this:

Sum(Aggr(

If(FiscalMonth < Today_FiscalMonth,

count({<FiscalYear = {'$(=maxstring(FiscalYear))'},[Gift Type] = {[Recurring Gift Pay-Cash]}, DonorCurentSegment = {'New'}>} distinct [Constituent ID]),

sum({<DonortypeBDFC = {'New'}>} Numofdonors_Forecast))

, Measures, Month))

View solution in original post

6 Replies
vinieme12
Champion III
Champion III

can you post a snapshot of the pivot for more clarity

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
deniscamh
Creator
Creator
Author

Hi Vineeth,

Please see the snapshot belowPivot num of donors.JPG

sunny_talwar

May be you need this:

Sum(Aggr(

If(FiscalMonth < Today_FiscalMonth,

count({<FiscalYear = {'$(=maxstring(FiscalYear))'},[Gift Type] = {[Recurring Gift Pay-Cash]}, DonorCurentSegment = {'New'}>} distinct [Constituent ID]),

sum({<DonortypeBDFC = {'New'}>} Numofdonors_Forecast))

, Measures, Month))

vinieme12
Champion III
Champion III

try

SUM(

          AGGR(

If(FiscalMonth < Today_FiscalMonth,

count({<FiscalYear = {'$(=maxstring(FiscalYear))'},[Gift Type] = {[Recurring Gift Pay-Cash]}, DonorCurentSegment = {'New'}>} distinct [Constituent ID]),

sum({<DonortypeBDFC = {'New'}>} Numofdonors_Forecast))

          ,Measures,Month))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
deniscamh
Creator
Creator
Author

Thanks a lot Sunny,

I totally forgot about aggregation function.

It worked thank you

deniscamh
Creator
Creator
Author

Thanks Vineeth!

It worked!