• 1
    September 21, 2011 - 17:44 | Permalink

    Will this method work if the expression contains a reference to another variable? For example, we allow the user to select their unit of measure, such as Cases or Pounds, and store their selection in a variable. Then the expressions use that variable in calculations. So we end up with:


    I haven’t been able to load this type of expression from an external file because QlikView wants to interpret the vMeasure variable as it’s loaded.

    • 2
      September 21, 2011 - 21:49 | Permalink

      It should. I had the same problem with Dollar-sign expansions getting interpreted by the script. The solution was not to use an intermediate variable to store the expression code in the script, instead I peek it straight into the variable.

      • 3
        Pablo Labbe
        October 31, 2011 - 13:01 | Permalink

        Barry, Interesting solution. Can you post a sample code to exemplify your solution to avoid the interpretation in the load script ?

        • 4
          November 2, 2011 - 22:34 | Permalink

          Hi Pablo,

          The example QVW contains examples of Dollar-sign expansion in the variables that does not get interpreted by the script, but is written straight to the variable definition. Drop me an email via the contact form if you want to know more.



  • 5
    September 21, 2011 - 17:46 | Permalink

    That expression got altered when I posted but I think you can see what I mean.

  • 6
    September 21, 2011 - 22:10 | Permalink

    Yep, seems to work. Thanks!

  • 7
    September 22, 2011 - 01:04 | Permalink

    Nice solution, and one I use myself. It can get a bit more involved where you have expressions that need single quotes in them. I have in the past switched these out with a substitute in Excel (to a ¬ or somesuch) and then replace them back with a char(39) in QlikView.

    A further step is to have a table of expressions loaded in QlikView that cant then be selected – so you can have a single chart for which you can pick the expression.

    • 8
      September 22, 2011 - 10:07 | Permalink

      Agree about the single quotes, those can be a real hassle. QlikView could use an unescape function.

      I’ve used the table of expressions to create a cycle group for expressions as well. Might be a nice subject for a short blog sometime.

  • 9
    September 23, 2011 - 09:20 | Permalink

    Hi Barry,

    Great solution! Do you know a way to sync the variables between the excel and the qvw’s > if I remove a variable in the excel file it will continue to exist in the qvw isn’t it?

    best regards

    • 10
      September 23, 2011 - 20:13 | Permalink

      Hi William,

      You could write a macro that checks the existing variables to the ones that are being loaded and removes everything that does not belong. Not a pretty solution though, especially if you have application specific variables or variables that are being generated as part of your time dimension. Something to think about 😉


  • 11
    September 23, 2011 - 18:06 | Permalink

    Nice know-how! I have been looking for some thing similar to this for some time today. With thanks!

  • 12
    October 8, 2011 - 14:18 | Permalink

    Hi Barry,

    This is a great tip! Always found it a big hassle to administrate and keep track of all variables in the QlikView interface



  • 13
    Chris Cammers
    October 8, 2011 - 14:44 | Permalink

    If you don’t want to be quite so fancy you could just use an include file.

    I think the key advantage is the ability to store the formulas AND DESCRIPTIONS in a table and then present them in a list box on your applications.

  • 14
    October 18, 2011 - 22:18 | Permalink

    Great post Barry!

    One convention I try to follow when using this technique is to wrap the entire expression (in the Excel cell) with parentheses. I do this to help manage operator precedence. Example:

    sum(Price * Amount) –> ( sum(Price * Amount) )

    That first example was trivial, but you can see more relevance in this example:

    ( sum(Price * Amount) / sum(NbrOfUnits) )

    So now if this variable is used in the denominator of an expression, the “sub” denominator sum(NbrOfUnits) will be correctly handled.


    • 15
      October 19, 2011 - 07:17 | Permalink

      Good tip! I’ll add it to my coding conventions.

  • 16
    March 13, 2012 - 17:54 | Permalink

    Awesome blog and post barry!

    Very curious about the Daft Punk image on the post, tried to find the association but maybe it’s just because you’re a fan? 🙂

    • 17
      March 13, 2012 - 18:08 | Permalink

      Hi Francois,


      Regarding the Daft Punk image, I definitely enjoy Daft Punk* but the association is probably a bit too far-fetched. This was my third post in a row about QlikView variables, I thought the subject was getting a bit monotonous but figured: One more time…..


      * My interest in Daft Punk was recently rekindled by these funky 8 bit mixes: http://dachip.com/

  • 18
    August 19, 2013 - 08:59 | Permalink

    Can anyone please help me in understanding the code which is used to writeback the data from qlikview to the database.I do not have any idea on vbscript or javascript.

    It would be a great help

  • 19
    Simon Brosig
    July 20, 2015 - 16:29 | Permalink

    Hi Barry,

    I would like to define a variable that is calculated via two other variables. Can I simply define the expression like $(varX)*$(varY) in my excel spreadsheet?



  • 21
    Simon Brosig
    July 21, 2015 - 12:34 | Permalink

    Hi Barry,

    thanks a lot for your reply. You might be interested in this one:


    Greetings from Germany


    • 22
      July 21, 2015 - 14:08 | Permalink

      Hi Simon,

      If you change the // comment for /* and */ comments then it should work.

      Kind regards,

  • 23
    Simon Brosig
    July 21, 2015 - 14:43 | Permalink

    Thanks again, Barry! That did the job.

  • 24
    Rahul Varma
    August 7, 2015 - 06:02 | Permalink

    Hi Simon,
    The best solution you have provided for people like us, who were trying to use expressions which are in Excel. I myself is having little different issue. I have row wise expressions i.e. all the rows in excel sheets (about 24 rows & 8 columns) have different expressions, so how can I use your solution. Below is an example.


    Required Output :


  • 25
    Madhu Balaji
    December 1, 2015 - 20:11 | Permalink

    Hi Barry,

    We have similar requirement to do in Sense. Do you have a Sense solution I can lookup? Also, as I’m new to Qlik world, it would really help if you could send me steps on how to deploy the solution & other use cases like Webservice integration etc.

    Again, the solution you have provided here is great. Thank you!

  • 26
    December 20, 2016 - 13:25 | Permalink

    Hi Barry

    I have create my excel sheet with the variables but when I use the variable in a SQL LOAD Where Clause it seems to pull in the text instead of the actual value?

  • 27
    February 4, 2017 - 01:53 | Permalink

    It is very help full to resolve my issue with Set analysis and Variables.

    I appreciate your work.

    I have a doubt,.

    I have used externalizing variables to create the variables. Some complex calculations did not return results.
    I have figured out the issue based on your sample attached here.
    I have modified the For Loop as below, I got results for set analysis that uses a variable and the variable return results based on set analysis.

    Name | Expression
    monthtodaysetanalysis | only( {$} Month)
    FebAmontv2set | sum({$}Amount)

    $(FebAmontv2set) return results for the For loop used in creation of variables is as below :
    // Create variables
    for i = 0 to NoOfRows(‘Temp_Variables’) – 1
    let vName = peek(‘Name’, i, ‘Temp_Variables’); // Name of the variable
    let vComment = peek(‘Comment’, i, ‘Temp_Variables’); // Comment/description of the variable
    let $(vName) = peek(‘Expression’, i, ‘Temp_Variables’) & chr(10) & ‘/* $(vComment) */’; // Expression
    next i

    I do not get results for the below For Loop
    // Create variables
    for i = 0 to NoOfRows(‘Temp_Variables’) – 1

    let vName = peek(‘Name’, i, ‘Temp_Variables’); // Name1 of the variable
    let vComment = peek(‘Comment’, i, ‘Temp_Variables’); // Comment1/description of the variable
    let Expression = peek(‘Expression’, i, ‘Temp_Variables’) & chr(10) & ‘/* $(vComment1) */’; // Expression
    let $(vName) = ‘$(Expression)’;
    next i


    The difference between the above 2 set of For Loop is
    First For Loop uses only one statement like $(vName) to set expression directly.

    Second For Loop uses 2 statements as below to set expressions

    Could you please explain why the second set of For Loop not returning results.

    Thank you very much.


  • 28
    January 19, 2020 - 20:59 | Permalink

    Is there a way to do the inverse of this ?
    I have a Qlik sense app that already has variables defined from the UI.

    I want to try and extract the script for them to move their definition from UI to the load script and with this solution move it to an external excel.

  • 29
    March 23, 2021 - 14:17 | Permalink

    In my experience Qlik (View / Sense) are the worst developers I have come across. No comments, don’t stick to coding conventions like variable naming conventions. To me these qlik developers – I definitely don’t mean all – are the ‘Cowboy Builders’ or the ‘rogue Traders’ of the coding world.
    I saw one very impressive UK coder: who loads in a huge table as and when a field is needed join it to a table, then drop the table.🤮🤦‍♂️ It seems these sorts are in abundant in some parts of the world.

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Read previous post:
    Testing the performance implications of variables and label referencing versus direct expressions

    After my previous post about variables, I got an interesting question from DV. He asked me about the reuse of...