Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
naveenchellaara
Contributor III
Contributor III

Multiple IF statements in expression

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)

1 Solution

Accepted Solutions
phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

15 Replies
phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

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
Contributor III
Contributor III
Author

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
Partner - Specialist III
Partner - Specialist III

See attached...

naveenchellaara
Contributor III
Contributor III
Author

Thanks a lot

This is what i want.

Anonymous
Not applicable

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

jjbom1990
Creator
Creator

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)

  )))))

Not applicable

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
Contributor III
Contributor III

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
Not applicable

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