Multiple IF statements in expression

[naveenchellaara]

Hi

Can any one tell me how to use multiple 'if' statements (not nested,separate 'if' statements)in expressions?

I need have a condition where more than one possible outcomes is possible.

E.g.

HeatMap_Rules_Key	Condition	MinValue	MaxValue	Color
1	Between	0	25	Red
2	Between	25	50	Blue
3	Between	50	100	Green
4	Between	100	125	Orange

This is the lookup table and the Coloumn can have any values. I need to set the Color for the column based on the value range in this table

consider the

column1

-------------

.747

.968

.45

.50

I need to be able to do use both

IF(Condition1 = 'Between',

          IF(column1<ULimit1 and column1>= LLimit1,$(ColourCode1))

)

IF(Condition1 = 'Between',

          IF(column1<ULimit2 and column1>= LLimit2,$(ColourCode2))

)

The values of the variables are

ULimit1 = 25

ULimit2 = 50

LLimit1 = 0

Limit2 = 25

$(ColourCode1) = RGB(255,0,0)

$(ColourCode2) = RGB(0,255,0)

[phaneendra_kunc]

Try this...

IF(Condition1 = 'Between',

           IF(column1<ULimit1 and column1>= LLimit1,$(ColourCode1),

           IF(column1<ULimit2 and column1>= LLimit2,$(ColourCode2),

           IF(column1<ULimit3 and column1>= LLimit3,$(ColourCode2),

           IF(column1<ULimit4 and column1>= LLimit4,$(ColourCode2)

  )))))

[naveenchellaara]

Thanks a lot,

I tried this. but i am am getting only Red Colour. I need the colours to be based on Range.

When

IF(Condition1 = 'Between',

           IF(column1<ULimit1 and column1>= LLimit1,$(ColourCode1),

is satisfied, it does not go to the else part. I am only getting red colour and not the green colour.

I want both to be present.

[phaneendra_kunc · Accepted solution]

See attached...

[naveenchellaara]

Thanks a lot

This is what i want.

[Anonymous]

can u please paste tha code here i am unable to open your qvw file

[jjbom1990]

IF(Condition1 = 'Between',

           IF(column(1)<25 and column(1)>= 0,red(),

           IF(column(1)<50 and column(1)>= 25,Green(),

           IF(column(1)<75 and column(1)>= 50,Blue(),

           IF(column(1)<100 and column(1)>= 75,rgb(255,0,0)

  )))))

[#8]

Hello team,

I'm new in the Qlik experience and I'm struggling with a simple formula.

Whenever I apply my formula I only have the first two ones. Anyone can help?

iF(Fabs((((Sum([Actuals Total $$]))-(Sum([YTD Budget Total $$]))))/(Sum([YTD Budget Total $$])))<=0.1,green(),

iF(Fabs(((Sum([Actuals Total $$])-(Sum([YTD Budget Total $$]))))/(Sum([YTD Budget Total $$])))>0.1 ,yellow(),

iF(Fabs(((Sum([Actuals Total $$])-(Sum([YTD Budget Total $$]))))/(Sum([YTD Budget Total $$])))>0.25,red())))

Thank you

Mohammed

[Demlet]

Hi Mohammed,

The problem is that one of your first two conditions will ALWAYS be met. If a value is >.25 it is also >0.1. The IF statement never gets to the >.25 because the previous IF condition is met.

the second IF statement needs to account for being between 0.1 and 0.25.

[Anonymous]

Hi Mohammed,

You need to modify the second statement as below:

=iF(Fabs((((Sum(Amount1))-(Sum(Amount2))))/(Sum(Amount2)))<=0.1,green(),

iF(Fabs(((Sum(Amount1)-(Sum(Amount2))))/(Sum(Amount2)))>0.1 and  Fabs(((Sum(Amount1)-(Sum(Amount2))))/(Sum(Amount2)))<0.25,yellow(),

iF(Fabs(((Sum(Amount1)-(Sum(Amount2))))/(Sum(Amount2)))>=0.25,red())))