topic Re: Set Analysis Aggr Sum by Max Date in QlikView

Set Analysis Aggr Sum by Max Date

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

I would like the result to look like:

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 — Mon, 13 Feb 2017 12:08:45 GMT

May be this?

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

rahulpawarb — Mon, 13 Feb 2017 12:09:23 GMT

Hello Wayne,

Refer attached sample application.

Regards!

Rahul

Anonymous — Wed, 01 Mar 2017 11:50:42 GMT

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 — Thu, 02 Mar 2017 10:05:41 GMT

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