Most QlikView developers have experienced first-hand the havoc that a complex conditional expression can wreak on the performance of their otherwise beautiful application. In this article, we discuss a simple way that some of these expressions can be improved to perform as fast as if they were non-conditional!
Overview
There are 2 types of conditional expressions that are used in charts. The first type is an expression that does not vary depending on the chart dimensions. For example, if a chart's dimension is simply Product, the first type of conditional expression might look like this:
The second type of conditional expression is one that varies based on the current chart dimension. For instance:
The "fix" described in this article is only applicable to the first type of expression. This type of expression is often used for chart sorting, but is also occasionally used in a chart expression itself.
The Problem
Through experimentation, we hypothesized that when QlikView evaluates a conditional expression, it does not stop evaluating when it reaches a match. Meaning, in the first example above, even if vExpressionSwitch is equal to 'Sales', QlikView would still evaluate both of the Count expressions before presenting the result of the Sum expression as the final answer. This appears to be the primary reason that conditional expressions are so harmful to application performance. What led to this hypothesis was a case where we had a pick(match()) conditional expression with 10 possible conditions. The 10th and final expression was, by necessity, extremely heavy; however, the first 9 expressions were quite light. Nevertheless, even when the condition dictated that one of the first 9 expressions be evaluated, the calculation time was still the same as was required to evaluate the 10th expression. This was clearly not acceptable: while users may understand that asking a very complex question in QlikView can lead to a commensurate delay, they are often much less understanding of poor application performance when a simple question is asked.
The Solution
The key to resolving this problem is to hide the non-relevant expressions (the ones excluded by non-matching conditions) from QlikView so it does not attempt to evaluate them unnecessarily. And the key to doing that is variables. Specifically, we need to move the entire conditional expression into a variable but write it in such a way that the variable returns a simple string value of the final expression. The following are the complete steps needed to create a suitable variable:
- Create a variable via the Variable Overview. For our purposes, let's call it vExpression.
- Take the entire conditional expression and paste it into the variable. Ensure that the variable begins with an = sign.
- Do not make any modifications to the conditions in the expression—the if() and/or pick(match()) part of the expression.
- For each possible expression returned by the conditional expression, make the following modifications:
- Replace all instances of $ with @ (this is necessary to prevent variable expansion from automatically applying)
- Replace all instances of ' (single quote) with ' & chr(39) & '
- Place one single quote ' character at the beginning and end of the expression (the one at the end should be placed before the comma that indicates the next condition)
Using our example above, vExpression would look as follows:
Once the above variable has been created, you can test that it is working properly by creating a text box of vExpression. In the example above, if vExpressionSwitch is equal to 'Sales', then the value returned by vExpression should be: sum({<@(vMySetAnalysis),Country={'US'}>} Sales) (note the @ character in place of $). Once you have verified that the variable is working as expected, simply replace all instances of the original expression in your charts/objects with the following: $(=replace(vExpression,'@','$'))
This trick essentially makes the other expressions (which may or may not be heavy) invisible to QlikView when they are not needed. These expressions, consequently, will have no adverse effect on the application performance and your charts will once again perform as they should!
Oh yeah this is completely new to me though I’ve been working on QlikView for three years now
Hi,
Thank you very much for this insight. Since you have done a proper investigation on this topic, could you elaborate if the evaluation of the expressions would still happen before the conditional checks if
a) we assigned the expressions (count, sum etc.) to variables
b) the expressions were available in an own field and would be displayed in a comma separated string format in the pick(match()) condition using the concat function.
Thanks,
Martin
Martin, thanks for your comment. The key to the solution is making sure that QlikView doesn’t evaluate the conditions for every dimension. We need to prevent QlikView from checking which expression to evaluate for each row of the chart/table. The reason that’s important is that there seems to be an “undocumented feature” in QlikView, which is in reality a pretty severe limitation of the tool, that causes all expressions to be evaluated (including the ones that are not relevant). That’s why I am taking the conditional logic to a variable that begins with an ‘=’, so that it is evaluated only one time for the entire application.
To your question, making the expressions variables or field values is an interesting idea, but I don’t think it will have any effect on the original problem. QlikView will still try to evaluate each one on every row, and performance will be detrimentally affected. I encourage you to try this for yourself, but I’m pretty certain that is what would happen. I’m at a loss to think of a way other than what I wrote about above to resolve this problem.
Cheers,
Vlad
Martin, take a look at Jason’s suggestion, below. Although I haven’t tried it myself, a 2 field island would probably be another way to fix this problem.
Vlad
Hi,
that’s a clever workaround. I tried it myself and got a chart which calculated 3 times faster. I did notice however that the new chart als used considerably more ram (objects overview). Any idea what the reason of that would be?
thanks,
Wim
Wim, that’s really interesting, and I can confirm that the same thing happens on my end. I’m at a loss to explain it. My best guess is that either the Memory number in Document Properties isn’t actually accurate (either before, after, or both) or that there’s some sort of internal QV logic on how a chart is allowed to use RAM that changes depending on how fast it calculates. I think the latter is more likely. Anyway, the difference in memory will most likely be in the low MBs, so hopefully this won’t be a deal breaker for anybody.
Regards,
Vlad
Hi Vlad,
Thanks for this and Happy New Year! It is a strange “feature” and although I have not performed any qualitative testing I have seen it too. Somewhere else something similar seems to crop up is in calculation conditions for charts – I swear QV pretty much calculates the chart expressions before realising it shouldn’t and stopping!!
Another solution to this challenge for your readers – a data island of expressions as field values. E.g. One field of the 2-field Island (ExpressionName) is equivalent to your vExpressionSwitch, and a second field (Expression) contains the full expression but as a string. In your chart expression all you then need is $(ExpressionName). As the only ‘visible’ expression is determined by an actual field selection (a listbox for ExpressionName) then within QV’s most basic associative logic the others are excluded.
Enjoy 2015 sir – keep the great stuff coming to us all please!
Jason
Thanks, Jason. That actually makes sense, although I haven’t tried it myself. In my reply to Martin, above, who asked whether this would fix it, I didn’t consider having a 2 field island. Good stuff!
Vlad
What has your experience with performance been when storing these values in a table (column 1: measure name, column 2: expression), then evaluating the selected expression?
Mike, I haven’t tried this myself, but see Jason’s suggestion above. Seems like it would work. Could be another way of solving the same problem.
Vlad
Hi Vlad,
I am trying your above solution in our application but no success.I am not getting any value.The string return is not the filed of chart but it is available for users on filter.
Below is my expression which is broadly used across application.
=Pick(
Match(GetFieldSelections([Type]),
‘Months’,
‘Fiscal Year’,
‘Rolling 4 Quarters’,
‘Calendar Quarters’,
‘Rolling 12 Months’,
),
dual(num(((sum({}$(vVOL))/sum(nbr_of_wks))/(sum({}$(vVOL))/sum({}nbr_of_wks)))-1,’#,##0.0%’)&’???’,(((sum({}$(vVOL))/sum(nbr_of_wks))/(sum({}$(vVOL))/sum({}nbr_of_wks)))-1)),
dual(num(((sum({}$(vVOL))/sum(nbr_of_wks))/(sum({}$(vVOL))/sum({}nbr_of_wks)))-1,’#,##0.0%’)&’???’,(((sum({}$(vVOL))/sum(nbr_of_wks))/(sum({}$(vVOL))/sum({}nbr_of_wks)))-1)),
dual(num(((sum({}$(vVOL))/sum(nbr_of_wks))/(sum({}$(vVOL))/sum({}nbr_of_wks)))-1,’#,##0.0%’)&’???’,(((sum({}$(vVOL))/sum(nbr_of_wks))/(sum({}$(vVOL))/sum({}nbr_of_wks)))-1)),
dual($(vABDCGrowthCalQuart)&’???’,$(vABDCGrowthCalQuart)),
dual(num(((sum({}$(vVOL))/sum(nbr_of_wks))/(sum({}$(vVOL))/sum({}nbr_of_wks)))-1,’#,##0.0%’)&’???’,(((sum({}$(vVOL))/sum(nbr_of_wks))/(sum({}$(vVOL))/sum({}nbr_of_wks)))-1))
)
Can you please help me out so that it wont execute all the conditions?
Regards,
Mayank
Hi, Mayank. I think perhaps something went wrong when you were copy/pasting, since this expression isn’t valid QlikView syntax. Your sets {} are empty. But if you follow the 4 step process I outlined above, and apply it to your actual valid QlikView expression, you should be good to go.
Cheers,
Vlad