How to Calculate the Average of the previous 3 values in load script in Qlik Sense?

[BryanFontes]

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)

...

Thanks in advance!

[marcus_sommer]

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

[BryanFontes]

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)

[marcus_sommer · Accepted solution]

Try a slight change to:

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

- Marcus

[BryanFontes]

Holy! It worked perfectly! Thank you!