10 Replies Latest reply: Apr 19, 2012 5:45 AM by flipside

# Need  help

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
• ###### Need  help

Can you explain your reqiurement clearly

• ###### Need  help

use below code

sum( if(DPD>=180,AMT))

hope this helps

• ###### Need  help

it is giving Sum(Amt)=1000, but i want sum(Amt)=1960

• ###### Re: Need  help

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

• ###### Re: Need  help

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

• ###### Re: Need  help

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

• ###### Need  help

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)

• ###### Re: Need  help

Hi,

I attached sample QV file.

Hope its helps for u

Regards,

Iyyappan

• ###### Re: Need  help

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:

• the limitation of result set in the dimension
• the calculation in the expression
• ###### Re: Need  help

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:
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)
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