Replacing IF() with Pick, Match, Floor and Class in text color expressions for KPIs

[SuwerenPL]

Hello,

I'm having a hard time replacing some huge nested IF conditions that determine the text color in my app with something more lightweight. The expression is quite complex and the data set is huge, so the table loading time is now unacceptably long.

The Expression result can be any number, both positive and negative, however at the moment there are no results that cross the barrier of 100. Typically it oscilates in <-5, 5> range.

The base color expression looks like this:

If(IsNull(EXPRESSION), ARGB(0,255,255,255),

If((EXPRESSION) < (0.95), Red(),

If((EXPRESSION) >= (0.95) and (EXPRESSION) <1.05, DarkGray(),

If((EXPRESSION) >= 1.05, Green(), 0))))

I tried to combine the following methods that I know and use to apply colors in KPIs:

=Pick(Match(Sign(EXPRESSION), -1, 0, 1), Red(), DarkGray(), Green())

=Pick(Match(Class(EXPRESSION, 0.05), Class(0.95, 0.05), Class(0.9, 0.05)) + 1, Red(), Green(), DarkGray()) 

=Pick(Match(Floor(EXPRESSION, 0.05), 0.95, 0.9) + 1, Red(), Green(), DarkGray())

so that the expression could be evaluated only once and not 5 times, and without the IF function whatsoever. Sadly, everything I tried works only partially. Any help would be much appreciated .

[rubenmarin]

Hi, at least I think you can simplify the nested if as:

If(IsNull(EXPRESSION), ARGB(0,255,255,255),

If((EXPRESSION) < (0.95), Red(),

If((EXPRESSION) >= 1.05, Green(), DarkGray())))

I'm not sure if using column name or a master item instead of the expressioncan help with performance.

The performance can also be improved optimizing the expression or the dimensions used.

[barnabyd]

G'day @SuwerenPL,

I usually solve this sort of problem with a combination of tricks:

1. simplify the nested ifs as shown by @rubenmarin 
2. place a limit on the display table so it only displays if some selections are made - I often limit to 100,000 rows
3. move as much of the EXPRESSION into the load script so that it is pre-calculated in the overnight data load - e.g. create boolean flags to use in set analysis rather than complex expressions.

If you paste the EXPRESSION into the chat, then we might be able to find some tricks to apply to it.

I hope this helps. Cheers, Barnaby.

[Lech_Miszkiewicz]

just to be clear = using Pick vs IF may not necessarily be better in terms of performance: John W has explained that here: https://community.qlik.com/t5/QlikView-App-Dev/IF-vs-Pick-Match/td-p/1292507

I know it is not very fresh post but principles are the same

cheers

[marcus_sommer · Accepted solution]

Your approach to replace the nested if-loops with a pick(match()) approach in which the aggregation is calculated only one time is already the best from a performance point of view.

The output-matching could of course be extended with a more sophisticated rounding and/or replacing the float-values with integer (just multiplying the values with 100) and/or fetching the lower/upper outliers with rangemin() and rangemax() and adding 100 (making negative values positive) which may end with something like:

pick(rangemin(rangemax(floor(EXPRESSION))) + 100, $(=concat(MyColor, ',', MyColorIndex)))

and MyColor is just an island-table with the wanted colors per percent. You may need here and there some adjustments but the general logic should be applicable even in more complicated scenarios.

Beside this you should take a look on the aggregation itself if they could be optimized and also to the data-model because it will also impact the performance.

[SuwerenPL]

@marcus_sommer Thanks for the clarification and explanation. That's the general direction I will follow in my implementation 🙂

Regards