How to Exclude range in If Condition

[sspawar88]

Hi Everyone,

I am creating calculated dimension in stacked Bar Chart.

Each bar in Chart gives 100% in which each stack has belongs to some defined ranges for colour.

Calculative dimension are as below:

if(param_name='TEMP' and (param_value)<='-1' and (param_value)>= '-10', 'Red',

if(param_name='TEMP' and (param_value)<= '-10.1' and (param_value)>='-13.9', 'Yellow',

if(param_name='TEMP' and (param_value)<= '-14' and (param_value)>='-17.9', 'Amber',

if(param_name='TEMP' and (param_value)<= '-20','Green')))

Problem is this If condition also consider below range:

param_value<=-18 & param value>=-19.9

it has to be excluded.

How to achieve this?

Thanks in Advance.

[sunny_talwar]

Give this a try, not 100% sure if this will work, but I guess no harm in trying:

If(param_name='TEMP',

If((param_value)<='-1' and (param_value)>= '-10', 'Red',

If((param_value)<= '-10.1' and (param_value)>='-13.9', 'Yellow',

If((param_value)<= '-14' and (param_value)>='-17.9', 'Amber',

If((param_value)<= '-20','Green')))

[alexandros17]

It should work as is, could you share an example?

[sspawar88]

Hi Sun,

Thanks for reply

Thing is in the range of  '-20' i.e. Green , It also consider value of param_value<=-18 & param value>=-19.9

which should be excluded.

[alexandros17]

So Try this:

if(param_name='TEMP' and (param_value)<='-1' and (param_value)>= '-10', 'Red',

if(param_name='TEMP' and (param_value)<= '-10.1' and (param_value)>='-13.9', 'Yellow',

if(param_name='TEMP' and (param_value)<= '-14' and (param_value)>='-17.9', 'Amber',

if(param_name='TEMP' and (param_value)<= '-20','Green', 'Black')))

[sspawar88]

Hi Alessandro,

I have to exclude that Black part which you are showing in else condition.

My bar chart should not include value for that range not even in false condition.

[alexandros17]

So according to me you should work in Dimenision field to exclude these values setting them to null and cheching the "Exclude null values" box

I need further details ....

[sspawar88]

I am using Qliksense 2.0. It does not have this option.

Even I am trying in calculation condition, but not getting exact syntax.

[#9]

Why the condition param_value<=-20 considers values <=-18 and >=-19.9? Any value from -18 to -19.9  are not less than -20.

And try to use single comparison operator like param_value<-1.1 and param_value>10.1 etc. And don't put the numbers in single quotes. Remove them.

Regards,

KKR

[sasiparupudi1]

if(param_name='TEMP' and (param_value)<='-1' and (param_value)>= '-10', 'Red',

if(param_name='TEMP' and (param_value)<= '-10.1' and (param_value)>='-13.9', 'Yellow',

if(param_name='TEMP' and (param_value)<= '-14' and (param_value)>='-17.9', 'Amber',

if(param_name='TEMP' and ((param_value)> -18  and (param_value)< 19.9),'Green')))