Rolling 12 months average in chart

[Anonymous]

Hi All,

I have the following set expression for rolling 12 months total:

Sum({$<[Period]={'>=$(=Monthstart(Max(Period),-11))'}>} [Sales])

It works fine in a KPI box. It returns the sum of the past 12 months with the largest value of [Period] as the start.

However, when i use the same expression in a chart, only the sales for each month is plotted.

What I am looking for is for the chart to show the rolling 12 months total for each data point.

Please kindly advise what am I missing.

Thanks!

[ecolomer]

What is the value of Period? Is a Date?

[Anonymous]

Yup. It is a Date.

[tresesco]

Not sure, may be you are talking about accumulation. Try 'Full Accumulation' in the expression tab.

[Anonymous]

Is it possible to do it on Qlik Sense?

I can't seems to find this function.

[tresesco]

Instead, you could try with rangesum(), like:

rangesum(above(total Sum({$<[Period]={'>=$(=Monthstart(Max(Period),-11))'}>} [Sales]),0,Rowno(Total)))

[Anonymous]

Hi Tresesco,

Can you roughly explain what the formula is doing?

I tried looking up the Chart function guide but I do not really understand.

The chart output i get from the formula is a cumulative sum of sales from Dec-2014 to Aug-2014 (My max period) but what I am looking for is the rolling 12 month sum.

For example, the data point for Dec-2014 should be the sum of sales from Jan-2014 to Dec 2014.

I have verified using a pivot table that I did loaded sales data since 2012 into Qlik.

[tresesco]

Well, not yet sure though. Perhaps you are looking for 12 months aggregation at each month. If so, try like:

sum( aggr( rangesum( above( sum( {$<Month>}  Sales),0,12) ),Month))

Please refer: Accumulative Sums for better understanding.