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 🙂

[robert_mika]

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

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

  rgb(0,255,0)))

[r_wroblewski]

Thanks Graham,

I created a sample qwv.file and have same problem.

It seems that QV has a problem with the formula from excel.

I will take a deeper look when I have a few minutes.

First idea is to define your Internat RAG Rating inside load script.

I will create later and let you know when found a solution.

Regards

Ronny

[awhitfield]

Hi Graham,

Just loaded the data from your xls with

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

And get:

Andy

[#5]

Hi Andy,

Excellent – that works now! Many thanks for such a fast response ☺

Regards,

Graham

[#6]

Hi Ronny,

Just had a reply from Andy Whitfield that has solved the problem.

If( = 'Red', rgb(255,0,0),

If( = 'Amber', rgb(255,128,0),

rgb(0,255,0)))

And it works…..

Many thanks for your help – it’s much appreciated ☺

Regards,

Graham

© 1993-2014 QlikTech International AB | Copyright & Trademarks<http://www.qlikview.com/us/info/copyright> | Privacy<http://www.qlikview.com/us/info/privacy> | Terms of Use<http://www.qlikview.com/us/info/terms-of-use> | Software EULA<http://www.qlikview.com/us/info/software-eula>

[awhitfield]

No Problem, please remember to mark answers as CORRECT and others as HELPFUL, where applicable.

Cheers Andy

[#8]

Will do Andy – is there a specific way to do this as I can’t find an option on the stream to mark it as Correct or Helpful?

Regards,

Graham

[awhitfield]

No problem, you click Actions at the bottom of the replies

[#10]

Tried that, but getting message – ‘No actions are available’:

I’ll try later in case there’s a delay in the system or something…..

Regards,

[robert_mika]

You need to use the main topic thread

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

not the

https://community.qlik.com/inbox?objectType=-1805099612&objectID=1002