topic Re: Colour cells based on first sorted value logic in QlikView

Colour cells based on first sorted value logic

LeeSmithBtn — Sat, 16 Nov 2024 19:33:32 GMT

Hi All,

me again so attached is an app that @sunny_talwar has been helping greatly with and i am at the next crossroad...

so within the app there is a column that finds the most used salesagent by customer, what i now would like to do is colour these based on the salesagent. so each sales agent is set a colour and for arguments sake it would be ARGB(255,0,48,135) for agent 1, then slightly transparent for sales agent 2 argb(200,0,48,135) and so on for 3,4,5 etc.

as this column finds the most used sales agent using an aggr and first sorted value im unsure how to colour this based on the sales agent and any help would be greatly received. app is all inline so can be refreshed.

using pick match i can create the below table where this is set, but it will be dynamic and ideally needs changing dependant on the first sort value.

=pick(match(SalesAgent,'SalesAgent1','SalesAgent2','SalesAgent3','SalesAgent4','SalesAgent5')
,argb(255,0,48,135),argb(200,0,48,135),argb(155,0,48,135),argb(100,0,48,135),argb(55,0,48,135))

so i want these colours to replicate down this column: *** updated, just to say there is a mapped in ARGB colour field called SalesAgentColour ***

any help would be amazing

Re: Colour cells based on first sorted value logic

sunny_talwar — Fri, 29 Nov 2019 15:56:51 GMT

Can you attach a sample app please

Re: Colour cells based on first sorted value logic

LeeSmithBtn — Fri, 29 Nov 2019 16:26:53 GMT

well thats my bad haha... attached is now the app we last completed.

thank you

Re: Colour cells based on first sorted value logic

sunny_talwar — Mon, 02 Dec 2019 01:26:31 GMT

May be this

=Aggr(Pick(Match(Sum([Total Sales]), Max(TOTAL Aggr(Sum([Total Sales]), SalesAgent)), Max(TOTAL Aggr(Sum([Total Sales]), SalesAgent), 2), Max(TOTAL Aggr(Sum([Total Sales]), SalesAgent), 3), Max(TOTAL Aggr(Sum([Total Sales]), SalesAgent), 4), Max(TOTAL Aggr(Sum([Total Sales]), SalesAgent), 5)), ARGB(255,0,48,135), ARGB(200,0,48,135), ARGB(155,0,48,135), ARGB(100,0,48,135), ARGB(55,0,48,135)) , SalesAgent)

Re: Colour cells based on first sorted value logic

LeeSmithBtn — Thu, 05 Dec 2019 14:50:13 GMT

Hi Sunny,

thanks for the reply, its fine in the little table doing that, but ideally i want the SalesAgent colour to be static, so its always Dark blue for agent 1 etc.

the main table where the column has Most Used Agent is the coloumn im ideally looking to colour this way. so it finds the most used sales agent so for customer 12 this is sales agent 3, ideally i want that to be a shade of blue in that column everytime it sees sales agent 3 for the same blue, then a different blue in that column for agent 1, 2,3,4,5 etc and could go up to many more sales agents

i hope that makes sense

so in effect it would be like this (colours are random colours) but all agents have same colour in this table. and then throughout the app the same sales agents have the same colour as this table.

so in effect a colour pallet for every sales agent then when it finds the sales agent it assigns this colour to the cell

i hope that makes sense

Re: Colour cells based on first sorted value logic

sunny_talwar — Thu, 05 Dec 2019 17:50:36 GMT

I am not sure I follow completely. I understand that you want the color to be dashboard wide... but not entirely sure what is wrong with how you select the color?

Re: Colour cells based on first sorted value logic

LeeSmithBtn — Fri, 06 Dec 2019 12:03:27 GMT

Hi Sunny, so the way we find the main sales agent is the complex aggr function as per the app... i want to create a colour app wide for the sales agent (mwhich will be more than 5 and possibly up to 100,) so using a pick function to write out 100 agents may be quite consuming. so i want to in theory pick the colour based on the agent in a similar way we find the agent name,

im hoping that makes more sense.

if i supply the logic you have sent into the main table it wont colour the cells correctly in the table where the sales agent name is

so in the table the agent colour is determined by a mapping load, and then dependant on the agent most used column it will colour that cell the colour assigned to that agent

Re: Colour cells based on first sorted value logic

sunny_talwar — Fri, 06 Dec 2019 19:18:25 GMT

Can you point me to the exact location where you are having trouble in the dashboard?

Best,
Sunny

Re: Colour cells based on first sorted value logic

LeeSmithBtn — Mon, 09 Dec 2019 15:31:22 GMT

Thanks Sunny,

so the point in the app im looking at is the big main table in the middle of the app. the column is Most Used Agent and for customer 12 this is sales agent 5 equating to 21.91% of the time.

so the column is finding the correct sales agent. so what i would like to do with the background colour is then show the salesagentcolour field as the background colour for each cell.

as per image, the calc finds the most used sales agent and ideally i want these cells to be coloured based on sales agent... there if a 4 fields in the app called sales agent Col A, Sales Agent Col R, Sales Agent Col G and Sales Agent Col B. also

updated app with colours and also text objects pointing out what im trying to do 🙂 this is complicated and i appreciate everything, all colours and fields et are inline loads

Re: Colour cells based on first sorted value logic

LeeSmithBtn — Mon, 09 Dec 2019 15:34:13 GMT

however i think by asking the question and publishing i may have answered it myself 🙂 for once haha.

Re: Colour cells based on first sorted value logic

sunny_talwar — Mon, 09 Dec 2019 16:53:04 GMT

Amazing... I didn't see your response and used this... not sure if this is right or not... but do have a look

Re: Colour cells based on first sorted value logic

Brett_Bleess — Tue, 10 Dec 2019 20:43:59 GMT

Here is a Design Blog post on setting colors the way you mentioned, think you may already have things solved, but just in case. Be sure if you have a solution to mark any posts from Sunny that helped you get there using the Accept as Solution button on them...

https://community.qlik.com/t5/Qlik-Design-Blog/Colors-in-charts/ba-p/1475280

Regards,
Brett