Avg, aggr and max date

[moshé]

Morning.
I'm trying to make a calculation, I try to explain it below with some images, I leave a small set of data only as an example and also the attempts I made by their can be helpful.
This is my dataset (example, which is available in .csv format in the attachments area)

As you can see, each line represents the state of stock for a particular batch/warehouse/date. There are only lines for those dates in which that batch/warehouse pair has undergone some change in its stock (so there is not a line for each date).
My ultimate goal is to obtain an average level of stock from all lots in all warehouses. For this, I believe, the procedure I must follow is:

1/ Get a table showing the available stock for each batch/warehouse pair at maximum date, I think the result should be something like this:

*Note as for the case of lot 1400 with warehouse 2 I have taken by default the last line, understanding that this field can come with complete date+time format that allows me to take the last effective record.

2/ Once obtained this table make an avg() with which to calculate the KPI.

I have tried several things, like for example:

aggr(avg({<Date = {"$(=Max(Date))"}>} [Stock]), Batch, Warehouse)

Thanks for the help.

[sunny_talwar · Accepted solution]

May be try this

Avg(Aggr(FirstSortedValue(Stock, -DateTimeField), Batch, Warehouse))

[sunny_talwar · Accepted solution]

This will not work properly with just the Date field as it is repeating for 1400. But with a unique max value row... this should work...

[moshé]

i think i can get a field that includes both date and time, so this (i hope) is a problem i can save

thx

[moshé]

I think it works, I'll try a little and see if I square the data, but in principle it works!

thank you very much sunny