Help on weighted average

[n1ef5ng1]

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              

[kaushiknsolanki · Accepted solution]

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   

[n1ef5ng1]

Thanks!

[#4]

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?