2 Replies Latest reply: Nov 9, 2017 10:49 PM by Luis Madriz RSS

    Cumulative Sum in Pivot Table

    William Walsh

      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