topic rolling average in QlikView

rolling average

joeallen — Wed, 25 Nov 2020 16:16:04 GMT

Hello. I need help with rolling 12M average and having rangeavg() function work on filters for specific periods. Please see attachment .qyw for problem description and desired output. Any help would be appreciated!

Re: rolling average

sunny_talwar — Fri, 04 May 2018 15:40:27 GMT

Try this expression

RangeAvg(Above(Sum({<YearMonth>} Sales), 0, 12)) * Avg(1)

Re: rolling average

joeallen — Fri, 04 May 2018 16:20:54 GMT

Hi stalwar1‌

Thanks, that works great for table view with dimension but I also need to get it to properly show the desired value as a stand alone expression result without dimensions (like in a text box) as I need to reference the value in another calculation. Now it shows 9360 if I put the expression there. Is this possible to solve? See picture and attachment qvw..

Re: rolling average

sunny_talwar — Fri, 04 May 2018 16:32:29 GMT

Try this for a text box object

=Avg({<YearMonth = {"$(='>=' & (Max(YearMonth) - 100) & '<=' & Max(YearMonth))"}>} Aggr(Sum({<YearMonth = {"$(='>' & (Max(YearMonth) - 100) & '<=' & Max(YearMonth))"}>} Sales), YearMonth))

Re: rolling average

joeallen — Fri, 04 May 2018 16:45:22 GMT

Thank you Sunny, that works perfectly.