8 Replies Latest reply: Jul 28, 2017 7:51 PM by Jinghua Ji RSS

    How to achieve accumulative sum in charts

    Jinghua Ji

      Hi all, I have come across a problem, which sounds simple but i just could not get it done.

      Basically, i used RANGESUM(ABOVE(SUM([measure]),0,ROWNO()) in a chart to demonstrate accumulative sum by month. as shown below.

      b57c079dc46c4e89bb785a5f05d22b92.png

      when i pick a month or a few month, the accumulative number will get reset, which is normal.

      b478c8be2df44a33b87899892641c463.png

      however, what i really like to see is the numbers stay as they are no matter which month(s) i pick. for example, if I pick feb, it should display 1.77k instead of 1.27k.

      i also want flexibility so i prefer in-chart function rather than summary data set calculated in load process.

      in theory, i could use some sort of set analysis that sum all the value where month<= the month picked, or min(months picked), but i just could not come up with a satisfactory solution.

      has anyone solved similar problems like this before? thanks in advance.

       

      p.s.

      data table as follow

       

      Load

      *,

          MONTHNAME(Date) AS MonthYear

      ;

      Load

      DATE('01/01/2001' + RECNO() - 1) AS Date,

          RECNO() AS Amount

      autogenerate(NUM(DATE#('30/04/2001'))-NUM(DATE#('01/01/2001'))+1);