4 Replies Latest reply: Nov 21, 2017 6:21 PM by William Walsh

# Cumulative Sum in Pivot Table

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

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

Hi Luis,

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

Thanks for the help.

William

• ###### Re: Cumulative Sum in Pivot Table

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

Hi,