topic Average at max dates in QlikView

Average at max dates

yvonne-c — Sat, 16 Nov 2024 03:37:45 GMT

I need to calculate the average of a set of data at the latest recording date and then the last three recordings- can you help with the expressions?

Data looks like... (excel copy attached)

Batch No

Date In

DLWG

and I would like to create

Latest batch	3 batch average
1.11 (avg(DLWG))	1.02

I have tried:

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

Thanks

bharathadde — Thu, 09 May 2019 19:14:37 GMT

Average for Latest Batch

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

3 Batch Average

=Avg({$<[Batch No]={'UC1','UC2','UC3'}>} DLWG1)

=(Avg({$<[Batch No]={'UC1'}>} DLWG1)+Avg({$<[Batch No]={'UC2'}>} DLWG1)+Avg({$<[Batch No]={'UC3'}>} DLWG1))/3

Depending how the way you are looking at the data any of above 3 Batch Average expression can be used.

qliker124 — Fri, 10 May 2019 00:32:00 GMT

if you want both to be on the same chart (straight table) .. below are expressoins

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

total avg: Avg(TOTAL{<[Batch No]>}DLWG1)

yvonne-c — Thu, 23 May 2019 14:56:57 GMT

Thanks for these, although they do work they are not quite what I need!

There may be more than three batches so I can't use TOTAL - I just want to calculate on the three latest batches

I don't really want to put the actual "Batch No" in the set analysis as these will change for different clients

How would I write an expression to just include the three latest dates?

yvonne-c — Fri, 24 May 2019 10:18:21 GMT

So I have managed to get the 3 batch average to work as below

DATE IN	Avg(DLWG1)	Avg(aggr(rangeavg(above(Total Avg({<[DATE IN]=>}DLWG1),0,3)),[DATE IN]))
16/04/2018	1.01	1.00770497
23/07/2018	0.96	0.981749378
03/12/2018	1.11	1.024545232

How would I get the 1.02 figure (the final 3 batch average) in a stand alone box

I have tried the following but this doesn't work:

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

Thanks