Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
l_smythe80
Partner - Contributor II
Partner - Contributor II

Colour cells based on first sorted value logic

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.

Screenshot 2019-11-29 at 14.19.31.png

=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 ***

Screenshot 2019-11-29 at 14.21.09.png

 

any help would be amazing

 

11 Replies
sunny_talwar

Can you attach a sample app please

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

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

 

thank you

 

sunny_talwar

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)
l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

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.

Screenshot 2019-12-05 at 14.44.35.png

 

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

 

sunny_talwar

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?

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

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

 

sunny_talwar

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

Best,
Sunny

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

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 

Screenshot 2019-12-09 at 15.15.08.png

 

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

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

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

Screenshot 2019-12-09 at 15.33.34.png