topic Pivot table totals in App Development

Pivot table totals

deniscamh — Wed, 25 Jan 2017 14:14:33 GMT

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

Re: Pivot table totals

vinieme12 — Wed, 25 Jan 2017 14:40:59 GMT

can you post a snapshot of the pivot for more clarity

Re: Pivot table totals

deniscamh — Wed, 25 Jan 2017 14:46:45 GMT

Hi Vineeth,

Please see the snapshot below

Re: Pivot table totals

sunny_talwar — Wed, 25 Jan 2017 14:52:43 GMT

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

Re: Pivot table totals

vinieme12 — Wed, 25 Jan 2017 14:53:32 GMT

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

Re: Pivot table totals

deniscamh — Wed, 25 Jan 2017 14:57:35 GMT

Thanks a lot Sunny,

I totally forgot about aggregation function.

It worked thank you

Re: Pivot table totals

deniscamh — Wed, 25 Jan 2017 14:58:15 GMT

Thanks Vineeth!

It worked!