How can I create traffic light colour ratings in a bar chart?

[#1]

Hi,

I have been trying to use the colour by expression feature to set the colours in a bar chart to either Red, Amber or Green depending on the values of the data.

In my source data, I have created a field named 'Internal RAG Rating' which contains values Red, Amber or Green (calculated field based on certain criteria). My bar chart uses the Internal RAG Rating as a measure, and count(Internal RAG rating) as a dimension so I have a simple bar chart with 3 columns showing numbers of Red, Amber and Green data items.

I want to set the colours of the individual bars to match the data type, and I have tried using colour by expression but I am failing miserably to define the expression correctly, even for just 2 colours!

I have tried If([Internal RAG rating] = Red, rgb(255,0,0), (If([Internal RAG rating] = Amber,rgb(255,128,0),rgb(0,255,0)),rgb(0,255,0))

and variations on that, but I keep getting the 'Error in Expression' message, even when I try a simple 2 colour option like If([Internal RAG Rating] = Red,rgb(255,0,0),rgb(0,255,0)) it is ignoring the red data and just returning the default colour.

Can someone more technically gifted than me please advise what I am doing wrong and how to code this expression correctly?

Thanks 🙂

[r_wroblewski]

Hi Graham,

try

=If([Internal RAG rating] = 'Red', rgb(255,0,0),

  If([Internal RAG rating] = 'Amber', rgb(255,128,0),

  rgb(0,255,0)))

Regards,

Ronny

PS: You need to use ' ...' when you use a text value.

[awhitfield]

Hi Graham,

you need to add single quotes as below:

If(Internal_RAG_rating = 'Red', RGB(255,0,00),
if(Internal_RAG_rating='Amber',RGB(255,153,0)
,
RGB(0,204,0)))

See attached

HTH Andy

[#4]

Thanks Andrew,

Tried that but just getting the default colour of Green. Could this be a data problem as the colours are based on an Excel formula?

Regards,

Graham

[r_wroblewski]

Hi Graham,

in your post you use e.g. [Internal RAG rating] .

replace Internal_RAG_rating by [Internal RAG rating] and try again.

Regards

Ronny

[#6]

Thanks Ronny,

Tried this but I’m still only getting the default colour of Green…

Could it be the data?

Regards,

Graham

[r_wroblewski]

Graham,

can you share a sample file ?

Ronny

PS: I mean a sample file with dummy data.

[awhitfield]

Hi Graham,

the RAG values are case sensitive remember, do you wan to upload you qvw

Andy

[#9]

Hi Andy,

It’s confidential data so I will have to create a sanitised extract; will try to do this later today.

Thankss,

Graham

[#10]

Hi Ronny,

Here’s a sanitised version of the data file. The Internal RAG Rating field is in column ‘P’.

Regards,

Graham