Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

elie_issa
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 :


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

Thanks.

1 Solution

Accepted Solutions
ali_hijazi
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
jordywegmanclim
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!

ogautier62
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

agigliotti
Honored Contributor II

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

you should try to move the logic in script.

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

elie_issa
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 ?

agigliotti
Honored Contributor II

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

what's the value of  vNationalizationRatioLast26Weeks variable ?

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

)

ogautier62
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 !

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