topic Rolling average, 3 period average in QlikView

Rolling average, 3 period average

yvonne-c — Sat, 16 Nov 2024 03:26:40 GMT

I need to create the figures in the bottom table as KPIs - just struggling with the expression for the 3 batch average - it works in my main table but I can't get it to work alone

Can anybody help with this?

Thanks

DATE IN	Avg(DLWG1)	Avg(aggr(rangeavg(above(Total Avg({<[DATE IN]=>}DLWG1),0,3)),[DATE IN]))
20/12/2018	1.02	1.02646717
10/01/2019	1.06	1.033968929
24/01/2019	1.13	1.070667567
07/02/2019	1.25	1.146228954
21/02/2019	1.27	1.216261005

Latest Batch	3 Batch Avg
1.27	1.22

Re: Rolling average, 3 period average

Anil_Babu_Samineni — Wed, 29 May 2019 13:19:01 GMT

Perhaps this?

1. Max({<[DATE IN] = {"$(=Max([DATE IN]))"}>} Aggr(Avg(DLWG1), [DATE IN]))

2. Avg({<[DATE IN] = {"$(=Max([DATE IN]))"}>} aggr(rangeavg(above(Total Avg({<[DATE IN]=>}DLWG1),0,3)),[DATE IN]))

Re: Rolling average, 3 period average

yvonne-c — Wed, 29 May 2019 13:46:58 GMT

Number 2 works great!

Thankyou

Re: Rolling average, 3 period average

Anil_Babu_Samineni — Wed, 29 May 2019 13:57:14 GMT

Glad you like my second expression, But can you simplify as this for first one?

Avg({<[DATE IN] = {"$(=Max([DATE IN]))"}>} DLWG1)