Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
This may be?
=FirstSortedValue(Aggr(SUM(DISTINCT AMOUNT),iCustomer_ID, NEW_ID, [AMENDMENT NUMBER]), -Aggr([AMENDMENT NUMBER], iCustomer_ID, NEW_ID, [AMENDMENT NUMBER]))
Try this:
=FirstSortedValue(Aggr(Sum(DISTINCT AMOUNT) ,CLIENT), -Aggr([AMENDMENT NUMBER], CLIENT))
Try
Subfield(ID &';1' , ';' ,2) AS [AMENDMENT NUMBER]
This is very smart Stefan . I have not tested this, but this might work also?
Alt(Mid(ID,14,3)), 1) as [AMENDMENT NUMBER]
Thanks Guys for the quick response, I also learnt something new.
However a client like Adeliade should show 3 records with a total amount of 450,252.00 and not 200,128.00
ADELIADE | TF1634818035 | 200,128.00 | 1,603.93 | 1 | 09/01/2017 |
ADELIADE | TF1634890173 | 50,068.00 | 214.50 | 1 | 09/01/2017 |
ADELIADE | TF1634898830 | 200,056.00 | 1,603.55 | 1 | 09/01/2017 |
May be you need a new ID field which is just like this:
LOAD Left(ID, 12) as New_ID
and then this:
=FirstSortedValue(Aggr(Sum(DISTINCT AMOUNT), CLIENT, New_ID), -Aggr([AMENDMENT NUMBER], CLIENT, New_ID))
Hi Sunny,
Tried that but but did not avail....
Would you be able to share your qvw or a sample?
Your email please?
I have added you as a connection and you should be able to send me a private message (PM). I can share my id on PM.