Displaying top brands on multiple graphs

[eliran]

Hi,

I have 6 Combo charts, and in them I display 6 different brands info using set analysis.

The problem is, i'm choosing them manually, and the top 6 are changing from time to time.

I would like to find a way to take the top 6 brands according to their quant.

For example:

Brand Quant Sum

A 500 10000

B 600 12000

C 700 14000

D 800 16000

E 900 18000

F 200 4000

G 1000 20000

So in this example, in each chart I want to set in the set analysis to take top1, top2, top3, top4, top5, top6.

Meaning i'll eventually see graphs for brands: a,b,c,d,e,g

I hope i made my self clear enough.

Thanks for any help.

[#2]

Hio Eliran,

what about using a calculated dimension which shows in your chart the top 6 Brands? This could be done in a way like this (syntax not checked):

=if( aggr(rank( aggr(max(Quant), Brand), 4, 2), Brand) <= 6, Brand)

HtH

Roland

[eliran]

Hey Roland,

Thanks for the help.

Using the rank was my thoughts, but not as you suggested.

Plesae see the attached example of my intentions.

I have tried something using the rank but it didn't work

Edit:

I can't use it as a dimension because I'm using years, so I have to use it inside the formula.

Eliran.

[#4 · Accepted solution]

Hi Eliran

I checked your expressions in the single charts without dimensions. To make these charts working use the following expr.:

sum( if (aggr(rank(Aggr(Sum(Sum),Brand)), Brand)=1, Quant))

Note: Your expression was't far away from working correctly, only the second outer aggr() was missing. Hope it will survive the adding of year as a dimension 😉

RR

[eliran]

Dear Roland,

Thanks a lot for your help.

I can now understand where was my mistake.

as for a bonus question, can I get the brand name using the same logic?

[#6]

Hi Eliran,

glad to help you. To answer the bonus question take a look at the new version of your app. Don't ask me, why QV needs an only() or a maxstring() function to display the return-value of the if(), it is necessary.

Regards, Roland

[eliran]

Dear Roland,

I can't thank you enough.

Thank you for your kind help! 🙂

Eliran.

[#8]

Ronald what does the 4 and the 2 represent?

[#9]

Hello Joseph,

what do you mean with "4" and "2"

RR