Qlik Community

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.

Thanks in advance.

Chris

1 Solution

Accepted Solutions
Luminary
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
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?

Please clarify...

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