Setting chart colours from field values

[jag7777777]

Hi All,

I have a field - [Response/result] that contains either 'Green', 'Amber', 'Red' or 'None'.

I want to be able to have my charts render red, amber or green based on this field.

We've created a new field in the extract that calculates the individual RGB numbers:

,IF([Response/result] = 'Green', 173, IF([Response/result] = 'Amber', 255,IF([Response/result] = 'Red', 255, 255))) AS [PQIColour_Red]
,IF([Response/result] = 'Green', 214, IF([Response/result] = 'Amber', 128,IF([Response/result] = 'Red', 0, 255))) AS [PQIColour_Green]
,IF([Response/result] = 'Green', 44, IF([Response/result] = 'Amber', 0,IF([Response/result] = 'Red', 0, 255))) AS [PQIColour_Blue]

We've then attempted to use this in the colours of a chart using the following expression:

=RGB(
PQIColour_Red,PQIColour_Green,PQIColour_Blue
)

But this returns Black - we're assuming it's being evaluated at RGB(0,0,0)

We've create a list box with the same expression and it is showing as we'd expect - i.e. only four values:

RGB(255,255,255)

RGB(255,128,0)

RGB(255,0,0)

RGB(173,214,44)

Any ideas?

Thanks,

John

[stigchel · Accepted solution]

That should work but make sure there are no multiple values of one of the colors possible where you use the rgb expression. You probably can't use this in a chart object\properties color tab as these calculated colors are not evaluated by the dimension for example. Instead use the expression background color option by clicking on the + sign in front of it.

[Gysbert_Wassenaar]

Try the approach described in this blog post: Colors in charts

[jag7777777]

Thanks Piet - I was using the expression in the colours section. Moving it into the expression properties work perfectly!

[stigchel]

You're welcome John, glad to be of help.

Another tip, you can also use the following instead of these nested if statements. (The +1 after the match is for the default value for any non matching value)

pick(match([Response/result],'Green','Amber','Red')+1,255,173,255,255) as [PQIColour_Red]