Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set Analysis Aggr Sum by Max Date

Hi,

I am struggling with doing a sum function.

I have a many ID's, various dates representing a change in status and an amount.
I want to sum up the last date of the status change by ID.

  

IDStatusAmountDate
3B302016/01/14
2B202016/01/12
3C302016/01/11
5A502016/01/11
1B102016/01/10
2C202016/01/10
4C402016/01/05
4A402016/01/04
4C402016/01/04
2A202016/01/03
3A302016/01/03
2A202016/01/02
1A102016/01/01

I would like the result to look like:

StatusAmount
A50
B60
C40

This shows the last status change as the table above shows - the last status for the ID.

ID 3, ID 2, ID 1 all ended in B therefore 30 + 20 + 10 = 60.

ID 4 Stayed in C  = 40

ID 5 only stayed in A = 50.

Any help would be much appreciated.

Thank you in advance.

4 Replies
sunny_talwar

May be this?

=Sum(Aggr(If(Status = FirstSortedValue(TOTAL <ID> Status, -Date), Amount), ID, Status))

rahulpawarb
Specialist III
Specialist III

Hello Wayne,

Refer attached sample application.

Regards!

Rahul

Anonymous
Not applicable
Author

Thank you, this looks like it does what I need.

However I want to be able to click a date and see what the current balances are for all the ID's in that status?

For example, if I select 2016/01/04

I should have A = 60 and C =40. Not just the current amounts on that date.

I am struggling with the set analysis to get the individual status's to show in text boxes.

Is this possible?

sunny_talwar

How did you come up with these number A = 60 and C = 40? I am unable to find the logic behind these?