Average at max dates

[yvonne-c]

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)

ID

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]

Average for Latest Batch

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

3 Batch Average

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

or

=(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]

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]

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]

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