Weighted Average of Rows?

[#1]

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.

!

[sunny_talwar]

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

[#3]

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.

[#4]

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

[#5]

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

[sunny_talwar]

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