Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am looking for a dynamic way to create a table that would like this:
Company | Sales |
---|---|
Total Companies | 600 |
Favorite Company | 20 |
Company 1 | 100 |
Company 2 | 90 |
Company 3 | 80 |
Company 4 | 70 |
Company 5 | 60 |
other | 180 |
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.
Thanks for any lead.
R/
Claude
Maybe something like this as the calculated dimension:
aggr(if(Company='ABC Corp' or rank(sum(Sales))<=5,Company,'Other'),Company)
Maybe something like this as the calculated dimension:
aggr(if(Company='ABC Corp' or rank(sum(Sales))<=5,Company,'Other'),Company)
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.:
Rank | Company | Sales |
---|---|---|
- | Total | 600 |
1 | Company 1 | 100 |
2 | Company 2 | 90 |
3 | Company 3 | 80 |
4 | Company 4 | 70 |
5 | Company 5 | 60 |
11 | Favorite Company | 20 |
- | Other | 180 |
My thinking was using Aggr() within Rank(), however the expression I used always yielded Rank #6 for Favorite Company
Thanks for any lead.
R/ Claude
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.
Perfect - works! Thanks a lot
R/
Claude