4 Replies Latest reply: Jul 8, 2011 4:32 PM by Claude Jakob

# How to create top5 + favorite company + other table

Hi all,

I am looking for a dynamic way to create a table that would like this:

CompanySales
Total Companies600
Favorite Company20

Company 1

100
Company 290
Company 380
Company 470
Company 560
other180

I want to be able to always have the top 5 companies in the table and always show my favorite company (even if it is not within the top5). The top5 companies are not always the same depending on filters chosen, so I would need a way to dynamically select for top5 companies. Using the ValueList function appears to be not the right way as it seems static.

R/

Claude

• ###### Re: How to create top5 + favorite company + other table

Maybe something like this as the calculated dimension:

aggr(if(Company='ABC Corp' or rank(sum(Sales))<=5,Company,'Other'),Company)

• ###### Re: How to create top5 + favorite company + other table

Hi John

the expression works fine - thanks.

My short follow-up question is when using the calculated dimension how to get a absolute/true rank number e.g.:

RankCompanySales
-Total600
1Company 1100
2Company 290
3Company 380
4Company 470
5Company 560
11Favorite Company20
-Other180

My thinking was using Aggr() within Rank(), however the expression I used always yielded Rank #6 for Favorite Company

R/ Claude

• ###### Re: How to create top5 + favorite company + other table

I built an example this time, because I was thinking, "Why wouldn't aggr() and rank() give me the right rank?"  They worked fine for me.  Ah!  I think I see the problem now that I'm reading more closely.  You said "Aggr() within Rank()", where it needs to be the other way around.  See attached.  The expression is this:

aggr(rank(sum(Sales)),Company)

The idea here is to ignore our calculated dimension with its seven values, and instead aggr() by Company so as to include all companies.  I think of aggr() as building a chart internally.  So we've basically built a chart of all companies, not just the ones we want to see.  The expression inside the aggr() is our expression for this internal chart, so now we're ranking sum(Sales) by company but including all companies.  Finally, we map these numbers back to our original and displayed chart, and find that only the top five and our favorite company are being displayed, so only fill in those numbers.

• ###### Re: How to create top5 + favorite company + other table

Perfect - works! Thanks a lot

R/

Claude