Recurring aggregation

[#1]

Hi,

Has anyone tried to do an aggregation like this:

Fields: Sales, YearMonth

Create a straight table with:

dimension: YearMonth

expression: The total average of last 12 months (for YearMonth on same row)

As output I expect to get, for example, the row corresponding to dimension March-2009 the expression would be the sum of Sales from April-2008 through March-2009, divided by 12

Is it possible at all?

Thanks.

[#2]

Maybe a rangeavg(Value, peek(Value), peek(Value,-2)...)? (table must be sorted by YearMonth when calculating)

[#3]

Thanks for the response. Isn't peek a script only function? I'm trying to do this with formulas and set analysis. With a relational DBMS is trivial but within qlikview dimensional model is quite hard, at least for me and some of my colleagues. But it would be rather useful to get it working outside the data sources.

[msteedle]

There's an expression called RangeAvg() you will want to check out. For your example, with a straight table with a dimension of YearMonth and a measure of Sales, this would be the expression for rolling 12-month averages: RangeAvg(Above(Sum(Sales),0,12))

[#5]

That's it! Thanks much.