Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Try the approach described in this blog post: Colors in charts
Thanks Piet - I was using the expression in the colours section. Moving it into the expression properties work perfectly!
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]