Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
elie_issa
Creator II
Creator 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.

14 Replies
hani_saghir
Contributor III
Contributor III

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
MVP & Luminary
MVP & Luminary

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
ali_hijazi
Partner
Partner

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

I can walk on water when it freezes

View solution in original post

elie_issa
Creator II
Creator II
Author

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

Thanks man.