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: There’s a great post by d3noob that demostrates how to add thumbnails to (or add thumbnails to gists).

Also worth watching is this video by @enjalot, it shows how to start using

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 [...]

Make a UK polygon map with Tableau

Below is a polygon filled map of London, United Kingdom.

Learn About Tableau

Polygon maps are easy to make in Tableau provided you’re wanting to map US regions or very top level regions in Europe (country level for example). But if you want to make a post code level map in Tableau or map postal areas in Tableau then you’ll become a bit stuck.

Thankfully there is a “hack” that allows you to make your own custom maps that was highlighted by Craig Bloodworth at The Information Lab. The method works by plotting the longitude and latitude of every edge that makes up a polygon. Give Tableau the order that these should be connected (path id) and you’ll be able to draw a custom shaped polygon on a Tableau map. You can even add functionality like parameters to show different metrics on your area fill map.

One sticking point is the longitude and latitude of every UK borough isn’t readily available. Thankfully, the link above contains several Tableau data extracts that are free to download. Each one allows you to map UK data at different levels.

The example I shared at the start of the post shows how you can also map a second data series on top of the polygons. Using a dummy data set, I’ve shown how you might map incidents on top of London boroughs (just an example, obviously you could just shade each London borough according to incidents). The trick to achieving this result is to use a dual axis when dragging the longitude and latitude on to columns and rows for the second series.