
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Colormix in Top 5 Chart
Hello,
I need to apply Colormix1 in a Top 5 Chart.
=Colormix1(count(value)/count(TOTAL value), red(), green())
The issue is count(TOTAL value) counts all, I onl need to count the top 5.
How can I count only the top 5 values?
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you try this
=Colormix1(Count({<Client = {"=Rank(Count(value)) < 6"}>} value)/Count(TOTAL {<Client = {"=Rank(Count(value)) < 6"}>} value), Red(), Green())

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Not sure if I understood you problem correctly. But, can you try using rank function
=Colormix1(count(value)/if(rank([TOTAL value])<=5, count(TOTAL value)), red(), green())
cheers,
Basav

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Basav,
Thanks fr your reply. I have the same result as before. Although you are using Rank, I think it is still considering all counted values instead of the 1st 5.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Okay, lets try this. Please keep the expression as it is(=Colormix1(count(value)/count(TOTAL value), red(), green())). No changes there, but Can you try limiting the dimension itself. Go to Dimension Limits, please find below:
cheers,
Basav

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, I've already limited the dimension. But still doesn't work.
Maybe adapting the script?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What is/are your chart dimension/s?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
One dimension is Client, other is Score. Value is IDs.
I could solve it by creating a table in the script and then using this formula :
=Colormix1(rank(CountClient)/count(total CountClient),rgb(48,90,234),
white())
Script:
Sort_top5_clients:
Load
count(distinct ID) as CountClient,
"Client",
Score*count(distinct ID) as Ponder
resident Clients
where Score>2 and len(Client)>0
group by Client, Score
;
Top5_clients_tmp1:
Load
Client ,
sum( CountClient) as CountClient,
sum(Ponder) as P
Resident Sort_top5_clients
group by Client;
Top5_clients_tmp:
Load
Client ,
CountClient,
RowNo() as Row
Resident Top5_clients_tmp1
order by P desc;
Top5_clients:
Load
Client ,
CountClient
Resident Top5_clients_tmp
where Row<6;
drop table Top5_clients_tmp1;
drop table Top5_clients_tmp;
drop Table Sort_top5_clients;
I'm sure it can be optimized.
Thanks
Elina

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you try this
=Colormix1(Count({<Client = {"=Rank(Count(value)) < 6"}>} value)/Count(TOTAL {<Client = {"=Rank(Count(value)) < 6"}>} value), Red(), Green())
