The #MakeoverMonday recap posts often cite the need to keep your charts, text and everything else simple and accessible, to remove any barriers your audience may encounter when first glancing at the dashboard. The use of colour falls into the broader category of simplicity; use it sparingly and use it consistently.

My submission for #MakeoverMonday uses colour consistently in five areas, and this post will go into detail about a couple of those areas, and will touch upon the simpler applications of that colour consistency.

The title

No need for much detail here. It’s obvious when you think about it – if you colour dimension members in your title, then you remove the clutter of a separate colour legend, AND you immediately let your audience understand what they’re looking at, because the title is probably the first thing they see.

Dual-axis chart circles

The circles on the dual-axis DNA / barbell chart are coloured according to the gender of the worker. Again, no need to dally on the methodology here. As my chart was a dual-axis one, I used Measure Names on colour to create this effect, and coloured using a combination recommended on coolors.co.

Tooltips

Nothing clever here either. I have calculations which are gender specific, so it’s logical to colour those in the same way as demonstrated in the title and the circles on the dual-axis chart.

If you align those calculations in the Edit Tooltip window as shown below, then the tooltip functionality (more or less a filter-on-hover) will only colour the Country based whether they have a higher proportion of Male or Female workers.

A couple of the calcs underpinning the (Female Country) are shown here:

Now onto the two areas that I was quite smug about. Simple stuff for many (the majority?) but nice touches that are easy to implement and add to the overall colour consistency.

Colouring the line on a dual-axis chart

A fairly subtle feature of my main chart is that the horizontal line connecting the circles is also colour-encoded. The colour represents whether the Country has a higher proportion of Female or Male workers.

It’s a tiny, tiny detail but I feel it helps to reinforce the story at the Country level. It could be argued to be superfluous given that the right-most circle per Country (when the relevant parameter is set) already shows this, but I think it’s a nice touch.

Doing it, is a doddle:

It’s a boolean calculation (will only return True or False). Where it’s True then I can set the colour to the dark red I am using for Female, else it’ll be coloured green to be consistent with the Male colour used throughout.

This just gets dropped on the Colour card on the Line mark-type card.

This is an example of why dual-axis charts can be so tailorable – you can use different colour encoding for the different elements, but you should exercise restraint when doing so!

Country header is coloured

You can’t colour a header in Tableau based on a calculation. It’s a little bit frustrating, but it’s easy enough to workaround.

For my viz, I wanted to colour the Country names running down the page in the same way as the line connecting the circles on the dual-axis chart. As usual, the solution is simple.

Create a new sheet with Country on Rows and also on Text:

In the image above, I have right-aligned the label, in order for it to sit closer to the dual-axis chart in the final dashboard. How to colour it? Just reuse the [Line Colour] calc from a few images above.

See how the Header remains in a boring black, but the Dimension members are coloured? That’s just how Tableau works. We can just hide the Header to remove the boring black text, and we’re left with our colourful Countries.

In my viz, I had a parameter which allowed users to choose the Sort Order of the DNA chart. Clearly the Sort Order of this sheet has to mirror that logic, or things are going to get messy. That’s simple too:

This is the same as on the dual-axis sheet, and refers to this calculation:

The final detail was to bring the old “Dummy Header” trick into play, to allow me to manually control the point down the page where the first Country name shows (so it aligns with the dual-axis chart). Here’s how the dashboard looks without this trick implemented:

It’s misaligned horizontally because the dual-axis chart has a visible axis AND a hacky axis title above that. Those two components mean that the actual data is plotted further down that worksheet than our coloured Country sheet.

If I switch back to the coloured Country sheet, take a look at this:

The outlined “Countries” is the worksheet title. The data is plotted immediately below that. If I manually type “” into Columns, a dummy header is created:

Now “” only appears on the chart as that is the Field Label for Columns. Right-clicking that and turning it off, yields this:

See how Vietnam appears further down the page than it did before we added in the dummy header? Better still, you have absolute control over the depth of that white space, as you can just hover over the blank header until arrows appear at its lower-most point, and you can then expand or contract the header as required.

So that’s it. No major stuff but simple little tricks in Tableau that allow you to extend your use of colour consistency.

]]>https://learningtableaublog.wordpress.com/2018/09/08/takeaparttuesday-consistent-use-of-colour/feed/0charliehtableauScreen Shot 2018-09-08 at 11.23.00Screen Shot 2018-09-08 at 11.28.29Screen Shot 2018-09-08 at 11.29.37Screen Shot 2018-09-08 at 11.33.25Screen Shot 2018-09-08 at 11.33.14Screen Shot 2018-09-08 at 11.38.06Screen Shot 2018-09-08 at 11.42.02Screen Shot 2018-09-08 at 11.45.08Screen Shot 2018-09-08 at 11.46.56.pngScreen Shot 2018-09-08 at 11.49.24Screen Shot 2018-09-08 at 11.49.35Screen Shot 2018-09-08 at 11.52.25Screen Shot 2018-09-08 at 11.51.23Screen Shot 2018-09-08 at 11.55.34Screen Shot 2018-09-08 at 11.56.45Screen Shot 2018-09-08 at 12.00.04#TakeapartTuesday Bars inside a 100% boxhttps://learningtableaublog.wordpress.com/2018/09/03/takeaparttuesday-bars-inside-a-100-box/
https://learningtableaublog.wordpress.com/2018/09/03/takeaparttuesday-bars-inside-a-100-box/#respondMon, 03 Sep 2018 10:19:43 +0000http://learningtableaublog.wordpress.com/?p=8640Recently I’ve seen a few #MakeoverMonday submissions where individual bars in bar charts are plotted within a “shell” to help the audience understand precisely where the value sits relative to the 100% mark. This week, I was inspired by #MakeoverMonday debutant Ruchika Agrawa, whose viz looked like this:

Whilst I am somewhat renowned for using bar charts, I’ve never created a chart exactly like this. It’s simple to do but I think it adds a great extra layer of audience guidance to what is already an easy-to-understand chart type.

You start by just creating a conventional bar chart. In this instance, we’re looking only at the Top Ten countries, in terms of the % of workforce who are female. The only vaguely complicated element here is the Top N filter:

The key to this chart type is the border around an unseen 100% benchmark. How to do that? It’s easy:

The average of 1 tends to be 1, so by plotting this across the Rows we end up with the equivalent of a 100% value in each Row. If you make this a dual-axis and synchronise, you’ll probably end up with a bunch of Circles as Tableau likes to think it knows best – set the Mark type to Bar on the All Marks Card.

You may now end up with a whole load of 100% values:

That’s just because AVG(1) is “at the front”. If you right-click the axis with the Measure you want to put to the front, select “Move marks to front”, then that Measure will appear on top of the AVG(1):

Now you have some design choices to make about your use of colour and borders. I always err on the side of simplicity, so I’ll elect to make the AVG(1) Measure white, with a black border:

From hereon in, it really is just a case of basic formatting to get things looking like the end product. To get Country as a left-aligned label, just put Country on Label on one of your axes, left-align it and then hide the Country header:

To plot the value at the end of the red bar, just add cell level reference lines and then format them so they’re left-aligned and centred:

And then you’re at the clean-up stage. My tendency to simplify sees me remove pretty much everything that Tableau frustratingly forces upon us by default. No axis ticks or rulers, no gridlines, no zero lines, no Row or Column dividers.

I don’t need to see the axis headers as I’ve labeled the bars directly. However, if the title isn’t specific, I like to let my audience understand what Measure is being plotted, so occasionally use a basic “hack” to create a dummy axis header:

Directly typing into Columns in inverted commas lets me set a “freeform” axis header, which isn’t bound to the title of an actual Measure. I can then right-click it to remove the field label for columns, and then right-click again to align and format the header exactly as I want:

After a few more formatting tweaks, you can end up with a super simple bar chart, but with the additional contextual visual aid of that outline of the 100% mark:

]]>https://learningtableaublog.wordpress.com/2018/09/03/takeaparttuesday-bars-inside-a-100-box/feed/0charliehtableauScreen Shot 2018-09-03 at 10.50.27.pngScreen Shot 2018-09-03 at 10.53.18Screen Shot 2018-09-03 at 10.54.46Screen Shot 2018-09-03 at 11.00.56.pngScreen Shot 2018-09-03 at 11.01.42Screen Shot 2018-09-03 at 11.02.32.pngScreen Shot 2018-09-03 at 11.03.57Screen Shot 2018-09-03 at 11.06.14Screen Shot 2018-09-03 at 11.07.16.pngScreen Shot 2018-09-03 at 11.10.56Screen Shot 2018-09-03 at 11.12.41Screen Shot 2018-09-03 at 11.14.38.pngThe Joy Of Sets – Setting a dashboard to default to the current month (still allowing manual overrides)https://learningtableaublog.wordpress.com/2018/08/13/the-joy-of-sets-setting-a-dashboard-to-default-to-the-current-month-still-allowing-manual-overrides/
https://learningtableaublog.wordpress.com/2018/08/13/the-joy-of-sets-setting-a-dashboard-to-default-to-the-current-month-still-allowing-manual-overrides/#commentsMon, 13 Aug 2018 20:29:34 +0000http://learningtableaublog.wordpress.com/?p=8632A week or two back, I said that I’d do a #TakeapartTuesday each week for the remainder of the year, by selecting a viz from the recap posts shared by Andy and Eva each week.

I lied.

This week I’m doing something different while it stays fresh in my mind. If I hadn’t deviated from this #TakeapartTuesday plan, I’d have referred to this Pareto chart guide and banged one of those out.

So what supersedes the unstoppable juggernaut that is #MakeoverMonday? A practical solution to a real-life work problem. The source of that problem was likely to be a poor initial implementation by me, but I learned something new today when solving the issue, so I thought I’d share it.

SCENARIO

The gist is this – you’re a sales manager. You have a daily subscription to a Tableau dashboard scheduled for 8am every day, which shows you who has been selling what, when and where. Sounds good? It does – but the dashboard was published a couple of months ago and the image of the viz in your subscription email shows an old cached image from months ago. It’s misleading and the sales manager keeps assuming that “the data has broken”.

As an end user it’s also annoying to have to change the date parameter in the dashboard when you click through to the dashboard, in order to set it to the current month. Isn’t the solution just to filter the view to default to the current month? Almost – but the end user wants to be able to override the current month so they can refer back to performance in prior periods too.

SOLUTION

Two parameters and a Set.

Parameter number one. This is a toggle which will be set to ‘Current month’ when the dashboard is published to ensure that the Sales Managers always have the latest view of data when they click through their subscription.

Parameter number two. A monthly stepped Order Date Parameter (in the world of Sample – EU Superstore). This will be used as a secondary input by end users where they want to switch away from the ‘Current month’.

The Set. Here’s where the (simple) *MAGIC* happens:

The Condition formula is easy enough. If the first parameter is set to ‘Current month’, then it’s going to truncate the system date to the month level and then retrieve Order Dates which match when truncated on the same basis.

Truncates the specified date to the accuracy specified by the date_part. This function returns a new date. For example, when you truncate a date that is in the middle of the month at the month level, this function returns the first day of the month.

Where the [Date anchor] parameter is not ‘Current month’ (i.e. it will be ‘User defined’), then the DATETRUNC() becomes dynamic by referring to the second [Order Date Parameter].

It’s effective, but still has limits. The end user needs to understand that in order to have control over the dashboard, then the first parameter must be set to ‘User defined’ in order for the Condition formula in the Set to be triggered. If it isn’t, then no amount of changing the slider on the secondary parameter will make a difference.

The downloadable workbook showing this technique is available on Tableau Public here. I’m not sure if this is an inelegant solution to a problem which has been better resolved elsewhere, but it worked for me and I hope it can help one or two others as well.

Worry not though! If you’re as taken by Nil’s work as I was, then this link takes you to a series of posts detailing the fiddly maths behind bendy lines.

Back to this week’s #TakeapartTuesday; Rodrigo’s work was inspired by a 2015 YouTube video created by Tableau newbie, Mr. Andrew Kriebel:

It looks like this Andy guy has a future with Tableau, so I’ll make use of his video to recreate Rodders’ viz if I get stuck.

Let’s get started. One thing I don’t like about Rodrigo’s viz is the mix of case types used in the text. For me it should all be UPPERCASE, or it should all be lowercase. For my recreation I’m going to go down the UPPERCASE route, which just means naming the various calculations accordingly.

Rodrigo is focusing on the last decade of data. There are more dynamic methods than the one I took to limit the data to this period of time – a range specific data source filter:

With hindsight, I should have used a Relative dates filter to achieve dynamism – especially as the calculations I created supported dynamic data (apart from their names – wouldn’t dynamic calculation names be a great thing for a future release?)

To check the data now that this data source filter is in place, I created the following view:

What this showed is that there are over 30 countries with data for each of the 19 data points in the remaining data. I made a choice to only show those countries with “all” the data, so created this boolean calculation to filter the data:

Sticking that on Filters and setting it to True means I only now capture the countries with a complete set of data. Let’s now start to figure out the calcs needed to build out the tabular section of Rodrigo’s viz. First of all, let’s find a way to pull through the dollar value for the first date in my now reduced dataset – June 2008.

I like to use Level Of Detail calcs in circumstances like these, so created basic minimum and maximum date calculations:

You can probably figure out the [Max Date] version (!) With this set up, I then set about incorporating this in my [JUNE 2008] calculation:

The [JULY 2008] equivalent just substitutes the [Min Date] calc for the [Max Date]. These two calcs allow me to create a [Change] calc, so I can show the value difference between the two dates:

And using that last calc, I can work out the percentage change between the two dates as well:

Some of these calcs represent a deviation from Rodrigo’s viz, but my preference was to show the “in country” change over time, rather than comparing to the US as an external benchmark. When you have set your number formats and converted these Measures to Discrete Measures (or Dimensions), you can start to assemble a busy looking Rows shelf:

Now it’s time to add in the Continuous time series data:

I could have plotted a table calculation of the percent change relative to the first date, but elected to plot the [Dollar Price] instead. To convert these into “sparklines”, it’s just a case of editing the axis, unchecking “Include zero”, and setting the range to “Independent axis ranges for each row or column”:

Once the [Dollar Price] header is hidden, things start to look good. Next it’s the simple LAST() table calc trick to get the “dot on the end” of the line. The calc is:

If it’s the last record across the table, then pull through the [Dollar Price]

That calc added to the Rows shelf, set to a Circle, converted to a Dual Axis and then synchronised gives us a dot (remember to hide the header and increase the size of the dot until you’re happy). At this stage, it makes sense to add a splash of colour to the second axis to denote whether the country has seen an increase or decrease in Dollar Price relative to the first date – crazily simple:

It’s another boolean – it’s true or it’s false. Whacking that on colour, adding a border and selecting your colours gives you this:

Now then – how do we get a “title” above that line chart so people know what they’re looking at / gain an idea what to do next?

This is an old trick but a good one. It creates a column field label which can easily be hidden and then the label itself can be realigned as you wish. The downward arrow character is taken from a website linked below.

The tooltips are easy ones and I elected to not use the worksheet action Rodrigo used, as I don’t think it makes sense to use a highlight action on a tall viz where you can’t see all of the countries in the view. For me the key analysis is comparing the movement within the country, rather than comparing it against all of the others.

For the title I used this website to bung some geometric shapes into the title, to act as a mini colour legend:

And the smallest of small tips is the use of the oft-overlooked Caption feature. I used this on the worksheet to include the data source, attribution etc.:

It’s a good thing to do, as it saves you from messing about with an extra text object when bringing the dashboard together at the end.

And that’s that. My final viz isn’t precisely the same as Rodrigo’s, but it is certainly inspired by it, and it was fun to recreate. As stated in my last post, I fully intend to recreate a viz from each #MakeoverMonday recap, so let’s see what the next week brings!

It was a unique take on a dataset which inspired great creativity across the community, so let’s see what sorcery Sarah employed to create those lines.

First of all, we need to understand what Sarah has plotted; the x-axis is showing the duration of paid maternity leave in weeks, whilst the y-axis depicts the average payment rate (i.e. what percentage of “full” pay was honoured during the maternity leave period.) This explains why we have a mix of steady horizontal lines, disrupted by reductions in the payment rate as the duration of maternity leave extends over time.

With that in mind, it soon becomes clear that to create this visualisation, Sarah has had to do some data preparation to get the source data into the right shape. The original source is arranged like this:

Yes – that is Excel. I’m sorry.

However – what that image highlights is that the source data is aggregated to one row per Country, and that isn’t too helpful if trying to plot a continuous line depicting payment rates over time.

Data prep is a weakness of mine (I’m lucky to work alongside colleagues eminently more competent at “technical stuff” than I am), so to recreate Sarah’s viz, I’m sticking with Excel. The aim is to create a row of data per paid week of maternity leave, per Country. If I ensure that I include the correct [Paid maternity leave avg payment rate (%)] for the week, then we should be good to go.

Let’s use Bulgaria as an example. We can see from the image of Sarah’s viz that Bulgaria offers 59 weeks of paid leave, with the first 46 weeks at 100% before reducing to 78% thereafter. Can we validate that in the source data?

It’s clear that 46 weeks are at full pay, but is the 78.4% the overall average for the 59 weeks, or just for the period between weeks 47 and 59? The data dictionary on data.world didn’t help, nor did a general Google search. In order for the average pay rate for 59 weeks to be 78.4%, the pay rate after week 46 would need to be about 3%, which seems pointless. In the absence of certainty, I’ll mimic Sarah’s approach of reverting to the average payment rate after the period of full payment. So – Bulgaria looks like this:

With this as a test sample, I can now demonstrate how this all hangs together in Tableau – it’s nice and simple:

In the final visualisation, Sarah leverages the LAST() table calc to draw a dot at the end of each line, to make it clear where each country’s paid maternity periods end – useful for when multiple countries start out with a similar payment rate. To achieve this, it’s a basic table calculation:

If the value is the last one across the table, then return the Sum of the [Rate] measure

Once that is dropped on Rows, converted to a Dual Axis and synchronised, this is the end result:

Minor tweaks to the size of the dot and the colour of the two chart elements make things a lot tidier. With that achieved, you have the confidence to extend the data prep to every Country, which allows you to create this worksheet:

Well on track now, and just a bit of formatting to jazz things up. As part of her data preparation stage, Sarah took the time to assign each Country to a Region, and that Region is used to colour the lines. In addition to this, the “Dot” is used to host a label showing the Country name:

Note [Region] on Colour, and [Country] on Label. Simple.

Also note the bespoke data prep Sarah applied to make the United States appear as it does. Ordinarily the US wouldn’t feature in the viz as there are zero weeks of paid maternity in America. To emphasise how inconsistent this is with the other countries in the dataset, Sarah deliberately pads the American data to force Tableau to plot 59 weeks (as per the Bulgarian paid maternity leave duration) at 0%.

Next is the layer of detail provided by annotating marks. No great science behind this, it’s just a question of identifying key points to draw the eye of the audience to:

There are no fancy Tableau shenanigans involved with annotating data – it’s just a question of taking the time to explore your data and selecting messages to emphasise to your audience.

One final neat touch in the viz is the colour legend, which can be created via a separate worksheet:

Whack it all together in a dashboard with a title and subtitle to reinforce the intended messaging behind the analysis, and you have a strong visualisation. Final flourishes were a highlight action driven by the colour legend, plus the general [Country] Highlighter tool.

In this #TakeapartTuesday it became clear to me that there is great value in stepping back from the supplied data and thinking of an end product that you want to create. You don’t have to be constrained by the shape of the original data. Sarah’s viz was inspired by some training she’d had in the previous week, so she thought about how to reshape her data to support the delivery of that viz.

The Tableau end of things was pretty straight forward, and I’m sure there are better ways to shape the data than my manual Excel approach (Alteryx? Tableau Prep?), but I have no idea how that can be done!

]]>https://learningtableaublog.wordpress.com/2018/08/02/takeaparttuesday-sarah-bartlett-oecd-paid-maternity-leave/feed/0charliehtableauDi9eyh6WwAAifYMScreen Shot 2018-08-02 at 19.58.32Screen Shot 2018-08-02 at 20.36.22Screen Shot 2018-08-02 at 20.53.42Screen Shot 2018-08-02 at 20.56.22.pngScreen Shot 2018-08-02 at 20.59.24Screen Shot 2018-08-02 at 21.01.34.pngScreen Shot 2018-08-02 at 21.06.02Screen Shot 2018-08-02 at 21.10.28.pngScreen Shot 2018-08-02 at 21.13.48Screen Shot 2018-08-02 at 21.18.38#TakeapartTuesday Groovy Groups!https://learningtableaublog.wordpress.com/2018/06/10/groovy-groups/
https://learningtableaublog.wordpress.com/2018/06/10/groovy-groups/#respondSun, 10 Jun 2018 15:13:00 +0000http://learningtableaublog.wordpress.com/?p=8575#MakeoverMonday week 24 challenged the community to visualise data which compared the population of a country, with the number of tourists it attracts each year. Inevitably it revealed that for the selection of countries included, some are overwhelmed with inbound tourism, whereas others attract fewer visitors.

My visualisation was simple and separated the countries regarded as suffering from “overtourism” (more tourists than population) and “undertourism” (the opposite).

However, this short article has nothing to do with my submission! Instead it looks at some sneaky tricks employed by Andy Kriebel in his take on the data this week, which looked like this:

After downloading the workbook and taking a look “behind the scenes” to see how this was constructed, I praised Andy for his sneakiness on Twitter, but he was pretty underwhelmed by the feedback, insisting that he was just leveraging standard functionality.

He was, but it was still a nice example of using that feature-set to carve up the view. In this instance, the sneakiness is in the arrangement of the data in the view. How has Andy managed to get the countries in descending order by Rank in a single sheet, as shown above? Groups.

My initial assumption was that there would be some shenanigans involving INDEX() or one of the Rank table calculations, but they just aren’t needed because Rank was an existing Measure in the data. It is this field that Andy used as the basis for Row and Column Dimensions, which were formed using Groups.

If we start with Columns and consider that for each Category (“Overtourism” and “Undertourism”) we have ten Countries ranked, then you can pretty soon figure out that this lends itself well to a 2X5 grid. With that in mind, here is how the Column Group was constructed. Right-click on the Rank Measure to reveal the option to create a Group:

If you look at the image of Andy’s viz, you’ll see that the 2X5 grid means that the odd ranks run down one column, and the even numbers are adjacent. This is simply a case of creating a Group of odd numbers, and one of even numbers, which looks like this:

Referring once again to that image of the viz, we can see that the rows are formed by having Ranks 1 and 2 in the first row, 3 and 4 in the next, and so on. It isn’t rocket science, but it is fiendishly simple application of standard functionality. The Rows Group is constructed thus:

As soon as the Groups are created, Tableau stores them as Dimensions and you can start to build out the view. Not much happens initially, when no other Dimensions or Measures are used, but the 2X5 grid is shown:

Adding “Category” in does what you would expect – it basically forms the 2X5 grid twice, once for each Category:

When you put Country and Rank onto Text, the penny reaches its final destination:

This may be an eminently unsurprising article for many within the community, but small stuff like this always makes me smile. Beautiful simplicity. The final viz had a bit more polish to the formatting of the Label, which is along these lines (where the <Upper> calculation is just UPPER(Country) to convert the Proper formatted Country into uppercase only):

Tableau is full of great functionality which can be used in many ways. You can do fancy stuff with maps now that I can’t even begin to think about. You can break out your maths and create all manner of wonderful, mind-bending visualisations.

And you can leverage basic functionality to make your life easier. Groups are cool!

]]>https://learningtableaublog.wordpress.com/2018/06/10/groovy-groups/feed/0charliehtableauDashboard 2Screen Shot 2018-06-10 at 15.49.51Screen Shot 2018-06-10 at 16.14.22Screen Shot 2018-06-10 at 15.58.37Screen Shot 2018-06-10 at 16.00.24Screen Shot 2018-06-10 at 16.02.01Screen Shot 2018-06-10 at 16.03.38Screen Shot 2018-06-10 at 16.04.51Screen Shot 2018-06-10 at 16.06.55Screen Shot 2018-06-10 at 16.09.44Time formattinghttps://learningtableaublog.wordpress.com/2018/05/15/time-formatting/
https://learningtableaublog.wordpress.com/2018/05/15/time-formatting/#respondTue, 15 May 2018 19:28:26 +0000http://learningtableaublog.wordpress.com/?p=8558The 20th airing of #MakeoverMonday in 2018 was a nice basic one, with just ten rows of data. As ever, the challenge was to improve upon the original visualisation.

In a rare moment of clarity, I decided that whilst I’d stick with the bars (this is me, after all), I’d convert the supplied metric into something a little more tangible. To me at least, knowing that drivers in London spend 74 hours in traffic jams over the course of 240 commuting days a year is a bit too abstract. How long are drivers actually stuck in congestion on each of those days, on average?

Metrics supplied were just the total hours spent in congestion, and then the somewhat arbitrary assumption of 240 commuting days per year (seems to be 5 working days multiplied by 52 weeks, less an assumed 20 days of annual leave).

OK – that gives us the basis of what we need. It’s at the first hurdle that the issue of time formatting crops up. I created a “Days” metric:

Which was pulled into a very basic calculation:

And highlighted my bugbear:

What is 0.31 of an hour? It isn’t 31 minutes. How can I make this more sensible? I worked out the minutes in a day stuck in congestion, but that didn’t really help:

It just gives me another decimal value:

18.50 minutes isn’t “a thing”. It is 18 minutes and 30 seconds, in the same way as 17.25 minutes is 17 minutes and 15 seconds. Hmm. What if we convert our minutes into seconds, and then divide that value by the total number of seconds in a day?

See what I did there?

The number of seconds in a day is just the 24 hours multiplied by 60 minutes, multiplied by the 60 seconds in each of those 60 minutes:

Dividing the [Seconds per day] calculated field by the [Seconds in a day] calculated field yields another decimal value:

So what? I’ve just gone from having 0.31 hours per day stuck in London traffic, to having 0.01285 (or 1.285% of the day). Still doesn’t mean much, does it? Not yet, but because this data only contains results where the elapsed times are less than a day, then I just need to do a sneaky bit of number formatting:

This minor tweak results in a more traditional means of displaying the passing of time:

I elaborated further, just to make it even more obvious:

The multiple calcs listed above aren’t needed in the final solution, and were just to illustrate the various stages of the thought process for this blog post. My actual calculation was:

Which is a curious mish-mash of logic and laziness. Ultimately, it formed part of the final visualisation, and I feel that the effort expended formatting time in this way reduces the cognitive load demanded of the viewer:

]]>https://learningtableaublog.wordpress.com/2018/05/15/time-formatting/feed/0charliehtableauScreen Shot 2018-05-15 at 19.48.18Screen Shot 2018-05-15 at 19.48.26Screen Shot 2018-05-15 at 19.48.37Screen Shot 2018-05-15 at 19.56.23Screen Shot 2018-05-15 at 19.56.29Screen Shot 2018-05-15 at 19.59.42Screen Shot 2018-05-15 at 19.59.49Screen Shot 2018-05-15 at 20.01.56Screen Shot 2018-05-15 at 20.10.37Screen Shot 2018-05-15 at 20.10.58Screen Shot 2018-05-15 at 20.12.53Screen Shot 2018-05-15 at 20.12.59Screen Shot 2018-05-15 at 20.19.21Screen Shot 2018-05-15 at 20.22.47#MakeoverMonday Chocolate gridhttps://learningtableaublog.wordpress.com/2018/03/25/makeovermonday-chocolate-grid/
https://learningtableaublog.wordpress.com/2018/03/25/makeovermonday-chocolate-grid/#respondSun, 25 Mar 2018 18:00:19 +0000http://learningtableaublog.wordpress.com/?p=8547I have a bit of a reputation for churning out bar charts, and it’s entirely justified. I love them and find them highly effective means of communicating data in a variety of circumstances. For #MakeoverMonday this week I created yet another bar chart, but it didn’t look like the majority I create:

A bar chart? Yep. The data this week looked at the way in which different age groups in the UK ranked their preferred brand of chocolate bar. In practise, this resulted in quite a few brands which were ranked by one age group, but not by others. I made a decision to only include those brands ranked by all age groups and achieved that with this calculation:

For each Brand, I counted the occurrences of a ranking. I popped this calc on the Filters shelf and set it to only include instances where the SUM of Filter was at least 3 to ensure that I only included Brands ranked across all 3 age groups.

A consequence of this filtering was that I was creating a new form of rank. Technically I’m no longer looking at the top 10 Brands in the UK, so I added the sub-title to reinforce the fact that I was selectively limiting the data. How did I create a new rank to reflect that?

Good old INDEX(), concatenated to a prefix to create a tidy Rank #X label. All INDEX() is doing is counting along the table to work out the number of columns in the view; it’s a rank.

Let’s look at the overall worksheet structure:

We can see the INDEX() Measure. Why is the original Rank field in the view? If I unhide its Header, you’ll see:

Removing INDEX() and revealing Rank shows the unusual ranking that is a consequence of my Brand filtering. Where is rank 6? What about Rank 8? If I remove the filter you’ll see:

The Bounty bar was only ranked by 65+ year old pollsters (they are wrong – it’s a horrible chocolate). Boosts and Picnics were also only ranked by individual age groups. This is why I filtered the data in the way that I did, and why the new ranking makes some sense in this context. Let’s move on. What’s that Age Band field? It wasn’t in the original data. A simple string calc:

Speed to insight is always my intention, and I felt that the absence of a suffix to explain what the Age Group field showed was a hindrance. By tacking on ‘yrs’, I believe it allows the audience to readily understand what they’re looking at.

Another interesting part of my worksheet is the AVG(1) field on Columns. What’s that all about? I’m simply forcing Tableau to plot something and fill a space. Without it, this happens:

Nothing appears because there is no value to show. By plotting the average of 1 (i.e. 1), then I make Tableau draw a uniform value across each data intersection. Note that I had to fix the axis to run from 0 to 1 to avoid this annoying gap:

That’s pretty much it. A basic chart but not a standard Bar, so I feel like I’m diversifying!

This week I was “volunteered” to cover for Andy Kriebel on #MMVizReview. This was a great opportunity for me, and meant I got to chat with newly-crowned Tableau Zen Master Eva Murray for an hour or so while we constructively appraised a couple of dozen #MakeoverMonday submissions. It was great fun and I’m grateful for the experience I gained from it.

What stood out for me was the number of submissions which leveraged sized circles in their analysis. It was quite understandable as medals tend to be round, so it was a relatively obvious visual metaphor to use. The problem is – circles suck for a number of use cases. Us humans really struggle to interpret them in comparison to something like a humble bar chart, as Andy Cotgreave expertly elaborates on here. It’s important to make sure that when you use them, the context and data supports this visualisation choice.

I’m stubborn and I wouldn’t let #MakeoverMonday this week pass without making a dig at this visualisation faux-pas, so I banged out a quick example of why this means of articulating data is usually the wrong one.

So…..what was the value of German exports? Swiss? Belgian? What was the difference between the export values across these countries? It’s absolute carnage and I switched off tooltips as well, to be even more obtuse. If you’re interested, then you can download that workbook here.

The alternative option is available here. That second iteration looks like this:

Now I’ve obviously layered on additional cues here to aid interpretation, so it’s far from a fair comparison, but if I’d added the Export values into the circles, would you really have been able to understand from the size of the circles the magnitude of difference? No – you wouldn’t.

The second viz is very simple, but still utilises a couple of useful tricks, so I thought they’d merit a word or two. Firstly, I created a Set to allow users to choose the number of Exporting nations they wanted in the view. This is because originally I was just going to create a Pareto chart, but I soon realised that this would lose sight of the nations who were contributing to the majority of exports and that seemed a really crap idea.

By Exporter, I’m pulling through the Top N nations based on the USD value of those Exports. The [How many expor…] “thing”, is a Parameter:

Again, it’s simple. Let the users select whatever value they want and then we control the number of nations to explicitly see in the viz, and the remainder will be grouped together.

Once I’d ditched the Pareto idea, I realised I could simply re-purpose this Set. It started with an extra calc:

Essentially: If the exporting nation is in the Top N Set, then call it by its name, else lump it together as one entity called ‘The Rest’. In the grand scheme of a workbook, it looks like this:

Basic but required in this context. It makes the viz and the colouring dynamic, and anything that automates a process is a good thing.

This idea of easing processes extends to the second minor tip of this post – the sort order. Note how the exporting nations are sorted in descending order by the value of exports? However, also notice how “The Rest” sit at the bottom, in spite of having a greater value that the topmost nation? That requires a calculation too:

What does that look like in a table?

By inverting the export value for “The Rest”, it inevitably becomes the lowest value in the view, hence it assumes a position at the foot of the view when sorted based on this calculated view.

As an addendum to this post, Lilach Manheim rightly posted that the tone of this article was pretty absolutist and implied that sizing of circles should NEVER be used.

Clearly that is not the case, but I’m comfortable standing by the assertion the sized circles in isolation should not be used where precision is required, and that they should be complemented with a more traditional means of interpreting the underlying data (a discrete value or label, as an example) in these instances.

As a means of creating impact where precision is not required, they certainly have a place.

]]>https://learningtableaublog.wordpress.com/2018/02/18/dont-use-sized-circles-to-visualise-data/feed/0charliehtableauScreen Shot 2018-02-18 at 18.18.34.pngScreen Shot 2018-02-18 at 18.07.21Screen Shot 2018-02-18 at 18.35.14Screen Shot 2018-02-18 at 18.35.25Screen Shot 2018-02-18 at 18.38.51Screen Shot 2018-02-18 at 18.38.59Screen Shot 2018-02-18 at 18.42.23Screen Shot 2018-02-18 at 18.43.18#TakeapartTuesday – Stepped Lineshttps://learningtableaublog.wordpress.com/2018/02/12/takeaparttuesday-stepped-lines/
https://learningtableaublog.wordpress.com/2018/02/12/takeaparttuesday-stepped-lines/#commentsMon, 12 Feb 2018 10:01:08 +0000http://learningtableaublog.wordpress.com/?p=8501The seventh week of #MakeoverMonday in 2018 offered participants the opportunity to reviz a great small-multiple visualisation by @RodyZakovich. As tends to be the case, I went with my first instinct and created this viz, which makes use of a common baseline to see which sports have resulted in the greatest number of medalists since the Winter Olympics began.

Seconds after posting, I saw @VizWizBI had submitted a similar (better!) chart using similar principles. His viz was based on another of Rody’s tips and Rody’s approach is one that has escaped me until now, so now seems like a time to take a look.

I’m not entirely wet behind the ears with step lines, as I’ve used a prior version of the technique from @TableauTim:

As far as I can tell, step line hacks have a limited shelf life as Tableau 2018.1 is going to deliver this as an out-of-the-box line chart option:

Let’s see what Andy did.

It’s a pretty busy worksheet, so there’s going to be a lot to look at here. I’ll start with the continuous Dimension on the Columns shelf:

A nice easy start. A basic Level of Detail calculation to subtract the first year of competition for each Country from the current year. But why is it stored as a Dimension rather than a Measure? Because that way, you avoid any aggregation (SUM, MEDIAN, COUNT) etc.

Andy has gone down the dual-axis route on Rows, which can be determined by looking at the shape of the Green (Continuous) Pills on the shelf:

The back-to-back vertical line indicates that this is a dual-axis. The first Pill makes use of the functionality to add multiple Measures to a single Mark, and this is the core component of Rody’s stepped line technique. The two Measures Andy has picked up are [Cumulative Medals] and [Next games along Years Since First Games]:

A straight-forward Table Calculation to calculate the RUNNING_SUM of Medals. The second calculation is as below:

Here, the calc is actually picking up the prior Year of competition’s RUNNING_SUM of medals, so the title of the calc is a bit misleading. If you plot these Measures independently and dual-axis them, you end up with this:

Not what we’re after at all because we end up with separate values, whereas we want to force Tableau to plot two values in each partition. Hence the use of Measure values. Let’s rewind a bit and see exactly how Andy had this configured:

If you look carefully, you’ll spot the inclusion of INDEX() on Path. Without this calc, the two values are being connected by Measure Names along each month and so they appear as distinct Measures. If I look at Germany in isolation, here is how the INDEX() calculation is working to form a single line:

The final calc on Rows is just to plot the dot at the end of the lines. It’s a nice detail to add as it acts as a sort of minimalistic cue which draws attention but is subtle enough not to be distracting:

OK, let’s look at the other “bits” knocking about on the worksheet. On Filters we have a dynamic computed Set:

So the view will adapt based on the Dimension members selected in the filters to always return the 5 nations to have accrued the greatest number of medals for the relevant data intersections.

Next, you’ll observe the grey filters for Gender, Medal and Sport. The grey colouration denotes that these Dimension filters have been added to Context. If this step wasn’t taken, then the Level of Detail calcs behind the scenes wouldn’t update and you’d only see the overall medal haul per Country, regardless of what filters were set, and the y-axis will remain fixed as it continues to plot those overall medal counts:

Andy always leverages a sneaky trick here and there too, and I was observant enough to find a couple of them. The first is an axis buffer calculation for the medal count:

It simply adds 10% to the highest medal count in the view, which gives breathing space on the y-axis. Andy pops it on the view as if it’s on the Tooltip card, even though it isn’t included in the Tooltip. Weirdly I noticed a change to its impact if it is moved to Detail instead. Not sure what the reasoning is behind that, but here’s the difference:

Same principle as before, but referencing time rather than medals. This calc is required on the x-axis for some of the newer events, such as snow-boarding. To leverage it, it is utilised as a reference line:

If it wasn’t included for snow-boarding, the horizontal space becomes compressed:

As I get more comfortable with Tableau, I’ve come to appreciate that it’s the ability to recall little tricks like this which really enhances the overall polish of final dashboards.

That’s it! Nothing new for people who have read Rody’s version (which I linked earlier), but if, like me, you need to absorb something two or three times before it sticks, then hopefully this will be the straw that breaks the camels back!