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 |
Can you explain your reqiurement clearly
use below code
sum( if(DPD>=180,AMT))
hope this helps
it is giving Sum(Amt)=1000, but i want sum(Amt)=1960
Hi,
PFA.
I have used a flag field to identify the customer ids which has any one DPD equal to 180.
Check and let me know if you ve got the solution.
Regards
Andrew Hudson
Talluru,
here is your solution in an expression:
sum({<customerid = p({$<dpd = {'180'}>}customerid)>}amt)
You can create a variable to replace the fixed value 180, like this:
sum({<customerid = p({$<dpd = {'$(talluru_value)'}>}customerid)>}amt)
use a slider to select the talluru_value
Hi Talluru,
Another option would be to create a calculated dimension in a chart with this ...
=if(aggr(max(DPD),CustomerID)>=180,aggr(max(DPD),CustomerID))
So you have 3 dimensions (CustomerID, Agreementid and the above) then sum(AMT) as your expression. The calculated dimension sets to Null any value below 180, so you need to tick the box Suppress when Value is Null.
flipside
Here
in Dimension:
dimension 1: =aggr(if(sum(DPD) > 180, CustomerID),CustomerID) //check "Suppress When Value is Null"
dimension 2: Agreementid
dimension3: DPD
in Expression:
sum(AMT)
Hi,
I attached sample QV file.
Hope its helps for u
Regards,
Iyyappan
Please be aware that calculations in a dimension are inefficient compared to calculations in an expression, they take more time and resources.
And,
If the selection is done in the dimension then there are 2 different places to consider for the end result: