topic Weighed average formula for given condition in QlikView

Weighed average formula for given condition

Learner — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

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

Product Name	Particular	UOM	Value
A	Qty	MT	100
B	Qty	MT	150
C	Qty	MT	200
Total	Qty	MT	450
A	Rate	Rs/MT	800.00
B	Rate	Rs/MT	900.00
C	Rate	Rs/MT	600.00
Total	Rate	Rs/MT	744.44

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

Thanks & regards,

Ashish

Re: Weighed average formula for given condition

Learner — Thu, 30 Jul 2020 09:02:17 GMT

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