Qlik Community

Qlik Sense App Development

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

william_walsh2
New Contributor II

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 CodeJul-17Aug-17Sep-17Oct-17Nov-17Dec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18
Spend2012162400000000
Forecast161820282014181216141710

This is what I want the pivot table to show.

Funding CodeJul-17Aug-17Sep-17Oct-17Nov-17Dec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18
Spend2032284000000000
Forecast0000607492104120134151161

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 CodeJul-17Aug-17Sep-17Oct-17Nov-17Dec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18
Spend2032284000000000
Forecast0000203452648094111121

Thanks,

William

4 Replies
luismadriz
Valued Contributor

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

luismadriz
Valued Contributor

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

luismadriz
Valued Contributor

Re: Cumulative Sum in Pivot Table

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

william_walsh2
New Contributor II

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

Community Browser