topic Re: Help with Weighted Averages in QlikView

Help with Weighted Averages

Fri, 01 Feb 2013 19:26:13 GMT

I've searched but haven't found my solution. I need to create a weighted average. I want to weight it based on LinesNr. The measure I want to weight is Rev. I created a simple expression that seems to work at the subtotal level, (2?), but not the line-item level (3?). It is LW_Avg = Sum(Rev) * Sum(LinesNr). Then I created another expression which is LW_Avg / LinesNr.

What is the proper way to get a weighted average at the right subtotal level?

Thanks.

Re: Help with Weighted Averages

Fri, 01 Feb 2013 20:39:38 GMT

Could be = Sum(Rev * LinesNr) / Sum(LinesNr)

With one or two expressions, it is up to you.

Re: Help with Weighted Averages

Mon, 04 Feb 2013 21:02:36 GMT

Thanks, but I'm trying to do this in a Pivot Table and it seems to be working at Dimensionality=2, but not 1 or 0. I've been playing around with the formula but still haven't found the answer. Any other suggestions?

I think part of the problem is the field/number I want to be a weighted average is already an "average" number (i.e. Avg. Rev).

I've attached a file with sample data, this time trying to calculate average Margin%.

Thanks!