topic Re: Help on weighted average in QlikView

Help on weighted average

n1ef5ng1 — Tue, 23 Apr 2013 09:13:52 GMT

Below are my table list

Staff Name Base Commis Formula for Wted Ave Weighted Average

adam 22.5 1,357 (1,357 / 4,974) x 22.5 6.37

Kota 22.5 1,081 (1,081 / 4,974) x 22.5 4.89

Sour 21.7 929 (929 / 4,974) x 21.7 4.05

Bitter 19.0 723 (723 / 4,974) x 19.0 2.76

Sweet 22.6 485 (485 / 4,974) x 22.6 2.20

Kicker 25.2 399 (399 / 4,974) x 25.2 2.02

Total Boxes 4,974 Total Weighted Average 22.29

My desire output that I want in qlikview pivot table is only

Total Weighted Average 22.29

How can i derived that with so complex formula

Thanks in advance

Re: Help on weighted average

kaushiknsolanki — Tue, 23 Apr 2013 09:48:19 GMT

Hi,

Have a look at the attached file.

Use below expression.

sum

(aggr((sum(Commis) / sum(total Commis)) * sum(Base),Name))

And Dimension as Name

Regards,

Kaushik Solanki

Re: Help on weighted average

n1ef5ng1 — Tue, 23 Apr 2013 10:35:00 GMT

Thanks!

Re: Help on weighted average

Thu, 27 Jun 2013 08:12:03 GMT

Hi, I have a different problem.

Here's a sample data set:

Total [Absolute Error] is sum of absolute [Error]. (Thus the difference from total [Error])

[Absolute % Error] is fabs( [Absolute Error] / Actual).

The problem is that the total of [Absolute % Error] works out as a weighted average and not as the calculation of the total of [Absolute Error] / total of Actual. (3260 / 17421).

I've tried using the aggr function over the 3 dimensions at the front, but for some reason I'm getting no results then.

Any advise?