Aggr() function help - division within an aggr

Wed, 07 Aug 2013 08:11:57 GMT

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

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

Wed, 07 Aug 2013 10:29:12 GMT

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.

topic Re: Aggr() function help - division within an aggr in QlikView

Aggr() function help - division within an aggr

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