App Development

Announcements
cancel
Showing results for
Did you mean:
Contributor II

Average of the previous 3 values in load script

Hey guys,

I have a table with Dates and Values that contains future dates with empty values. I need to fill these null values with an average of the last 3 values, ordered by date. Does anyone know how to do it?

E.g.:
Date, Value
19/01/2022, 3
20/01/2022, 6
21/01/2022, 9
22/01/2022, -
23/01/2022, -
24/01/2022, -

...

Should be:
Date, Value
19/01/2022, 3
20/01/2022, 6
21/01/2022, 9
22/01/2022, 6 (3+6+9)
23/01/2022, 7 (6+9+6)
24/01/2022, 7.333 (7+6+9)

...

Labels (1)
• General Question

1 Solution

Accepted Solutions
MVP & Luminary

Try a slight change to:

Date, Value,
alt(Value, rangesum(peek('ValueNew', -1), peek('ValueNew', -2),peek('ValueNew', -3))/3) as ValueNew

resident Source order by Date;

- Marcus

4 Replies
MVP & Luminary

Maybe with something like this:

t: load Date, Value, alt(Value, (peek('Value', -1)+peek('Value', -2)+peek('Value', -3))/3) as ValueNew
resident Source order by Date;

- Marcus

Contributor II
Author

Thanks for the reply @marcus_sommer ! But sadly it didn't work. It seems like Qlik doesn't do the substitutions line by line, it does in the whole table simultaneously. This means the peek(,-1) values aren't substituted for values, they're still counting as null values, which gives this output:

Date, Value
19/01/2022, 3
20/01/2022, 6
21/01/2022, 9
22/01/2022, 6 (9+6+3)
23/01/2022, null (Null+9+6)
24/01/2022, null (Null + Null + 9)

MVP & Luminary

Try a slight change to: