Using Column Visibility

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

Share

8 thoughts on “Using Column Visibility”

  1. Hey Rob,

    Thanks for the nice overview! Very good article!

    A while ago, I’ve been looking into hiding a dimension in a pivot table. There is the conditional field in the dimensions tab but I’d like to keep the level of detail so the dimension should still be calculated. I haven’t found a way to do this so I made the text white and made the column size as small as possible. Any other ways on how to do this?

    Thanks

    1. Use this macro..

      sub Squeeze

      call HideColumn(“CH01”,1)

      end sub

      private sub HideColumn(ch, n)
      set ch = ActiveDocument.GetSheetObject(ch)
      ch.SetPixWidth (n-1), 0
      end sub

      This will squeeze the size of specified charts specified column to 0pts.
      Try on Charts Copy first. Then try on actual chart.

      Regards,
      Pratik

    2. Use this macro..

      sub Squeeze

      call HideColumn(“CH01”,1)

      end sub

      private sub HideColumn(ch, n)
      set ch = ActiveDocument.GetSheetObject(ch)
      ch.SetPixWidth (n-1), 0
      end sub

      This will squeeze the size of specified charts specified column to 0pts.
      Try on Charts Copy first. Then try on actual chart.

      I know its a late reply.. But still..
      Regards,
      Pratik

  2. Hello Rob,

    I have the same question Mathias posted back in April 29th 2014. Just like him, I want the column to be included in the calculation but I don’t need to display it. I also make the fonts white and shrink the column but, it will be nice to do it the right way, unfortunately, I have not been able to find an answer.

    Thanks for all your contibutions to the Qlikview Community, it is very much appreciated.

    (I tried the macro submitted by Pratik but it fails).

Leave a Reply to Pratik Cancel reply

Your email address will not be published. Required fields are marked *