Qlik Community

Qlik Sense App Development

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

_k0zm0_2
New Contributor III

Cumulative value of rolling 12 months

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!

1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: Cumulative value of rolling 12 months

Attachment Sense Version

5 Replies
antoniotiman
Honored Contributor III

Re: Cumulative value of rolling 12 months

Hi Victor,

You need AsOf Table.

See Attachment.

Regards,

Antonio

antoniotiman
Honored Contributor III

Re: Cumulative value of rolling 12 months

Attachment Sense Version

antoniotiman
Honored Contributor III

Re: Cumulative value of rolling 12 months

Without AsOf Table

try this in Expression

If(RowNo() > 10, RangeSum(Top(Sum(Value),RowNo()-10,Rowno())))

_k0zm0_2
New Contributor III

Re: Cumulative value of rolling 12 months

Thank you very much, antoniotiman.

This is was I looking for.

I was doing something wrong.

Thanks again!

_k0zm0_2
New Contributor III

Re: Cumulative value of rolling 12 months

Thanks for your answer, but the values doesn't match with de desired output.

I'll try the AsOf table but I'll take a look at your example.

Thanks again!