How To Create A Variable In Qlikview

Are you gasping right now after reading the title? Shaking your head in disagreement? Well, let me explain…..

At Qonnections this year I gave a talk on various do's and don'ts when creating large enterprise sized Qlik Sense deployments. One common thread (although not referenced by myself in any big way) was the difference between QlikView and Qlik Sense when it comes to development. I spoke about this recently in my blog post "Prepare to be searched". The idea being that we, as developers, need to focus more on the data model than we did when developing in QlikView because we now expect the user to search for more information and insights.

We also expect them to create their own visualisations on the fly and for that they need an easily understandable data model and with that in mind we need to think about how we supply our users with Dimensions, Measures and Visualisations in the Master Items.

In QlikView it was fairly standard to place all expressions into variables before releasing the dashboard into production. There are a number of reasons for this:

Metric Governance – You only have to edit the variable once to know all instances of that measure will be consistent as they all reference the same variable
Hierarchical Metric Governance – For measures such as Margin, instead of writing the expression in full you reference the lower level variables for Sales and Cost. As before you only have to update one to know all instances of that measure will be consistent.
Performance – Little known one this. If you have two expressions "=sum(Sales)" & ="sum( Sales )" (notice the redundant spaces in the second expression!) the engine parses them as two separate expressions and therefore takes twice the processing power to calculate. If they were written identically then they would be calculated once regardless of how many times they are used.

With all these benefits why on earth would we stop using expressions?

Its because of the user experience, if we're using variables when the user wishes to edit a master measure they don't get a great starting place to work from…

Qlik Master Measure Variable

The above example is not very helpful and the user is likely to feel frustrated. I believe reading something and understanding the context is far simpler than creating something from scratch. If you look at the following example try to see it with fresh eyes as if you're a Qlik end user:

Here when the user edits the master measure they immediately get insight into what calculation is being performed. You could take this a step further by embedding help within the expression itself such as with the following more detailed example.

Now the user can see exactly whats going on even with the addition of Set Analysis. Most users should be able to edit the measure or perhaps take it a step further using this and other measures as a starting point for something new, cherry picking the information from each and creating a new calculation to self serve a discovery from the data.

How am I going to manage all this extra work !

From a developer point of view how are we going to manage this? Challenges are:

Fast population and even more importantly automatically updating the Master Items with calculation changes
Reduce duplication by allowing measures to reference themselves

Firstly, As most developers do with variables we can still handle creation and updating of the Master Items with an external file such as a workbook. A quick search of "Master" in Qlik Branch Garden brings up a number of extensions that automate the process (shown below). This is done by leveraging the power of Qliks amazing APIs!

I've also heard whispers in the community that rather than relying on a third party extension to speedup the process Qlik are going to make that part of the core product …. we shall wait and see !

From a risk point of view in regards to extensions, if the one you choose stops working you can simply switch back to a manual process. So unlike a chart extension availability and performance your risk is pretty much none existent.

The final thing you need to consider is Hierarchical Metric Governance. During the talk I was asked a question around Master Items referencing each other. This can't be done currently although I believe its in the pipeline. Regardless I don't think this is required or causes an issue as I had to resolved a similar problem many years ago on QlikView!!!

The challenge was to automatically create time iterations of the same metric such as Yesterday, Last Year, Qtr to Date, etc. I created a script in QlikView (blogged about here) which did this and which even took into account measures that are dependant on each other so its totally possible to automate the process and its something I'm going to do specifically for Qlik Sense in the near future.

With these two points covered I don't see any reason why you should still be using variables for expressions in Qlik Sense.

Still not convinced?

Well I'm really having to work to win you over today! Okay, so let me tell you about my hatred of variables (Vloged about before). The issue is how they persist unless you physically delete them, once they have a value after the script has stopped running you're stuck with them.

In QlikView one of may standard pre-release tests was to delete all the variables, run the script (which should recreate all the required variables) and test all is okay. Often I've found issues where an old variables is removed from the script or the control sheet but not deleted from the app and it becomes a bug in the calculations or functionality.

Without leveraging the APIs in QlikSense you have to manually delete all the variables (which can take an age so most people don't bother). For this very reason I would only use variables where absolutely necessary keeping the list to an absolute minimum.

Take care, Rich

PS – Its a leaning curve, my first post on the subject (ca. 2015) Storing Expressions In Variables did explore the benefits of using Variables although my preferred choice even back then was always a data island 😉