Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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!