Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
moshé
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 (4)
2 Solutions

Accepted Solutions
sunny_talwar

May be try this

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

 

View solution in original post

sunny_talwar

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
sunny_talwar

May be try this

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

 

sunny_talwar

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é
Contributor II
Contributor II
Author

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é
Contributor II
Contributor II
Author

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