How to get different background colours using a calculated expression?

[#1]

I am attempting to make the background of a 'text in chart' expression different colours dependent on the value of a calculated expression i.e. Value is 1 - background colour is Blue, Value is 2 - background colour is Green, Value is 3 - background colour is Orange, Value is 4 - background colour is Red.

Using an 'if' function I can get it to work for 2 values/colours but cannot work out how to use an 'if' function for all four (nested if's don't seem to work unless I have the syntax incorrect)

if(firstsortedvalue([GRADE],-[Date])=3,RGB(73,133,222),RGB(0,240,120))

There doesn't appear to be a CASE function so if anyone could offer me advice as to how I could achieve I would be most grateful.

[#2 · Accepted solution]

The given expression is working for you? Can you post the nested ifs expression?

I just whipped up an example with nested ifs and this worked in a sample app:

=if(Sum(TOTAL <Customer> Sales)>75, rgb(255,0,0),
if(Sum(TOTAL <Customer> Sales)>50, rgb(0,255,0)))

[#3]

Instead of providing a RGB value in the True and False parts of the if you can also just create another if. Keep track of the brackets!

if ( A = 1, 1, if (A = 2, 2))

[martin59]

Hello,

You have to imbricate your conditions :

if (Condition1,Color if it's true, if(Condition2, color if it's true, if(Condition3, color if it's true, color if it's neither Condition1 nor Condition2 nor Condition3)))
Martin</body>

[boorgura]

Try this for the Calculated expression:

=if(Value = 1, rgb(0,0,255), if(Value = 2, rgb(0,255,0), if(Value = 3, rgb(255,150,0), if(Value = 4, rgb(255,0,0)))))

Let me know if it works!!!

[#6]

Dear All

Thanks for all the solutions offered, they all work!

I tried this originally but must of mistyped somewhere.

I have to say that I use a number of forums for various things and this forum is the best I have used, thanks to everyone for the help.

Cameron