rolling average

[joeallen]

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!

[sunny_talwar]

Try this expression

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

[joeallen]

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..

[sunny_talwar · Accepted solution]

Try this for a text box object

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

[joeallen]

Thank you Sunny, that works perfectly.