5 Replies Latest reply: Aug 31, 2017 5:35 AM by Victor Torres Jurado RSS

    Cumulative value of rolling 12 months

    Victor Torres Jurado

      Hello Experts!!,

      I need to show the value of a month, and the value of the rolling 12 of this month in Qlik Sense.

       

      Sample data:

      Data:

      LOAD

      Date(D) as D

      ,DateNum

      ,Value

      ;

      LOAD * INLINE [

      D | DateNum | Value

      01/09/2014  |   41883   |   81.721,40

      01/10/2014  |   41913   |   87.481,20

      01/11/2014  |   41944   |   75.499,00

      01/12/2014  |   41974   |   80.794,50

      01/01/2015  |   42005   |   83.687,40

      01/02/2015  |   42036   |   84.260,40

      01/03/2015  |   42064   |   92.112,50

      01/04/2015  |   42095   |   89.557,40

      01/05/2015  |   42125   |   88.138,70

      01/06/2015  |   42156   |   96.974,30

      01/07/2015  |   42186   |   102.522,20

      01/08/2015  |   42217   |   96.040,30

      01/09/2015  |   42248   |   106.034,20

      01/10/2015  |   42278   |   107.147,90

      01/11/2015  |   42309   |   107.078,30

      01/12/2015  |   42339   |   107.308,90

      01/01/2016  |   42370   |   100.684,60

      01/02/2016  |   42401   |   110.935,50

      01/03/2016  |   42430   |   113.167,40

      01/04/2016  |   42461   |   111.773,50

      01/05/2016  |   42491   |   108.695,60

      01/06/2016  |   42522   |   116.286,00

      01/07/2016  |   42552   |   104.033,20

      01/08/2016  |   42583   |   112.373,00

      01/09/2016  |   42614   |   120.217,80

      01/10/2016  |   42644   |   112.347,90

      01/11/2016  |   42675   |   118.047,40

      01/12/2016  |   42705   |   112.572,70

      01/01/2017  |   42736   |   124.785,10

      01/02/2017  |   42767   |   121.136,10

      01/03/2017  |   42795   |   138.467,30

      01/04/2017  |   42826   |   113.138,00

      01/05/2017  |   42856   |   129.866,40

      01/06/2017  |   42887   |   138.104,90

      01/07/2017  |   42917   |   123.780,40

      01/08/2017  |   42948   |   100.913,90

      ] (delimiter is '|') ;

      Note: Dateformat is 'DD/MM/YYYY'


      For example, in April/2017 (01/04/2017 or 42826) it would show the sum of May/2016 until April/2017.


      I get the correct output with this formula :

      sum({<D = {">$(=AddMonths(makedate(2017,4), -12))<=$(=makedate(2017,4))"}>}Value)

       

      But, in a table, when I replace the makedate with the dimension D, it shows nothing.

       

      The original data has more fields (Customer, Country, Seller....) and I'd like to use it with aggr, selections, filters...

      I've been trying to create an AsOf table, but I'm not sure if it will be a efficient solution.

       

      This would be the desired output:

      DateValue

      Cumulative R12

      01/09/2014

      81.721,40

      01/10/201487.481,20
      01/11/201475.499,00
      01/12/201480.794,50
      01/01/201583.687,40
      01/02/201584.260,40
      01/03/201592.112,50
      01/04/201589.557,40
      01/05/201588.138,70
      01/06/201596.974,30
      01/07/2015102.522,20962.749,00
      01/08/201596.040,301.058.789,30
      01/09/2015106.034,201.083.102,10
      01/10/2015107.147,901.102.768,80
      01/11/2015107.078,301.134.348,10
      01/12/2015107.308,901.160.862,50

      01/01/2016

      100.684,601.177.859,70
      01/02/2016110.935,501.204.534,80
      01/03/2016113.167,401.225.589,70
      01/04/2016111.773,501.247.805,80
      01/05/2016108.695,601.268.362,70
      01/06/2016116.286,001.287.674,40
      01/07/2016104.033,201.289.185,40
      01/08/2016112.373,001.305.518,10
      01/09/2016120.217,801.319.701,70
      01/10/2016112.347,901.324.901,70
      01/11/2016118.047,401.335.870,80
      01/12/2016112.572,701.341.134,60
      01/01/2017124.785,101.365.235,10
      01/02/2017121.136,101.375.435,70
      01/03/2017138.467,301.400.735,60
      01/04/2017113.138,001.402.100,10
      01/05/2017129.866,401.423.270,90
      01/06/2017138.104,901.445.089,80
      01/07/2017123.780,401.464.837,00
      01/08/2017100.913,901.453.377,90

       

      Thanks in advance!