Colormix in Top 5 Chart

[elinamcastelletta]

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?

[basav]

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

[elinamcastelletta]

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. 

[basav]

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

[elinamcastelletta]

Yes, I've already limited the dimension. But still doesn't work.

Maybe adapting the script?

[sunny_talwar]

What is/are your chart dimension/s?

[elinamcastelletta]

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

[sunny_talwar · Accepted solution]

Can you try this

=Colormix1(Count({<Client = {"=Rank(Count(value)) < 6"}>} value)/Count(TOTAL {<Client = {"=Rank(Count(value)) < 6"}>} value), Red(), Green())