Not all variables are created equal

By Barry Harmsen

Beware of the equals signIt 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:

Raw data

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:

Revenue per store using a variable starting with an equals sign

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.

Revenue totals when the equals sign is not used in the variable expression

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

About The Author

Barry Harmsen

Hi there, I'm Barry and I'm a Business Intelligence Consultant at Bitmetric and based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past 10 years I have been specializing in Qlik and a more user-centric form of BI. I have done numerous QlikView and Qlik Sense implementations in many different roles and industries. In 2012 I co-authored the book QlikView 11 for Developers. You can follow me on Twitter at @meneerharmsen.

22 Comments

  • 1
    Jimmy
    June 8, 2011 - 16:20 | Permalink

    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.

    • 2
      June 10, 2011 - 22:34 | Permalink

      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

  • 3
    Alexis Hadjisoteriou
    June 8, 2011 - 18:09 | Permalink

    Great tip and lovely example app. Keep them coming.
    A reader from Cyprus

    Alexis

  • 4
    June 8, 2011 - 22:51 | Permalink

    Hi Barry – great to see a a new post on here – keep them coming!

  • 5
    June 9, 2011 - 21:40 | Permalink

    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!

    • 6
      June 10, 2011 - 08:17 | Permalink

      Thanks Ralf, well spotted!

  • 7
    June 11, 2011 - 05:56 | Permalink

    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.

  • 8
    DV
    June 12, 2011 - 11:39 | Permalink

    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

    • 9
      June 12, 2011 - 12:32 | Permalink

      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

  • 10
    DV
    June 12, 2011 - 13:25 | Permalink

    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

  • 11
    Anton
    June 20, 2011 - 17:48 | Permalink

    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

  • 12
    Christian
    July 21, 2011 - 11:03 | Permalink

    Good post.
    Thx

  • 13
    Jason Newman
    September 29, 2011 - 10:19 | Permalink

    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

    • 14
      October 5, 2011 - 12:05 | Permalink

      Jason,

      Can you send me that expression via the contact form? WordPress tends to mangle QlikView expressions.

      Cheers,
      Barry

  • 15
    Vikram
    March 27, 2012 - 05:32 | Permalink

    Great Post . Very helpful for newbies like me.

  • 16
    helen gonzalez
    February 25, 2013 - 05:18 | Permalink

    Thank you so much for this example!

  • 17
    Smitha
    October 10, 2013 - 05:15 | Permalink

    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

  • 18
    Anosh
    January 5, 2014 - 18:58 | Permalink

    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

  • 19
    Anosh
    January 5, 2014 - 19:02 | Permalink

    Hi Berry, Thanks for sharing the wonderful concept.

    Anosh

  • 20
    Bhumika Kandpal
    September 14, 2017 - 10:34 | Permalink

    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

  • Read previous post:
    Excel and VBA: the poor man’s QlikView

    Today I have something completely different for you. It is only tangentially related to QlikView and lacks any practical application...

    Close