Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All
My source as below.
CustomerID | Agreementid | DPD | AMT |
100 | C01 | 20 | 100 |
100 | A02 | 30 | 200 |
100 | B04 | 10 | 300 |
200 | X101 | 180 | 500 |
200 | Y102 | 30 | 200 |
200 | Z104 | 60 | 60 |
300 | M105 | 60 | 400 |
300 | N106 | 180 | 500 |
300 | O107 | 20 | 300 |
If one customer one Agreementid have DPD>180 i want to show all the Agreementids amt.
Ex:
CustomerID | Agreementid | DPD | AMT |
200 | X101 | 180 | 500 |
200 | Y102 | 30 | 200 |
200 | Z104 | 60 | 60 |
300 | M105 | 60 | 400 |
300 | N106 | 180 | 500 |
300 | O107 | 20 | 300 |
Hi Michiel,
Yes, that's a fair point. In fact, if it was me I would probably do this in script anyway by appending the max(DPD) value as a new column and referencing it in a normal expression (apologies if anyone else has already suggested this in their attached examples).
Data:
load * inline [
CustomerID, Agreementid, DPD, AMT
100, C01, 20, 100
100, A02, 30, 200
100, B04, 10, 300
200, X101, 180, 500
200, Y102, 30, 200
200, Z104, 60, 60
300, M105, 60, 400
300, N106, 190, 500
300, O107, 20, 300 ];
left join (Data)
LOAD
CustomerID,
max(DPD) as maxDPD
resident Data
group by CustomerID;
Then use expression similar to this in the chart (the 180 can be swapped to a variable as in your solution) ..
=sum(AMT)*if(maxDPD>=180,1,0)
flipside