Making a d3 map extension for Qlikview

d3 qlikview map extension

How to bring d3 and Qlikview together with extensions

The aim of this post is to demonstrate how to make this. I’ll assume the following:

You have Qlikview Personal Edition 11.2 installed (other editions may work but I haven’t tested) You know a little bit about d3.js and the concept of projections

Along the way, I’ve also realised that you might need a recent version of IE installed (I am using IE11). I think this is because d3.js contains certain characters not recognised by earlier versions of IE.

Step 1 – Make a folder for our new extension:

I’m on Windows 7 and any new qlikview extension I have lives here

C:\Users\[your name]\AppData\Local\QlikTech\QlikView\Extensions\Objects

. So make a folder called “d3Map”.

 

Step 2 – Add the necessary setup files

A Qlikview extension appears to need the following:

Definition.xml Script.js

More on these can be found here

 

. I’ll go through the finer detail of each for this d3 map.

Definition.xml

The contents of this file are as follows:

<?xml version=”1.0″ encoding=”utf-8″?> <ExtensionObject Label=”d3 Map”> <Dimension Multiple=”” Label=”Dimensions:” TargetName=”Dimension” Initial=”” PageSize=”200″/> <Expression Multiple=”” Label=”Expressions:” TargetName=”Expression” Initial=”” PageSize=”200″/> <Initiate Name=”Caption.Font.Bold” Value=”1″ /> <Initiate Name=”Caption.Text” value=”d3 demo” /> </ExtensionObject>

The key thing to note here is that we’re anticipating multiple dimensions and expressions (dimensions and metrics). Script.js is covered in more detail below. It will hold the javascript that takes Qlikview data and makes a visual using d3.

It’s also advised to add a tiny Icon.png for your new extension, but it’s not a must have. Here’s mine. When the time comes, you should be able to see the new d3 map extension like below:

Those who have been using d3 for a while will understand the range of amazing things that can be built, so your Qlikview dashboards should now [...]

Prevent users from entering trailing spaces using custom data validation

Custom data validation can prevent trailing spaces

Data validation in Excel is a much underused tool. If you’re collecting data from people and you want to make sure that the data they enter is accurate then data validation is the way to go. If anyone has ever collected data containing dates from people then you’ll understand just how inventive people can be. To many, a date is a date, but in Excel dates are numeric and people entering “October 3rd 2014″ instead of “03/10/2014″ will make life difficult later on.

I won’t cover all the options for validating data here (they include lists, dates, text length, integer ranges) and instead focus on custom validation in Excel.

Imagine you want to collect surnames from people. This data might then be looked up (using vlookups) against another data set. If someone named John types their name as “John ” (note the trailing space) this could lead to lookups not working as intended if not accounted for.

So here’s how to prevent people from entering data in Excel with trailing spaces. In the Excel ribbon, under “Data” is “Data Validation”. The criteria we need is “custom”. Now imagine you are collecting this data in column B, starting with cell B2. In the “formula” section enter =RIGHT(B2,1)” ”

This works as we are checking the last character (1st from the right) and making sure it’s not equal to () a space ” “.

Chart Withings data using IFTTT, Google Spreadsheets and d3.js

Visualise your quantified self

A few months ago I purchased a Withings Smart Body Analyser. It’s a set of WiFi scales that track your weight, fat percentage, air quality, room temperature etc. I like it a lot, but as good as it is, what I don’t like is the app’s charts have a y axis that doesn’t start at 0. A small change in weight looks vastly exaggerated (to some maybe this is a good thing).

y axis doesn’t start at 0

Withings do have an API, but try as I might, I wasn’t able to get the data I wanted from it. The solution I came up with was to use a combination of the following:

IFTTT Google Spreadsheets Google App Scripts d3.js

So let’s first cover IFTTT. There’s a channel for Withings in IFTTT. So whenever you stand on your scales (a trigger) an action takes place. Such an action might be to make use of the Google Drive channel. This recipe here shows how every time you step on your scales, a new row with your stats will be added to a Google Drive spreadsheet. If you like, you can paste in your previous readings so that new data is appended to your existing records. Only weight and fat data is available, not temperature or air quality.

Next, courtesy of the weekly Dashing D3 newsletter, I came across an article showing how a Google Spreadsheet can be converted to JSON. To make use of their script, you must find your spreadsheet’s key/id in the url of your spreadsheet and the name of the worksheet (default is Sheet1). You may need to publish your spreadsheet before the app works with converting your spreadsheet to JSON. Once working, you should be able to see your Withings data appearing [...]

Using bl.ocks and github

Just a quick note to say I’ve started collating all previous visualisations I’ve mentioned in posts online here: http://bl.ocks.org/danharr. There’s a great post by d3noob that demostrates how to add thumbnails to bl.ocks.org (or add thumbnails to gists).

Also worth watching is this video by @enjalot, it shows how to start using bl.ocks.org:

Recreating popular d3.js charts in Excel

d3.js excel forecast paths

Useful and innovative charting techniques are often demonstrated when someone is stretching the limits of what can be done with d3.js. One that struck a chord with me was this visual below by IPWright83. It shows a custom path and line generator. The chart is useful for time series data that you want to show a forecast for. The graph starts as a line then develops into a path to help highlight areas of increased error in the forecast.

What you might not realise though is that charts like this can actually be done with Excel. Let’s see how.

The trick to my excel version below is to combine a line chart on one axis with a stacked area chart on another axis. The second ‘trick’ is to hide one of the data series in the stacked area chart. The final stage, if needed, involves stacking multiple charts on top of each other.

Stage 1 – make a stacked area chart and hide the ‘bottom’ series

The forecasted section, the path in the original chart, is shown below in the Excel version. It’s just a stacked area chart with two series. The blue series will be hidden by setting its fill to ‘no fill’.

Stage 2 – just add another data series for the data which represents the line that meets the forecast. Move this to a secondary axis and make sure both axis now have the same scale. You should now have something like below.

One disadvantage of this method is that if, like the d3.js version, you have multiple lines then the stacked area hack won’t work quite so easily. The most straight forward solution is…

Stage 3 – when adding a second chart on top of the other just make the background to [...]

Connecting to Salesforce using Tableau Desktop

Problems connecting to Salesforce using Tableau Desktop?

This post isn’t a tutorial, it’s simply a way for me to share a few issues I’ve had connecting to Salesforce data using Tableau. It’s nice to know others are having the same problem as you sometimes!

Firstly, I’m a big user (and fan) of Tableau. For a client I work on they had plans to show live sales data using the built in Salesforce data connection. For some clients this worked really well, with incremental updates happening every 15 minutes showing ‘opportunities’ in the current hour. To do this I had been connecting to a ‘single table’ – the Opportunities feed.

Onto the problems

The issues I have relate to how long it takes to process Salesforce data using Tableau. I understand this may be a fault of Salesforce more so than Tableau, though I’m not sure either way.

When you start the connection to a single table, you get a pop up showing ‘Processing Request’ and ‘Preparing Data’. You’ll also see ’0 rows retrieved so far’. Depending on the amount of data you have in Salesforce (I assume), you’ll see this message for many hours.

Some have suggested showing your log file to Tableau support (C:\Users\[your name]\Documents\My Tableau Repository\Logs) but I find that you simply need to be patient. Incremental refreshes will happen far quicker.

Usually, I found the log files showed “Getting data from server Status: Transferred 0 B Observers: 0″ every 10 minutes for several hours. The 0s looked concerning, as if no data was being processed, but eventually ‘something’ happens and the log files say something different and the process completes.

So, as I said, not a tutorial, but hopefully it will persuade others having the same issues to simply be patient the first time [...]

‘Developing a d3.js Edge’ book released

For intermediate d3.js developers

Developing a d3.js Edge is a welcome addition to the growing series of books for learning d3.js. It’s also, to my knowledge, the first book that assumes some prior knowledge of d3.js.

Developing a d3.js Edge attempts to show you how to make more structured data visuals with less ‘spaghetti’ code. I’ve been using d3.js for over a year now and if I was to make a bar chart, when I came to make it again I would end up duplicating a lot of the code. Developing a d3.js Edge aims to change this by introducing us to reusable APIs that you can make yourself.

The book illustrates this with one main example, the creation of a map for 3 cities with accompanying radial histograms. This tutorial will also cover the topics of brushing and using crossfilter.js (also written by Mike Bostock)

One small complaint is that the source code has line numbers. This made things slightly harder to read on a kindle.

I’ll add much more to this review with an example of what it has taught me once I’ve re-read the book.

The example in the book is available online via GitHub.

Improving Google Analytics Funnel visualisations

Can the way Google Analytics visualises Goal Flow be improved?

Funnels have often been a great source of bad data visualisations. People take the term funnel very literally and often produce things such as below:

Data 2 > Data 1

I don’t have the time needed to tell you everything that’s wrong with this chart. In general though, people like to stick to a funnel shape and to hell with displaying data accurately. Google Analytics funnels can be accessed in GA under Conversions>Goals>Funnel Visualisation. They help show data relating to paths that you wish people to use on your site when achieving a goal (such as downloading a brochure). Google’s standard way for showing funnel data isn’t too bad. It’s fairly clean and you can get a sense for how people are using your site and where they might be dropping off in your conversion paths.

GA Goal Funnel

The above shows you how people start requesting a test drive for a car and how they drop off along the way. There’s useful information here, but what could be improved?

To start with, the left side of the funnel shows entrances. These should add up to 2,546. Google Analytics only shows the top 5 entrances though. A default “Other” could be added to show that total entrances total 2,546. The data is shown here in a table too. Could this not be visualised in the same way that the rest of the funnel is?

The next problem is that the horizontal green bar is the same width in each stage. Surely it should get shorter as people exit the process? The green section shows the portion of people who progress to the next stage. This varies in width depending on the total number of people in [...]