What to use instead of IF to make my expression faster

[elie_issa]

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 :

avg(aggr($(vNationalizationRatioLast26Weeks),Week,[Business Unit]))

Thanks.

[hani_saghir]

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.

[marcus_sommer]

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

[Anil_Babu_Samineni]

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

[ali_hijazi · Accepted solution]

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

[elie_issa]

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

Thanks man.