Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
fyrworx25
Contributor III
Contributor III

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

fyrworx25
Contributor III
Contributor III
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?