topic Cumulative Sum in Pivot Table in App Development

Cumulative Sum in Pivot Table

Anonymous — Tue, 24 Oct 2017 04:55:45 GMT

Hi,

I have a pivot table that I want it to show the cumulative sum of the spend and forecast.

I want to show for the months that have past the forecast row has to change to 0.

For the remaining months for the forecast the starting total has to be the total of the last amount of cumulative spent.

The following table is my data set.

Funding Code	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18
Spend	20	12	16	24	0	0	0	0	0	0	0	0
Forecast	16	18	20	28	20	14	18	12	16	14	17	10

This is what I want the pivot table to show.

Funding Code	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18
Spend	20	32	28	40	0	0	0	0	0	0	0	0
Forecast	0	0	0	0	60	74	92	104	120	134	151	161

I have a variable 'CurrentMonth' that dictates the current month of the year.

My current code for the measure of the Pivot Table is below.

If([Funding Code] = 'Spend',

if(Month> (CurrentMonth), 0,

RangeSum(before( sum(Financials) ,0,columnno(TOTAL)))))

If([Funding Code] = 'Forecast',

if(Month< (CurrentMonth+1), 0,

RangeSum(before(sum({$<Month={'>$(=CurrentMonth)'},[Funding Code]={'Forecast'}>}Financials)

sum({$<[Funding Code]={'Spend'}, FinancialYear={'2017'} >}Financials),

0,columnno(TOTAL)))))

This is what my code is currently showing below in the pivot table in qlik. The code in red is not adding to the total of the forecast.

How do I get the current Spend of 40 to be added to the Nov-17 forecast and the months following.

Funding Code	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18
Spend	20	32	28	40	0	0	0	0	0	0	0	0
Forecast	0	0	0	0	20	34	52	64	80	94	111	121

Thanks,

William

Re: Cumulative Sum in Pivot Table

luismadriz — Tue, 24 Oct 2017 05:31:24 GMT

Hi William,

I would try something like this.

For the Spend measure: SUM( IF(YearMonthField <= $(CurrentMonth),Spend,0) )

For the Forecast measure: SUM( IF(YearMonthField > $(CurrentMonth),Forecast,0) )

Regards,

Luis

Re: Cumulative Sum in Pivot Table

luismadriz — Fri, 10 Nov 2017 03:49:03 GMT

Hi William,

Just curious to see how did you go.

Remember to close it when you're done (Correct / Helpful)

Qlik Community Tip: Marking Replies as Correct or Helpful

Cheers,

Luis

Re: Cumulative Sum in Pivot Table

luismadriz — Tue, 21 Nov 2017 22:50:21 GMT

Hi,

Just wondering to know how you went about this one,

Cheers,

Luis

PS. When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

Re: Cumulative Sum in Pivot Table

Anonymous — Tue, 21 Nov 2017 23:21:12 GMT

Hi Luis,

I was able to use the range sum I had with the changes you suggested.

Thanks for the help.

William