Colour cells based on first sorted value logic

[LeeSmithBtn]

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

[sunny_talwar]

Can you attach a sample app please

[LeeSmithBtn]

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)

[LeeSmithBtn]

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

[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?

[LeeSmithBtn]

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

[LeeSmithBtn]

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

[LeeSmithBtn]

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