topic Re: How to Exclude range in If Condition in QlikView

How to Exclude range in If Condition

sspawar88 — Mon, 20 Jul 2015 10:37:45 GMT

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.

Re: How to Exclude range in If Condition

sunny_talwar — Mon, 20 Jul 2015 10:43:53 GMT

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

Re: How to Exclude range in If Condition

alexandros17 — Mon, 20 Jul 2015 10:44:44 GMT

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

Re: How to Exclude range in If Condition

sspawar88 — Mon, 20 Jul 2015 10:49:15 GMT

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.

Re: How to Exclude range in If Condition

alexandros17 — Mon, 20 Jul 2015 10:50:50 GMT

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

Re: How to Exclude range in If Condition

sspawar88 — Mon, 20 Jul 2015 10:54:44 GMT

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.

Re: How to Exclude range in If Condition

alexandros17 — Mon, 20 Jul 2015 10:59:12 GMT

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 ....

Re: How to Exclude range in If Condition

sspawar88 — Mon, 20 Jul 2015 11:05:30 GMT

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

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

Re: How to Exclude range in If Condition

Mon, 20 Jul 2015 11:43:43 GMT

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

Re: How to Exclude range in If Condition

sasiparupudi1 — Mon, 20 Jul 2015 11:45:39 GMT

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

Re: How to Exclude range in If Condition

sspawar88 — Mon, 20 Jul 2015 12:21:57 GMT

we are trying to exclude values from -18 to -19.9

Re: How to Exclude range in If Condition

sasiparupudi1 — Mon, 20 Jul 2015 12:32:40 GMT

=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 and (not (param_value> -18 and param_value< 19.9))),'Green'))))

Re: How to Exclude range in If Condition

sspawar88 — Mon, 20 Jul 2015 12:51:16 GMT

No..Its Not working. its still considering range which will exclude from showing.

have to try out some differerent way.

Re: How to Exclude range in If Condition

Tue, 21 Jul 2015 04:41:03 GMT

Please upload a sample QVW with the issue for quick resolution.

Regards,

KKR