topic Weighted average calculation in QlikView

Weighted average calculation

Anonymous — Fri, 03 Jun 2016 21:55:49 GMT

Hello,

I need a little help calculating weighted average in QlikView.

In database table, with customer orders, i have column for quantity and column for price per unit. I need to calculate weighted average price for customer in expression on bar chart:

(Price * Quantity)/ total Quantity = weighted average price for customer orders

I tried few things, but I couldn't calculate it correctly.

Thanks in advance.

Bojan

Re: Weighted average calculation

sunny_talwar — Fri, 03 Jun 2016 22:34:53 GMT

May be this in a straight table

Dimension:

CustomerOrder

Expression:

Sum(Price * Quantity)/Sum(Quantity)

or this in a text box object

Sum(Aggr(Sum(Price * Quantity)/Sum(Quantity), CustomerOrder))

Re: Weighted average calculation

Anonymous — Mon, 06 Jun 2016 20:39:16 GMT

This with expression helped, thank you very much, I wasn't thinking in that direction.

Regards

Re: Weighted average calculation

Anonymous — Mon, 16 Jul 2018 19:19:46 GMT

And how would you apply this solution (e.g. Sum(Price * Quantity)/Sum(Quantity)) in a set analysis?

namely where you defined Price as sum( {<measure={'Price}>} Value) and Quantity as sum( {<measure={'Quantity'}>} Value)

Re: Weighted average calculation

sunny_talwar — Mon, 16 Jul 2018 19:21:57 GMT

I guess something like this

Sum(Aggr(Sum({<measure={'Price}>} Value)*Sum({<measure={'Quantity'}>} Value), Item))/Sum({<measure={'Quantity'}>} Value)

Re: Weighted average calculation

veronicahernand — Sun, 13 Dec 2020 18:16:43 GMT

This is wonderful!!!! thankssssssssss!!!!!!!!!!!!!