Chart properties provide the capability to suppress the display or calculation of a column. In this post we’ll explore the difference between those two options and look at some use cases.
The Presentation Show/Hide Column feature was introduced in QV10. The Dimension/Expression Conditional is new in QV11.
The Presentation pane of a Straight Table Chart provides an option to show or hide each individual column. The decision to Show/Hide may also be specified as a conditional. If the condition evaluates to true, the column will be displayed.
Hidden columns are calculated and are “present” as data in the chart. The resulting column is not visible in the rendered chart. Let’s look at a use case for this feature.
Straight Tables expressions may reference other columns in the table as data items.
The Net Sales expression above uses column labels:
=[Gross Sales] – [Sales Tax] – Cost
We can hide the intermediate columns and still use them as data to calculate the “Net Sales” column. This is a useful technique for building up a complex calculation.
The final chart displays only the “Net Sales” column.
The same hidden column technique is available in Bar and Line Charts as well. However, in this case we hide the column by unchecking the Bar (or Line) Display Option on the Expression pane.
Let’s turn our attention to hiding Dimensions. In the example below, if we want one row for each Order we must include a unique field like OrderId in the Dimensions.
What if we don’t want to display the OrderId, but still want one row per Order? If we remove OrderId from Dimensions the table rolls up to a row for each Description value. That is not what we want.
We can get the desired result by leaving OrderId in the Dimensions and hiding the column on the Presentation pane.
A hidden dimension column participates in defining the rows of a chart. This is a useful feature, although it’s utility is not always obvious.
Qlikview Version 11 introduced the Dimension/Expression Conditional property. This is enabled by checking Conditional (on the Dimensions or Expressions pane) and supplying an expression to be evaluated. If the expression evaluates to True, the column will be calculated. If False, the column will not be calculated. If it is not calculated, it cannot be referenced by another expression.
A common use for the Conditional property is to toggle columns on or off in a wide text chart.
The button toggles the variable “vShowDetail” which is tested in the Expression conditional:
Another common use case is the “create your own chart” or “dynamic reporting” where users are allowed to pick Dimensions and Expressions from a list. This property — Dimension/Expression Conditional — is the correct way to implement this. If you instead implement the conditional in the Presentation pane, resources will be wasted calculating values that will not be displayed or referenced.
I find an interesting application for conditional Dimensions in Pivot Tables. I sometimes use buttons or other conditions to enable dimension levels. This presents the same output as Pivot table Expand/Collapse commands but without the clutter of the +/– buttons.
A QVW containing all the examples shown here may be downloaded from Qlikview Cookbook: Tutorial – About Column Visibility.
-Rob
