topic Recurring aggregation in QlikView

Recurring aggregation

Thu, 07 Jan 2010 19:13:35 GMT

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.

Recurring aggregation

Thu, 07 Jan 2010 19:59:35 GMT

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

Recurring aggregation

Fri, 08 Jan 2010 14:00:59 GMT

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.

Recurring aggregation

msteedle — Fri, 08 Jan 2010 16:57:42 GMT

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

Recurring aggregation

Fri, 08 Jan 2010 17:01:02 GMT

That's it! Thanks much.