Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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.