Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.