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