Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
with the below data set I need to get the sum of stock, for the latest datetime per ID
ID | Datetime | Transaction | Stock |
---|---|---|---|
1 | 2012/01/01 | 150 | 500 |
1 | 2012/01/16 | 900 | 600 |
1 | 2012/06/30 | -400 | 300 |
2 | 2012/01/01 | 700 | 250 |
2 | 2012/08/01 | 600 | 300 |
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.
Chris
Hi Mate,
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))
Cheers,
DV
Hi Chris,
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?
Please clarify...
Thanks,
DV
Hi Deepak,
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?
Thanks,
Chris
Hi Mate,
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))
Cheers,
DV