Conditional functions in QlikView - Qlik View

There are several conditional functions in QlikView that can be used in the frontend to give our charts a higher level of flexibility in terms of handling and presenting the data, both in expressions and dimensions. Let's go through some examples of these functions to enhance the analysis in our QlikView app.

The If function

Though sometimes neglected because of its high resource usage when compared to other methods (such as Set Analysis, which is covered in the next chapter), the If function has important uses when creating QlikView documents. Essentially, it is used when two or more different outputs should result from a single expression, depending on a condition that is evaluated to either be true or false.
In this section, we'll explain how it works and discuss a use case.

The syntax

As the first step, we should describe the syntax used by this function and the parameters it needs to work. If you've worked with Microsoft Excel previously, then it's very likely you well have come across the If function in a spreadsheet.The syntax of the If function in QlikView is almost the same as that in Excel.The function takes the following three parameters:

A use case

You should be advised upfront that the If function is, in fact, resource heavy. It's often a good alternative to move all calculations based on the If function from the frontend to the script whenever possible and handle the results through calculated fields or flags in the data model. However, there are cases in which the calculation cannot be handled anywhere but in the frontend. Here, we describe one scenario in which you can take advantage of the If function's capabilities.

Heat charts

A heat chart is a cell matrix in which each individual value is color-coded based on a threshold. We will create one of those charts using the Airline Operations document to demonstrate the concept.

  1. From the Airline Operations.qvw document, activate the Analysis tab. Then, click on the Create Chart button from the design toolbar.
  2. From the New Chart dialog window, select the Pivot Table option in the Chart Type section and enter Load Factor % Heat Map in the Title field.
  3. Click on Next and the Dimensions dialog will appear, from which we will add the Carrier Name, Year, and Month dimensions to our new chart. Click on Next to continue to the Expressions dialog window.
  4. From the Edit Expression window, type the following expression:
    $(eLoadFactor)
  5. Click on OK to close the Edit Expression window and, from the Expression dialog, type Load Factor % as the expression Label.
  6. Next, we will define a background color from the expression's attributes. To do that, click on the small plus sign located next to the expression name in the Expressions dialog window. Highlight the Background Color attribute.

    Conditional-functions-Figure

  7. Once the attribute is highlighted, go to the Definition pane to the right and click on the ellipsis button to bring up the corresponding Edit Expression window.
    The expression we will define for this attribute is as follows:

Click on OK to close the Edit Expression dialog.
Notice how we are not recalculating the Load Factor % value but instead referencing the actual expression we created in the previous step, using the label we set. This will help us save some valuable CPU resources.

  1. Next, highlight the Text Color attribute for the Load Factor % expression and, in the Definition pane, type White(). This will ensure that the cell text uses a white color to make it more readable within the three different background colors we previously defined.
    The "conditional formatting" functionality presented here can also be accomplished using the Visual Cues tab of the Chart Properties window. However, the Visual Cues option only supports up to three levels. Using the expression's attributes, we can define a more complex formatting condition, with four or more levels.
  2. Click on Next six times until you get to the Number dialog window; set the following format to our expression:
    • Fixed to 2 decimals
    • Show in percent (%)
  3. Click on Next three times to get to the Caption dialog window and enable the Auto Minimize option.
  4. Click on Finish.

Initially, only the first dimension (Carrier Name) will be visible. Expand the other two dimensions and drag them to the top to create the matrix. The heat map should look like the following screenshot:

Conditional-functions-Figure

To accommodate the new chart into the Analysis sheet, enable the Auto minimize option in the Passengers vs Mail scatter chart. Then, resize and position the new heat chart to occupy the same space as the scatter chart.

Resize and position their corresponding minimized icons as well, just as we did with the Reports tab previously.

A solo exercise

It's time for a little challenge. We've defined the threshold with static limits (0.85 and 0.7). How can we make them variable, and how can we let the users define their own limits?
The key is in the first section of this chapter.

Building a heat chart with the Colormix wizard

In our previous example, we used the Green(), LightBlue(), and LightRed() color functions in conjunction with the If function to define the Background color attribute. Now, we will edit the attribute's expression to make use of the Colormix Wizard option and see if we can come up with a better looking heat chart.

  1. Right-click on the pivot table we created above and select Properties,.
  2. Then, navigate to the Expressions tab and click on the expand icon to reveal the attributes list. Double-click on the Background Color attribute to open the Edit Expression window, in which we will replace the current attribute's definition.
  3. Clear the expression's current content and, from the File menu, select Colormix Wizard,.

    Building a heat chart with the Colormix wizard

  4. The Colormix Wizard window will pop up. Click on Next in the first dialog, which is just informative, and we'll enter Step 1, which is about defining the Value Expression.
  5. Enter the name of the main expression, in our case Load Factor %, enclosed in square brackets. This expression will be used to determine the color and intensity that should be set on each cell.

    Conditional-functions-Figure

  6. Click on Next to move on to Step 2, in which we will define the upper and lower limit specifications. The settings will be defined as follows:
    • The Auto Normalize option will be disabled for us to explicitly define upper and lower limits, instead of using the chart's max and min values
    • The Upper Limit value will be set to 1, which represents a 100 percent load factor level
    • The Upper Limit color will be left as the default green
    • The Intermediate color checkbox will be enabled, with a value of 75 and a blue color
    • The Lower Limit value will be set to 0.5, which represents a 50 percent load factor level
    • The Lower limit color will be left as default red

      Conditional-functions-Figure(5)

  7. Click on Next to navigate to the third step in the wizard.
  8. We will use the default values in the Step 3 window (Enhanced Colors enabled and Value Saturation set to Use Upper (Lower) Color), so just click on Finish for the new color expression to be generated. The new expression, which will be automatically inserted into the Edit Expression window, should look like the following expression:
    This expression uses a combination of different color functions, as well as nested If statements. Even though the expression looks complex, it shouldn't be a problem for us since it is auto generated by the Colormix wizard.
  9. Click on OK in the Edit Expression window to apply the changes and then OK again from the Chart Properties window.

The modified heat map should now adopt the following look:

Conditional-functions-Figure

If we compare the new chart to what we had previously, it's evident that the new look is more dynamic and has additional levels of intensity, thus providing a more detailed insight.

Numeric versus text comparisons

An important consideration when inserting comparisons into conditional functions is that text-based comparisons will be slower than numeric comparisons.
Take the following two expressions, for example:

  1. If(Month = 'January', exprl, expr2)
  2. If(MonthNum = 1, exprl, expr2)

The only difference is that the first expression is comparing the Month value as a literal value, that is, a text, while the second expression is performing the comparison based on a numeric value. The latter will be faster.

Similarly, it's also important to consider that, when defining a numeric comparison value, it shouldn't be enclosed in single quotes (MonthNum = 'l') as it will cause QlikView to treat it as a text-based comparison.

The Class function

Conditional functions are widely used in conjunction with numeric intervals either to find where a specific value falls in a set of ranges, or to group results into predefined bins. The Class function is particularly helpful in these cases.

Take, for example, the analysis we made in a previous section about the number of interstate routes per carrier. The initial chart is:

The Class function

We can group the carriers based on the number of interstate routes they serve, only instead of using the individual number of routes, we can use intervals. Add the following expression to our chart:

Class(Count(Distinct [From - To State Code]), 100)

The result will be the following screenshot:

Conditional-functions-Figure(8)

Essentially, the Class function takes the individual result of an expression, in this case the count of routes, and automatically creates the corresponding bin based on the bin width specified.

The Class function only supports fixed bin widths.

We can take this further to use the Class function into a nested aggregation, by having a new straight table chart with the following calculated dimension:

Aggr(Class(Count(DISTINCT [From - To State Code]), 100), [Carrier Name])

Add the following expression:

Count(DISTINCT [Carrier Name])

The result will be as seen in the following screenshot:

following screenshot

As you can see, this one is easier to read than having all individual values listed in the dimension column.

The dimension values presented just now show the default format for the bin names. This is fixed within QlikView, but we can create our own custom format with the Replace function, as follows:

This expression will result in the following dimension values:

expression will result in the following dimension values

A solo exercise using the Class function

Now that you've seen how to create nested aggregations with the use of intervals, take a moment to create a chart to visualize the number of airlines falling into different load factor ranges with a bin width of 10 percent. Place it into the Dashboard tab.

Conditional-functions-Figure

The fascinating thing about this chart is that when the user clicks one of the bars corresponding to a specific interval, all carriers that fall into that interval will be automatically selected. This selection would be made on the Carrier Name field, as the Load Factor % bins do not exist as a field in the data model. This combination of interactivity, associations, and complex calculations on the fly allows for further navigation and extends the discovery experience.

The Pick function

Another interesting and powerful conditional function available in QlikView is the Pick function. In a way, it can be said to act as a simplified nested If. The parameters this function takes are:

Pick(n, exprl, expr2)

Where n is an integer number that determines which of the subsequent expressions should be evaluated. exprl is an expression to be evaluated when n= 1 and expr2 is an expression to be evaluated when n= 2

The same result of the Pick function can be accomplished using a nested If; for example:

If(n = l,exprl, If(n = 2, expr2))

However, we can easily see that the Pick function is much simpler to use in this case and can even be lighter in terms of resource usage.

Let's look at a practical example.

Using Pick with Dimensionality

One of the examples we described using the Aggr function required us to make a sub aggregation in the chart's expression to obtain the average load factor per airline per route. In the example, we used only one chart dimension, Airline, and the corresponding Aggr function only had two fields in the dimensions parameter: Route and Airline. However, the defined expression will not work as expected when a new dimension is added to the chart.

For instance, if we were to use a pivot table with several different dimensions, and in which the active dimensions are dynamically being expanded or collapsed, the sub-aggregation used to calculate the average load factor should be adapted with each new dimension arrangement; the correct aggregation expression will depend on which dimensions are visible in the pivot table.

To account for the different possible arrangements in the chart's dimensions, we will make use of the Pick function in conjunction with the Dimensionality function and the Aggr expression we previously used.

The Dimensionality function is used in pivot tables to indicate which level of aggregation is active in the pivot table for each of its segments or rows. For instance, if all dimensions are collapsed and only the first dimension is visible, then the Dimensionality function would return 1; if the first dimension is expanded, the Dimensionality function would return 2, and so forth.

The result of the Dimensionality function is row-specific, so we could have one row with one level of aggregation (depending on which of its dimensions are expanded) and another row with a different level. The Dimensionality function will account for each rows' aggregation level to provide the correct result. The following screenshot illustrates this concept, with the result of the Dimensionality function presented as the second expression column and color-coded for easier understanding:

Conditional-functions

So, to approach the presented scenario (of course, this will only keep working when users do not modify the pivot table by dragging dimensions), start by activating the Aggrsheet in the workspace and creating a pivot table with the following dimensions: Flight Type, Carrier Group, and Carrier Name, in that order. Then, enter the following expression:

Label the created expression as Avg Load Factor per Route. Then, create another expression and enter the following:

$(eLoadFactor)

Label the new expression as Direct Load Factor. Then, navigate to the Presentation dialog window and enable the Show Partial Sums option for all three dimensions.

The resulting chart will be:

Conditional-functions-Figure

We can compare the result from both expressions in this screenshot. A higher result in the Direct Load Factor % column means the low-occupancy routes have just a few flights that don't affect the overall result. However, the impact of those routes can still be seen in the Avg Load Factor per Route column since that's where all routes are equally accounted for, no matter the amount of flights performed.

For each Dimensionality level, the chart depicted earlier is using a different sub­aggregation to calculate the average load factor per route. Additionally, when Dimensionality is zero (the total row at the bottom), the chart is calculating the average for all routes, all carriers, and all flight types. We used Dimensionality() + as the n parameter of the Pick function because otherwise there would be no way of adding an expression for when Dimensionality() equals zero.

Since the expression's definition is based on a certain, predefined, arrangement of the dimensions in our pivot table, we must be cautious when using an expression like this because it will yield unexpected results when the dimensions are re-ordered by the user (for by dragging the corresponding columns).


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Qlik View Topics