1 Reply Latest reply: Aug 7, 2013 6:29 AM by Borja Rodriguez RSS

    Aggr() function help - division within an aggr

      Hi

       

      I'm trying to find the maximum % of the field Sett_Amount_GBP divided by PremiumDiscountAmount_GBP and I'm struggling with my coding.

       

      I know that this works, getting the maximum Sett_Amount_GBP per LedgerThirdPartyID,TransactionID,Instalment,PremiumSettCcy

       

      max

      (Aggr(sum(if(LedgerAccountID=LedgerThirdPartyID and(TransactionPeriod='0-12Month'),Sett_Amount_GBP,0))
      ,LedgerThirdPartyID,TransactionID,Instalment,PremiumSettCcy))

      However when I bring in the division by PremiumDiscountAmount_GBP , I can see that my results aren't correct (checking against the dataset manually)

       

      max

      (Aggr(sum(if(LedgerAccountID=LedgerThirdPartyID and(TransactionPeriod='0-12Month'),Sett_Amount_GBP,0))
      /
      Sum(PremiumDiscountAmount_GBP)
      ,
      LedgerThirdPartyID,TransactionID,Instalment,PremiumSettCcy))

      Can anyone advise where I'm going wrong?

       

      Thanks

       

      CJ

        • Re: Aggr() function help - division within an aggr
          Borja Rodriguez

          Hi Chris,

           

          Even though it is quite a tricky AGGR expression, try dividing inside the sum:

           

          max

          (Aggr(sum(if(LedgerAccountID=LedgerThirdPartyID and(TransactionPeriod='0-12Month'),Sett_Amount_GBP / PremiumDiscountAmount_GBP,0))
          ,LedgerThirdPartyID,TransactionID,Instalment,PremiumSettCcy))

           

          But if it will work or not will depend on your datamodel (the granularity of the fields you use in the division) and the dimensions in the aggr.