Managing expressions in QlikView: the use of variables

Posted by Juan on 27/09/2009 under How To | 13 Comments to Read

In this post I want to share with you a good practice in handling the various expressions that exist in a QlikView document. The most used expressions are the ones used in charts, where they hold measures such as Sum(Sales), Sum(Price*Quantity), etcetera. These are the ones more likely to be reused by other objects and in different sheets. There many other expressions including Chart Attributes, Color Expressions and Show Conditions, you can see them all by going to the menu Settings/Expression Overview.

The use of expressions can be intensive in QlikView, especially when having a sophisticated user interface. There is a growing need to handle these expressions in a more efficient way, and this can be accomplished by the use of variables.

Reasons for holding expressions in variables:
*To achieve reuse: the formula for a measure such as Sales usually remains the same across a QlikView document, so it doesn’t make sense to write it on every chart.
*To enforce consistency in the formulas: by avoiding the risk of having different formulas that calculate the same measure.
*To provide a single point to apply changes: if and when a formula needs to be changed, you only need to change one variable and all the charts and other objects that refer to that variable will follow.
*To allow the end user to make changes through an input box, when needed. This could be the case of targets for KPIs or general parameters.

Variables can be created manually by going to the menu Settings / Variable Overview or by using the SET/LET statements in the script. They have a name and a value, which can hold any sort of strings or numbers, and they can be used as a reference from every sheet object. The Input Box is the object designed to show variables in the user interface.

If you want to start experimenting with moving your expressions to variables try the following:
1)Go to the Expressions tab on one of your charts and copy one of the formulas, for instance Sum(SalesValue)
2)Go to the menu Settings / Variable Overview and click on the “Add” button to create a variable. Give it a name such as vFormulaSales (it is a best practice to have all variable names starting with a v to help differenciate them from Fields).
3)Select your variable from the variable list an paste the formula from the chart in the “Definition” text box. If the formula starts with an = sign, remove it. Finally click on “OK” to save the changes.
4)Go back to the Expressions tab of your chart properties and replace the formula with the following: $(vFormulaSales)

The $ sign expansion indicates the string contained in the variable is a formula that needs to be calculated.

The final step is to replace replace the cloned formulas in all the other objects so they all refer to the same formula contained in the new variable. Every new object that needs to show Sum(Sales) should also refer to the variable.

You may already have quite a few QlikView documents where you didn’t apply this practice, but it’s never too late to get started. In the long term it’s really worth it.

Tags: , , ,

  • Tweets die vermelden Managing expressions in QlikView: the use of variables | Quick - Qlear - Qool -- Topsy.com said,

    [...] Deze blogartikel was vermeld op Twitter door Dink Intelligence. Dink Intelligence heeft gezegd: RT @gillespol: New blog post @ http://www.quickqlearqool.nl: Managing expressions in QlikView: the use of variables – http://bit.ly/nfHer [...]

  • Gilles said,

    Hi Juan,

    Great post! And indeed a good practice to have common formulas in variables. I’ll introduce that to our standard way of working at it-eye!

    Cheers,
    Gilles

  • Amit said,

    Hi Juan ..thanks for this article.

    Actually I has used the same concept earlier during my reporting (although not extensively). $(variablename) works but in expression it is displayed in Red colour. ( i.e. looks like warning …like wrong syntax).
    I am afraid will not create any problem during report refresh/distribution ?

    Will appreciate your reply/comment on the same ?

  • Juan said,

    Hi Amit, thanks for your feedback. The red colour is probably a bug in the syntax check feature of the Edit Expressions dialog. It is safe to use $(variablename) this way, so in this particular case you can disregard the red colour as a “false alarm”.

  • Daniel said,

    Nice posting! As long as QlikView has no central repository of reusable expressions, this is a good alternative. I think they are planning to a central repository in the future, according to the outlook they gave at the Qonnections conference in Putten.

    Do you get this us of variables working as well for expressions which make use of set analysis? I was not able to do that (in 8.5).

  • Juan said,

    Thanks for you comment, Daniel. Reusing expressions across documents requires having consistent data models (same field names, same granularity for fact tables).
    As you said, there’s no official repository of expressions yet, but you can do the following in the meantime: create an Input Box object, include all the vFormulaXYZ variables in it. Then export the contents of the object to a .qvo file (it’s a tab separated values file). Once you’re done with all your documents, you can create a new document Expr_Metadata.qvw and read all .qvo files. That will give you a central view of all expressions in all your documents. I will expand later on this idea in a next post.

    Regarding set analysis, it should work, I think the key is getting the = sign right, by replacing it with $() wherever you can.

  • Bhasin Naik said,

    Hi Juan,

    Thanks for the post. Can we access the variable in our script. Actually i want to use the text which entered in the text box by the user to script. can we do that?

    Thanks

  • Juan said,

    Hi Bhasin, thanks for your comment. I’m not sure why you would want to do that, it really depends on what you want to accomplish. You can set up a variable and provide an input box to enable editing from the user interface. The variables can be accessed from the script. But if you are on a server environment, that becomes tricky because there are many users and many possible values for that variable, so those values are stored by the QlikView Server in a separate file outside the document. You wouldn’t be able to access them from the script.

  • Hector said,

    how the use of variables fro expressions impact the server performance in an application of a considerable size (let’s say 200 Megabytes?) and extensive amount of objects?

    It is stil reccomendable?

    Thanks

  • Juan said,

    Hi Hector, thanks for your question. Moving formulas/expressions to variables for reuse and easy access is more a best practice from a maintenance point of view. This practice is neutral in terms of performance. Regarding response time, it is driven by a number of factors such as the complexity of the formulas/expressions, the number of objects on any given sheet and the quality of the data model design.

  • Deepak said,

    This is very useful! Thank you.

  • Mike said,

    Is there a way programatically (perhaps with a VBScript module/macro) to loop through all variables that exist in a QV app, one by one determine if the variable is being used in an expression or displayed anywhere (chart, table, etc.) and then delete the variable if it’s not being used? Here’s why I ask… Frequently when a QV developer at my company starts working on a new app, they make a copy of an existing app and then just change the script and the GUI. All the variables from the previous app are carried over into the new app. So we have many QV apps that contain lots of unnecessary variables. I can write a VBScript module to loop through and display all of the variables (and their current values) in the QV document.

    Function Vars()
    rem ** Show name of all variables in document **
    set vars = ActiveDocument.GetVariableDescriptions
    for i = 0 to vars.Count – 1
    set v = vars.Item(i)
    msgbox(v.Name & ” = ” & v.RawValue)
    next
    End Function

    Adding “ActiveDocument.RemoveVariable v.Name” inside the loop would delete the varaiable. The problem is that the loop goes through ALL the variables. I wouldn’t want to delete variables that are being used and I would probably get an error if I tried to delete a system variable.

    Does anyone have suggestions for getting around the issues I mentioned above and only deleting unused variables?

  • jagan said,

    Very Very Very helpful Juan.

Add A Comment

home top

Get Adobe Flash player