Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
BryanFontes
Contributor II
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)

...

 

Thanks in advance!

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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
MVP & Luminary
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

BryanFontes
Contributor II
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)

marcus_sommer
MVP & Luminary
MVP & Luminary

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

Holy! It worked perfectly! Thank you!