How To Create A Variable In Qlikview

Simply put, variables in QlikView are used to store data, either static or dynamic, and they can contain text, numbers, or any other data type. They are stored as a separate entity and are given a name to be able to reference them from any object in the whole document.

Even when one variable can store a single data value, their use can be extended to a much broader scope once we understand its inner workings.

At a general level, we can say that variables in QlikView are used in two different ways:

To store a value or string either static or based on a formula. This type of variable can also be used to receive and interpret input from the user.
To store an expression definition that can be used in charts. This is an approach we explored in Chapter Building Dashboards.

The main difference between the previously mentioned options is that one calculates the output before sending it to the sheet object that makes use of the variable, whereas the other stores only the definition of the expression and the object using it is in charge of evaluating it and getting a result.

In this section, we will cover the use of variables up to an advanced level, but let's first get the basics in order and move on from there.

Creating a variable

We've already worked through the process of creating a variable in a previous chapter, and you've seen that it is fairly simple. However, let's make a quick review of the steps involved in using our Airline Operations document. Open the corresponding QVW file and create a new sheet so that we don't mess up the objects already created. Name this new sheet Variables.

After practicing with some examples in a separate sheet, we will apply the learned concepts by extending our Dashboard and Analysis sheets.

Now, go to the Variable Overview window by using the keyboard shortcut Ctrl + Alt + V or by clicking on the Settings menu and selecting the Variable Overview. option, as shown in the following screenshot:

Creating a variable

From this window, we are able to see the variables that have been previously defined. Click on the Add button and the New Variable dialog will appear, where we will type the name we want to assign to our new variable.

The default name is Variable! Change it to vTop and click on OK. The variable will be created and we will now be able to assign a value to it. To do that, highlight the new variable by clicking on its name and, in the Definition pane on the lower part of the window, type the number 5. Then, in the Comment field, add the following comment to describe what the variable is for:

Variable used to dynamically change the number of displayed values in a Top N Chart.

It's important to first highlight the variable by clicking on its name before entering the variable's definition, otherwise the definition we enter will not be correctly applied.

Using variables in charts

Our new variable will, for now, contain a simple and static value. We will use that value to manipulate a chart. Specifically, this value will represent the number of carriers that should be shown in a bar chart, based on their number of flights. Only the top five carriers shall be shown.

Create a new bar chart with Carrier Name as dimension and Sum([# Departures Performed]) as the expression. From the Sort dialog window, make sure the Carrier Name values are sorted by Y-Value in descending order.

Our new chart should so far look like the following screenshot:

Using variables in charts

As you can see, the chart is a little clogged with bars in the limited space we have available. We need to limit the number of bars shown so that only the first N Carriers are visible. N is the number that our vTopvariable holds.

Go to the Chart Properties window and activate the Dimension Limits tab. Enable the Restrict which values are displayed using the first expression checkbox. The configuration for the limit we will set is:

The Show only radio button must be selected
From the drop-down list, the Largest value will be selected
On the Values field, we will enter our variable using the following syntax:
$(vTop)
The Show Others checkbox should be disabled

The previous configuration is shown in the following screenshot:

Using-variables-Figure

The syntax we used to enter the variable is called Dollar Sign Expansion. In this particular case, the variable could have been entered without the dollar sign, but there are some circumstances in which it is a must, so it's a good practice to always include it. We will talk about this syntax later in the chapter.

Using-variables-Figure

Change the chart's Orientation to Horizontal from the Style tab of the properties window so that it's even more readable.

Interactively changing a variable's value

The reason for using a variable to limit the number of dimension values shown in the chart is to enable the user to dynamically change it as pleased. There are two main layout objects through which a variable's value can be changed:

The Input Box object
The Slider object

We will describe the two of them and their uses.

Using the Input Box object

The Input Box is basically an Excel-like cell on which the user enters values. An Input Box can hold any number of variables, each one with its own associated cell.

To describe how it is used, click on an empty space of the sheet workspace and, from the New Sheet Object section, select Input Box....

The New Input Box dialog window will appear and the General tab will initially be active. From the Title field, we can assign a display text to be placed on the object's caption bar. Type Enter number of top values into this field.

In the Available Variables list to the left, we will see all created variables. There is also a button to create a New Variable, which is very convenient if we previously forgot to add the variable we want to use.

We are going to add the vTopvariable to the Displayed Variables list on the right. Do this by highlighting the variable's name and clicking on the Add >button. Once the variable is in the Displayed Variables list, highlight it and the Label field, located below, will be enabled. On this field, replace the name of the variable with Top Values.

There are other options that can be set in the rest of the tabs, but we can click on OK at this point and the object will be created with just what we need.

Most of the other tabs hold settings that are similar to those used in other sheet objects, such as charts. For instance, we also have the Presentation tab or the Layout tab. We invite you to explore those tabs and change their settings based on what you've learned from previous chapters.

When the object is created, the input cell will have the number 5, which is the value we previously defined for our variable. If we click on the value cell, we will enter into edit mode and be able to change the variable's value; upon doing so, the chart will be instantly updated to reflect the change.

Change the variable's value to 10 using the Input Box object and see the effect on the chart.

Resizing the Input Box object is achieved using the method previously described for resizing a multi box, which consists of simply resizing the label cell and the value cell individually.

Using the Slider object

Similar to the Input Box object, the Slider object is used to interactively change a variable's value from the frontend. The main difference is the way in which the user interacts with the object. The Slider object is a little more visual.

To create a Slider object, right-click on an empty space of the sheet workspace and, from the New Sheet Object section, select Slider/Calendar Object....

The New Slider/Calendar Object dialog window will appear and the General tab will be active. For a Slider/Calendar object, the Input Style option can be set to either Slider or Calendar. The Slider object is the one we will be using in this example, since the Calendar object is used to work with date values.

It's important to note that this object can be used not onlyto interact with variables, but also to make field selections.

From the Data section, make sure to activate the Variable(s) radio button and select the vTopvariable from the drop-down list. The Mode and Value Mode settings will be left with the default options.

We must set min and max values to delimit the slider's range of possible values. Set the Min Value option to 5 and the Max Value option to 3 0.

To specify that only integers should be used in the slider, enable the Static Step checkbox and set its value to 1.

Click on OK and the slider will be created in horizontal form. It can be changed to vertical, if desired, from the Presentation tab of the slider's properties.

Using the Slider object

The initial value the slider will hold is the one we previously set the vTop variable to. To modify the value, click on the thumb tack and drag it to its desired value. The chart will automatically reflect any change made to the variable and, at the same time, the value stored in the Input Box object we previously created will be kept in sync with the Slider object, since both objects are using the same variable.

Using variables based on expressions

In the previous section, we used a variable to store a static value. That is to say, the value was not based on a calculation and therefore didn't respond to user field selections.

We will now create a variable with a dynamic value, one that responds to the document state and calculates an output value based on user selections. To keep this example as simple as possible, we will create a value that will hold and return the total number of FTEs based, on current selections, and use that value in a chart expression.

Go to the Variable Overview. window (Ctrl + Alt + V) and click on the Add button. From the New Variable window, type vTotalFTEs to name the new variable and click on OK.

Next, highlight the variable and type the following expression from the Definition pane:

=Sum([# Equivalent FTEs])

Don't forget the equal to sign. This tells QlikView to calculate the variable across all dimensions regardless of the context in I which the variable is used.

Click on OK to close the Variable Overview. window.

We will now proceed to create a new chart in the form of a straight table with Carrier Name as the only dimension and having the following expression:

Sum([# Equivalent FTEs]) / $(vTotalFTEs)

From the Number tab of the Chart Properties window, assign a two-decimal format and specify it to be shown as percentage.

These settings will result in a table with a list of carriers and the percentage of FTEs each of them contributes to the total. Notice we have included our vTotalFTEs variable as the divisor with the Dollar Sign Expansion syntax.

Since the variable holds a single value, all rows in the straight table will be divided by the same number, which represents the total number of FTEs employed by all reporting airlines; the numerator value, which is the number of corresponding FTEs, will be different for each carrier.

FTE % to Total

Since the variable's value is an active calculation, the output value will respond to all user selections, and the chart will be updated to reflect the changes as well.

There are other ways for achieving the above calculation, one of which is by using the TOTAL qualifier. We will describe how to use it in a later section.
Another way is enabling the Relative checkbox in the Expressions tab.

Using variables to store expressions

So far, we've discussed variables that store a single static value and variables whose output value is based on a calculation. Now, it's time to take variables to a new level and expand their usability.

You may remember from Chapter Building Dashboards, how we used variables to store expression definitions and then used them in charts. To expand on the topic, we will quickly review the theory and proceed to discuss the advantages of these types of variables and additional use cases.

Go to the Variable Overview... window and add a new variable, with the name of eFTEs. The contents of this variable will be:

Sum([# Equivalent FTEs])

Add the following comment to the variable:

Total Equivalent FTEs.

Notice the variable definition is almost the same as that of the vTotalFTEs variable we previously created. The difference, the equals sign at the beginning, though small, is in fact huge in terms of impact.

When creating variables intended to store expression definitions, the equals sign must be omitted so that the calculation is performed on the chart side and not as the variable output.

After creating the variable, head on to create a new chart in the form of a straight table with Carrier Name as dimension and having the following expression:

$(eFTEs)

The new straight table will be created with the total number of FTEs for each carrier, just as if we had used a direct expression instead of a variable.

As discussed previously, the main advantage of using variables for handling chart expressions is that, when using the same expression across several sheet objects, it's easier to administer when a new change needs to be made to the expression.For instance, suppose the number of FTEs should now be shown in thousands; in this case, you just add the divisor to the variable definition and all charts are automatically updated to reflect the change.

Sometimes, one single chart requires the use of the same expression to define different properties; for example, to add a text in the chart with the expression result, or to define thresholds with different colors for each. The use of a variable to store the expression will ensure consistency across all these configuration settings.

Variable naming convention

We have now seen the different ways in which a variable can be used to interact with a QlikView document and to handle different chart's calculations. Before continuing let's review a quick guideline on variable naming convention.

It's important, when working with variables, to assign names based on certain "rules" to help better understand what each variable's purpose is, how it should be used, and to better administer them when the list of variables in a QlikView document grows.

The one basic rule in naming consistency is the use of predefined prefixes. For instance, you previously saw how we named the first two variables (vTop and vTotalFTEs) with the v prefix. A v prefix was used in naming those variables because the output is a single value, as opposed to the third variable we created (eFTEs) where we use the prefix e as the variable value is an expression.

Being consistent to this convention will ensure that even when the list of variables is long, any particular variable can be found on the list whether you are looking for an expression or a value-based variable.

The Dollar Sign Expansion syntax

We have been using a particular syntax to reference variables; it's called Dollar Sign Expansion (DSE). Let's quickly describe how and why we should use this syntax.

You can see the role of the Dollar Sign Expansion syntax as that of simply evaluating a variable's contents, that is, calculate (expand) the result of the variable and then return the output value.

We said earlier that, when the variable's output value is simply a plain number (such as the vTopor vTotalFlightsvariables) the Dollar Sign Expansion is not actually required since there is actually nothing to "expand". However, it's good practice to always use DSE even when the variable to be expanded does not require it, because of the following two reasons:

At any moment you might decide to change the variable's definition and modify it to one that does actually require DSE syntax
So that your objects maintain consistency with respect to the use of variables

There are, however, cases in which the DSE is ineffective on its own. When the variable's output value is a text string, the result of the Dollar Sign Expansion will be a null or missing value because a text value cannot be interpreted numerically, so we need to either enclose the Dollar Sign Expansion into straight single quotes or simply not use DSE. An example would be:

Variable name:
vUsername
Variable definition:
=OSUser ()
Variable output (as literal value):
DomainUsername
When called from a sheet object (a text object, for instance) we should use any one of the following two expressions:
='User Name: $(vUsername)' ='User Name:'&vUsername

As mentioned before, using the DSE syntax and enclosing it in single quotes is the recommended approach in the previous example.

Dollar Sign Expansion with parameters

It is also possible to create variables with parameters and then call them via a DSE specifying the parameter's value, thus allowing extended flexibility and reusability of variables.

The way we create a variable with parameters is as follows:

Open the Variable Overview, window (Ctrl + Alt + V) and create a new variable. Name it eDeparturesPerformed_VarUnit.
The variable definition will be:
Sum([# Departures Performed]) / $1
In the Comment field, enter Variable to calculate the number of departures performed with variable divisor.
Close the Variable Overview... window by clicking on OK.

We already have a similar variable, called eDeparturesPerformed, which is used to store an expression that calculates the number of flights performed in thousands. The difference this time is that we are inserting a parameter as the divisor. This parameter is represented by $1, and will allow us to use the same variable to obtain the corresponding expression and calculate the number of flights performed in millions (by defining the parameter as 1000000), thousands (when the parameter is 1000), units, and so on.

To use the above variable in an expression, start by creating a new chart in the form of a straight table with Carrier Group as the dimension and the following three expressions:

The first expression, labeled # of Flights, will be: $ (eDepartures Performed_Var Unit(1))
The second expression, labeled # of Flights (thousands), will be: $ (eDepartures Performed_Var Unit(1000))
The third expression, labeled # of Flights (millions), will be: $ (eDepartures Performed_ VarUnit (1000000))

The only difference in the previously mentioned expressions, apart from the label, are the parameter values inserted into the variable.

After properly formatting the expression values and the chart's presentation, we will have the following chart:

chart's presentation

A variable can have any number of parameters defined, all specified with a dollar sign and a number that indicates the parameter's number: $1, $2, $3, and so on.

When expanding the variable, the parameters are specified by enclosing them in parentheses as a comma-separated list. For example, a variable with three parameters would be expanded as follows:

$(VariableName(30, 20, 50))

Where the values 3 0, 20, and 50 are inserted into the corresponding parameter's position in the variable definition.

Parameters in a variable can either be numbers or text; we can also arrange a parameter to receive a field name to be used in the calculation, or even receive values read from fields.

Double Dollar Sign Expansion

We've already discussed how storing expressions in variables can be a good idea for re-using expressions, easily manage changes in them, as well as for data consistency across sheet objects. We will push this idea a bit further by using those expression variables to allow the user to switch among different metrics at his/her convenience.

First, make sure the following expression variables are already in the Airline Operations document. If the variables are not yet defined, refer to Chapter ( Building Dashboards), to find their definitions and apply them:
- eDeparturesPerformed
- eEnplanedPassengers
- eAirTime
Now, we will add a new island table to the data model via a Load Inline statement. Open the Edit Script window and add a new tab at the end of the script; name it Metrics. In this new tab, we will add the new table using the Inline Data Wizard dialog (Insert | Load Statement | Load Inline):

The corresponding script that will be generated will be:

The first column of the previous table holds the metric names, which the user will be able to select from a listbox. The second column holds the corresponding expression variable names, used to calculate any of the selected metric.

Reload the script to add the new table to our data model.
Then, we will create a new listbox based on the Metric Name field and place it in the Variables sheet.
Once the listbox is created, select one of its values and enter the listbox properties window to enable the Always One Selected Value setting from the General tab. This way, we ensure that we will have something to calculate at all times.
The Always One Selected Value setting can only be applied when there is one selected value in the listbox at the time the properties window is opened. Otherwise, it will be grayed out.
Sometimes, this setting can be removed and might need to be reapplied if the document is reloaded with no data in the corresponding field (for example, in the case of a script error).
We are now going to create a new chart, whose expression will be dynamically changing based on the Metric Name selection. The settings of the new chart are as follows:
- Select the Pie Chart option in the Chart Type section
- Enter the following expression into the Window Title field:
  =[Metric Name] &' by Carrier Group'
- Disable the Show Title in Chart checkbox
- Set the dimension as Carrier Group
- Set the following as the expression:
  $($(=eMetric))
- From the Presentation window, enable the Limit Legend checkmark and set it to 15

Once the chart is created, we will be able to switch its active expression by selecting the desired metric from the Metric Name listbox.

desired metric from the Metric Name listbox

The metric selection based on a listbox works the same as having grouped expressions in the chart to be able to switch them with a cycle button (as described in Chapter Seeing is Believing). However, the listbox selection makes it possible to change the metric on several charts at the same time, something that is not possible with the cyclic expression method.

We are performing two expansions before actually arriving at the calculation we need. The first, inner-most, Dollar Sign Expansion pulls the value from the island table corresponding to the user selection and indicates which variable the second, outer, Dollar Sign Expansion is going to evaluate.

Now that we have reviewed the different ways in which we can use variables in QlikView objects, let's complement the acquired knowledge by exploring other uses of advanced expressions.