topic Re: Weighted Average in QlikView

Weighted Average

Mon, 03 Dec 2012 10:00:56 GMT

Hello,

does someone would know how to calculate an average in a total according to others records?

Let say columns A is a sum(quantity), column B is already an average result of the quantity (price)

Qty Price

100 2 €

150 3 €

200 2,5€

------------------

450 avg(price) sould be ((100 * 2) + (150 * 3) + (200 * 2.5) ) / 450

How can I get this?

best regards

Chris

nirav_bhimani — Mon, 03 Dec 2012 10:18:28 GMT

HI,

You want to do Avg of sum then try the following exp.

Avg ( Sum (Quantity* Price) ) / sum(Qunatity)

Regards,

Nirav Bhimani.

Mon, 03 Dec 2012 11:21:25 GMT

Sorry, but you can't write this expression

I tried with an avg(aggr (sum(Q*P, Dim) / sum(Q)

but it doesn't give a good result either

christian

jonathandienst — Mon, 03 Dec 2012 12:11:35 GMT

Christian

You are almost there. I think you need this:

Sum(Aggr(Sum(Q * P, Dim), Dim)) / Sum(TOTAL Q)

Regards

Jonathan

Mon, 03 Dec 2012 13:06:39 GMT

Thank you John,

it matches with the result I got whan I compute in the script.

best regards

christian

hic — Mon, 03 Dec 2012 13:21:49 GMT

Jonathans formula calculates the correct answer, but... it is unnecessarily complicated. The Aggr() function is not needed. Just do

Sum(Price*Qty)/Sum(Qty)

HIC

jonathandienst — Mon, 03 Dec 2012 15:25:46 GMT

Well spotted Henric!