Stop storing your Qlik Sense Expressions in Variables!

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.
  • PerformanceLittle 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:

  1. Fast population and even more importantly automatically updating the Master Items with calculation changes
  2. 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  😉

 

13 responses to “Stop storing your Qlik Sense Expressions in Variables!

  1. Hey Rich,
    Excellent post, it make sense not to use variables to store expressions all in the name of Self Service.
    However, I think some level of control has to be in place, particularly when a non-technical user edit expression from master item, make mistake in the process especially with Set Analysis commas and brackets, which cascades down to other viz using same expressions.
    What would be your advice?

    Regards
    Gabriel

    Like

  2. Hello Richard,
    Your post is very interresting !
    There is an extension named « variable destructor «  which can delete all variables 🙂
    Regards,

    Like

  3. To be honest, with the new variable tools (e.g. showing the fully expanded expression and variable contents) available in Sense expression editor, I’m much less inclined to listen to the “but it’s opaque to the user!” argument. All Qlik needs at this point is to allow copying of the expanded expression 😉

    Like

    • True, the latest version of Qlik Sense allow you to view the variable definitions one at a time. Problem is if a expression references more than one variable. The user must then search for each in turn, not to mention some expressions I’ve worked with in the past are like a Russian nesting doll of variables referencing each other. For me it still stands that as developers we need to think more of the user experience than speed / ease of development.

      Like

      • Not exactly one by one – the coolest thing is that you can see the fully expanded expression, e.g. the whole Russian doll dismantled and neatly laid out, without any clicking even.

        I think from the user experience point of view there is also something to be said for splitting the formula into variables (or component master measures, if it were technically possible… smaller blocks, generally) – if the variables are properly named, this can be something like self-documenting code. When you have a formula that needs vertical scrolling in the editor window, parsing it in one go can be quite painful; on the other hand if it’s composed from variables you can “drill down” only along the paths that interest you.

        Of course you could say that the model should be built in a way that precludes the necessity of creating giant Frankenstein’s formulas, but that’s not always practical.

        Like

      • Thanks again Kabu. I’ve double checked and you can see the variables but only if they’re not evaluated (i.e. no ‘=’ at the start). Sometimes you do need to evaluate….

        Furthermore the user can’t edit or even copy it .. totally crazy! To do this you have to copy them one at a time as per my previous comment.

        Takes us back to the point that variables don’t really help the end user they just put obstacles in their way as we shouldn’t expect them to do all that leg work just to customise an expression.

        Like

  4. Although I see your point, I cannot agree to it, and it is exactly because of the self service aspect of Sense.

    If there is a possibility that a business user will want to change anything in a master item (rewrite the formula, change something in a set part of it etc), than that user must have a good understanding of the actual datamodel, business meaning of the KPI and Qlik syntax. Without the above 3, the possibility that the changed formula will be corrupted for some reason is very high, which could easily lead to a wrong business decision and that could have minor or even major consequences. Not to mention the fact, that it can be highly frustrating for a less experienced user to understand a complex formula and/or figuring out what they did wrong if they end up with an nonworking expression.
    Therefore allowing business users to start playing with complex expressions isn’t the way to a successful self service application. I’m not saying there aren’t some experts out there who also happens to be business users and can handle monstrous formulas easily, but that’s not very common.

    Creating master items with simple and generic formulas for business users and using variables for the complex formulas used in the objects serving as guided analytics is what I found beneficial for every party. This way developers can easily handle the formulas in one place (like in case of QlikView) and business users can start building their personal visualizations without spending too much time understanding more complex formulas with complex set analysis, aggregations, filtering eliminations, etc. Also the list of master items would be much shorter this way, making the life of end users much easier. The only thing making it even more friendly for business users would be, if they could build measures from multiple master items, like calculating share with a help of Sales and Total Sales master items.

    Of course the above reflection is irrelevant if the application does not contain a guided part where specific objects must look a specific way no matter the selections, etc. But I haven’t yet seen a project where the client haven’t had some crazy needs :).

    What I completely agree with is the fact, that building a really good and as simple as possible datamodel is much more important in Sense than it was in View. Actually the success of a self service application stands or falls mostly on the datamodel.

    Like

    • Thanks Rezi

      Sure, we have to remember this is addressing frustrating issues that may arise perhaps when the official development team are not available.

      Users can create a measure from scratch so why not give them access to a measure they can edit. Far more sensible.

      master items can not be corrupted. Only the duplicated sheet isn’t governed. That forms part of your sign off to base sheet procedure which has all the ticks and balances you’d expect.

      We’re not taking away from a governed approach but giving users a better experience in these exceptional, unforeseen circumstances . . Means more work for developers but worth it.

      R

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s