Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
naveenchellaara
New 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)

Tags (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: Multiple IF statements in expression

15 Replies
Partner
Partner

Re: Multiple IF statements in expression

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

Re: Multiple IF statements in expression

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
Partner

Re: Multiple IF statements in expression

naveenchellaara
New Contributor III

Re: Multiple IF statements in expression

Thanks a lot

This is what i want.

sreerangasane
Contributor

Re: Multiple IF statements in expression

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

jjbom1990
Contributor

Re: Multiple IF statements in expression

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

Re: Multiple IF statements in expression

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

demlet123
New Contributor III

Re: Multiple IF statements in expression

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.

sara_8154
Contributor

Re: Multiple IF statements in expression

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