Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Using the If Statement in the colour expression

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!

Tags (4)
8 Replies

Re: Using the If Statement in the colour expression

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

MVP
MVP

Re: Using the If Statement in the colour expression

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)

1.jpg

MVP
MVP

Re: Using the If Statement in the colour expression

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.

sbobbyraj
Contributor III

Re: Using the If Statement in the colour expression

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())) 

Not applicable

Re: Using the If Statement in the colour expression

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?

MVP
MVP

Re: Using the If Statement in the colour expression

Hi,

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

Regards,

Jagan.

Not applicable

Re: Using the If Statement in the colour expression

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

MVP
MVP

Re: Using the If Statement in the colour expression

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.