Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Getting sum of latest amounts recorded per ID

Hi,

with the below data set I need to get the sum of stock, for the latest datetime per ID

IDDatetimeTransactionStock
12012/01/01150500
12012/01/16900600
12012/06/30-400300
22012/01/01700250
22012/08/01600300

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

1 Solution

Accepted Solutions
IAMDV
Luminary Alumni
Luminary Alumni

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.

http://qlikshare.com/392

Here is what you need....

=Sum(Aggr( FirstSortedValue(Stock,  -Datetime), ID))

Cheers,

DV

www.QlikShare.com

View solution in original post

3 Replies
IAMDV
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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

IAMDV
Luminary Alumni
Luminary Alumni

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.

http://qlikshare.com/392

Here is what you need....

=Sum(Aggr( FirstSortedValue(Stock,  -Datetime), ID))

Cheers,

DV

www.QlikShare.com