8 Replies Latest reply: Feb 21, 2018 7:05 AM by kaan erisen RSS

    pivot table cumulative sum

    onur yilmaz

      Hello everyone,

       

      I am trying to calculate cumulative sum in a pivot table in Qlik Sense.

       

      My row is Year; (2018,2017,2016...)

      My columns are Month(Jan,Feb...) and Scenario_(Actual,Plan)

      My measure is sum of Amount_

       

      Data sample is below:

      sample.PNG

       

       

      What I want is, Amount_ values should be cumulatively summed in pivot table for the months.


      For example: March 2016  sales values should be sum of Jan 2016 and Feb 2016 on pivot table and rest of them also should be the same.

       

      I have tried "=RangeSum(Above(Sum(AMOUNT_), 0, RowNo()))" but it did not work.

       

      For now, I have below pivot table.

       

      Capture.PNG

      Please suggest me any solution if anyone of you gone through the same problem. Any help will be appreciated.

       

      Thanks.

      OY

        • Re: pivot table cumulative sum
          youssef belloum

          Hi,

           

          your expression is correct.

           

          can you attach sample data ? the QVF ?

            • Re: pivot table cumulative sum
              onur yilmaz

              Hi,

               

              result of "=RangeSum(Above(Sum({1}AMOUNT_), 0, RowNo()))" is below, it is not what I want.


              Screenshot_1.png

              sample data:

                

              MONTHMONTH_NUMSCENARIO_YEAR AMOUNT_
              Jan1Plan2018         2.102
              Feb2Plan2018         2.102
              Mar3Plan2018         2.102
              Apr4Plan2018         2.102
              May5Plan2018         2.102
              Jun6Plan2018         2.102
              Jul7Plan2018         2.102
              Aug8Plan2018         2.102
              Sep9Plan2018         2.102
              Oct10Plan2018         2.102
              Nov11Plan2018         2.102
              Dec12Plan2018         2.102
              Jan1Actual2018         2.270
              Feb2Actual2018         2.270
              Mar3Actual2018         2.270
              Apr4Actual2018         2.270
              May5Actual2018         2.270
              Jun6Actual2018         2.270
              Jul7Actual2018         2.270
              Aug8Actual2018         2.270
              Sep9Actual2018         2.270
              Oct10Actual2018         2.270
              Nov11Actual2018         2.270
              Dec12Actual2018         2.270
              Feb2Actual2017         1.775
              Jan1Actual2017         1.800
              Dec12Actual2017         1.812
              Nov11Actual2017         1.886
              Jan1Plan2017         1.975
              Feb2Plan2017         1.975
              Mar3Plan2017         1.975
              Apr4Plan2017         1.975
              May5Plan2017         1.975
              Jun6Plan2017         1.975
              Jul7Plan2017         1.975
              Aug8Plan2017         1.975
              Sep9Plan2017         1.975
              Oct10Plan2017         1.975
              Nov11Plan2017         1.975
              Dec12Plan2017         1.975
              Apr4Actual2017         2.008
              Sep9Actual2017         2.035
              Oct10Actual2017         2.038
              Mar3Actual2017         2.122
              Aug8Actual2017         2.139
              Jul7Actual2017         2.228
              Jun6Actual2017         2.541
              May5Actual2017         2.586
              Jan1Plan2016         1.481
              Feb2Plan2016         1.481
              Mar3Plan2016         1.481
              Apr4Plan2016         1.481
              May5Plan2016         1.481
              Jun6Plan2016         1.481
              Jul7Plan2016         1.481
              Aug8Plan2016         1.481
              Sep9Plan2016         1.481
              Oct10Plan2016         1.481
              Nov11Plan2016         1.481
              Dec12Plan2016         1.481
              May5Actual2016         1.681
              Jan1Actual2016         1.686
              Feb2Actual2016         1.829
              Mar3Actual2016         1.902
              Sep9Actual2016         1.929
              Oct10Actual2016         1.946
              Nov11Actual2016         1.949
              Jun6Actual2016         1.999
              Aug8Actual2016         2.130
              Apr4Actual2016         2.147
              Jul7Actual2016         2.194
              Dec12Actual2016         2.311
              Aug8Actual2015         1.237
              Jul7Actual2015         1.539
              Jun6Actual2015         1.550
              Jan1Plan2015         1.584
              Feb2Plan2015         1.584
              Mar3Plan2015         1.584
              Apr4Plan2015         1.584
              May5Plan2015         1.584
              Jun6Plan2015         1.584
              Jul7Plan2015         1.584
              Aug8Plan2015         1.584
              Sep9Plan2015         1.584
              Oct10Plan2015         1.584
              Nov11Plan2015         1.584
              Dec12Plan2015         1.584
              Feb2Actual2015         1.616
              Sep9Actual2015         1.693
              Jan1Actual2015         1.716
              Nov11Actual2015         1.721
              Dec12Actual2015         1.977
              Apr4Actual2015         2.057
              Mar3Actual2015         2.063
              Oct10Actual2015         2.088
              May5Actual2015

                       2.196

               

              regards;

              OY.

                • Re: pivot table cumulative sum
                  youssef belloum

                  here I applied your expression with your data and it seems good.

                   

                  screen rangesum.png

                   

                  I think your problem is related to the type of sorting of the field Year (asc/desc).

                   

                  If you put the sorting of the year on descending, it will start to calculate the range from the latest year.

                   

                  you need maybe to put the sorting on ascending ?

                  • Re: pivot table cumulative sum
                    onur yilmaz

                    Hi, I think I have fixed it, I have add one more measure

                     

                    now two measures like below:

                    for Actuals:

                    sum({<MONTH={"<=$(=max(MONTH))"}>}aggr(rangesum(above(Sum({<MONTH=,YEAR=,SCENARIO_={'Actual'}>}AMOUNT_),0,RowNo())),YEAR,(MONTH,(num))))

                    for plans:

                    sum({<MONTH={"<=$(=max(MONTH))"}>}aggr(rangesum(above(Sum({<MONTH=,YEAR=,SCENARIO_={'Plan'}>}AMOUNT_),0,RowNo())),YEAR,(MONTH,(num))))

                     

                    result is below:Screenshot_2.png

                • Re: pivot table cumulative sum
                  kaan erisen

                  Hi Onur,

                   

                  You just need to use RangeSum(Before(Sum(AMOUNT_), 0, ColumnNo())) as measure on your pivot table. If you want to get running sum on horizantal, you have to use Before() instead of Above()  and ColumnNo() instead of RowNo(). Above() and RowNo() can be used for vertical running sums.

                   

                  For example: March 2016  sales values should be sum of Jan 2016 and Feb 2016 on pivot table and rest of them also should be the same.

                  I am not pretty sure about your actual need but if you want to show sum of Jan and Feb on Mar Column (except Mar value). you need to

                  edit this expression.

                   

                  RangeSum(Before(Sum(AMOUNT_), 1, ColumnNo()))

                   

                  Untitled.png

                   

                  Untitled.png

                  Hope it helps,

                  Kaan ERİŞEN