Discussion Board for collaboration related to Creating Analytics for QlikView.
with the below data set I need to get the sum of stock, for the latest datetime per ID
so the sum of stock for this data would 600, however if I selected January as the datetime month I would want to see 850.
Thanks in advance.
Go to Solution.
Now it makes sense. This is one of my favourite topics in QlikView - FirstSortedValue() function. I have made a video series on this one. Please check when you get some time.
Here is what you need....
=Sum(Aggr( FirstSortedValue(Stock, -Datetime), ID))
Little confused with your example. How are you getting Stock as 600 for latest Datetime? I thought you need 300 for the lastest date i.e. for 2012/08/01. Also how are you getting 850?
So 600 for stock using all the data would be
ID = 1, max datetime =2012/06/30 stock=300
ID = 2, max datetime =2012/08/01 stock=300
Whilst selecting January would be
ID = 1, max datetime =2012/01/16 stock=600
ID = 1, max datetime =2012/01/01 stock=250
The trouble is getting the latest value for each ID before summing them.
Does that make more sense?