We sometimes have a requirement to select between two or more expressions dependant on user selections or some other condition. For example, this chart Expression.
if(vWithCommission=1
// Calculate with Commision
,sum({<CommissionFlag={1}>}
SalesAmount - SalesAmount * CommissionRate)
// Calculate without Commission
,sum(SalesAmount)
)
The if() will select one or the other sum() expression based on the value of the vWithCommmision variable. Because a variable is being tested — not a row value — only one sum() will be chosen for the entire chart.
If performance is important, this is not a good way to implement the requirement. QlikView does not “short circuit”. Short circuit means to stop evaluating when the condition is true. QlikView will calculate both sums and then return one of those results.
Some people mistakenly believe that putting the above expression in a variable changes the behavior. This is incorrect. The if() evaluation is still done at the chart level and the performance impact remains.
A performant way to solve the requirement is to put each calculation into a separate chart Expression and use the Conditional property to select the Expression.
An Expression with a Conditional evaluating to False will not be calculated or displayed.
There is also the possibility of choosing the calculation in a variable, but you have to follow a few rules.
- The variable should return the string representation of the formula. Note the single quotes in the example below.
- The variable definition should begin with “=”. This causes the if() to be evaluated only once.
- In the chart Expression, reference to the variable should be made with $(). eg $(vChooseCalc)
=if(vWithCommission=1
,'sum({<CommissionFlag={1}>}
SalesAmount - SalesAmount * CommissionRate)'
,'sum(SalesAmount)'
)
-Rob
For more performance tips, join us at the upcoming “Masters Summit for Qlikvew” in San Francisco May 2015 where Oleg Troyansky presents an always enlightening session on Performance Tuning. Can’t make SF? Check out the other dates and locations on the website.
Rob, I was recently informed that if you are desiging for the Ajax client you should NOT begin the variable definition with “=” as it impacts performance. Can you verify that is correct?
Brian, I have not heard of this and can’t imagine why, but I’ll ask around. Do you have any source for that recommendation? The choice of whether to use “=” is about when something should be evaluated — once at the variable level or later per chart row. This post by Henric has a good discussion on the = topic.
http://community.qlik.com/blogs/qlikviewdesignblog/2014/11/24/the-equal-sign
It was during a consult with a Qlik employee at the World Conference in Orlando. It was within the context of chart performance in the ajax client.
I load variables (constants and expressions) using a text file. He said that I should define the variables without using a “=”. I haven’t throughly tested it yet, but the expressions don’t seem to work if you don’t have the “=”.
Do you know if the same applies to PICK statements as to IF statements? We have a scenario where we’re using those to select between a 5 options for how to calculate a specific field in the chart… if it’s calculating all 5 options each time, that could obviously be a pretty big performance hit!
Hi Rob
As interesting as usual. Just a question, i use the pick() function when there are more than one expression , does it performs like an if, or just evaluates the “picked” expression?
Thanks
Pick performs just like an if(). That is, all expressions are evaluated and then the pick() is applied to select a result.
Hello Rob,
this is pretty interesting, but a little bit disturbing, at least for me.
your definition of ‘short circuit’ doesn’t match with these ones http://stackoverflow.com/questions/1232603/do-all-programming-languages-have-boolean-short-circuit-evaluation
you said that qlikview will calculate both sums before to evaluate condition, also with a pick, as you wrote in a comment above.
if this is true, this is a not really good feature for qlikview.
Is there any kind of official doc about this from QlikTech?
Thank you very much for your blog, I enjoy it a lot.
Best regards
Alberto,
Actually, the lack of short circuiting is not always bad. In this thread http://community.qlik.com/thread/119635 read Henric’s explanation of why they don’t short circuit (about half way down the thread). In most cases it’s a good thing. It only becomes a potential negative when testing a variable (vs a row by row condition) and even then is only noticeable in large apps.
Hello Rob,
Is it still true that false branches are not terminated? I have made different attempts to test this (11.2 SR6), but I can not verify it. Instead it seems like the following expressions get the same calculation time (SEK selected in SelectedCurrency):
if(SelectedCurreny=’SEK’,sum(LineAmount_SEK),0)
compared to
if(SelectedCurreny=’SEK’,sum(LineAmount_SEK),
if(SelectedCurreny=’NOK’,sum(LineAmount_NOK),
if(SelectedCurreny=’USD’,sum(LineAmount_USD),
if(SelectedCurreny=’EUR’,sum(LineAmount_EUR),
if(SelectedCurreny=’GBP’,sum(LineAmount_GBP),
0)))))
(I would however anyway write this expression differently)
I’m not aware of any changes to the eval logic. I did some recent testing and it appeared that all branches were evaluated. I should clarify that I have no way of knowing for sure what was actually executed, but I speculate based on timing tests as you do.
I’m not sure why your test shows the same calc time. It could be your testing methodology or it could be something else I’m missing.
I have a nested-if for a formatted Income Statement report that performs decently, but it’s not ideal. Since the chart can have a blank line (for formatting purposes), an Earnings Per Share line (which is formatted differently – out to 3 decimals), and then all other lines formatted normally, the approach below is the only way I’ve thought to handle this. Is there any way around a nested-if in this case?
if([Level] = ‘Space’ ,’ ‘, // Blank Line
if([Level] = ‘EPS’ ,$(eEPS(Actual)), // EPS
if([Calculation]=’Total’, num(sum(-[Actual]*[Amount])/vDivideBy,’$#,##0;($#,##0)’),
num(sum(-[Actual]*[Amount])/vDivideBy,’#,##0;(#,##0)’)
)
)
)
Mark,
This is actually a very good use of if(). At some row of the chart, each condition of the expression will be true. This is a situation where QV’s lack of “short circuit” is actually advantageous.
The issue discussed in this post applies when only one condition could be true. Your if() statement looks fine to me.
if condition are in which level gives good performance in script level are expression level?
can any one help me.
It is usually better to calculate in the script when possible.