topic Re: Weighted Average of Rows? in QlikView

Weighted Average of Rows?

Wed, 25 Nov 2020 16:16:04 GMT

Please see below and attached. I'm trying to show the year over year Change % in sales of the Top 10 customers. The values are correct per customer line, but what's needed on the total line is the Change % in total sales. Neither Expression Total nor Average of Rows gives me what I need. The best I can get is an equally weighted average of the ten Change %'s shown, when in fact I need a weighted average -- or, alternately, the expression applied at the total line level. Do you see a way to get this? Thanks.

Re: Weighted Average of Rows?

sunny_talwar — Wed, 08 Feb 2017 19:47:58 GMT

Try this:

=Sum({$<Year={$(=Only(Year))}, Customer = {"=Rank(Sum({$<Year={$(=Only(Year))}>} Sales)) < 11"}>} Sales)/Sum({$<Year={$(=Only(Year) - 1)}, Customer = {"=Rank(Sum({$<Year={$(=Only(Year))}>} Sales)) < 11"}>} Sales) - 1

Re: Weighted Average of Rows?

Wed, 08 Feb 2017 20:14:00 GMT

Brain,

Copy this expression to change% - ((column(1)-column(2))/column(2))*100

and change total mode in expressions tab for this expression to 'kutosis'

Hope this will help.

Re: Weighted Average of Rows?

Wed, 08 Feb 2017 21:00:44 GMT

Thanks. That sort of works. The total percent is correct to some power of 10, but the expression * 1000 messes up the percentages.

Re: Weighted Average of Rows?

Wed, 08 Feb 2017 21:01:13 GMT

That's really messy, but it seems to work and not impact performance. So thanks!

Re: Weighted Average of Rows?

sunny_talwar — Wed, 08 Feb 2017 22:41:43 GMT

you have dimension limits which are causing issue.... I passed those limits to your expression via set analysis. Calculate this percentage for only those customers who have Rank(Sum({$<Year={$(=Only(Year))}>} Sales)) < 11

If you got what you wanted, I would suggest you to close this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny