How to Get QlikView Pivot Charts to Behave – Everything in its Place

QlikView Pivot Chart - Featured Image - Kitchen Utencils

The QlikView Pivot Chart is an essential tool in the BI tool set. We will explore some tips to quickly set the layout to your preferences.

The Problem

“Is there an easy way to fully expand and fully collapse my pivot chart?” or its cousin,“Can I just press a button to get the pivot chart back to a default?”

These are relatively frequent questions that I never had fully satisfactory answers for.

Before we get into the tip, I used to recommend Right Click –> Collapse All or Right Click –> Collapse Dimension Columns to collapse the chart and Right Click –> Expand All to expand columns (one-by-one).

QlikView Pivot Chart - Properties - Collapse Dimension Columns

That solution is not great because first, if you are expanding the chart all the way out, you still have to do it one column at a time.  But the biggest problem presents once you pivot a dimension horizontally.  At that point, you cannot collapse the pivoted column without first un-pivoting the column.

Solution – The QlikView Layout Bookmark

The solution to this is actually pretty neat.  It involves a special kind of bookmark – The Layout Bookmark.  Our example will simply involve a collapsed state and a fully expanded and pivoted year column state.

First create a pivot chart and manipulate the columns so that none are pivoted and all are collapsed.

QlikView Pivot Chart - Properties - Collapsed Chart

Then we simply create a document bookmark Bookmarks –> Add Bookmarks as follows.

QlikView Pivot Chart - Properties - QlikView Layout Bookmark Creation

Notice that we have unchecked “Include Selections in Bookmark”.  We want to be able to invoke this bookmark without affecting the current selections.  The only thing this bookmark will change will be the “layout state” of the application.

Next, expand out all the columns and then pivot the year column horizontally.

QlikView Pivot Chart - Properties - Expanded Pivot Chart

 Create another layout bookmark.

 

QlikView Pivot Chart - Properties - Bookmark Creation 2

Now we need to create a couple buttons that will simply invoke each of these layout bookmarks.  But first, we need to know the id’s.  Go to Bookmarks –> More…

QlikView Pivot Chart - Properties - Bookmark Maintenance

There you can see that the id’s for these bookmarks are BM01 and BM02.  Take note of them.

Finally, create the buttons.

You can name the button whatever you want and then for the button action, use Bookmark –> Apply Bookmark along with the id for the bookmark you with to trigger.

QlikView Pivot Chart - Properties - Button Properties

Here is the finished product.  Hitting the buttons will expand or collapse the chart to the desired setting.

QlikView Pivot Chart - Properties - Collapsed Chart with Buttons
QlikView Pivot Chart - Properties - Expanded Chart with Buttons

Layout Bookmark Tips

There are some complications to be aware of when creating layout bookmarks.

  • The layout state affects the entire sheet.  When you create a layout bookmark, it is taking a snapshot of the entire sheet layout, not just the pivot chart you are concerned with.  This includes minimize/restored settings and cyclic dimensions for all objects within the sheet.
  • The layout state activates the sheet.  This means that if you put this button on a different sheet, it will activate the sheet the bookmark was created on.  This could be good or bad but is important to understand for your use-case.
  • Layout states are hidden in containers.  If your charts are in a container, the layout state for objects within the container will not be saved.

Conclusion

Some of the other things that might be done using layout bookmarks.

  • Default sheet state
  • Set a cyclic dimension to be shown
  • Set a grouped expression to be shown
  • Set restored or minimized objects on a sheet
  • Set which object is shown within a single style container

I would love to hear any other ideas you have for QlikView Pivot Charts. Are there more uses for the layout bookmark?

4 comments… add one
  • Reply Abhijit July 25, 2014, 8:07 am

    awesome ideas….
    Thanks

    ———-
    Abhijit

  • Reply Maxime September 14, 2017, 7:36 am

    Hy,
    I did this before but this solution is not efficient, because the bookmarks set the layout for all the objects in sheet … you can’t manage each Grphics separatly.
    And if you want to add some dimensions to your graphics you have to scratch bookmark and make a new one…

    A better solution is to Hide/Unhide columns in the PivotTable, also this allow you to display as many visualizations you want.

    See my post on this topic : (Maxime Alabert on August 31)
    https://community.qlik.com/thread/105463?sr=stream

    best regards
    Max.

  • Reply Gilson October 19, 2017, 6:28 pm

    Thank you for shared Aaron Couron !! Help me a lot.

Leave a Comment

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

Show Buttons
Hide Buttons