Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Avg, aggr and max date

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)

 

image1.png

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:

2.png

*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.

Labels (5)
2 Solutions

Accepted Solutions
Highlighted

May be try this

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

 

View solution in original post

Highlighted

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...

View solution in original post

4 Replies
Highlighted

May be try this

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

 

View solution in original post

Highlighted

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...

View solution in original post

Highlighted
Contributor II
Contributor II

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

 

thx

Highlighted
Contributor II
Contributor II

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