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.

[JordyWegman]

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!

[ogautier62]

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

[agigliotti]

you should try to move the logic in script.

[elie_issa]

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.

[elie_issa]

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 ?

[agigliotti]

what's the value of  vNationalizationRatioLast26Weeks variable ?

[elie_issa]

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

)

[ogautier62]

ok,

but d'you think this code is readable and easy to maintain 😉 !

you could too challenge the requirements of your users !

[mikaelsc]

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)