Use of variables in QlikView to create powerful data stories

Introduction

An application with good Front-end and poor Back-end is like Beauty without brains. You are awed by it initially, but you get irritated by it over time. On the other hand, efficient Back-end with poor Front-end may not excite users to use the application. In today’s world, you need excellence on both the aspects.

Sadly, a lot of people associate data visualization with only front end engineering. In today’s article, I want to bring out what does Back end engineering look like in case of QlikView and how can it add tremendous power to your data stories.

The Natural progression in a programming language

If you have learnt coding on any language, you would have experienced this progression yourself.

The first stage of learning a language is to understand its syntax, ways to print output, doing mathematical calculations etc. At this stage, you typically don’t do any thing complex. This usually isn’t very exciting or helpful. After all you don’t learn a programming language to calculate 2 + 3!

The second stage of learning a language is when you start using variables to accomplish some of the more complex tasks. With use of variables, you can create generic logic, which are more complicated and useful. So it you want to compare 2 columns with a million numbers each, you can’t do that unless you use variables (May be you can, not me!). The beauty of the process is that once you learn use of variables, it is impossible to think that there was even a stage 1 involved in the learning process!

There are some more stages in learning a new programming language, but for today’s article we will focus on what I just mentioned as second stage of any programming language — the use of variables. If you are a complete newbie to QlikView, you should first learn the basics from our learning path — Resource to learn Qlikview from scratch and become an expert

Real life situation — variables to my rescue!

I started using QlikView some time in early 2012 and I fell in love with it. We had a team of passionate developers and over next 6 months (with a lot of dark nights), we were able to replace the traditional clunky excel / access reports on a nice QlikView dashboard for entire Sales process of our organization. We were now the envy of other Intelligence units in our organization and were quoted as a successful transition across the Organization.

In September 2012, the Sales Director called me. This was the time of sales planning for next year. So, I expected him to ask the plans for the same. And I was well prepared for this. Over years, we had created an Excel based application which would simulate a whole lot of scenarios and then spit out (literally) the targets for all the sales people.

Then came the bomb! The Sales Director said that this time he wants the planning to happen on our QlikView application. He said that he wanted a new sheet in our dashboard, where the sales people could enter parameters for their efforts (e.g. Taking 10 sales calls a day) and the application provides an estimate of where they would end taking seasonality and past trends in account.

I had no clue how to do this in QlikView, so I just listened to him. I didn’t want all the efforts from last 6 months to end and the people to go back to older ways of running business intelligence. Thankfully, I came across variables and their use to run what-if scenarios, which we went on to build in our application.

What is a Variable? What are the methods to create it?

Variables store data (static or calculated) of any type (numbers, string, boolean, float) and get referenced using expressions or directly by using its name. There are a few ground rules of variable naming convention in QlikView:

Whenever we create a variable for static value, we prefix variable name with “ v “ (lowercase).

Whenever we store an expression in a variable, we prefix variable name with “ e “ (lowercase).

After the first character, we should keep relevant names so that it is developer friendly. There are various methods to create a variable. The commonly used are:

Method 1: By going into menu -> settings/variable overview

Method 2: By using SET and LET statements.

Let’s look at them one by one:

Method 1:

Go to Setting menu -> Click Variable Overview

Note: You can define a variable with an expression also.

Method -2:

Whenever you start a new document and move to edit script, you must have noticed that there are some predefined values which QlikView loads based on your system configuration. Right ?

This is because, these are the variables that QlikView creates using Set Statement. You can also use “ Set “ statement for defining variable(s) in script.

Let’s see how do we do it. Look at the below syntax of defining variable:

Syntax: Set Variable_Name=Value;
Example: Set vPrevYr='2014';

Another method of variable creation is using “ Let” statement. Let statement evaluates the expression on the right side of ‘=’ and assigns it to the variable on the left side of ‘=’.

Syntax: Let Variable_Name=Expression;

Example: Let vTest=5+8;

If you will use Set statement to store expression output in a variable, it will store the expression as a value. I’ll show the detailed effect of defining a formula with Let and Set statement in the coming up sections.

Example: Set vTest1=5+8;

In the screenshot below, you can see all the defined variables(vPrevYr, vTest, vTest1) after running script. Similarly, new variables can also be defined / found here.

Also Read: How to implement incremental load in qlikview?

How can we access variable?

The value of a variable can be accessed using equals (=) sign. If the variable is prefixed with equals ‘ = ‘ sign, QlikView tries to evaluate the value as a formula (QlikView expression) and then displays or returns the result rather than the actual formula text.

Let’s understand it by accessing above created variable “ vTest” (created using Let) and “ vTest1 “ (Created using Set).

Let’s create a text box object and put an expression as “=vTest” and similarly in another text box, we put “=vTest1” (as shown below).

Just notice that, the variable (vTest) created using “ Let” has got evaluated well. On the contrary, variable (vTest1) created using “ Set” hasnot been evaluated. This is the difference between creating a variable using Set or Let statement.

Now, to evaluate variable vTest1, we can use Dollar Sign Expansion (DSE). It is method to evaluate a formula. Let’s look at this method of using dollar sign expansion. Also, this is also a standard way of accessing a variable in QlikView.

Syntax: $(variable_Name)

Now, I’ll access variable “ vTest1″ and check the output.

Also Read: The concept of synthetic keys in Qlikview — Simplified !

What are most Common Uses of variable(s)?

Variables are the mainstay of data visualization softwares like Qlikview, D3.js etc. Optimizing the use of variables always reduces application development time. Let’s look at the some common uses of variables:

1. Portability: Variables are used when we want to store values and use them in multiple objects / charts without wanting to type those values again. Variables are also used when we want to perform dynamic calculations. For example: A variable created for representing previous year as “vPrevYr=$(=Max (Year)-1)”, refers to a dynamic calculation as per current selection and can be accessed by all objects of the document.
2. Use in Chart Objects: We can make use of variables in charts at multiple places using:
3. What-if analysis: We can use variable in what if analysis expression also using “Input Box” or “Slider Object”. In such cases, we take value of variable from these objects (Input, Slider) and used them in expression.
4. In expressions (Set Analysis and Aggr): You can use variable in advance expression like Set Analysis, Aggr() and others. For more detail of these expression you can follow these articles (Set Analysis, AGGR())

Apart from the uses mentioned above, we can also use variable for incremental data load and in various scripting methodology to improve our data models.

End Notes:

In this article, we looked at the importance of variables, methods to create it and the common uses of variable in daily development. Basically, we have looked at two methods (Set & Let and Variable Overview) to create them.

If I summarize the most common use of variable, then it would be expression development using other charts objects like Input box, Slider, Text Box, Buttons and others. My advise to you would be using the variable in your dashboard. It reduces the static nature of dashboard and can change the value across the expression by changing it at one place only.

I hope this article helped you to get the detail of variables in QlikView. We would love to hear about it from you. Did you find it useful? Feel free to post your thoughts through comments below.

Originally published at https://www.analyticsvidhya.com on June 18, 2015.