Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Limonadi
Contributor
Contributor

Rolling 12-Months Sum and Average starting and ending period

Hi,

I'm calculating rolling 12-months sum and average and have problems with starting and ending periods. I was able to get right numbers for the most of the part but what I need is to get rid of the first 11 months and ending the calculation to current period. 

I'm using these two expressions:

12-months rolling average:

  • if(rowno()>11,RangeAvg(Above(Sum({<Month, Year>}[#DevItems]), 0, 12)) * Avg(1),null())

12-months rolling sum:

  • if(rowno()>11,RangeSum(Above(TOTAL Sum({<Year, Month>}[#DevItems]) , 0, 12))* Avg(1),null())

And get the result below. So when I use that Year/month dimension in a chart I would like to see only time period 2017.12-2019.10 so that it calculates  2017.01-2017.12 for the period 2017.12  and the last period should be current month. I have a variable vL_current_period I hope I can use here but I just don't know how.  Do you have any suggestions? Thank you!

12 months pic.JPG

 

1 Reply
miskinmaz
Creator III
Creator III