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

[#1]

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

[prieper]

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