Set Analysis Aggr Sum by Max Date

[Anonymous]

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.

Thank you in advance.

[sunny_talwar]

May be this?

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

[rahulpawarb]

Hello Wayne,

Refer attached sample application.

Regards!

Rahul

[Anonymous]

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?