Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
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
Partner - Specialist III

See attached...

15 Replies
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)

)))))

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.

Partner - Specialist III

See attached...

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

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

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

Community Browser