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.



Encryption In QlikView – Securing your data, for whatever reason…

I recently had the requirement to encrypt and decrypt data in a QlikView document using a key. This post details the problem, solution and advanced examples for securely storing sensitive information in QVDs.



The Problem — No Built-In Encryption

Encryption appears to be possible with Expressor, but I was unable to find any documented encryption functions for QlikView Desktop.

There was one discussion on the QlikView community where an individual was trying to implement encryption with a VB script macro, but certain values would not encrypt. I spent a little time trying to get that to work before giving up and taking a few days off from the problem.



The Solution

QlikView has two macro modes — JavaScript and VB Script. Using a self contained JavaScript encryption implementation and a few helper functions in the macro module, it is possible to encrypt and decrypt values in QlikView.

CyrptoJS fit the problem perfectly. Create an empty QVW and copy this into the macro module. Also, add the encrypt/decrypt helper functions below at the end of the macro module.

function encrypt(value, key) {
    return CryptoJS.AES.encrypt(value, key).toString();
function decrypt(value, key) {
    return CryptoJS.AES.decrypt(value, key).toString(CryptoJS.enc.Utf8);

Now QlikView can encrypt and decrypt values using a specified key.

// Setup the encryption key
Let vEncryptionKey = 'Your Encryption Key Goes Here!099';

// Example with variable
Let vName = 'Justin';
Let vEncryptedName = encrypt('$(vName)', '$(vEncryptionKey)');
Let vDecryptedName = decrypt('$(vEncryptedName)', '$(vEncryptionKey)');

// Example with LOAD
    decrypt(EncryptedName, '$(vEncryptionKey)') as DecryptedName
    encrypt(Name, '$(vEncryptionKey)') as EncryptedName
    'Justin' as Name
AutoGenerate 5;

Note the load example will produce 5 unique encrypted values for the same input. This will be problematic for maintaining compression when storing to QVD.


Advanced Examples

To understand the examples below, it is important to understand how QlikView stores data. At a high level, QlikView will keep a list of distinct values for every field in the data model as well as a pointer table that makes up the actual representation of the data.

If the encryption routine returns a unique value for every instance, QlikView will not be able to compress the data set as it normally would.

The code below demonstrates this behavior —

Load * Inline [
First Name, Last Name, SS#, DOB
John, Smith, 123-45-6789, 3/22/1944
John, Smith, 234-56-7890, 5/22/1990
Alex, Moore, 554-76-8859, 12/2/1974
Alex, Johnson, 577-62-9281, 3/22/1944
Stephanie, Moore, 112-24-1988, 8/21/2003

Store UnencryptedDataSourceWithDuplicates Into UnencryptedDataSourceWithDuplicates.qvd(qvd);

Qualify *;

    encrypt([First Name], ‘$(vEncryptionKey)’) as [Encrypted First Name],
    encrypt([Last Name], ‘$(vEncryptionKey)’) as [Encrypted Last Name],
    encrypt(Text(Date(DOB, ‘MM/DD/YYYY’)), ‘$(vEncryptionKey)’) as [Encrypted DOB],
    encrypt([SS#], ‘$(vEncryptionKey)’) as [Encrypted SS#],
Resident UnencryptedDataSourceWithDuplicates;

Unqualify *;

Store EncryptedDataSourceWithDuplicates Into EncryptedDataSourceWithDuplicates.qvd(qvd);

By building mapping tables for each field using the distinct field values and their encrypted value, we can ensure QlikView does not store multiple encrypted values for the same input.

For i = 1 To NoOfFields(‘UnencryptedDataSourceWithDuplicates’)
    Let vField = FieldName($(i), ‘UnencryptedDataSourceWithDuplicates’);
    Let vMapTbl = ‘$(vField) Encrypted Map’;

    Mapping Load
        Distinct [$(vField)] as key,
        encrypt(‘$(=[$(vField)])’, ‘$(vEncryptionKey)’) as value
    Resident UnencryptedDataSourceWithDuplicates;

Qualify *;

    ApplyMap(‘First Name Encrypted Map’, [First Name], ‘ERROR’) as [Encrypted First Name],
    ApplyMap(‘Last Name Encrypted Map’, [Last Name], ‘ERROR’) as [Encrypted Last Name],
    ApplyMap(‘SS# Encrypted Map’, [SS#], ‘ERROR’) as [Encrypted SS#],
    ApplyMap(‘DOB Encrypted Map’, [DOB], ‘ERROR’) as [Encrypted DOB],
Resident UnencryptedDataSourceWithDuplicates;

    ApplyMap(‘First Name Encrypted Map’, [First Name], ‘ERROR’) as [Encrypted First Name],
    ApplyMap(‘Last Name Encrypted Map’, [Last Name], ‘ERROR’) as [Encrypted Last Name],
    ApplyMap(‘SS# Encrypted Map’, [SS#], ‘ERROR’) as [Encrypted SS#],
    ApplyMap(‘DOB Encrypted Map’, [DOB], ‘ERROR’) as [Encrypted DOB]
Resident UnencryptedDataSourceWithDuplicates;

Unqualify *;

 // This should be roughly the same size as the UnencryptedDataSourceWithDuplicates.qvd
Store EncryptedDataSourceWithDuplicatesAndDataCompressionQvd Into EncryptedDataSourceWithDuplicatesAndDataCompression.qvd(qvd);

Drop Table EncryptedDataSourceWithDuplicatesAndDataCompressionQvd;


To recap, it is possible to encrypt and decrypt values in QlikView. I hope this post was helpful and informative. If you have any questions, please leave a comment.

A copy of the Encryption in QlikView QVW used in the examples above is available on github.


No Comments

Video: Building a background image + shadow objects in QlikView

Recently on QlikView’s demo website I’ve noticed a lot of applications with abstract background patterns with accent colors, as well as shadows used within dashboards to separate different objects and areas. I have created a video to show how these effects can be approximated using just QlikView and Google, without the need for a nice graphics editor. The end result will look like this:




No Comments

Magnetic Fields in QlikView (+ open source QV!)

Recently a zoology study about dog defecation has gone viral. No, seriously.

In an attempt to test dogs’ sensitivity to Earth’s magnetic field, researchers recorded the urination and defecation of a sample of dogs and plotted the angular position of their bodies during these acts against the magnetic field. The results of the study produced great visualizations like these:


Each dot represents the mean angle of a dog across all of it’s observations. I believe the dotted line through the middle represents either the mean of all observations and the arcs some sort of confidence interval or magnetic field spread, but I am not certain.

I reproduced the chart in QlikView using radar charts, with the dotted line as the mean and the highlighted arcs as a 95% confidence interval. Random data was used to produce the following:

Read the rest of this entry »


No Comments

Showing Missing Time Data in QV

A common problem I encounter in QlikView is missing data in trend charts. Take the following table for example:

Division MonthYear Sales
A Nov 2013 100
B Nov 2013 50
A Dec 2013 123
B Dec 2013 43
A Jan 2014 109
A Feb 2014 84
B Feb 2014 60
A Mar 2014 99
B Mar 2014 58

Division B did not record any sales for Jan 2014. Therefore, a row was never entered in the table. If we try to plot this data in QlikView as a line chart, we will see the following:

Multiple Lines

Read the rest of this entry »


1 Comment

QlikView Viz Hodgepodge

Here is an assortment of random ideas I’ve been playing with in QlikView recently:

Visualizing Press Sizes

Imagine you produce cardboard. You want to visualize the different sizes of cardboard you can produce against the different size machine presses you have to make the cardboard. There are two ways to accomplish this.

Scatter Chart

Scatter Chart Read the rest of this entry »


1 Comment

Splitting Trend Lines in QlikView

This post is a follow-up to the Strip Plots post. The Strip Plots post contains more details on the implementation of these charts. See that post for technical background.

Line charts excel at showing trending data. However, when too many lines are plotted at once, the resulting chart can be difficult to interpret:



One way to alleviate this is to split the x-axis so that each line has it’s own axis with the same domain of values. In the above example, that means that dimension A would have an x-axis from 2010-2013, as would dimension B, etc. All of these lines would share the same y-axis. This can be done in QlikView using a scatter chart:

Split Line

Read the rest of this entry »