Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
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

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

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.