FirstSortedValue

[Anonymous]

Hello All,

My requirement is to get only the records highlighted in yellow with the bold font as in the image attached.

Essentially if a record is amended the its ID get updated i.e ; followed by the number of times.So in such instances I only want the most recent update. thus the expression ;

=FIRSTSORTEDVALUE(AGGR(SUM(DISTINCT AMOUNT),CLIENT,ID)
,
-AGGR(SUM([AMENDMENT NUMBER]),CLIENT,ID)) where Amendment number is derived as MID(ID,14,3)) AS [AMENDMENT NUMBER].

But unfortunately this fails to work for client's with no amendment like Moses, Victor just a few.

Please help, Thanks

[Anonymous]

As request please find attached.

[sunny_talwar]

This seems like the data you have in the chart, I would rather look at the aggregated data or the your qvw application

[Anonymous]

Sunny, PFA.

[sunny_talwar · Accepted solution]

This may be?

=FirstSortedValue(Aggr(SUM(DISTINCT AMOUNT),iCustomer_ID, NEW_ID, [AMENDMENT NUMBER]), -Aggr([AMENDMENT NUMBER], iCustomer_ID, NEW_ID, [AMENDMENT NUMBER]))