Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

Weighted average

Hi all, need some help please in calculating the weighted average, for this contrived example I have 2 figures which are in a Pivot table, in  the following format (one fact table), lets say one figure represents the count of sales and the another figure represents the average time of those sales per department.

How can I calculate the weighted average time of sales of each different product for a company (i.e. not by department)???

Capture.PNG

I've tried something like this but I think I need to use the AGGR function but not quite sure how.

=SUM(({$<Type={"Count of sale"}>}[Shoe Sales]) * ({$<Type={"Average (days)"}>}[Shoe Sales]))/SUM({$<Type={"Count of sale"}>}[Shoe Sales])

Many thanks in advance!

1 Solution

Accepted Solutions

Re: Weighted average

May be this:

Sum(Aggr(Only({<Type = {'Count of sale'}>} [Shoe Sales]) * Only({<Type = {'Average (days)'}>}[Shoe Sales]), [File Month], [Supplier Name], Department))/Sum({<Type = {'Count of sale'}>} [Shoe Sales])

View solution in original post

3 Replies

Re: Weighted average

May be this:

Sum(Aggr(Only({<Type = {'Count of sale'}>} [Shoe Sales]) * Only({<Type = {'Average (days)'}>}[Shoe Sales]), [File Month], [Supplier Name], Department))/Sum({<Type = {'Count of sale'}>} [Shoe Sales])

View solution in original post

Not applicable

Re: Weighted average

Many thanks for this, works perfectly, I was actually trying it with Only instead of Sum but was missing the AGGR bit, thanks again!

Re: Weighted average

Awesome