Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Reply
Not applicable
Author

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.