given accumulated data matrix table, ?make previous 5-day average?

Fri, 01 Jan 2010 03:26:55 GMT

Hi,

I have a load statement that works and finds the accumulated values of an attribute over days.

X1:

load attribute, a_date, count(customer_id) as Accum

resident Input_Table

group by attribute, a_date;

But what I need now is a load statement to make a new table that calculates, for every a_date and attribute, the average of the Accums for the previous 5 days. ?Can you help me please?

Now, I can do this in a pivot-table using the RangeAvg function just fine, but you can't access the results of that nice pivot table by anything in QV at all, so I need a resident table that I can refer to later on.

Thx, Bob

given accumulated data matrix table, ?make previous 5-day average?

prieper — Sat, 02 Jan 2010 03:22:53 GMT

The PEEK-function may be of some use for this task. Script should look like the below (not tested)

LOAD
attribute,
a_date,
Accum,
(Accum + IF(attribute = PEEK(attribute, -1), PEEK(Accum, -1) + IF(attribute = PEEK(attribute, -2), PEEK(Accum, -2) ....) / (1 + IF(attribute = PEEK(attribute, -1), 1) + IF(attribute = PEEK(attribute, -2), 1) ....) AS Accum_sliding
RESIDENT
X1
ORDER BY
attribute,
a_date;

There might be some more elegant solutions, but think that this plain approach should do.

Peter

topic given accumulated data matrix table, ?make previous 5-day average? in QlikView

given accumulated data matrix table, ?make previous 5-day average?

given accumulated data matrix table, ?make previous 5-day average?