Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Limonadi
New 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
Contributor III

Re: Rolling 12-Months Sum and Average starting and ending period