QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Chris

1 Solution

Accepted Solutions
Luminary

Re: Getting sum of latest amounts recorded per ID

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

3 Replies
Luminary

Re: Getting sum of latest amounts recorded per ID

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?

Thanks,

DV

Not applicable

Re: Getting sum of latest amounts recorded per ID

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

Luminary

Re: Getting sum of latest amounts recorded per ID

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