It has been a while since my last post. To get back in the habit of regular updates, I am starting today with a short tip on a caveat of the use of the equals sign (=).
Starting an expression with or without an equals sign may almost seem like an arbitrary decision. Most developers quickly figure out that this is not true for text objects. However, there is another, less obvious area where the use of the equals sign can greatly impact how (and more importantly, when) your expression is calculated.
When creating a variable in the variable overview (Ctrl + Alt + V) an expression that starts with an equals sign is directly calculated by QlikView. An expression that does not start with an equals sign is not calculated. The implication of this difference becomes clear when you consider the following example:
An application contains data on yearly revenue per store. The raw data is shown above. To get the total revenue, a variable called v_Revenue is created than contains the following expression:
=sum(Revenue)
When this variable is used in a straight table to display the total sum of revenue per store for all years, the following result is shown:
This is obviously not the correct amount, it is the total revenue for all stores and years. Because the variable expression started with an equals sign, it was immediately calculated at the document level by QlikView, without taking the Store dimension into consideration.
The solution is to remove the equals sign from the start of the variable expression:
sum(Revenue)
The next step is to modify the expression in the straight table so that the variable is enclosed in a Dollar-sign followed by parentheses:
$(v_Revenue)
This is called “Dollar-sign expansion” and it tells QlikView to calculate the variable. Because the variable is now being calculated at the straight table expression level, it correctly takes the Store dimension into consideration.
The example QVW below shows the difference between the two variable expressions. As always, if you have questions or comments, feel free to drop them in the comment section below.
Download the example QVW
22 Comments
Thank you for your example. It help clarify the difference.
Side question: Other than maybe key strokes, is there an advantage to declaring a variable versus just typing
Sum(Total Revenue)<within the expression? Thanks.Jimmy,
The main advantage of using a variable is reuse. Reuse makes maintenance easier and reduces the risk of typo’s etc. For a straightforward expression like
sum(Total Revenue)a variable may not be necessary. However, if you have complex calculations that you need to reuse many times in multiple objects, a variable is a good solution.Variables become even more interesting when you need to ensure that your calculations are the same across all of your QlikView applications. In that case you store your expressions outside of QlikView and load them into variables in the load script. But that could be the subject of an entire blog post 😉
Cheers,
Barry
Great tip and lovely example app. Keep them coming.
A reader from Cyprus
Alexis
Hi Barry – great to see a a new post on here – keep them coming!
Hi Barry,
this is good to learn QlikView! But you should fix the typo to not confuse newbies:
$(v_Revenue) instead of $(Revenue)
Greets from Leipzig,
– Ralf
PS: nice logo!
Thanks Ralf, well spotted!
Yes! I agree it is very important to learn to use variables, not only as a repository for expressions and calculated values, but as part of the design strategy of an application.
I’ve been using them for a while now for many different things and in many different ways: with and without the equal sign, as simple expressions, as variables and expressions that take “parameters” when they are called, even to store the Set part of an expression with set analysis.
There is definitely a lot to know about QlikView expressions and I agree that there is a lot of material for a blog post (or several of them).
Thanks Barry!
Mike.
Hi Barry,
Thanks for the tip. This is very useful. Though I had not fully understood this concept before reading this post. I had encountered the difference by using with and without “=” sometime back and now after the post I had fully understood. Many thanks for this.
Quick question… I had also stumbled up on re-using the chart expression Label. I mean for example my first chart expression is Sum(Sales) and the label TotalSales and I had observed that I can use TotalSales as my second expression instead of Sum(Sales). So I am curious to understand what is happening in this case… does this work like a variable with the scope of the variable is the chart object?
Many thank in advance.
Cheers – DV
Hi DV,
You are correct in that you can reference the result of an expression within a chart object by using the chart expression label. Basically, it works like a local variable within the scope of the object.
An example where I often use this is in straight tables containing Targets and Actuals, where you also want to see an “Actual vs Target” percentage. I do not copy and paste both expressions, but simply reference them via their expression labels. So the expression is “=Actual/Target”.
This might actually be a nice subject for a short blog post and example application, so thanks for triggering me 😉
Kind regards,
Barry
Thanks Barry. Makes sense and it will be good idea to post on this one. Also please try to write on performance implications in terms of using Variables & Labels against direct expressions.
I am following you on Twitter and subscribed to your blog. I’ll look forward for more posts…
Cheers – DV
Hi Barry,
You just confirmed what I’ve been looking for 🙂
Your blog is very helpful as it provides fundamental info with practical examples that newbies like me can understand them easily and thus, can apply them in our application with confidence.
Thank you very much and I hope to see more tips to come!
Best regards,
Anton
Good post.
Thx
Barry,
Very interesting article, thanks.
I have a quick question: I have the following equation:
COUNT({<Employee={"=SUM({} [Project time])/SUM([Project time])} DISTINCT Employee)
It works fine in isolation, but in a chart to map different periods I am getting document wide calculations.
I have defined a variable vRed as the above calculation (without an equals sign at the front) and used it in a calculation on the chart (without an equals sign at the front) and yet I am getting document wide figures in part of the calculation.
Is the = sign in the middle of my equation responsible?:
Regards
Jason
Jason,
Can you send me that expression via the contact form? WordPress tends to mangle QlikView expressions.
Cheers,
Barry
Great Post . Very helpful for newbies like me.
Thank you so much for this example!
hi,
Could you please let me know how to use “not equal to” in the set expression.
I need to write condition OperationalCategorizationTier1!=’Batch’
I tried writing in the following forms,
[Operational Categorization Tier 1]={*}-{‘Batch’},
[Operational Categorization Tier 1]-={“Barch”},
it is not working. throwing error. please advise
Hi Berry,
Great post for learning. I have a question.
As you have told that with equal sign a variable is calculated immediately at document level, does it mean that even though that variable is not being used in current UI but if present in variable list, will always be calculated at document level once the document is opened.
Also I have observed that the value of such variables is dependent upon the current selections. so it means that down the line of development, if we have few complex variable with equal sign at start, not being used in current document, will they still be calculated everytime whenever any selection is being made? If so will they not affect performance of document?
I am asking this because we have an application where we have externalized all the expressions (nearly 1000 variables) using variable into excel sheet. There are many equal sign variables. If the scope for them are at document level then definitely removing them from variable list will improve the performance.
What is your though about it? and what is good practice for externalization of expression?
Thanks,
Anosh
Hi Berry, Thanks for sharing the wonderful concept.
Anosh
Hi Barry,
Thanks for the Blog.
I have one doubt with regards to variables.
Is there any way we can create two different variables with same name and use both of them as per the specific requirement.
Thanks
Bhumika Kandpal