A scrollable, expandable filter panel for QV 11 – without extensions

A common problem when building QlikView dashboards is where to place listboxes. Often, users want to be able to filter many fields, but there isn’t enough room on the dashboard for all of the listboxes. Some common solutions to this problem are using multiboxes, dynamic listboxes, or creating entire sheets dedicated to housing just filters. I’d like to introduce another out-of-the-box solution to this problem: the filter tree.

Filter Tree

Read the rest of this entry »


1 Comment

Co-post with QlikShow: a Qlik Sense Grid Chart extension

I recently collaborated with Patrick Tehubijuluw of the QlikShow blog on a Qlik Sense application. If you haven’t been to Patrick’s blog before, you are really missing out. QlikShow showcases some of Patrick’s excellent examples of UI/UX and data visualization design in QlikView. Patrick also includes tools to help with your own dashboard designs, which I have found very helpful.

The goal of our collaboration was to redesign a QlikView 11 dashboard of Patrick’s for Qlik Sense. While he focused on the challenge of redesigning the dashboard to fit into the Sense framework, I reproduced a chart from QlikView 11 that is not available in Qlik Sense: the grid chart.


Patrick’s post on designing the dashboard in Qlik Sense can be found here.

The Grid Chart extension, as well as instructions on how to use it, can be found downloaded from GitHub.

UX Improvement: Selections

I want to highlight one portion of the grid chart extension that I believe is an improvement from the QlikView 11 version: the selections. In the original grid chart, selections can be made in two ways:

  1. selecting a value on one axis to select a specific dimension value in a single dimension axis selection
  2. selecting a single bubble to select a specific dimension value in both dimensionsnode selection

However, there is only one way to deselect values in the original grid chart: selecting an already selected value on the axis. This is due to the selection toggle mode. In QlikView and Qlik Sense, there are two possible selection toggle modes:

  • On: when the toggle is on, selecting a value that is already selected in a field removes that selection
  • Off: when the toggle is off, selecting a value that is already selected in a field does nothing

Based on these two options, we can see that the original grid chart has the toggle mode on for the axes selections, but off for the bubble selections. This setup exists to avoid a confusing user experience that can result from having toggle mode enabled on both selections. I will illustrate with a sequence of hypothetical selections.

Let’s assume toggle mode is on for both the axes and the bubbles. The beginning bubble chart state, with no selections:

starting chart

We want to filter the chart by the Dim1 value of B. We click on “B”. Because toggle mode is enabled, and no selections have currently been made, the chart correctly filters to “B”.

B selected

Next, we want to filter by Y, now that we have selected B. There are two options for making this selection: click Y on the Dim2 axis, or click on the giant Y bubble. Let’s click on the giant Y bubble we want, since that is the most natural selection for the user. Because toggle mode is ON for the bubble in this hypothetical scenario, it will have a weird result. It will correctly select “Y” in the Dim2 axis because there are no selections there currently. However, we already have a selection of “B” in Dim1. QlikView will make another selection on “B” in Dim1, and because toggle mode is ON, this selection will actually remove the existing “B” selection. This gives us an odd result based on the user’s behavior of selecting the green bubble above.

Y selected

This result does not make sense based on the intention of the user when clicking a bubble. QlikView 11 solves this problem by turning toggle mode to OFF for the bubbles in the grid chart. This allows the user to always select a single bubble when clicking on it and avoids the scenario above. However, the tradeoff is that selections cannot be removed by clicking the bubbles.

The Qlik Sense Grid Chart improves upon this by incorporating the ability to click on a selected bubble to remove the selection in both dimensions. This functionality results from custom logic used to check the state of selections before determining which action to take whenever a user clicks on a bubble. The end result is a more intuitive and natural selection function for the users.

In a future post, I will provide a tutorial on incorporating selections into Qlik Sense extensions.



1 Comment

Tutorial: How to Build a Qlik Sense Extension with D3

The new Qlik Sense APIs make it extremely simple to build extensions that produce new visualizations. A common source of these visualizations is d3.js, a powerful javascript library for visualizing data.

The following tutorial will show how to create an extension using existing d3 code. The tutorial will go into a bit of detail, so following each step will take some time. However, the steps themselves are simple and without explanation can be completed in under 10 minutes. While the tutorial goes into some granular detail, there are 4 basic steps:

1) Get some d3 code to use

2) Get some test data to use and load it into Qlik Sense

3) Initialize a new extension and set up its properties

4) Insert the d3 code into the extension and modify it to source data from the extension

Read the rest of this entry »



Qlik Sense Extension – Hover Drill Bar Chart


In QlikView, users are very familiar with the functionality of clicking a dimension in a drill group to navigate to the next level of a hierarchy. These drill downs can be simplified in some cases by using a hover to expand the drill down as opposed to a selection. The Qlik Sense extension above is one idea for using hovering as a drill down. Read the rest of this entry »



Extending Qlik Sense Interactivity with a Qlik Mashup

The QlikView and Qlik Sense platforms offer an amazing ability to interactively filter through dynamic data sets. However, the visualizations that sit on top of these data sets have in the past been mostly static.

Qlik Sense has introduced new functionality for interacting with visualizations beyond just filtering the data. Techniques like brushing, panning, and zooming are included that expand upon the data discovery process of QlikView 11. This is a new way to think about data discovery: users can interact with the visualizations themselves, not just the underlying data.

Thanks to the new user-friendly APIs, we can extend interactivity in Qlik Sense even further by building custom visualizations with powerful libraries like D3. These custom charts can improve the user experience and information design through elements like annotation, linking, and instant feedback loops.

I’ve built an example mashup that demonstrates some ideas for interactivity that I’ve seen used in data visualizations outside of QlikView. The source code and deployment instructions are posted on GitHub. For those not using Qlik Sense yet, you can view a sample of the visualization without the Qlik integration here.

Chart Overview

The data set I’ve used for illustration is sales data. The data set includes two metrics: # of sales, and the average discount rate given per sale. The metrics are reported by sales office, which rolls up to region. There is also a Sales Type field to demonstrate Qlik Sense filtering.

The goal of the visualization is to understand how discount rates have changed year over year across offices.


Read the rest of this entry »



Qlik Sense Extension – Network Diagram

I built my first Qlik Sense Extension using code from an old network visualization I put together with d3js. The extension is not the best general, all-purpose extension, but it’s a good starting point for anyone needing to build a network diagram. It includes examples of interactive components in d3 – you can hover over the nodes to brush the graph. This implementation highlights both directly neighboring nodes and second-degree neighboring nodes:

The extension API’s in Qlik Sense are really impressive. With QV11 extensions, I always felt like I was stumbling in the dark as I tried to get them working. Building this Qlik Sense extension was a completely different experience. The API was easy to understand and work with, and I was able to get a solution up and running quickly.

Source code is posted here.




Flat, Dynamic UI Navigation in QlikView

A common requirement in a QlikView application is to custom build a tab row or sub-tab row for navigation on a sheet. There are several ways to build this functionality, with various advantages and disadvantages. One approach is to use block charts to create navigation elements like so:

Nav Elements


There are several advantages to this implementation:

  1. Minimalist, flat styling
  2. Dynamically driven by a loaded field of data, so can be configured easily
  3. The “button” widths are based on the size of the text, so a long string of text for 1 button does not unnecessarily alter the widths of the other elements

This solution is possible with block charts because a block chart can have bars of variable width. The above visual consists of two block charts: 1 for the underline, and 1 for the text.

The solution uses an island table called “SampleList” that loads in a list of the navigation elements. This list box is set to “Always One Selected”. The charts are coded as follows:

Text portion

Chart type: Block

Dimension: SampleList

Expression: =dual(only({1}SampleList),len( only({1}SampleList)))

Background Color: =argb(0,0,0,0)

Text Color: =if(SampleList=SampleList,rgb(50,150,200),rgb(200,200,200))

Values on Data Points: Enabled

All other chart elements, like axes and background color, are disabled or made transparent.


Underline portion

Chart type: Block

Dimension: SampleList

Expression: =len( only({1}SampleList))

Background Color: =if(SampleList=SampleList,rgb(50,150,200),rgb(230,230,230))

Values on Data Points: Disabled

All other chart elements, like axes and background color, are disabled or made transparent.


A few notes

In the Text portion, we use the dual function to put the name of the SampleList on top of the bar, as opposed to it’s width. The width is defined by the length of the text for the button, giving us the correct relative proportions between elements. Because we always have a value selected in this navigation field, we have to use the {1} reference so that our selections are ignored and all the navigation elements are included.

The Underline portion is broken out into a separate chart from the text portion so that we can have better control over the height of the blocks, which equates to the thickness of the underline, as well as the position in relation to the text. Also notice that the grey color for the inactive blocks is lighter than the grey used for the inactive text. This is because the text is much thinner than the block underline and thus requires a darker color to be legible.

One disadvantage of this approach is that the cursor on these buttons is QlikView’s chart selection cross-hair cursor. This may be annoying or confusing to users. For a mobile implementation however, this solution works well since the user will not see the cursor.

Download the example QVW here.




AskQV: QlikView Search Engine

Steve Dark at Quick Intelligence has launched AskQV QlikView Search Engine, “a more useful QlikView search engine,” based on the premise that QlikCommunity searches are too narrow and Google searches are too broad and may return lower quality results.

We have scoured the net for the best QlikView content and blogs maintained by QlikView consultants and Qlik employees. Tutorials, videos, reviews and working applications available for download. These sites power a custom Google search engine that allows you to find what you are looking for from only trusted sources – with none of the dross.

We’re honored have been included in Steve’s curated list of blogs, along with (currently) 32 others.

Try it: AskQV.com



1 Comment

Reusing the QlikView cache (or not) across the front end of a QVW

In a recent post on the QlikView Design Blog (The QlikView Cache), Henric Cronström stated:

The cache is global. It is used for all users and all documents. A cache entry does not belong to one specific document or one user only. So, if a user makes a selection that another user already has made, the cache is used. And if you have the same data in two different apps, one single cache entry can be used for both documents.

Based on my experience, I said:

I would be surprised to find out how the same cache entry can be used across documents, considering that QlikView doesn’t even seem to reuse the same expression grouped by the same dimension in different chart types within one application. Ex. a straight table with Sum(Sales) by Customer and a bar chart with Sum(Sales) by Customer are calculated independently within an application. At least, this appeared to be the case when I tested this in an earlier version of 11.0.

Henric responded:

It should use the cache across documents and my experience is that it does.

If you have a case where the same combination of dimension and expression doesn’t use the cache, there is probably a reason, e.g. that the expression (or a calculated dimension) is written in different ways in the two places. Either that, or you have found a bug. Because it should re-use the cache entry…

It’s been a few years, so I think it’s time for a retest in 11.2 SR5. Here is what I used to generate sample data:

LOAD 'C' & Left(RowNo(),1) as Country,
Left(RowNo(),4) as OrderID,
Round(Rand()*500) as Sales,
Round(Rand()*30) as UnitCost,
Round(Rand()*10) as Quantity

(My laptop smokes, so you may want to generate fewer rows for yours.)

This is my chart definition. All of the charts were created by copying and pasting the first Straight Table, so there is no chance of variance in the writing of expressions.

  • Dimension: Country
  • Expression 1 (Avg Order Sales): Avg(Aggr(Sum(Sales),OrderID))
  • Expression 2 (Avg Order Sales Index): Avg(Aggr(Sum(Sales),OrderID)) / Avg(TOTAL Aggr(Sum(Sales),OrderID))

Sheets on front end:

  1. Landing page (empty)
  2. Straight table with one dimension and two expressions
  3. Copy of Straight table (unlinked)
  4. Copy of Straight table, but changed to a combo chart
  5. Copy of Straight table, with the second expression disabled

The test was to click through the sheets, in sequence, and check the calculation times of the object on each sheet. I did this three times and averaged the results.

Cache results

Two conclusions that could be surprising about how the cache is working:

  • A copy of a chart calculates instantaneously with the original chart cached, while copying the chart and changing only the chart type takes significantly longer, although not as long as the original. In other words, cached results from one chart type do not appear to create the same, instantaneous results in other chart types, despite having the same measures and dimensions.
  • Even I was surprised to find that a copy of a chart with one of the expressions disabled did not reuse the cache from the original chart.

This information, taken in conjunction with the fact that QlikView is not good at recognizing two expressions really mean the same thing, leads me to believe that the cache does little to improve performance across objects within an application, only making a meaningful difference when recalculating a chart based on its own cache.

Again, I am doubtful QlikView is currently finding cache efficiencies across applications, based on these kinds of results–and I can see other areas where optimization work would be better spent. Namely, if they were able to improve cache reuse within an application and better recognize when expressions are written differently but actually the same, customers could perhaps see meaningful gains in performance with no effort on their parts beyond upgrading the server software.


No Comments

The best way to move QVD files quickly

During development, one often saves considerable reload time by having local copies of QVDs instead of using files on network or server locations. (Stored locally in an encrypted volume, of course.)

Your first inclination might be to simply copy and paste the QVDs to your machine, but zipping the QVDs first yields significant compression, which will save you considerable time with larger files. (NB: Depending on the environment, you may want to copy the QVDs, then zip the copies, to prevent locking the originals from being used or reloaded during the zipping process.)

Recently, I wanted to grab three QVDs from a server, totaling 1.55GB. In addition to zipping the files, I was also curious what loading all of them into a single QVW and using QUALIFY * would yield in file size.

QVD Size Reduction

To my surprise, zipping was even smaller than the QVW with compression set to High by about 8MB…thus saving myself from the immediate need to write an “unloading” application, which would binary load that QVW, alias to remove field name qualification, store to QVD, then drop tables from memory.

So next time you are moving files, save some time and bandwidth and zip them first.