4 Replies Latest reply: Jun 19, 2015 10:19 AM by Piet Hein van der Stigchel

# Setting chart colours from field values

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

• ###### Re: Setting chart colours from field values

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.

• ###### Re: Setting chart colours from field values

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

• ###### Re: Setting chart colours from field values

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

• ###### Re: Setting chart colours from field values

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]