Archive for category Miscellany

lasso.js, a D3 plugin for custom drawn brushing

Inspired by Qlik Sense’s lasso tool, I’ve built a lasso tool for D3 that accomplishes similar functionality.

The lasso takes in a d3 selection of elements that are lasso-eligible and allows a user to draw a custom path around those elements. Elements are tagged based on whether they are inside or outside the loops. Custom functions can be defined to do things with these elements, such as style them:

lasso screenshot

The lasso code and documentation is available on GitHub.

View a live example with a scatterplot.



No Comments

Shakespeare’s Work Emails

From McSweeney’s:

There are more things in heaven and earth, Horatio,
Than will fit in our engagement strategy.

Hath ye spied that client feedback? It is an email
Told by an idiot, full of sound and fury,
Signifying nothing.


1 Comment

Introducing senseUtils, a JavaScript library for Qlik Sense web development

After building a few mashups with the new Qlik Sense APIs, I ran into problems that required me to do complex coding around the APIs. These problems may arise again so it made sense to write boiler plate code with the APIs to tackle them in the future. I’ve stored these first few functions into a JavaScript library called senseUtils that I hope can become a repository for web development utilities that make Qlik Sense easier to extend.

The library is available on GitHub here.

The first two problems it addresses are:

  1. removing hypercubes after they’ve been created using the Workbench API; this can be useful for situations like a single page application where you need to disconnect from a dashboard view
  2. defining a visualization callback based on multiple cubes. A multicube function is included that can keep track of multiple hypercubes before calling a single callback function. This can be useful for complex visualizations that are dependent on more than one data source

Please feel free to contribute, whether it be by adding your own functions, reporting bugs, or requesting feature enhancements to the library. The latter two items can be entered in the Issues section of the repository.



No Comments

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

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.




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