Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
_k0zm0_2
Contributor III
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
Master III
Master III

Attachment Sense Version

View solution in original post

5 Replies
antoniotiman
Master III
Master III

Hi Victor,

You need AsOf Table.

See Attachment.

Regards,

Antonio

antoniotiman
Master III
Master III

Attachment Sense Version

antoniotiman
Master III
Master III

Without AsOf Table

try this in Expression

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

_k0zm0_2
Contributor III
Contributor III
Author

Thank you very much, antoniotiman.

This is was I looking for.

I was doing something wrong.

Thanks again!

_k0zm0_2
Contributor III
Contributor III
Author

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!