# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor II

## What to use instead of IF to make my expression faster

Dear All,

Can you please advise what is the alternative of the below nested if Statement to make the expression faster because it's taking too much time to generate the result on the screen :

Expression :

if (\$(vNationalizationAvg26WeeksRatio) < 9/100 , rgb(255, 0, 0) ,

if(\$(vNationalizationAvg26WeeksRatio) < 21/100 , Yellow() ,

if(\$(vNationalizationAvg26WeeksRatio) < 26/100, rgb(216, 228, 188),

if(\$(vNationalizationAvg26WeeksRatio) < 31/100, rgb(118, 147, 60),

if(\$(vNationalizationAvg26WeeksRatio) < 37/100, rgb(79, 98, 40),

if(\$(vNationalizationAvg26WeeksRatio) > 37/100, rgb(64, 63, 64), 'Black' ))))))

vNationalizationAvg26WeeksRatio :

Thanks.

Tags (2)
1 Solution

Accepted Solutions
Honored Contributor

## Re: What to use instead of IF to make my expression faster

create a new variable V2 for example

if your variable above is V1 then the definition of V2 would be as follows:
=\$(V1)

this will definitely reduce the time

14 Replies
New Contributor III

## Re: What to use instead of IF to make my expression faster

Hi Elie,

If you doing this in the front-end right now, put it in the back-end. Instead of giving a color statement, return numbers like 1/2/3/4 etc. for each statement.
if (\$(vNationalizationAvg26WeeksRatio) < 9/100 , 1 ,0) as 1

if(\$(vNationalizationAvg26WeeksRatio) < 21/100 , 2, 0) as 2

etc.

When you are done with that, make one column with each number in it and make it a master item. Then color every number with the color statements you have defined above.

Hope this works for you!

Valued Contributor II

## Re: What to use instead of IF to make my expression faster

Hi,

may be if you can change a little your colors you could try something like this :

color = rgb( 255*(1-\$(ratio)), 255 * \$(ratio), 255 *\$(ratio))  for example

regards

Honored Contributor II

## Re: What to use instead of IF to make my expression faster

you should try to move the logic in script.

Contributor II

## Re: What to use instead of IF to make my expression faster

Hi Olivier,

Unfortunately , I am obliged to use specific colors so they will match with the colors used in my legend and the other sheets of my dashboard.

Thanks anyway.

Contributor II

## Re: What to use instead of IF to make my expression faster

the avg and aggr with the if condition are taking too much time to show the result.

I don't think it is feasible to do the calculation from the script.

So any idea how to replace the IF statement with other function that give the same result ?

Honored Contributor II

## Re: What to use instead of IF to make my expression faster

what's the value of  vNationalizationRatioLast26Weeks variable ?

Contributor II

## Re: What to use instead of IF to make my expression faster

i don't think you want to know

check this :

(\$(vNationalizationSASubLast26Weeks)+

\$(vNationalizationSASub1TotalCeilingLast26Weeks)*4 + (\$(vNationalizationSASub1Last26Weeks) - \$(vNationalizationSASub1TotalCeilingLast26Weeks)) +

\$(vNationalizationPrisLess2YearsTotalCeilingLast26Weeks)*2 + (\$(vNationalizationPrisLess2YearsLast26Weeks)- (\$(vNationalizationPrisLess2YearsTotalCeilingLast26Weeks))) +

\$(vNationalizationStudentsTotalCeilingLast26Weeks) * 0.5 +

\$(vNationalizationKabaelNazihaLast26Weeks) + \$(vNationalizationWafedKhasLast26Weeks) + \$(vNationalizationSASub2Last26Weeks))

/

(

\$(vNationalizationContractDailyHiredLast26Weeks)

+

\$(vEOMLast26Weeks) - (\$(vNationalizationKabaelNazihaLast26Weeks)+ \$(vNationalizationSASubLast26Weeks) + \$(vNationalizationSASub1Last26Weeks) + \$(vNationalizationSASub2Last26Weeks) + \$(vNationalizationPrisLess2YearsLast26Weeks)

+  \$(vNationalizationWafedKhasLast26Weeks) + \$(vNationalizationStudentsLast26weeks) +  \$(vNationalizationContractDailyHiredLast26Weeks))

+

(\$(vNationalizationSASubLast26Weeks)+

\$(vNationalizationSASub1TotalCeilingLast26Weeks)*4 + (\$(vNationalizationSASub1Last26Weeks) - \$(vNationalizationSASub1TotalCeilingLast26Weeks)) +

\$(vNationalizationPrisLess2YearsTotalCeilingLast26Weeks)*2 + (\$(vNationalizationPrisLess2YearsLast26Weeks)- (\$(vNationalizationPrisLess2YearsTotalCeilingLast26Weeks))) +

\$(vNationalizationStudentsTotalCeilingLast26Weeks) * 0.5 +

\$(vNationalizationKabaelNazihaLast26Weeks) + \$(vNationalizationWafedKhasLast26Weeks) + \$(vNationalizationSASub2Last26Weeks))

)

Valued Contributor II

## Re: What to use instead of IF to make my expression faster

ok,

but d'you think this code is readable and easy to maintain ;-) !

you could too challenge the requirements of your users !

Contributor III

## Re: What to use instead of IF to make my expression faster

maybe something with the pick() and match() function in combination with class() function?

pick(

match(class(vNationalizationAvg26WeeksRatio,Interval,Offset), ClassValue1, ClassValue2,... ) ,

Color1,Color2,Color3)

you may have to chose the offset and interval wisely (or create 37+ classes)