Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

deniscamh
Contributor

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

Re: Pivot table totals

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))

6 Replies
vinieme12
Esteemed Contributor II

Re: Pivot table totals

can you post a snapshot of the pivot for more clarity

deniscamh
Contributor

Re: Pivot table totals

Hi Vineeth,

Please see the snapshot belowPivot num of donors.JPG

Re: Pivot table totals

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
Esteemed Contributor II

Re: Pivot table totals

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))

deniscamh
Contributor

Re: Pivot table totals

Thanks a lot Sunny,

I totally forgot about aggregation function.

It worked thank you

deniscamh
Contributor

Re: Pivot table totals

Thanks Vineeth!

It worked!

Community Browser