Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
colinodonnel
Creator
Creator

Rolling Average at the start of a chart

6 Month Rolling Averages.

 

Any thoughts on how to get the Rolling Average to start at the actual 6 month position?

It is taking 6 Months to become accurate in this chart (at the yellow blob)

At Month 1, there is only 1 Month of data in the Chart therefore it is an average of 1 month.

At Month2, there are only 2 Months of data in the Chart therefore it is an average of 2 months ,

And so on.

Its not a shortage of data in the datamodel. It goes back years. Its just when the Chart is filtered to show a certain time period the 6 Month average rolling position at the start of the chart is incorrect (in fact it is always incorrect but becomes more obvious when looking at smaller time intervals)

Thanks

 

 

 

6 Month Rolling Average.jpg

 

 

 

 

 

 

Labels (1)
2 Replies
Dalton_Ruer
Employee
Employee

Only thing I can think of is using AGGR and SET ANALYSIS to override the date filters you have to calculate the rolling average for the entire time period as though nothing was filtered. 

https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr....

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

 

colinodonnel
Creator
Creator
Author

Hi Dalton,

How do i modify this please?

A basic 6 Mth Rolling Average is:

RangeSum(Above(Sum(TimeToClose), 0, 6)) / RangeMin(6, RowNo())

Modifying to this doesn't work:

RangeSum(Above(aggr(Sum({<ClosedMonth>}TimeToClose),ClosedMonth), 0, 6)) / RangeMin(6, RowNo())

Thanks,Colin