Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
BryanFontes
Contributor III
Contributor III

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

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!

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

4 Replies
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
Contributor III
Contributor III
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)

marcus_sommer

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
Contributor III
Contributor III
Author

Holy! It worked perfectly! Thank you!