topic Re: Rolling Avg in the script in App Development

Rolling Avg in the script

ericheleine — Tue, 22 Mar 2022 15:33:56 GMT

Good Morning

I would like to create a rolling average of 4 business day for a specific identifier and side in my script

I try to use peek and SumAvg but it didn't work. Do you have some idea

Key (Identifier + Side)	Date	Identifier	SIDE	Amount
1	2022-03-15	AT0000383864	B	45000000
1	2022-03-16	AT0000383864	B	45000000
1	2022-03-17	AT0000383864	B	45000000
1	2022-03-18	AT0000383864	B	45000000
1	2022-03-21	AT0000383864	B	59000000
2	2022-03-15	AT0000383864	S	75000000
2	2022-03-16	AT0000383864	S	75000000
2	2022-03-17	AT0000383864	S	75000000
2	2022-03-18	AT0000383864	S	75000000
3	2022-03-21	AT0000383865	S	35000000

Re: Rolling Avg in the script

maxgro — Tue, 22 Mar 2022 16:17:01 GMT

Maybe

TMP:
LOAD
[Key (Identifier + Side)] AS KEY,
Date,
Identifier,
SIDE,
Amount
FROM
[https://community.qlik.com/t5/App-Development/Rolling-Avg-in-the-script/td-p/1908273]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

T:
LOAD
*,
RANGEAVG(A0, A1, A2, A3) as AVG
;
LOAD
*,
Amount AS A0,
IF(Peek('KEY', -1)=KEY, Peek('Amount', -1)) AS A1,
IF(Peek('KEY', -2)=KEY, Peek('Amount', -2)) AS A2,
IF(Peek('KEY', -3)=KEY, Peek('Amount', -3)) AS A3
Resident TMP
ORDER BY KEY, Date;

DROP TABLE TMP;

Re: Rolling Avg in the script

ericheleine — Tue, 22 Mar 2022 16:44:05 GMT

thank you work perfectly