Using the If Statement in the colour expression

[#1]

Hi guys,

I'm trying to change the colour of the data points in a scatter graph where those between 80-95% and 105-120% are blue, those between 95-105% are green, and the rest are red.

So far I have:

if(value > 0.8 and value < 1.2, green(), red())

But I have no idea how to add in a third colour.

Hope you guys can help!

[alexandros17]

if(value > 0.8 and value < 1.2, green(), if(value > 1.2 and value < 1.6, blue(), red()))

[maxgro]

you can add as many colors you want (change number and color in my example), just pay attention to position and number of brackets at the end; QlikView helps you highlighting the brackets (see arrow in image)

[jagan]

Hi,

Try this

'm trying to change the colour of the data points in a scatter graph where those between 80-95% and 105-120% are blue, those between 95-105% are green, and the rest are red.

=If(value > 1.05 and value <= 1.2, Blue(),

If(value > 0.95 and value <= 1.05, Green(),

If(value > 0.80 and value <= 0.95, Blue(),

Red())))

Regards,

Jagan.

[Anonymous]

Hi,
Try this:

if((value>0.80 and value<0.95) OR (value>1.05 and value<1.20),blue(),
if(value>=0.95 and value<=1.05,green(),red())) 

[#6]

Thanks everyone for the helpful comments But I still can't seem to get it to work. All data points instead turn red, so it's sort of working.

My value in question is:

(1-(fabs(Sum([Monthly_Data.Budget])))-(fabs(Sum([Monthly_Data.Actuals])))/(fabs(Sum([Monthly_Data.Budget]))))

So my expression ends up being a jumbled mess:

If (((1-(fabs(Sum([Monthly_Data.Budget])))-(fabs(Sum([Monthly_Data.Actuals])))/(fabs(Sum([Monthly_Data.Budget])))) > 0.80
and  (1-(fabs(Sum([Monthly_Data.Budget])))-(fabs(Sum([Monthly_Data.Actuals])))/(fabs(Sum([Monthly_Data.Budget])))) < 0.95)

OR ((1-(fabs(Sum([Monthly_Data.Budget])))-(fabs(Sum([Monthly_Data.Actuals])))/(fabs(Sum([Monthly_Data.Budget])))) > 1.05
and  (1-(fabs(Sum([Monthly_Data.Budget])))-(fabs(Sum([Monthly_Data.Actuals])))/(fabs(Sum([Monthly_Data.Budget])))) < 1.20), Blue(),

If ((1-(fabs(Sum([Monthly_Data.Budget])))-(fabs(Sum([Monthly_Data.Actuals])))/(fabs(Sum([Monthly_Data.Budget])))) >= 0.95
and (1-(fabs(Sum([Monthly_Data.Budget])))-(fabs(Sum([Monthly_Data.Actuals])))/(fabs(Sum([Monthly_Data.Budget])))) <= 1.05, Green(), Red()))

Probably a big ask, but can you guys spot any errors in that?

[jagan]

Hi,

If you attach the sample file it would be easier to fix the issue, otherwise it would be difficult.

Regards,

Jagan.

[#8]

Hi Jagan,

Not sure if I can disclose some of the data on the file so I'm going to play it safe.

Don't worry about fixing this issue actually, I've decided it's not necessary anymore. I did learn a lot about expressions though while trying to fix it so thanks everyone who helped

[jagan]

Hi James,

One more thing in Background color expression you can refer Expression name instead of actual expression like below

Assume Expression name is Exp1 then your color expression i

If ((([Exp1] > 0.80
and [Exp1] < 0.95)

OR ([Exp1] > 1.05
and  [Exp1] < 1.20), Blue(),

If ([Exp1] >= 0.95
and [Exp1]<= 1.05, Green(), Red()))

Hope this helps you.

Please close this thread.

Regards,

Jagan.