Author: Donna Coles

Let’s build a trellis chart!

Donna ColesLeave a comment

It only seems like yesterday I was writing a solution guide, and I’m back at it again. This week Sean asked us to recreate this challenge to build a small multiple / trellis chart using table calculations only.

A note on the data

After downloading and connecting to the provided data source, I found the dates weren’t coming through as intended – they’d been transposed from dd/mm/yyyy to a mm/dd/yyyy so consequently the only dates I was getting were for the first 12 days in January for every year. Rather than trying to solve this at source, I just created a new field which transposed the Date field back so it behaved as I expected

Date Corrected

(MAKEDATE(YEAR([Date]), DAY([Date]),MONTH([Date])))

You may not need to do this if the data pulls in correctly.

Filters

There are two filters that should be applied, which can either be added as data source filters (right click data source > Edit data source filters) or can be applied to the Filter shelf on any sheets created. Ultimately, this challenge only requires 1 sheet, but when building and verifying logic, I tend to have additional ‘check sheets’. I therefore added the filters below to the Filter shelf of the first sheet I started working with, but set them to apply to all worksheets using the data source (right click pill once it’s on the filter shelf -> Apply to Worksheets -> All using this data source).

Gender : All

Date Corrected : starting date = 01 Jan 2012

Setting up the data

As is good practice when working with table calculations, I start by building out the calculations I need and validating them in a tabular format before I build any vizzes. So let’s do that.

All the countries are displayed in capital letters, so we need

Country UPPER

UPPER([Country Name])

Add this to Rows

Additionally, for the purpose of validation and performance only, add this field to the Filter shelf too and just filter to Australia and Austria.

If you haven’t already added them as data source filters, apply the filters mentioned in the section above to this sheet too and set to apply to all worksheets using the data source.

Add Date Corrected to Rows as a discrete (blue pill) exact date. Format the date so it displays in month year format.

Add Unemployment Rate to Text. Format this number to 1 decimal place and add a % as a suffix.

Now for the table calcs

Median

WINDOW_MEDIAN(SUM([Unemployment Rate]))

Format this to display as a % using the same option as above. Add this to the table and set to compute using Date Corrected

You should find that your median value only differs by country.

Now we work out

Variance

SUM([Unemployment Rate]) – [Median]

Format this to display as a % and add to the table, setting the table calc to compute by Date Corrected again. This is the measure that will be used to plot the trend line against.

We also need to display the range of Unemployment rates for each country – ie we need to work out the minimum and maximum values.

Max Unemployment Rate

WINDOW_MAX(MAX([Unemployment Rate]))

Min Unemployment Rate

WINDOW_MIN(MIN([Unemployment Rate]))

Format both of these to display as 5 with 1dp, and add to the table, verifying the calculations are computing by Date Corrected once again. Verify you get the same values for all the rows associated to a single country.

Now we know the calculations are as expected, we can start to build out the viz.

Building the core chart

To start with we’ll just focus on getting the line chart with the associated text displayed for the two countries Australia & Austria. So on a new sheet add Country (UPPER) to Filter and filter to these selections. The other filters should automatically add.

Add Country UPPER and Date Corrected (green continuous exact date) to Columns and Variance to Rows. Set Variance to Compute By Date Corrected.

Add Unemployment Rate to the Tooltip and adjust the text to match.

To add the country title and other displayed text, we’re going to use a ‘fake axis’ and plot a mark at a central date. On hovering over the solution, October 2016 seems to be the appropriate date selected. So we need

Title Position to Plot

IF [Date Corrected] = #2016-10-01# THEN 1 END

Add this to Rows in front of the existing pill. Change the mark type of this measure only to a circle and re-Size to make it as small as possible and adjust the Colour Opacity to 0%. This will make the mark ‘disappear’.

Add Country UPPER, Median, Max Unemployment Rate and Min Unemployment Rate to the Label shelf of this marks card. Ensure all the table calculation fields are set to compute by Date Corrected. Adjust the text as required, and align centre. Ensure the Tooltip is blank for this marks card.

Change the colour of the variance line to grey, then remove all gridlines, row dividers and axis. Set the Column Dividers to be a thick white line (this will help provide a separator between the small multiples later).

Creating the trellis

There are multiple blog posts about creating trellis charts. My go to post has always been this one by Chris Love. It’s a more complex solution that dynamically flexes the number of rows and columns based on the number of members in the dimension you’re visualising. There have also been other Workout Wednesday challenges involving trellis charts, which I’ve blogged about too (see here).

Ultimately we’re aiming to determine a ‘grid position’ for each member of our dimension. In this case the dimension is Country UPPER and its a static list of 36 values, which we can display in a 6 x 6 grid. So Australia needs to be in row 1 column 1, Austria in row 1 column 2….. Costa Rica in row 2 column 1… USA in row 6 column.

As our members are static, the calculations we can use for this can be a bit simpler than those in Chris’ blog.

Firstly, let’s get our data in a tabular layout so we can ‘see’ the values as we go.

Duplicate the data sheet we built up, then move Measure Name and Date Corrected from Rows/Columns to the Detail shelf. Remove the Country UPPER field from the Filter shelf. You should have something like below, showing 1 row per country

Double click into the Rows shelf and type in INDEX(), then change the resulting pill to discrete (blue). You will see that index numbers every row. It’s a table calculation and although working as expected, let’s explicitly set it to compute using County UPPER.

Let’s now create our grid position values.

Cols

FLOAT(INT((INDEX()-1)%6))

This takes the Index value and subtracts 1, and returns the remainder when divided by 6 (%6=modulus of 6 – ie 6%6=0, 7%6=1). 6 is the number of columns we want.

Rows

FLOAT(INT((INDEX()-1)/6))

This takes the Index value and subtracts 1, and returns the integer part of the value when divided by 6. Again we’re using 6 as this is the number of rows we want to display.

Add these to the table, set to be discrete (blue) and compute using Country UPPER.

You can see that the first 6 countries are all in the same row (row 0) but different columns (0-5).

Now that’s understood, we can create the small multiples on the viz.

Duplicate the sheet we created further above which displays the trend graph for Australia & Austria. As we’re now going to make the changes to create the charts for every country, if things go a bit screwy, you can always get back to this one to try again :-).

Add Cols to Columns. Set to discrete and compute using Country UPPER. Add Rows to Rows and do the same thing. Move Country UPPER from Columns to the Detail shelf on the All marks card. Then remove Country UPPER from the Filter shelf.

Hopefully everything worked as expected and you have

Final step is to uncheck Show Header against the Cols and Rows pills so they don’t display and you can add to a dashboard.

My published viz is here.

Happy vizzin’!

Donna

Monthly Sales vs Targets – the #data22 Vegas Edition

Donna ColesLeave a comment

This week the whole #WOW2022 crew and myself were lucky enough to be able to attend #data22 – the Tableau Conference in Las Vegas. In the 10 years I’ve been involved in the Tableau Community, this was my very first US conference, and my first opportunity to meet some of the people I engage with on a weekly basis, in person. Meeting all those who have been involved in the WOW challenges over the years and my fellow regular participant, Rosario Gauna, was a big highlight for me.

Erica led the live #WOW2022 session at the conference on Thursday morning (not sure the organisers fully understood the title), with this challenge.

Erica walked through the challenge end-to-end in the session, but I attempted to build out my solution, just as I would at home, so let’s crack on.

Modelling the data

The viz compares actual sales vs target, and an additional data set was provided to store the target data. As a consequence this needs to be combined/joined/related with the actual sales data.

For this you need to download the two excel data sources provided in the challenge – SuperStore Sales and Superstore Category Monthly Sales Targets.

Connect to the Superstore Sales file and drag the Orders sheet into the canvas. The Add a new connection to the Superstore Category Monthly Sales Targets file and drag in the Sales Targets sheet to the right of the Orders object in the canvas.

This will try to create a relationship between the two objects, but can’t as it needs to be defined.

In the section at the bottom, relate the Category field from the Orders object to the Category field in the Sales Target object.

This creates a valid link between the two objects, but it’s not enough. We also need to relate on a date field. In the Sales Targets object there is a field Month which stores the date on the 1st of a month. So to relate the Orders data we need to

  • Click + to Add more fields
  • From the Select a field dropdown on the Orders side, select Create a Relationship calculation
  • In the calculation window type in DATE(DATETRUNC(‘month’, [Order Date])). This returns the 1st of the month related to each Order Date into a Date rather than Datetime datatype.
  • Select Month from the Sales Targets dropdown.

Building the bar in bar chart

You can build this type of chart using a dual axis chart, where both axis are set to use the bar mark type, but the sizes of the bars are different. However, if you go down this route you’ll struggle to get the labels right (as the labelling is the trickiest part of this challenge).

Instead for this challenge you need to build a combined axis chart, which is possible since both measures are represented by the same mark type, a bar.

First up, add Order Date to the Filter shelf and select to filter by the Year 2021.

Then add Order Date to Rows and set it to the discrete (blue pill) ‘month’ level (the ‘May’ option rather than the ‘May 2015’ option on the context menu). Add Sales to Columns and change the mark type to bar.

Then drag Sum Targets from the left hand pane onto the Sales axis, releasing the mouse when you see the ‘double column’ green icon appear.

This will result in a combined axis chart being created, and the fields Measure Names and Measure Values automatically added to the viz.

Move Measure Names from the Columns and place on Size. Additionally add another copy of Measure Names onto Colour and adjust accordingly.

On the Analysis menu, select Stack Marks -> Off so the bars both start from 0, rather than being placed on top of each other.

We now need to adjust the bar sizing. Edit the sizes via the Size legend on the right hand side, so the range between the sizes is less than that set.

We now need to work on colouring the Sales bars based on whether they met target or not. For this we first need to work out whether the Target is bigger or not.

Met Target?

SUM([Sales]) > SUM([Sum Targets])

This returns a boolean true/false result. We want this on the colour shelf in addition to the Measure Names field that is already there. If we just drag it onto colour, it will replace Measure Names. Instead, drag Met Target? onto the Detail shelf. Then click on the 3 dots immediately to the left of the Met Target? pill in the marks card, and select the Colour icon.

This has the effect of adding this as an additional colour field, so four options rather than two are now presented in the colour legend. Adjust the colours once again.

We’ve now got the core bar-in-bar chart. We can just add some final formatting to this section.

Format the month axis to set the dates to be abbreviated, and then rotate the labels.

Click the Order Date label at the top of the chart and Hide field labels for columns.

Edit the Value axis and rename the title to Sales ($).

Amend the number format of both the Sales and Sum Targets fields to be a number with 0dp and $ prefix. Add both fields to the Tooltip and adjust so that both values display when you hover over either the Sales bar or the Targets bar.

Labelling the bars

Labelling the bars to match Erica’s display is the trickiest part of this challenge. Bottom line – it shouldn’t be so tricksy but that’s just the way it is until Tableau see fit to fix it in the product.

Anyway, on examining Erica’s published solution before I started, I could see, by hovering my mouse over the viz, that there was a small mark highlighted above the bars that missed target. This provided a clue that there was a dual axis involved (hence the need for a combined axis to display the bars), with a mark plotted at some distance above the existing data…but where…?

Before working that out, I need to build a couple of fields

Difference

SUM([Sum Targets])- SUM([Sales])

which returns the difference between the Sales and Sum Targets for each month. But since I only want the values when the target hasn’t been met, I need

Missed Target Diff

IF NOT([Met Target?]) THEN [Difference] END

which is formatted to $ with 0dp.

I also needed some text to only display when the target was missed

Label Text Missed Target

IF NOT([Met Target?]) THEN ‘Target missed by’ END.

Now to figure out what measure to plot….

In Erica’s solution, she worked out an ‘uplift’ of the Sum Targets value to plot, but only when the target was missed. She did this using quite a complex looking nested LOD calculation (check out her solution for this).

After much deliberation, discussions, trial and error, I finally came up with a alternative that isn’t perfect, but is ‘good enough’ in my book.

Firstly, I needed another measure to plot

Target to plot

IF NOT([Met Target?]) THEN SUM([Sum Targets]) END

This plots the Targets value but only against the months where the target wasn’t month.

I then added this to the Columns shelf and I changed the mark type to circle on the Target to Plot marks card only.

I then made the chart dual axis and synchronised the axis.

Then, remove Measure Names from the Colour and Size shelf on the Target to Plot marks card, and also remove the Met Target? field from the Colour shelf too.

Adjust the Size of this mark to the smallest possible, and change the Opacity (via the Colour shelf) to 0%, so the circle mark becomes invisible except on hover.

Add Missed Target Diff to the Text shelf of the Target To Plot marks card. Edit the text in the label as below

I added a carriage return, formatted the text to 11pts and orangey/red and added 5 spaces to the front of the 2nd line.

I then changed the label alignment so the text was rotated and it was positioned top centre.

Next I added Label Text Missed Target to the Label shelf on the Measure Values marks card.

I edited the text as below, left aligning and again adding 5 spaces in front

Then I adjusted the alignment to be top left

Adding Category to the Filter shelf and testing with the different filters, suggested this technique seemed to work (at least on Desktop).

Last step is to remove the secondary axis, remove the row & column dividers, and hide the ‘nulls indicator’. Then add to a dashboard.

My published instance is here.

This challenge certainly raised questions over these formatting specifics. As I mentioned above, it shouldn’t be that hard to add a label that looks like this – left aligned and positioned above the bar. However the product doesn’t let you achieve this easily as yet, which is disappointing and certainly confusing to new users of the product – it seems such a straightforward requirement after all.

If I was doing this for my own work, I’d have kept with whatever options a single label allowed (where the text was all right aligned), or placed on a single line. The impact on performance of a complicated calculation along with the maintainability of a dashboard using it, are important considerations when deciding what’s ‘good enough’, and are arguments that should be made if a client/user insists. After all, what real benefit does this particular format provide over

or

Happy vizzin’!

Donna

Can you format the dashboard?

Donna ColesLeave a comment

This week’s #WOW2022 challenge was focused on dashboard layout, specifically the use of containers and padding to give your charts room ‘to breathe’ and be aligned beautifully.

This has come at a great time for me, as now I’m spending more time in my role developing business dashboards, I’m trying to find a ‘go to’ style that works for me. I know things will often need to be adapted on a client by client basis, but having a consistent reference point for anything I develop personally, or just want to conceptualize will make my life easier (less decisions to be made). As a result I’m currently spending a lot of time figuring out what padding/background colours etc I want to use for my dashboards.

A note on the data

You may notice that my solution looks a bit different from Luke’s in terms of content. The requirements state to use Superstore v2021.4, but the excel file that ships with Tableau doesn’t actually contain a Manufacturer field which Luke uses. I thought I’d try to use the file linked in the requirements, but (at the time of writing), that actually linked to a 2019.4 version, so the numbers didn’t match up. I therefore chose to use the Superstore v2021.4 excel file I already had on my laptop, and built the table using the Product Name field instead. I think the Superstore.tds file that ships may contain Manufacturer, but I couldn’t see to find that either at the time.

I also chose to use a count distinct function when counting the Order IDs rather than a count, as this seemed more appropriate to me, which is why that KPI differs.

As the focus on the challenge was on the layout, I didn’t think it was necessary to get these points resolved before building my solution.

Building the KPIs

I managed to build the visuals required for the solution using 5 sheets – 1 sheet per KPI ‘card’ and 1 for the table. After I completed my solution, the requirements were updated and suggested 9 sheets were required, essentially 2 per KPI card – the measure and the bar chart. Personally I think having 1 sheet for each KPI is ‘cleaner’ so I left it as is.

In order to build each KPI in a single sheet, I need additional calculations to provide the ‘total’ measure value.

Total Sales

{FIXED: SUM([Sales])}

Count of Orders

COUNTD([Order ID])

Total Orders

{FIXED: [Count of Orders]}

Profit Ratio

SUM([Profit]) / SUM([Sales])

formatted to % with 1 dp

Total Profit Ratio

{FIXED: [Profit Ratio]}

formatted to % with 1 dp

Total Profit

{FIXED : SUM([Profit])}

Then create a bar chart by adding Order Date to Columns and setting to the Month (May) date part (blue discrete pill), and adding Sales to Rows. Add Total Sales to the Detail shelf. Change the mark type to Bar.

Change the Colour to #afaaf3 and reduce the Size. Edit the title of the chart to reference the Total Sales field and format as below with the SALES in 9pt and Total Sales in 15pt.

Modify the Tooltip then remove all gridlines/axis rulers etc, and hide the axis and the month headers.

Rename this sheet as Sales, then duplicate, and create one for Orders by replacing Sales with Count of Orders and Total Sales with Total Orders. You should be able to drop the replacement pill directly on top of the pill being replaced, and everything will update. Update the sheet title to change the word SALES to ORDERS. Rename this sheet Orders, then duplicate again and repeat the process to create the Profit Ratio and Profit sheets.

Building the table

On a new sheet add Product Name (or Manufacturer if you have the right data set) to Rows and add Sales, Profit, Count of Orders and Profit Ratio into the table so you end up with Measure Names on Columns and Measure Values on Text.

Remove all row banding, but add row dividers across each row. Widen each row and the header. Sort by Sales descending.

Change the mark type to Square then add another instance of Measure Values to the Colour shelf. Right click on the Measure Values pill and select Use separate legends.

This will display 4 colour legends, which you need to amend as follows :

Sales Legend

Edit the colour and select Purple sequential. The click on the coloured square at the right end of the range, and change this colour to #675CF3.

Then select the Stepped Colour checkbox and change the value to 8.

Profit Legend

Edit the colour and choose a Diverging colour palette. It doesn’t matter which one. Click on the coloured squares at each end and change them both to white. Set the number of steps to 2.

Count of Orders Legend

Do exactly as described above for the Profit Legend.

Profit Ratio Legend.

Edit the colours and choose a diverging colour palette. Change the colour on the left to #9A1500. Change the colour on the right to #777777. Set the number of steps to 8.

Building the dashboard

Now we have the charts, we need to start building the dashboard, and I’ll see if I can step through my approach to doing it, and if I’ll end up with the right result….

Firstly create a dashboard and set the size to 1100 by 850.

Now a habit I’ve got into when I want to be really precise about the formatting I’m applying (ie I’m building a business dashboard), is I start by adding a floating container to the dashboard, which is set to the exact dimensions of the dashboard. This was a tip I picked up from Curtis Harris here. The main reason for doing this, is that I feel I have more control as I don’t then use any Tiled container type that automatically gets added. If any do appear, I find the objects I want out of them and move them elsewhere, then delete the whole tiled section. In this instance I’m going to start by adding a Vertical floating container, and position it at 0,0 and 1100 wide and 850 high. Use the fields on the Layout pane to set these.

I also get into the habit of renaming the containers, to help me keep track of what is where and whether the objects are in the right place.

The next thing I always do when working with containers, is add a blank object. As we build out, this object will eventually be removed, but it’s a recommended step to help position other objects you add into the containers.

So on the Dashboard tab, change the selection to Tiled and then drag a blank object into the main canvas.

This ‘blank’ is now in the container and the ’tiled’ option means its anchored to that container, which means if you move the container, the objects within will move with it (unlike if the object was floating).

In the item hierarchy, you can see the blank nested in the Base container.

In the item hierarchy, click the Base container, so it is selected on the dashboard (it will be surrounded by a blue frame). Adjust the settings so the background is pale grey, the outer padding is 20px all round, and inner padding is 30px all round.

Add a Horizontal container above the blank object. This is going to be a header section for the title and the export options. Name the container Header in the item hierarchy. Add a blank object into the header container.

Add a Text object into the Header container to the left of the blank object, and enter the text for the title and strapline. I used Tableau Book 16pt bold for the main title and 9pt for the subtitle (there was no instruction for this). Set the outer padding of this text box to 0 all round.

Download the image files Luke provided.

Add a Download object to the right of the blank in the Header container. Edit the button and set to export to an image, use an image button style and select the relevant image.

The image will probably look incredibly large, but don’t worry. First, set the outer padding of this object to be 10 all round.

Then select the Header container and edit the height to be 55px (use the down arrow on the selected object to open the context menu and select Edit Height).

Add further download objects to the right of the existing one (making sure you’re in the Header container) – one to export to PDF and one to PowerPoint. Set the outer padding of the PDF one to 10 all round, and for the PowerPoint one, set the outer padding to 10 for left, top & bottom, but 0 for the right.

Now add another Horizontal container below the Header container and above the blank. Rename this to Main. Set the outer padding of the Main container so that it has 25px at the top. This padding (25) + height of header container (55) + inner padding of Base container (30) gives us the requirement that the height of the grey header to the components needs to be 110px. Add a blank object into the Main container.

You can now remove the blank object that is at the bottom of the Base container – the one underneath the Main container.

Note It’s worth saying at this point, that when I built my solution most of this was all bit trial and error / tweaking this & that to meet all the specific layout requirements Luke states. As I’m rebuilding my original solution as I write, I know what order I want to add objects to the dashboard and what all the settings need to be as I add each object.

The Main container is horizontal as it it will consist of 2 columns; 1 with the KPIs and 1 with the table. However both columns need to contain a Vertical container each. For the KPIs columns, this is to stack each KPI card on top of each other. For the table, this is because we have to add the purple bar on top of the table.

So, add a Vertical container into the Main container, to the right of the blank. Rename this container Table Column. Then add a blank into this container, and underneath that add the Table sheet.

If you look in the item hierarchy, you should see that a Tiled section has been automatically added to the dashboard – boo!! This contains all the colour legends associated to the table, and you can just make out where they are (top right) as they’re behind the floating Base container.

In fact, the Tiled container added actually contains lots of other containers.

We don’t need any of these legends to display or any of the containers that have been added, so remove (right click on the first Tiled container and Remove from Dashboard). You’ll get a warning message but just hit Delete to continue. The section will be removed and your item hierarchy looks nice and clean again 🙂

Now that’s sorted, we can focus back on the Table Column container and the objects within. Select the Blank object in the container. Change the background colour to #675cf3, set the outer padding to 0 all round, then edit the height to be 10 px. Rename the blank object in the item hierarchy to ‘purple bar’.

Now select the table sheet itself. Hide the sheet title. Set the outer padding to 0 all round and set the inner padding to 10 all round. Set the background colour to white. Set the sheet to Fit Width. Don’t worry at this point about the table looking cramped.

Now add another Vertical container into the Main container, this time to the left of the blank. Rename this container KPI Column and add a blank into it. You can now remove the blank that is sitting between the KPI Column container and the Table Column container. This will cause the table to expand.

Edit the width of the KPI Column container to be 300 px.

Now we’re ready to deal with the KPIs.

Each KPI ‘card’ consists of a purple vertical line and the KPI sheet itself. This means we need a horizontal container per card to manage this. WHAT! More containers……. Fun isn’t it 🙂

So add a Horizontal container into the KPI Column container, above the blank object. Name this container Sales KPI. Add a blank object into this container. Then add the Sales sheet into the Sales KPI container to the right of the blank. Hopefully you have something like below…

Select the blank object in the Sales KPI container and set the background colour to #675cf3, and the outer padding to 0 all round and edit the width to be 10 px. Rename this object to ‘purple bar’.

Then select the Sales sheet. Set the background to white, the outer padding to 0 all round and the inner padding to 10 all round.

Now select the Sales KPI container, and set the bottom and right outer padding to be 5px. The top and left should be 0.

Add another horizontal container to the KPI Column container directly below the Sales KPI container. Name this container Orders KPI. Set the outer padding on this container to be left 0, right, top & bottom all 5.

Add a blank object into the Orders KPI container, then add the Orders sheet to the right of this. As before, select the blank object and set the background colour to #675cf3, and the outer padding to 0 all round and edit the width to be 10 px. Rename this object to ‘purple bar’.

Now select the Orders sheet. Set the background to white, the outer padding to 0 all round and the inner padding to 10 all round.

Repeat this process to create the Profit Ratio and Profit sections. When you add the Profit KPI container, set the outer padding for the top and right to 5, and the bottom and left to 0.

Remove the blank object that should now be at the bottom of the KPI Column container. The click on the KPI Column container in the item hierarchy to select it, and use the context menu from the drop down at the top right of the object and Distribute Contents Evenly.

The final tweak to make is to add some left padding to the Table Column container. Select the container and set the left padding to 5 and the rest to 0.

And that should hopefully be it, and meet all the requirements. My full item hierarchy is visible below.

Granted there are more containers than Luke suggests, but hopefully you can see how it’s organised and much more maintainable.

Containers can be tricky beasts, but if you work methodically with them, they become easier to use. In summary, the steps I try to follow are

  • Start with a floating container sized accordingly, and add ’tiled’ (ie not floating) objects into that
  • Always add a blank object into any container you add. Add required objects, then remove the blank if no longer required.
  • Rename containers as you go.
  • Deal with any Tiled containers as they appear – if you need a legend/filter that gets added in it, select that object via the item hierarchy and move it to where you want it to be, then delete the complete tiled container.

My published solution can be viewed here.

It’s Tableau Conference next week, and I’m actually going to be attending in person for the very first time (I’m super super excited!). I’m not sure when next week’s challenge will actually land or when I’ll actually get a chance to complete, let alone blog. I might have to end up playing catch, so I apologise in advance for those who can’t attend and may be waiting for my guide to help them out. It will get published… I just don’t know when!

Happy vizzin’!

Donna

This is me

Donna Coles1 Comment

In a break from tradition, this isn’t a #WorkoutWednesday solution guide. Instead this is likely to be more of a ‘heart on my sleeve’ type of post; a bit of self-reflection and musings on my current state of mind. Don’t be alarmed – I’m perfectly well; I’m just having a little identity crisis and hoping that by putting ‘pen to paper’, it alleviates some of those feelings that bounce around in my brain.

2022 so far has been an epic year in terms of my career, and we’re only 4 full months in…. I’ve joined a brilliant consultancy, been named a Tableau Visionary and in a few days, I’ll be heading to my first major Tableau Conference in Las Vegas.

It’s all incredibly exciting, but also overwhelming at the same time, and the classic ‘imposter syndrome’ anxieties have started to creep in… am I really good enough, do people think I’m a fraud…?

You see, I worry about what people think of me. This isn’t anything new, it’s been like that since my school days, and it affects all aspects of my life, both professional and personal. I’ve accepted it isn’t anything I’m ever likely to ‘get over’, and work hard to try not to overanalyse things too much (but hey, I’m a data person after all, and isn’t that just the type of people we are 🙂 ).

On a professional level, since leaving a company I’d been at for 20 years, my confidence levels and belief in myself have increased significantly, and certainly helped me get my current role. But I’ve now gone from companies where I was the acknowledged Tableau ‘expert’, to a consultancy firm which is full of brilliant Tableau people. It’s fabulous – I’m surrounded by people who love Tableau and just ‘get it’; who are all incredibly supportive and helpful. But still, I can’t help but worry about peoples’ expectations of me and my capabilities. I’ve built up a profile in the Tableau community over the years, and some people in the company had heard of me….. “Is that THE Donna Coles joining us?” Gulp!

And then came the Visionary announcement.

I knew I’d been nominated, but never did I think I’d actually get selected!

I complete the #WorkoutWednesday challenges and blog my solutions, and I commit myself to that task. I plan my weekly activities around fitting in completing the challenge and writing up my solution (it’s sooooo helpful when the challenge actually lands on a Tuesday). And that’s it. I don’t come up with any clever ways of using Tableau, I don’t produce beautifully crafted visualisations. I don’t organise user groups or run any community projects. I am a Tableau Forums Ambassador and have been for several years, but even there, I’m not the one at the top of the leader board answering question after question. I tend to dip in when I can (which definitely isn’t as often as I’d like) and manage the moderation queue and be vocal in the forum ambassador meet ups, sharing my thoughts and experiences about the platform and the program.

When I was originally asked to be an Ambassador, I was told “just keep doing what you do”, and those sentiments were echoed again when I joined my first Visionary meet-up. It’s what I’m already doing that has got me recognised; there’s no expectations to do more. There may well be new opportunities that open up, but it’s my choice to pursue if I wish.

So that’s what I took onboard, and try to keep telling myself….”just keep doing what you do”. I don’t feel I have time to do ‘more’ – some days I do just want to ‘Netflix & chill’ – and that’s just as it should be.

And I was gradually beginning to feel more comfortable with the moniker I’d been awarded.

But then I was on a remote internal training session at work, and part of the activities involved reworking some dashboards in a short space of time. And I panicked. The weight of expectation built up and I struggled to come up with anything I felt worthy of sharing in the timeframe for fear of judgement. Others were confident to share their work, while I, the Visionary, couldn’t.

And so the questions over my worthiness rose again, which has led me to writing this post. I’m not seeking validation for being in the role I’m in. I’m simply writing this down to help me accept who I am.

I know what I’m good at, and I also know what I’m not so great at – there’s also plenty I haven’t got a scooby about! And that is most probably the case with all the Visionaries. We’re only human after all!

It is the Community and Tableau that have recognised what I do and if it’s good enough for them, then who am I to object? I will endeavour to continue doing what I’ve been doing and help where I can.

If you’ve made it this far, then thank you. Writing this down has been quite a cathartic experience, and if it helps anyone else who reads this, then that’s a bonus too 🙂

If you’re at TC22 in person, then feel free to reach out and say ‘hi’. Just don’t ask me to rebuild a dashboard in 20 mins 😉

Donna

Can You Optimize This?

Donna ColesLeave a comment

For #WOW2022 week 18, Lorna set this challenge to allow us to familiarise ourselves with the new Tableau Workbook Optimizer feature released in version 2022.1 (so you’ll need this version of Tableau Desktop to complete this challenge).

You need to download the workbook Lorna provides from Tableau Public, and then the only stipulation is to get the workbook to fully pass all 12 checks made on the workbook – no changes to the dashboard presented is required.

So, open the workbook and start by running the optimizer via the Server > Run Optimizer menu

You’re presented with the following – 3 important Take Action suggestions and 1 warning Needs Review suggestion.

Each of the suggestions can then be expanded to see more detail

Take Action – unused data sources

You’re told exactly which data sources aren’t used. Close the Optimizer dialog then navigate to a worksheet so you can access the listed data sources in the top section of the left hand Data tab. Right click on the relevant data sources and Close. If you happen to click one that is in use, you will be warned.

Take Action – Unused Fields

On each of the remaining data sources listed, right click and select Hide all unused fields to hide all the fields mentioned in the optimizer.

The optimizer also identifies unused parameters, which are listed at the bottom of the left hand pane. Right click on these and Hide.

If you now run the optimiser again, you should find you’re now left with just 1 Take Action notification, since the removal of the unused data sources, also removed the Needs Review item which was advising about the number of data sources being used.

Take Action – Non-materialized calculations

For a bit further info on what this means and how to resolve it, check out these Tableau KB articles : Workbook Optimizer and Materialize Calculations in your Extracts.

But ultimately, to resolve this, right click on the relevant data source > Extract > Compute Calculations Now

Run the Optimizer again and fingers crossed, you should now get a 12/12 success!

My published version of the viz which has been optimized can be viewed here.

Happy vizzin’!

Donna

Can you switch between KPIs?

Donna ColesLeave a comment

In this instalment of #WOW2022, Kyle posed a challenge based on a previous viz he had published which in turn was inspired by Lindsey Poulter‘s Set Actions workbook.

I made an attempt to build a solution without referencing Lindsey’s viz, but ended up building multiple sheets and creating multiple calculations, which just ‘didn’t feel right’ – it would have worked, but it just seemed ‘too clunky’ of a solution, so I ended up checking out Lindsey’s viz as a guide. The key to the ‘simpler’ solution involves utilisation of a field in the data that allows the ‘measures’ to be arranged horizontally in a single sheet. I’ll explain further as we get to that section. I also don’t make any use of set actions in my solution. Instead I use parameter actions. So let’s crack on…

To build this solution I need 7 sheets

  • 1 sheet to display the 5 measure boxes with the summary stats per player for each measure
  • 1 sheet per sparkline that is displayed in each box (5 sheets in total)
  • 1 sheet to display the larger line chart at the bottom which changes the measure on selection of a card

Creating the measure calculations

Firstly, we need to set up the measures that are being used throughout this viz. Some are new calculations, some are just renamed fields. I renamed the following fields :

  • G -> GAMES (formatted to 0 dp)
  • H – > HITS (formatted to 0 dp)
  • HR -> HOME RUNS (formatted to 0 dp)

then I created

HITS/GAME

ROUND(SUM([HITS])/SUM([GAMES]),2)

AB/HR

ROUND(SUM([AB])/SUM([HOME RUNS]),2)

Building the measure swap line chart

I’m going to start by building out the line chart at the bottom of the dashboard, as once built and formatted, we can duplicate it to form the basis of the other 5 line charts.

As with any measure swap chart, we need to create a parameter that is going to store the value of the measure that has ben selected to show. In this instance we just need

pMeasure

a string parameter which is defaulted to the value ‘GAMES’

Then we need a field that is going to determine which measure to display based on the parameter value

Value to Display

CASE [pMeasure]
WHEN ‘GAMES’ THEN SUM([GAMES])
WHEN ‘HITS’ THEN SUM([HITS])
WHEN ‘HITS/GAME’ THEN [HITS/GAME]
WHEN ‘HOME RUNS’ THEN SUM([HOME RUNS])
WHEN ‘AB/HR’ THEN [AB/HR]
END

Show the pMeasure parameter, then add Year (as green, continuous pill) to Columns and Value to Display to Rows and Player to Colour. Adjust colours accordingly. Add Year (as blue discrete pill) to the Filter shelf, and exclude 2022, as Kyle chooses not to show this data – probably as its an incomplete year).

Manually change the values in the parameter to HITS, or HITS/GAME etc and check the display changes as expected.

On the Label shelf, select to Show Mark Labels and only label Max/Min values. Modify the colour of the label to Match Mark Colour.

Finally, format the Value to Display field to Number(Standard). This is a format that will automatically display the values as whole numbers or decimals, which is really neat (Note – it’s only while rebuilding the solution to blog that I remembered this and tried it out, so you may see that my published solution has a bit of a convoluted calculation to get the display as I needed).

Edit the Year axis to start at 2000 and end in 2022, and remove the title. Edit the Value to Display axis to not include zero and remove the title. Remove all gridlines.

Finally amend the tooltip, and then edit the title of the sheet to reference the pMeasure parameter.

Building the individual sparkline charts

We need to create a sheet per measure to be used in the sparkline display in the card. To create the first one, duplicate the Selected Measure Trend sheet you just created, then

  • Uncheck Show mark labels
  • Uncheck Show Tooltips
  • Hide the Year axis
  • Replace the Value to Display pill with the GAMES field (drag the GAMES field from the Dimensions pane and drop it directly on the Value to Display pill, so it is just replaced).
  • Hide the Games axis
  • Format to remove all zero lines/axis rulers
  • Set the worksheet background colour to None (which will make it transparent when we add it to the dashboard later, although you won’t notice anything at this point).
  • Name the sheet Games Trend or similar.

Now duplicate this sheet, and replace the GAMES pill with the HITS pill. Name this new sheet Hits Trend or similar.

Repeat this process for the HITS/GAMES, HOME RUNS, and AB/HR measures, so you have 5 trend charts.

Building the Measure Selector card

We’re going to use some Tableau trickery to ‘fake’ these cards. We’re looking to build a table of 1 row and 5 columns where each column contains 3 lines of text.

We can’t just use Measure Names & Measure Values split by Player, as we need to create a ‘box’ for each measure with the Players text displayed ‘together’ top left.

So we use some trickery.

Firstly, we’re going to ‘map’ the name of each measure to a Year in the data set which exists for each Player (ie we use the last years and not the first ones).

Metric Name

CASE [Year]
WHEN 2017 THEN ‘GAMES’
WHEN 2018 THEN ‘HITS’
WHEN 2019 THEN ‘HITS/GAME’
WHEN 2020 THEN ‘HOME RUNS’
WHEN 2021 THEN ‘AB/HR’
END

Then we need to capture the get the set of measures for each player into a dedicated field :

C Measures

IF [Player] = ‘Cabrera’ THEN
CASE [Metric Name]
WHEN ‘GAMES’ THEN {FIXED [Player]: SUM([GAMES])}
WHEN ‘HITS’ THEN {FIXED [Player]: SUM([HITS])}
WHEN ‘HITS/GAME’ THEN {FIXED [Player]: [HITS/GAME]}
WHEN ‘HOME RUNS’ THEN {FIXED [Player]: SUM([HOME RUNS])}
WHEN ‘AB/HR’ THEN {FIXED [Player]: [AB/HR]}
END
END

P Measures

IF [Player] = ‘Pujols’ THEN
CASE [Metric Name]
WHEN ‘GAMES’ THEN {FIXED [Player]: SUM([GAMES])}
WHEN ‘HITS’ THEN {FIXED [Player]: SUM([HITS])}
WHEN ‘HITS/GAME’ THEN {FIXED [Player]: [HITS/GAME]}
WHEN ‘HOME RUNS’ THEN {FIXED [Player]: SUM([HOME RUNS])}
WHEN ‘AB/HR’ THEN {FIXED [Player]: [AB/HR]}
END
END

We’re using FIXED LoDs to get the totals of each measure across all years for each Player

You can see below that the numbers highlighted match the numbers per player from the image above

With these calculations, we can now build the ‘card’.

Add Year (blue discrete pill) to Columns, and filter to only show the years 2017-2021. Type in MIN(1) on the Rows shelf. Change mark type to Bar and edit the axis to be fixed from 0-1. Set the Fit to Fit Width.

Add Metric Name, P Measures and C Measures to the Label shelf. Adjust the label so it is formatted with the right colours, and aligned left. I used some extra spaces at the start of each line so that there was a bit of padding.

To colour the bars, we need a further calculated field

Measure is Selected

[pMeasure]=[Metric Name]

Add this to the Colour shelf and adjust accordingly and reduce the opacity to 50%.

Hide all headers/axes, remove all gridlines/axis rulers and format the background colour of the worksheet to None as you did before. Don’t show the tooltip.

Finally we need to create & add a couple more fields to prevent the selected box from remaining highlighted when we click on it. Create fields

True

TRUE

False

FALSE

and add both to the Detail shelf.

Building the dashboard

Now we have the components to build the dashboard. The Measure Selector sheet and the 5 sparkline charts will all be floating objects carefully arranged. It’s up to you whether the remaining objects will also be floating or captured in tiled containers.

The key with the Measure Selector and Sparkline sheet is that all the sparkline sheets should be sent to back, so they are behind the Measure Selector card. This is where the transparency settings help as you can then ‘see through’ the sheet that is on the top.

You should also make sure each of your sparkline sheets are the same height & width and start at the same y position

Adding the interactivity

Once all your objects are placed, add a parameter dashboard action that on select of the Measure Selector sheet will set the pMeasure parameter, passing in the Metric Name field.

Then create a filter dashboard action that will on select of the Measure Selector object on the dashboard, will filter the Measure Selector sheet itself, setting the fields True = False. As this can never happen, the filter doesn’t apply, and the sheet removes the auto highlight that Tableau applies.

Hopefully, you now have a fully functional dashboard with a chart that changes on click of a KPI card in the top section. My published viz is here.

Happy vizzin’!

Donna

Can you make a hexbin map?

Donna ColesLeave a comment

Sean Miller was back this week to set this challenge to recreate a ‘rat sighting’ map using hexbins. I’ve only used hexbins in other #WOW challenges, so needed a bit of a refresher (the previous challenges pre-dated my own blog, so I couldn’t use myself as a reference). A quick google for ‘tableau hexbins’ and I found a variety of articles that provided the refresher needed.

Sean also used the opportunity to apply some other crucial skills – adding custom shapes and custom colour palettes, which I recommend is the first step you do in completing this challenge.

Adding custom shapes

Download the hexagon shape provided by Sean, and then save it into a folder in your …My Tableau Repository\Shapes directory. I have a folder called ‘Custom’ where I place random shapes I need. This post will help you out if you’re having difficulty with any of this.

Adding custom colour palette

Open a text editor such as Notepad, then open the preferences.tps file that is located in your .. My Tableau Repository directory. Copy & paste the block of code provided by Sean between the opening and closing <preferences> tag. Save the file and close the text editor. This post will help if you’re having trouble.

Building the map

The provided rat sighting data set contains a Longitude and Latitude value for every rat sighting since 2010.

Hexbins provide a way to group (bin) these Lat & Long values together. The size of the bin is typically determined by a parameter, so lets first set this up

pRatio

integer or float parameter which I set to a default value of 250 (Sean didn’t specify the value he’d used, but trial and error suggested to me this looked ‘about right’).

Now we can build the bins

HexbinX

(HEXBINX([Longitude]*[pRatio], [Latitude]*[pRatio])) / [pRatio]

Edit the geographic role of this field to be mapped to Longitude (right click on field -> Geographic Role).

HexbinY

(HEXBINY([Longitude]*[pRatio], [Latitude]*[pRatio])) / [pRatio]

Edit the geographic role of this field to be mapped to Latitude.

Add HexbinX to Columns and HexbinY to Rows. Modify each field so that it is a continuous dimension.

Change the mark type to Shape and select the hexagon shape you saved earlier.

Add the auto generated ‘count of dataset’ (Count of Rows) field to Colour, and adjust the colour to use the OrRd-5 palette you added earlier. Ensure to set the palette to Reversed.

Set the Tooltip so it doesn’t display anything on hover, and add Borough to the Detail shelf (this is needed for the interactivity later). Hide the nulls indicator that displays in the bottom left (right click -> hide).

On the Map menu, select Map Layers, and set the background style to dark.

Using the map controls, zoom in and pan to the left slightly, so the coloured area is mainly central and there’s less ‘sea’ at the bottom. It’s likely that you may need to adjust further once you’ve placed the map on the dashboard. But before publishing, we want to turn the map controls off, to prevent a user from shifting the display (Map -> Map Options -> uncheck all options).

Building the Bar Chart

On a new sheet, add Borough to Rows and Count of Rows to Columns. Add Borough to Filter and exclude Null and Unspecified. Sort the rows descending. Adjust the colour to suit.

Show mark labels, but only display the max & min values. Format the Count of Rows pill so the labels are displayed in K to 2 dp.

Hide the axes, right align the row label headings, adjust the tooltip. Hide the column heading. Remove zero lines and axis rulers. Set the background colour of the worksheet to ‘None’ (ie transparent).

Further formatting is required, but this is best done after the sheet is added to the dashboard, as you’ll lose visibility of the text at this point.

Creating the dashboard

Add the map to a dashboard, and remove all the additional containers/legends etc that are added, and the sheet title. Then add the bar as a floating object and position bottom left. Fit to entire view. Edit the title so it contains the ‘on hover’ instruction and format in light grey font.

Now you can format the row labels, the row headings and the gridlines to be appropriate colours – light grey rather than white.

Add a dashboard highlight action that on hover of the bar chart, highlights data in the Map

Then add floating text boxes and add the title and description. I ended up adding a text box just containing the OH, and then another position just below containing RATS! and then the description, as otherwise the carriage return between OH & RATS! made the spacing too wide. I used the controls on the layout tab to ensure both text boxes were positioned at the same x-coordinate.

Hopefully, you should now have a beautiful looking viz. My published version is here.

Side note – When I first started building this I tried from memory, and didn’t quite get things right. I then adjusted various fields as described above, but when I then tried to add the Count of Rows to Colour, I was only ever getting a value of 1 against each bin. I double & triple checked all the calcs and couldn’t see any issues. It was very weird. I simply ended up closing down my workbook and starting again from scratch and all was fine. I’m just letting you know this in case you too come across any oddities during your build, and things don’t behave as expected. It meant, what was ultimately quite a straight forward build (once I got the calcs right), ended up involving more time and head-scratching than really required 😦

Happy vizzin’!

Donna

Filter Challenge : How well do you know Tableau’s Order of Operations?

Donna ColesLeave a comment

It was Erica Hughes’ turn to set the #WOW challenge this week. This ended up being a bit of a journey for me, because of one requirement – to use the max date of the dataset.

I typically use a FIXED LOD to determine the max date, which I did initially in this case, and after building a solution, tripped up when it came to adding some of the filters ‘to context’. The context filter meant my Max Date was changing depending on the filter and subsequently I wasn’t getting the right results for the ‘days since last purchase’.

So I ended up having to put my thinking cap back on, and after a lot of trial and error and reading on the internet (including this article by the Flerlage Twins) , I finally managed to get a solution that involved both LOD calculations (including a rarely used INCLUDE LOD) and Table calculations, and no context filters at all. This was a pretty complex solution. The table of data had to include the Order Date on the Detail shelf, resulting in multiple rows per customer showing, which meant I had to then use an INDEX()=1 filter to only show 1 row for each customer. I then used an additional INDEX() against each customer, so I could filter to show only the customers in position 1-10. My solution to this is published here.

After publishing, I checked Erica’s solution and found how she’d handled the max date requirement. It used a concept I haven’t had to use so far, so I decided to rebuild a less complex solution, which is what I will now blog about.

Capturing the max date of data set which isn’t affected by context filters

The usual way to define the maximum date in the data set is to create a FIXED LOD calculation, and this is still required

Max Date

{FIXED :MAX([Order Date])}

This returns 30 Dec 2021.

The problem is, once a context filter is applied to a sheet, the value of this field can change. For example, if Region is applied as a context filter and set to ‘South’, then what Tableau will do, based on the ‘order of operations’, is first filter all the data to just those records where Region=South. It will then work out the max date of these filtered records, and if there are no orders on 30 Dec 2021 for the South Region, then the value of the Max Date field will differ. This is because context filters get applied before FIXED LOD calculations. We need a way to ensure we can retain the 30 Dec 2021 without hardcoding it.

The solution is to use a parameter.

pMaxDate

A date parameter which is set to use the value of the Max Date field when the workbook is opened.

This is quite a sneaky but clever way to retain this, which is why I’m reworking my solution to use it, so I have something for future reference myself. When the workbook opens, the pMaxDate parameter will get set to 30 Dec 2021 and won’t ever change, whereas the Max Date field will change if context filters are used.

Building the Table

It may seem a bit odd, but I’m going to start with the table of data that’s displayed, as this requires the most calculations, and the most validation.

On a new sheet, add Customer Name, Customer ID to Rows and Sales onto Text. Order by Sales desc. Format Sales to $ with 0dp.

We need to determine the latest order date for each customer in order to work out other information.

Latest Order Date

{FIXED [Customer ID] : MAX([Order Date])}

This finds the maximum Order Date for each Customer.

And with this, we can now work out

Days Since Last Purchase

DATEDIFF(‘day’, [Latest Order Date], [pMaxDate]

Note this is comparing the Latest Order Date to the pMaxDate parameter instead of Max Date.

Format this to custom number, with 0 dp and a suffix of ‘ days’.

Add Latest Order Date to Rows (exact date, discrete) and Days Since Last Purchase into the table.

Now we need to work out

Latest Order Amount

{FIXED [Customer ID]: SUM(IF [Order Date]=[Latest Order Date] THEN [Sales] END)}

For each customer, if the Order Date is the same as the Latest Order Date, then retrieve the Sales value.

Format this to $ with 0 dp and add to the view.

Now add Customer ID to the Filter shelf and set to filter by the Top 10 based on Sales.

To validate the behaviour of the calculations add Region to the Filter shelf and select ‘South’. You’ll notice only 8 rows are shown, and not 10.

This is because the data has been filtered based on the top 10 customers with the most sales first, and then restricted those top 10, to those with sales in the South. Only 8 of the top 10 customers have sales in the South, hence the 8 rows.

To resolve this, we need to add Region to Context (right click on the filter and Add to Context . This has the effect of filtering all the data by Region = South first, and then displaying the top 10 customers by Sales. We now have 10 rows displayed.

Add State to the Filters shelf, and add that to context too. Test the table of results by selecting different combinations of regions and/or states and comparing to the results on Erica’s solution to verify all the calculations are behaving as expected.

Now we’re happy the table is displaying the data as expected, we can format it and make it ready for the dashboard :

  • Remove Latest Order Date & Customer ID
  • Set the Row Banding
  • Set the Row Dividers to Level 0, so only row lines appear at top and bottom
  • Remove Column Dividers
  • Format all text (row/column headings & text data) to be 8pt.
  • Alias Sales to be Total Sales (right click on the Sales title in the columns and Edit Alias)
  • Remove Region & State from the Filter shelf. These will get re-added later.

Building the Map

On a new sheet, double click State to load a map (you may need to set your location to United States : Map menu -> Edit Locations).

Change mark type to a Filled Map, add Region to Colour and adjust colours.

Remove all map layers (Map menu -> Map Layers and uncheck all options listed on left hand side). Change border on Colour shelf to white.

Remove row & column dividers, and remove all map options (Map menu -> Map Options and uncheck all options).

Drag a new instance of State onto the canvas and Add a Marks Layer. This will create a 2nd marks card on the left. Change the colour of the circles to black. Add Sales to the Size shelf and adjust. Adjust the tooltip of both marks card (you’ll need to add Sales to the Tooltip on the map marks card).

Building the Legend

On a new sheet add Region to Columns and type in MIN(1) into the Columns shelf too. Add Region to the Label shelf, and the Colour shelf. Adjust the height of the rows, so you can see the text.

Edit the axis so it is fixed from 0 to 1. Then format the Label so the font is larger and centred.

Hide the axis and the Region (uncheck show header). Remove row & column dividers and any gridlines. Adjust the border on the Colour shelf to be white. Set the tooltip not to show.

Building the Bar Chart

On a new sheet, add Sub-Category to Rows and Sales to Columns. Order by Sales desc. Add Sub-Category to the Filter shelf and set to show Top 10 by Sales. Reduce the Size of the bars, and align the row labels to be left aligned, and the font to be 8.

Format the axis, so the values are displayed in $K.

Hide the row label, and adjust Tooltip. You may need to create an additional calculated field based on Sales to add to the Tooltip which you can then format to $ with 0dp.

Adding the interactivity & context filters

Add the sheets onto a dashboard. You’ll need to use a mixture of vertical & horizontal layout containers, inner and outer padding and background colours to get the required layout.

Add a dashboard filter action which runs on Select when the Legend sheet is selected, which affects all other sheets and shows all values when unselected.

Add another filter dashboard action, which this time runs on Select when the Map sheet is selected, which affects all sheets except the Legend sheet, and also shows all values when unselected.

Now click on one of the regions in the legend, then click on a state in the map. This has the effect of adding filters to the shelves on the other sheets. Navigate to the Table sheet, and add the 2 ‘Action’ filters to context

Do the same for the Bar char sheet.

And you should now have a completed viz. My published version based on this solution is here.

Happy vizzin’!

Donna

Can you track headcount?

Donna ColesLeave a comment

Luke decided to set us a challenge this week based on Human Resources (HR) data, using a dataset provided as part of the #RWFD Real World Fake Data project managed by Mark Bradbourne.

The challenge focussed on reporting monthly headcount, using a dataset that contained 1 row per employee with a column relating to the start date (Hire Date) and a column relating to the date the employee left (Termdate), which could have been a future date or NULL/blank.

I’ve worked on headcount reporting before in my day job, but have used a snapshotted data source, which captures the active employees as at midnight on the 1st day of every month. This method makes counting the headcount each month very simple, as it’s just counting the number of rows per snapshot date.

Obviously, this wasn’t an option in this case. Luke gave very little clues as to what approach would be required, apart from saying ‘you will also need to manipulate the data’….

A word about the data…

Just a little side note here…. I ended up in discussions with my fellow #WOW participant Rosario Gauna as after attempting one method, I wasn’t getting as high numbers as the solution originally suggested. There were rows in the data set that had NULL values for most of the data except job title, but I think this was only around 225 rows, so even including them I didn’t get close to the figures. Rosario however, was somehow managing to see approx 15,000 rows of null data. I ended up building and publishing my initial solutions based on a version of the data sent to me directly by Rosario.

Since then, it seems others were also confused, and so the solution was adjusted to ignore all the null rows. As a result I reworked one of my solutions to use the original data source I downloaded from the site, and it’s this I will focus on in the blog.

A word about my solutions…

With little instruction, it took a bit of head-scratching to figure out how to work through the problem. I initially decided to use a scaffold dataset to help. I built a simple file in Excel which contained 1 column listing 1 date per month which went from 01 Jan 2017 to 01 Jan 2021. I then joined this to the HR data in Tableau Desktop using the physical data layer (ie I did not use relations), using join calculations as below (note, this is using the amended copy of data I received where Termdate was already defined as a date field.

The solution using this method is published here.

However, I didn’t feel completely comfortable with this approach. Luke had suggested the difficulty of this was 9/10 and referred to nested tableau calculations… the above solution just used quick table calcs. So I decided to see if I could come up with an alternative, just using the provided data, and that’s what I’ll document below.

Setting up the data

I downloaded the Human Resources.csv file from the link provided, and connected to it in Tableau Desktop. I found the Termdate field presented as a string field, so I converted it to a date, just by clicking on the ABC symbol of the data type against the field. I also added a data source filter to exclude all records where the Id field was NULL.

I then added a new data source, and connected to the same file again. This meant I had 2 instances of the Human Resources data source listed in my Data pane. I renamed the 2nd instance to be Leavers Only, changed the data type of the Termdate field again and added a data source filter so that only records with non-Null Termdate values were retained.

Note – What I will be doing here is using the Human Resources data source to manage the months we’re reporting headcount over. This method only works if there is at least 1 new starter every month, which in this case there is.

Building the calculations

The requirements state that a new starter doesn’t count in the monthly headcount figures until the following month, so the date field we need to use in the output, needs to be created as

Report Date

DATE(DATETRUNC(‘month’,DATEADD(‘month’,1, [Hire Date])))

This basically shifts the date the new starter joined to the 1st day of the following month.

To make the calculations easier to read, I also created

Starters

COUNT([2022_04_06_WW14_Human Resources.csv])

which is literally a reference to the field that automatically gets added as part of the connection – it’s just a bit of a mouthful as its named according the data connection.

Let’s pop these out in a table as below – I’m deliberately using the dateparts of the Report Date field as they’ll be used in the viz like this.

So this is just showing the new starters against the month at which they will count for headcount. We need to get details of leavers now, and we’ll do this by blending.

When I blend data sources, I tend to create specific fields so it’s clear to me that they’re being used for blending, rather than defining blend relationships between differently named fields. So in the Human Resources data source I’ve created

BLEND – Date

[Report Date]

and then in the Leavers data source, I’ve created

BLEND – Date

DATE(DATETRUNC(‘month’,DATEADD(‘month’,1,[Termdate])))

which is shifting the Termdate forward to the the 1st of the next month, since leavers should also only be recorded in the following month.

In the Human Resources data source I then created

Leavers Per Month

ZN(COUNT([2022_04_06_WW14_Human Resources – Leavers Only].[2022_04_06_WW14_Human Resources.csv]))

which is just referencing the automatically generated ‘count’ field from the Leavers data source. I’ve wrapped it in ZN so 0 is reported in the event no match is found.

Add Leavers Per Month into the table, and ensure the linking field from the secondary Leaver data source is connected on the BLEND – Date field

So now we know how many starters and leavers per month, we now need to total these up by generating a cumulative running sum of the starters, but subtracting any leavers along the way.

Official Headcount

([Starters] + PREVIOUS_VALUE(0)) – [Leavers Per Month]

Takes the value of the Starters in the current row, adds it to the value of Official Headcount from the previous row, then subtracts any Leavers recorded against the current row. Add this onto the table, and as its a table calculation, edit it so it is explicitly computing my the month & year of Report Date

For the annual change, we’re looking to compare the difference of the Official Headcount value for the current month eg Jan 2021, with the Official Headcount value for the same month 1 year ago eg Jan 2020.

Annual Change

(ZN([Official Headcount]) – LOOKUP(ZN([Official Headcount]), -12)) / ABS(LOOKUP(ZN([Official Headcount]), -12))

Take the Official Headcount from the current row, subtract the Official Headcount from 12 rows before, then divide the result by the Official Headcount from 12 rows before.

Format this to a percentage at 1 dp. (For some reason my numbers here don’t seem to match Luke’s revised solution…).

Again add to the sheet and set the table calcs of both the nested calcs to compute by month and year

The final measure was a bit of a strangely named one IMO. I felt I should be doing some rolling calculation, but ultimately, it just seemed to be reporting the difference between the headcount now and that 12 months ago ie the numerator of the calculation above.

Net Rolling 12 Change

(ZN([Official Headcount]) – LOOKUP(ZN([Official Headcount]), -12))

Pop this into the view, adjust the table calc settings again

Building the viz

On a new sheet, add Report Date at the month level, but then set to be continuous, to Columns and Report Date at the year level to Colour. Add Official Headcount to Rows (adding the linking field for the blend when prompted), and adjust the table calc settings to compute over both month & year, ensuring the Year of Report Date is listed first.

Then in the legend, select all the years from 2000-2017 and ‘hide’ (this is probably a cheat way, but the quickest – we need to retain the data from the previous years, so can’t just ‘filter’).

Edit the axis to not start from zero. Adjust colours to suit.

Add Annual Change and Net Rolling 12 Change to the Rows shelf, adjust both of the table calc settings, so all nested calcs are computing by Year & then Month.

Edit the axis of both these additional measures to also exclude 0. On the All marks card, click the Label button and tick the Show mark labels option.

Final steps ….

…on the All marks card, click the Tooltip button and uncheck Show tooltips so no tooltips display on hover.

Right click on the Month axis and format the axis so the dates in the Scale section are displayed as abbreviated dates

Edit the same axis and remove the title, then format the whole chart to

  • remove all row and column dividers
  • to set the row banding with a band size of 1
  • adjust the colour of the row and column gridlines to be a slightly darker shade of grey

And you’re done 🙂 My published viz is here

Had a bit of all sorts this week… I’ll be intrigued to see how Luke solved it!

Happy vizzin’!

Donna

Can you use multiple mark layers?

Donna ColesLeave a comment

Lorna Brown provided a refresher on map layers and spatial calculations for the #WOW2022 challenge this week. You’ll need Tableau Desktop v2020.4 or later to complete this due to the functionality incorporated.

The data set provided contains rows of origin & destination airports including the latitude and longitude values for each.

I found there were some fields in the provided data set which I don’t think should have been there. It meant when I came to naming some of my fields, I had to be more creative due to the existing ones.

We’ll start off by setting up the calculations required.

Firstly we need to create a spatial object out of our Origin and Destination airports.

Origin

MAKEPOINT([Origin Latitude],[Origin Longitude])

Destination

MAKEPOINT([Destination Latitude],[Destination Longitude])

Next we’re going to need to have a line to connect these

Origin-Dest Line

MAKELINE([Origin],[Destination])

We’re going to need ‘buffer’ to define the circle displayed. The size of this is to be defined by the user, so we’ll need a parameter

pBufferSize

integer parameter defaulted to 1000

and with this, we can then define the buffer

Buffer from Origin

BUFFER([Origin],[pBufferSize], ‘miles’)

Finally we need to determine whether the destination airport is within the buffer ‘zone’.

Within Buffer

[Distance (Miles)]<=[pBufferSize]

Note: Distance (Miles) already existed within the downloaded data set. I was expecting to have to calculate the distance myself, given the nature of the challenge. If I had had to create it, I would have used the calculation DISTANCE([Origin], [Destination], ‘miles’)

This is all we need to build the viz. I’m going to start from the top – down ie Origin airport -> Destination airport -> Lines – >Buffer.

On a new sheet, add Origin Airport to Filter and set to LAS. Also add Within Buffer to the Filter shelf and set to True.

Then drag Origin onto the main canvas area and drop it when you see ‘Show Me’ displayed on the cursor. This will automatically add all the required fields into the relevant locations

Change the mark to a circle, increase the size and set the colour to black. Add Origin Airport to Label and Origin Name to Tooltip, and adjust the tooltip accordingly.

This is the first map layer.

Now drag Destination onto the canvas and drop it on the ‘stacked layer’ icon when you get the Add a Marks Layer option appear

This will create a new marks card on the left, and is the 2nd marks layer.

Add Destination Name to the Detail shelf, then change the mark type to square. Add Destination Airport, Origin Airport and Distance (Miles) to Tooltip and adjust tooltip to match.

Finally move the Destination marks card so it is below the Origin marks card. Click on the Destination card and drag to below the Origin card and drop when you see the orange line appear

Now drag Origin-Dest Line onto the canvas and drop it on the ‘stacked layer’ icon when you get the Add a Marks Layer option appear to create the 3rd marks layer. This will add all the ‘spokes’.

Change the colour to grey. Add Destination City to the Detail shelf. Add Origin Name, Origin Airport, Distance (Miles) and Destination Airport to the Tooltip and adjust accordingly. Finally, drag this marks card so that it is now below the Destination marks card.

Final layer now – the buffer zone.

Drag Buffer from Origin onto the canvas and drop it on the ‘stacked layer’ icon when you get the Add a Marks Layer option appear to create the 4th marks layer. Adjust the colour to light grey, and reduce the opacity to suit. Then move this marks card to the bottom, so its beneath the Origin-Dest Line marks card.

And that’s it. Add the sheet onto a dashboard, and show the Origin Airport filter as a Single Value dropdown so only 1 origin airport can be selected at a time. I also customised the control so the All option did not display either. Add the pBufferSize parameter to the display too, and test the viz by changing the size and the airport.

My published version of the viz is here.

Happy vizzin’!

Donna