Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor

Help on weighted average

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              

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Help on weighted average

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   

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Help on weighted average

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   

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

Highlighted
Contributor

Re: Help on weighted average

Thanks!

Highlighted
Not applicable

Re: Help on weighted average

Hi, I have a different problem.

Here's a sample data set:

Capture.JPG

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?