topic Colormix in Top 5 Chart in QlikView

Colormix in Top 5 Chart

elinamcastelletta — Sat, 16 Nov 2024 00:23:26 GMT

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?

Re: Colormix in Top 5 Chart

basav — Thu, 02 Jul 2020 06:07:37 GMT

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

Re: Colormix in Top 5 Chart

elinamcastelletta — Thu, 02 Jul 2020 12:26:33 GMT

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.

Re: Colormix in Top 5 Chart

basav — Thu, 02 Jul 2020 15:25:49 GMT

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

Re: Colormix in Top 5 Chart

elinamcastelletta — Thu, 02 Jul 2020 17:07:47 GMT

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

Maybe adapting the script?

Re: Colormix in Top 5 Chart

sunny_talwar — Thu, 02 Jul 2020 17:10:17 GMT

What is/are your chart dimension/s?

Re: Colormix in Top 5 Chart

elinamcastelletta — Thu, 02 Jul 2020 17:35:05 GMT

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

Re: Colormix in Top 5 Chart

sunny_talwar — Thu, 02 Jul 2020 18:30:05 GMT

Can you try this

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