Cumulative Sum in Pivot Table

[Anonymous]

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

[luismadriz]

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

[luismadriz]

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

[luismadriz]

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

[Anonymous]

Hi Luis,

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

Thanks for the help.

William