# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for
Did you mean:
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.

 ID Status Amount Date 3 B 30 2016/01/14 2 B 20 2016/01/12 3 C 30 2016/01/11 5 A 50 2016/01/11 1 B 10 2016/01/10 2 C 20 2016/01/10 4 C 40 2016/01/05 4 A 40 2016/01/04 4 C 40 2016/01/04 2 A 20 2016/01/03 3 A 30 2016/01/03 2 A 20 2016/01/02 1 A 10 2016/01/01

I would like the result to look like:

 Status Amount A 50 B 60 C 40

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.

4 Replies
MVP

May be this?

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

Specialist III

Hello Wayne,

Refer attached sample application.

Regards!

Rahul

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?

MVP

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