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

Need  help

TALLURU SANKAR

Dear All

 

My source as below.

CustomerIDAgreementidDPDAMT
100C0120100
100A0230200
100B0410300
200X101180500
200Y10230200
200Z1046060
300M10560400
300N106180500
300O10720300

 

 

If  one customer  one Agreementid have DPD>180 i want to show all the Agreementids amt.

Ex:

 

CustomerIDAgreementidDPDAMT
200X101180500
200Y10230200
200Z1046060
300M10560400
300N106180500
300O10720300
  • Need  help
    Sivaraj Seeman

    Can you explain your reqiurement clearly

  • Need  help
    Sunil Kumar Chauhan

    use below code

     

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

     

     

    hope this helps

  • Re: Need  help
    MichielvandeGoor

    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
    flipside

    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
    Alexis Tan

    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
    Iyyappan V

    Hi,

     

         I attached sample QV file.

     

         Hope its helps for u

     

    Regards,

    Iyyappan

  • Re: Need  help
    MichielvandeGoor

    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
      flipside

      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