9 Replies Latest reply: May 29, 2018 9:29 AM by Elie Issa

# 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.

• ###### 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!

• ###### 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

• ###### 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.

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

you should try to move the logic in script.

• ###### 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 ?

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

what's the value of  vNationalizationRatioLast26Weeks variable ?

• ###### 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))

)

• ###### 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 !

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

i believe the IF is the fastest, so for the slowness you cannot change the IF, you need to revise the variables being used. a marginal performance gain if any can be obtained if you replace the RGB() and Yellow with constant values.

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

I hope you are knowing what you are doing by using such monstrous expressions which contain only a mass of variables (variables are only useful if they simplify things). It must be a nightmare to develop and maintain it and it's no surprise that it is slow.

Beside this you could speed up your expression with a pick(match()) instead of an if-loop, for example with something like this:

pick(match(floor(\$(vNationalizationAvg26WeeksRatio), 0.01),

0.00, 0.01, 0.02, ...),

Yellow(), Yellow(), Yellow(), ...)

Such a listing of the search- and return values might be easier created in excel and they could in general be also read from a table.

If this isn't fast enough you couldn't avoid optimizations within your variables-stuff and even more likely to rethink your datamodel.

- Marcus

• ###### 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

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

Very nice Solution. It's taking now less than 1 second to return the result

Thanks man.

• ###### 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)

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

This will save some time for both of cases, either performance and select faster

https://b2it.co.za/blog/replace-nested-if-pick-and-match-functions