Data Preparation for Qlik Sense

Today, Capventis have made my latest book, Data Preparation for Qlik Sense Desktop using Pentaho Kettle, available for free download:

So, what is data preparation and why does anyone need it?

For those of us that have some expertise in QlikView and Qlik Sense development, we probably don't need to worry about this at all. This is because we already do our data preparation using the Qlik script. All of the data loading, joining, mapping that we do is all data preparation. A lot of us are really very good at using the script to manipulate data to meet the needs of business users.

There are, however, many potential users of QlikView and Qlik Sense who are not adept at scripting. To even tell them that they need to use script to load data will make them turn and run! But they are certainly happy to drag and drop files from one place to another and can handle setting properties in dialog.

For that population, the new feature in Qlik Sense Desktop of being able to drag desktop data-sources into an application makes it really easy to create the self-service analyses that they need to create. But that feature - even with announced changes to the data loader in Qlik Sense 2.0 - cannot really handle more complex loading and transformations, we need to start thinking of the script again.

That is where graphical data preparation tools come in. They enable business users to perform those more complex load and transforms in a graphical environment without having to learn any scripting. They can output a single file that can be dropped into a Qlik Sense app.

There are several Data Preparation tools on the market that have working plugins to extract data into QVX format that can be read into QlikView or Qlik Sense. Leading tools such as Lavastorm and Alteryx will also have server based options and integrations to advanced analytics engines like R.

I went for Pentaho Data Integration (PDI/Kettle) for this project because it is open source and the Community Edition is free - just like Qlik Sense Desktop. Once you have some experience with one, it makes it easier to transition to another. PDI doesn't have an out-of-box output to QVX, but output to Excel is usually good enough for most business users. For the more technical amongst you, there is Ralf Becher's excellent solution to stream data from Pentaho Kettle into QlikView via JDBC.

The eBook is about 80 pages and comes with support files to help you try out the exercises. Feel free to download it now.

---

Stephen Redmond is author of Mastering QlikView, QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a Qlik Elite Partner.
 

Explaining Pie-Gauges

Back in December 2013, I discussed different KPI approaches and introduced the Pie-Gauge as a form of representation. I used them in the dashboard of the winning app in the recent Qlik UK partner competition:

Last month, I described how happy I was that QlikView 11.2 SR10 was released so that we could change the segment color in a Pie chart - making Pie-Gauges look better.

Prior to that, and indeed in my entry to the partner app competition, I had used an extension object for creating Pie-Gauges. This is published on Branch.

So, what, exactly, is a Pie-Gauge anyway? It is a gauge because it is representing a KPI - one value versus a target value. However it solves one issue with gauges in that I don't ever have to worry about the scale. This is especially good when I may have several KPIs. Pie-gauges are all about ratios.

Like all pie charts, it is a part-to-whole comparison. In this case, there are always three segments, two of which are mutually exclusive, making up the whole.

The first segment represents the amount by which we have fallen short of the target. The second segment represents the lower of the target value or the actual value. The third segment represents the amount by which we have exceeded the target.

We can see that the whole is therefore the higher of the target or the actual value. We can also see that the first and third segments cannot exist together - we can't fall short and exceed the target at the same time.

The positioning of the segments is important. The first segment must be to the left of the top of the pie, and the third segment must be to the right - signifying below and above target.

The great thing about these gauges is that they will work no matter by how much we have fallen short or exceeded the target - they are always a part-to-whole comparison. Unlike gauges with fixed axes, they will just work.

The really important thing to grasp is that the actual % above or below target is not important!  It is the representation of the ratio that is important. It is whether we are above or below target, not by how much, that we are representing.

The three values can be very easily calculated using Qlik's RangeMin and RangeMax functions.

The first segment is:

RangeMax(Sum(Actual)-Sum(Target),0)

The second segment is:

RangeMin(Sum(Actual),Sum(Target))

The third segment is:

RangeMax(Sum(Target)-Sum(Actual),0)

In QlikView, we can have these as three separate expressions in a Pie chart. In Qlik Sense, we can only have one expression so I use a ValueList dimension and an expression like this:

If(ValueList(' ', '  ', '   ')=' ',
RangeMax(Sum(Actual)-Sum(Target),0)+0.001,
If(ValueList(' ', '  ', '   ')='  ',
RangeMin(Sum(Actual), Sum(Target))+0.001,
RangeMax(Sum(Target)-Sum(Actual),0)+0.001
))

Note the +0.001 on each - that stops Qlik Sense displaying the "chart contains zeros" message. The spaces in the value list are there just to stop additional text being displayed on the Pie.

The color can then be calculated like this:

If(ValueList(' ', '  ', '   ')=' ',
LightBlue(),
If(ValueList(' ', '  ', '   ')='  ',
RGB(240,240,240), LightGreen()
))

Have fun with Pie-gauges.

---

Stephen Redmond is author of Mastering QlikView, QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a Qlik Elite Partner.
 

The vaccine effect

Sparked by Alberto Cairo's tweet sharing a blog on Recreating a famous visualisation, I decided that I would go onto the Project Tycho website and grab the data myself to play with in Qlik Sense.

It was, of course, fairly quick to import an Excel file into Qlik Sense Desktop, adding a CrossTable command to break out the data by State.  First visualization, one that I usually turn to to see what I have, was the bar chart:

By adding a color expression to highlight bars up to 1963, when the measles vaccine was introduced, versus those after 1963, the data jumped out very quickly.

I used the new pivot table in Qlik Sense to recreate the WSJ heatmap, along with similar color rules to those used by Mick Watson:

I also decided to have a look at the geographic spread, both before and after the vaccine:

Vaccines are really amazing. Just to think that just over 50 years ago, people were dying from a disease that, for most of us now just doesn't exist.

Worth thinking about.

---

Stephen Redmond is author of Mastering QlikView, QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a Qlik Elite Partner.