Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Learner
Contributor III
Contributor III

Weighed average formula for given condition

Hello,

I wanted to calculate weighted average for the below mentioned data:

 

Product NameParticularUOMValue
AQtyMT100
BQtyMT150
CQtyMT200
TotalQtyMT450
ARateRs/MT800.00
BRateRs/MT900.00
CRateRs/MT600.00
TotalRateRs/MT744.44

 

I wanted a formula to calculate 744.44 which is sumproduct(Qty*rate)/total Qty......

 

Thanks & regards,

 

Ashish

1 Solution

Accepted Solutions
Learner
Contributor III
Contributor III
Author

Hello Everyone,

 

I have got the formula. Just wanted to share.

 

if((Dimensionality()=0 and Particular ='Rate'),num(sum(Aggr(sum({$<Particular = {'Rate'}>} Amount),[Product Name])*Aggr(sum({$<Particular = {'Qty'}>} Amount),[Product Name]))/sum(aggr(sum({$<Particular ={'Qty'}>} Amount),[Product Name])),'##.'),if(Particular = 'Value', num((sum(Amount)),'##.00'),sum(Amount)))

 

with regards,

 

Ashish

View solution in original post

1 Reply
Learner
Contributor III
Contributor III
Author

Hello Everyone,

 

I have got the formula. Just wanted to share.

 

if((Dimensionality()=0 and Particular ='Rate'),num(sum(Aggr(sum({$<Particular = {'Rate'}>} Amount),[Product Name])*Aggr(sum({$<Particular = {'Qty'}>} Amount),[Product Name]))/sum(aggr(sum({$<Particular ={'Qty'}>} Amount),[Product Name])),'##.'),if(Particular = 'Value', num((sum(Amount)),'##.00'),sum(Amount)))

 

with regards,

 

Ashish