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.

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:

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 Variable1. 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.

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:

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 vTop variable 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 previous configuration is shown in the following screenshot:

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.

After finishing setting the previous configuration, click on OK and the chart will be more readable and will look like the following screenshot:

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

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:

We will describe the two of them and their uses.

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.

Note

It's important to note that this object can be used not only to 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 vTop variable 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 30.

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.

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.

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])

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.

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.

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 7, 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.

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.

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.

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 vTop or vTotalFlights variables) 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:

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:

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:

1. Open the Variable Overview… window (Ctrl + Alt + V) and create a new variable. Name it eDeparturesPerformed_VarUnit.
2. The variable definition will be:

   Sum([# Departures Performed]) / $1

3. In the Comment field, enter Variable to calculate the number of departures performed with variable divisor.
4. 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:

1. The first expression, labeled # of Flights, will be:

   $(eDeparturesPerformed_VarUnit(1))

2. The second expression, labeled # of Flights (thousands), will be:

   $(eDeparturesPerformed_VarUnit(1000))

3. The third expression, labeled # of Flights (millions), will be:

   $(eDeparturesPerformed_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:

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 30, 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.

1. First, make sure the following expression variables are already in the Airline Operations document. If the variables are not yet defined, refer to Chapter 7, Building Dashboards, to find their definitions and apply them:
   • eDeparturesPerformed
   • eEnplanedPassengers
   • eAirTime
2. 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:

LOAD * INLINE [
    Metric Name, eMetric
    # of Flights, eDeparturesPerformed
    Enplaned Passengers, eEnplanedPassengers
    Air Time, eAirTime
];

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.

1. Reload the script to add the new table to our data model.
2. Then, we will create a new listbox based on the Metric Name field and place it in the Variables sheet.
3. 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.

   Note

   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).

4. 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.

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 3, 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.

Note

Notice how we have also set a dynamic chart title that changes according to the metric selection.

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.

Tip

Be sure to save the changes we've made to the Airline Operations document before continuing.