Tag: Week 24

Can you Iron Viz?

Donna ColesLeave a comment

For this week’s #WOW2025 challenge, I looked to recreate part of Kathryn McCrindle’s TC25 Iron Viz Final dashboard. The main focus was building the ‘cockpit dials’ – the concentric donut charts, but I also wanted to use the challenge to highlight a couple of techniques Iron Viz finalists use to make the build quicker – background images and custom themes, a new feature introduced in v2025.1. I provided 3 files for this challenge, accessible from here.

  • A simplified dataset
  • An image for the background
  • A json file for the custom theme.

Checking the numbers

To start, we’ll build out some of the calculations needed to sense check the logic.

After connecting to the data, make sure all the fields are defined as dimensions (above the Measure Names line on the data pane) – if they aren’t drag them up. The only field in the Measure Values section should be the count of the records.

We first need to identify how many times each part was recorded in a strike (or not). Drag Part to Rows and Struck? to Columns. Create a new field

# Strikes

COUNTD([Strike ID])

Add to Text. This gives our basis for the inner ring.

The True values above, form the basis for which we then want to understand the split between whether the struck part was then damaged or not. On a new sheet, add Part to Rows and Damaged? to Columns, then create field

# Struck

COUNTD([Strike ID – Is Struck])

and add to Text. Additionally add row grand totals (Analysis menu > Totals > Show Row grand totals). The total for each row should match the True value from the previous sheet (pop the sheets side by side on a dashboard to sense check).

Once again, the Damaged? = True values, form the basis for which we then want to understand the split between whether the struck part was then substantially damaged or not. On a new sheet, add Part to Rows and Substantially Damaged and Part Damaged? to Columns, then create field

# Damaged

COUNTD([Strike ID – Is Damaged])

and add to Text and show row grand totals. Add this sheet to the dashboard too. The total for this sheet should match the True values from the Damaged sheet.

Building the Donuts

Now we’re happy with the numbers, we can start to build the viz. We’ll be using map layers for this, but as we don’t actually have a geometric datatype field in the data set, we need to create one. I’ll be following the core principles discussed in this blog post I wrote for the company I work for, Biztory. In the blog post, we’re just building 1 donut chart, so created a Zero point using MAKEPOINT(0,0). In this instance, I want multiple donuts arranged in rows and columns. This can be done in 2 ways:

  1. use the MAKEPOINT(0,0) method and then create 2 dimension fields added to Rows and Columns that represent the row and column for each Part (akin to what we do when building trellis/ small multiple charts) or
  2. use the MAKEPOINT method to define the point to position the donut. This is the method I’m using, but will break the fields required down, so it’s clear.

Create a new field

Row

CASE [Part]
WHEN ‘Engine’ THEN 1
WHEN ‘Nose’ THEN 1
WHEN ‘Windshield’ THEN 1
WHEN ‘Lights’ THEN 2
WHEN ‘LandingGear’ THEN 2
WHEN ‘Radome’ THEN 2
WHEN ‘WingOrRotor’ THEN 3
WHEN ‘Fuselage’ THEN 3
WHEN ‘Propellor’ THEN 3
WHEN ‘Tail’ THEN 4
WHEN ‘Other’ THEN 4
END

Column

CASE [Part]
WHEN ‘Engine’ THEN 1
WHEN ‘Nose’ THEN 2
WHEN ‘Windshield’ THEN 3
WHEN ‘Lights’ THEN 1
WHEN ‘LandingGear’ THEN 2
WHEN ‘Radome’ THEN 3
WHEN ‘WingOrRotor’ THEN 1
WHEN ‘Fuselage’ THEN 2
WHEN ‘Propellor’ THEN 3
WHEN ‘Tail’ THEN 1
WHEN ‘Other’ THEN 2
END

Ensure both of these are dimensions, by dragging above the Measure Names on the data pane if required. These fields are simply ‘hardcoding’ the position of each Part (and can be used if adopting the ‘trellis’ route).

Then create a field

Grid Position

MAKEPOINT([Column],[Row])

On a new sheet, add Grid Position to Detail and for now, add Part to the Label shelf so we can see what Part each mark represents.

Change the mark type to Circle.

Drag another instance of Grid Position on to the canvas and drop when you see the option Add a Marks Layer. This will create another marks card called Grid Position (2).

Now we have 2 layers, we can reposition the marks. Note – doing the next steps before adding a 2nd marks layer, won’t let you then create any layers.

Use the swap axis button to change the axis so Latitude is on Columns and Longitude on Rows. Edit the Longitude axis (y-axis) and set the scale to be reversed. The Parts should all now be in the right place.

Now we can start to make the donuts. When using map layers, its good practice to rename the marks cards, so start by renaming the marks card Grid Position to be Centre and Grid Position (2) to be Struck Ring.

On the Centre marks card, change the colour to be dark grey (#44505b) and increase the size. Create a new field

Label – # Substantially Damaged

COUNTD(IF [Substantially Damaged and Part Damaged] THEN [Strike ID – Is Damaged] END)

Add this to the Label shelf and move Part to Detail instead. Position the label middle centre, but don’t adjust the font style in any way.

Create new fields

Tooltip – #Damaged

COUNTD(IF [Damaged?] THEN [Strike ID – Is Struck] END)

and

Tooltip – #Struck

COUNTD(IF [Struck?] THEN [Strike ID] END)

and add these to the tooltip shelf of the Centre marks card, and adjust Tooltip text – again don’t adjust the font style.

Click on the Struck Ring marks card. Drag it so it is under the Centre marks card. Add Part to Detail. Increase the Size until you can see a ring larger than the centre circle. Change the Mark Type to Pie and add #Strikes to the Angle shelf. Add Struck? to Colour. Adjust the colours, and re-order the colour legend so True is listed first.

Disable Selection on the Struck Ring marks card

Add another layer by dragging Grid Position on to the canvas. Rename this marks card to Inner Dark Grey Ring and move the card to the bottom. Add Part to Detail and then increase the Size. Change the mark type to Circle and set the colour to dark grey. Disable selection of the card.

Add another layer by dragging Grid Position on to the canvas. Rename this marks card to Damaged Ring and move the card to the bottom. Add Part to Detail and then increase the Size. Change the mark type to Pie, add #Struck to Angle and add Damaged? to Colour and adjust. Reorder the colour legend so True is listed first. Disable selection of the card.

Add another layer by dragging Grid Position on to the canvas. Rename this marks card to Outer Dark Grey Ring and move the card to the bottom. Add Part to Detail and then increase the Size. Change the mark type to Circle and set the colour to dark grey. Disable selection of the card.

Add the final layer by dragging Grid Position on to the canvas. Rename this marks card to Substantial Damage Ring and move the card to the bottom. Add Part to Detail and then increase the Size. Change the mark type to Pie, add #Damaged to Angle and add Substantially Damaged and Part Damaged? to Colour and adjust. Reorder the colour legend so True is listed first. Disable selection of the card.

Note – you may find you have to tweak the sizes of each layer – when published to Tableau Public, you can do this more accurately by setting a size % – on Desktop you just have to guess the slider position

Add in the custom theme

On the Format menu, select import custom theme and select the json file provided. When prompted select Override to apply the theme.

The font style and other properties of the chart should immediately update

Hide the axis, remove axis rulers and re-name the sheet.

Create the dashboard

Create a dashboard and set it to 1000 x 900. Add an Image object onto the dashboard, and select the background image provided, setting it to fit and centred. Remove the outer padding from both the image object, and the tiled container, so that the image object is exactly 1000 x 900.

You can see, that the labels for each donut, are actually part of the background image. So we now need to add the viz so it is positioned in the right place.

Change the dashboard objects to be ‘floating’ and add the Donut viz. Remove the Title, and delete the colour legends. Adjust the position and height/width to be x=448, y=104 and width=516 and height = 762 (this was done by trial and error – Iron Vizzers will have practiced so many times, they will know what they want these numbers).

But the background isn’t visible through the chart, so right click on the Donut sheet (while its on the dashboard), and Format and then set the background to be set to None.

And that is the crux of the challenge. I then added a floating title along with my standard footer. My published viz is here.

Happy vizzin’!

Donna

Can you visualise headcount distribution in multiple ways on a single sheet?

Donna Coles5 Comments

This week’s challenge focuses on allowing the user flexibility in both how they want to ‘slice’ the measure being reported (actual headcount), and how they want the results displayed to meet the needs of the question they are trying to answer. Whilst the above could be built using multiple sheets and ‘sheet swapping’ using dynamic zone visibility, the aim is to try to build within a single sheet.

Let’s get cracking.

Enhancing the data

Not all the fields we need to slice the data by are included initially in the data provided, so we need to define some additional calculated fields, as stated in the requirements.

Contract Type

IF [FTE] = 1 THEN ‘Full Time’ ELSE ‘Part Time’ END

To determine the Age Bracket an employee belongs to, we first need to know their age. Usually this would be based on today’s date (using the TODAY() function), but as this data is static, we’re going to pretend today is 01 Dec 2022, by capturing this in a parameter.

pToday

date parameter defaulted to 01 Dec 2022

We can then work out how old the employee is (in complete years) on 01 Dec 2022

Employee Age

IF DATEPART(‘dayofyear’, [Birth Date])<=DATEPART(‘dayofyear’,[pToday]) THEN
DATEDIFF(‘year’, [Birth Date], [pToday])
ELSE
DATEDIFF(‘year’, [Birth Date], [pToday])-1
END

The ‘dayofyear’ parameter of the DATEPART function returns as it suggests the day of the year, so is a number from 1 to 365 (or 366 in a leap year). So this calculation is basically saying, if the birthday falls before ‘Today’, then they’ve had their birthday in that year, so a simple difference between the years suffices, otherwise the birthday hasn’t happened yet, so take one off the difference in years.

We can then create

Age Bracket

IF [Employee Age] < 20 THEN ‘Under 20’
ELSEIF [Employee Age] < 30 THEN ’20-29′
ELSEIF [Employee Age] < 40 THEN ’30-39′
ELSEIF [Employee Age] < 50 THEN ’40-49′
ELSEIF [Employee Age] < 60 THEN ’50-59′
ELSE ‘Over 60’
END

Setting up the parameters

All the user interactivity is driven through selection of parameters.

pDisplayType

string parameter defaulted to Butterfly Chart with a list of values Stacked, Side by Side, Butterfly which each have a slightly different Display As value (up to you whether you want to do this).

pDisplayRowsBy

string parameter defaulted to Age Bracket with required options listed.

pSplitBarsBar

string parameter defaulted to Contract Type with required options listed

Building the Viz

The pDisplayRowsBy parameter defines the field that we want to display on the Rows shelf. The parameter is just a string value though which we need to map to a proper field from the data set

Dimension – Rows

CASE [pDisplayRowsBy]
WHEN ‘Age Bracket’ THEN [Age Bracket]
WHEN ‘Contract Type’ THEN [Contract Type]
WHEN ‘Department’ THEN [Department]
WHEN ‘Gender’ THEN [Gender]
WHEN ‘Nationality’ THEN [Nationality]
END

The pSplitBarsBy parameter defines the field that will be used to colour the bars. Again we need to map this to fields in the data set

Dimension – Colour

CASE [pSplitBarsBy]
WHEN ‘Gender’ THEN [Gender]
WHEN ‘Contract Type’ THEN [Contract Type]
END

The existing Employees(Count) field that is automatically included is just a counter for the number of rows in the data set, so is essentially our headcount number. However, we need to think about how the display needs to look for the different types of chart, specially the butterfly chart.

When building a butterfly chart, the bars to the right of the middle axis are plotted as positive numbers, while the bars to the left are plotted as negative numbers (so the middle is actually 0). So in the event the display type of Butterfly Chart is selected, we need to make some adjustments, so that some of the values are plotted on the negative axis. This means our calculation looks like

Headcount

IF [pDisplayType] = ‘Butterfly’ THEN
IF MIN([Dimension – Colour]) IN (‘Part Time’, ‘Female’) THEN COUNT([Employees]) * -1
ELSE COUNT([Employees])
END
ELSE
COUNT([Employees])
END

If it’s a Butterfly chart, then if the values associated to the field we’re colouring the bars by are ‘Part Time’ (pSplitBarsBy = Contract Type) or ‘Female’ (pSplitBarsBy = Gender), then negate the count of employees, otherwise just return the count of employees.

Let’s start putting some of this into a viz to see what we’ve got…

On a sheet, add Dimension-Rows to Rows and Headcount to Columns. Add Dimension-Colour to Colour. Show the 3 core parameters and adjust the colours to suit.

Change the pSplitBarsBy to Gender, and adjust colours again. Change to ‘N/A’ and adjust again, so all possible options for the Dimension-Colour field have been set. Change pSplitBarsBy back to Contract Type.

Now change pDisplayType to Stacked Bar.

We want Full Time segment of the bar to appear first against the zero line of the axis, so manually re-order the options in the colour legend, so Part Time is listed first.

Change pSplitBarsBy to Gender and verify that the Male segment of the bars are displaying first against the zero line of the axis. Change pSplitBarsBy back to Contract Type.

For the side by side bar chart, we need another dimension on Rows that is essentially the Dimension -Colour field. However, we only want the value when the pDisplayType is Side by Side. So we need

Dimension – Side by Side

IF [pDisplayType]=’Side By Side’ THEN [Dimension – Colour] ELSE ” END

Add this to Rows after the Dimension-Rows field.

This adds an additional column to the display. While we’re in side by side or butterfly ‘mode’ , nothing is displayed. Change pDisplayType to Side by Side and we get the separation required.

Since the Colour legend is defining what the bar relates to, we can hide this field (right click on the Dimension – Side by Side pill and uncheck show header.

We want to also show the % of Total headcount split for each row. Create a new field

Total Employees Per Row

{FIXED [Dimension – Rows] : COUNT([Employees])}

then create

% Total Headcount

[Headcount]/SUM([Total Employees Per Row])

and format to a custom number format of 0%;0%. This is a % with 0 decimal places, but the custom format is used as we want negative numbers (caused by the butterfly chart type) to be displayed as +ve percentages.

Add % Total Headcount to Columns and verify the display for the different display types. Hint – if the calc is working as expected, the Stack Bar option should show 100% 🙂

Labelling the Viz

We can’t simply add labels using the usual method (show mark labels) and adjusting the positioning, as it won’t always show what we want, where we want for all the different display types. So we need to employ different techniques based on the chart being displayed.

Let’s start with the stacked bar chart. For the Headcount bars, we want to display the total number of employees at the end of the bar, and for the % Total Headcount, we want the % displayed in the middle of the bar.

Create a new field

Ref Line – Stacked

IF [pDisplayType] = ‘Stacked’ THEN [Headcount] END

Add this to Detail shelf of the Headcount marks card, then add a reference line to the Headcount axis (right click axis > Add Reference Line) that for each pane references the Sum of the Ref Line – Stacked field, and just displays the Value on the Label. No line or tooltip is displayed.

Format the reference line (right click the ‘invisible’ line) to adjust the colour and alignment of the font.

Add % Total Headcount to the Label shelf of the % Total Headcount marks card. You may need to adjust the width of the bars so you can see the values. Adjust the font to be white.

Change the pDisplayType parameter to Side by Side. No labels should be displayed. This is because the label we used for the headcount bar was based on the display type, and the label we used for the % total has been coloured white and is therefore invisible on a white background (set it to black and you’ll see it displayed). So again we need more fields

Ref Line – Side By Side

IF [pDisplayType] = ‘Side By Side’ THEN [Headcount] END

Add this to the Detail shelf of the Headcount marks card, and again add a reference line to the Headcount axis, this time setting the scope at the cell level.

Once again format the reference line to set the font and alignment.

Create

Label – % of Total Side by Side

IF [pDisplayType] = ‘Side By Side’ THEN [% Total Headcount] END

Format to a % with 0 dp and add this to the Label shelf of the % Total Headcount marks card, and then adjust the Label so this field is appropriately coloured and listed before the other label (which is coloured white).

Change the pDisplayType parameter to Butterfly chart. The % labels should already display. We just need to sort the labelling for the actual headcount. For this we need

Ref Line – Butterfly Pos

IF [pDisplayType] = ‘Butterfly’ THEN
IF [Headcount] >= 0 THEN [Headcount] END
END

and

Ref Line – Butterfly Neg

IF [pDisplayType] = ‘Butterfly’ THEN
IF [Headcount] < 0 THEN [Headcount] END
END

Add both these fields to the Detail shelf of the Headcount marks card. Then add reference lines for each of the two fields

Then format both the reference lines so the font and alignment is set as required (in the case of the Ref Line – Butterfly Neg reference line, it needs to be left aligned)

Add Headcount and % Total Headcount to the Tooltip shelf of the All marks card, and then adjust the tooltip. Finally tidy up by

  • Adjusting format of the Dimension – Rows values and aligning middle left.
  • Hide field labels for rows
  • Remove column dividers
  • Adjust row dividers to be more subtle
  • Remove gridlines, zero lines, axis ticks & rulers
  • Remove the Headcount & % Total Headcount axis.

Add the viz to a dashboard. I used a horizontal container placed above the viz to add text objects to represent the headings. The first text box just the referenced the pDisplayRowsBy parameter value, so it was dynamic.

My published viz is here.

Happy vizzin’!

Donna

Can you build a mobile KPI app?

Donna Coles1 Comment

For week 24 of #WOW2023, Kyle set this challenge involving dynamic zone visibility to build a mobile friendly KPI visual.

The charts being displayed are relatively simple, and use techniques applied several times in other challenges. Let’s tackle the charts for each ‘page’ one by one.

The Home page

For the Sales KPI

  • Double click into Columns and type MIN(1)
  • Add Sales to Label.
  • Set to Entire View
  • Adjust the MIN(1) axis to be fixed from 0-1
  • Adjust the text in the Label dialog to include the word Sales and resize fonts.
  • Align the label middle centre
  • Increase the size to the maximum value
  • Adjust the colour
  • Format the Sales measure to be $ with 0 dp.
  • Uncheck the show tooltip option from the Tooltip dialog
  • Hide the Min(1) axis
  • Ensure no row/column divider lines are displayed and remove all gridlines, zero lines, axis ticks etc

Duplicate this sheet and create equivalent ones for Profit and Orders – you’ll need to create a field

Count Orders

COUNTD([Order ID])

for the orders KPI.

For the Sales Sparkline

  • Add Sales to Rows
  • Add Order Date as a continuous (green) pill set at the Month level.
  • Adjust the colour
  • Hide both the axis
  • Remove all gridlines, zero lines, axis lines, row/column dividers
  • Adjust the tooltip
  • Format the background of the worksheet to light grey (#f5f5f5).

Duplicate the sheet and create equivalent ones for Profit and Orders.

You should have 6 sheets for the home page.

The Category Page

The Category page is displaying values for the last 12 months based on ‘today’. If building this in a business environment, I would make use of the TODAY() function. But to ensure the viz doesn’t break in future, I’ll hardcode today within a parameter

pToday

date parameter defaulted to 14 June 2023

I then need a field to restrict the records to report over

Last 12 months only

[Order Date]>= DATEADD(‘month’, -12, DATETRUNC(‘month’,[pToday])) AND
[Order Date]<DATEADD(‘month’, 1,DATETRUNC(‘month’,[pToday]))

this will return true if the Order Date associated to the record is greater than or equal to the 1st of the month, 12 months ago, based on the pToday parameter, and the Order Date is less that the 1st of next month, based on pToday.

For the Category KPI sheet

  • Add Last 12 months only to the filter shelf and set to true
  • Add Category to Rows
  • Double click on Columns and type in MIN(1)
  • Add Sales and Category to Label
  • Set to Entire View
  • Adjust the MIN(1) axis to be fixed from 0-1
  • Adjust the text in the Label dialog and resize fonts.
  • Align the label middle centre
  • Increase the size to the maximum value
  • Adjust the colour
  • Uncheck the show tooltip option from the Tooltip dialog
  • Hide the Min(1) axis
  • Hide the Category column
  • Ensure no row/column divider lines are displayed and remove all gridlines, zero lines, axis ticks etc

For the Category trend

  • Add 12 months only to filter and set to true.
  • Add Category and Sales to Rows
  • Add Order Date as a continuous (green) pill set at the Month level.
  • Adjust the colour
  • Add Sales to Label and show the min & max labels for each Category pane
  • Hide both the axis
  • Remove all gridlines, zero lines, axis lines, column dividers
  • Adjust the tooltip
  • Format the background of the worksheet to light grey (#f5f5f5).

The Segment Page

Repeat the same steps as described above for the Category Page, but replace any reference to Category with Segment.

Sales by State Page

  • Add State/Province to Rows
  • Add Sales to Columns
  • Sort by Sales descending
  • Adjust the Colour
  • Click on the Label shelf and Show mark labels
  • Remove all gridlines, zero lines, axis lines, row/column dividers
  • Adjust the tooltip
  • Format the background of the worksheet to light grey (#f5f5f5).

Building the navigator

I went down a slightly longwinded route for this, but its still an acceptable method. I knew deep down it could be done in 1 sheet, but my brain just wasn’t quite wired up properly when I built it.

I basically ended up building 2 sheets per symbol.

Firstly, you’ll need to add the symbol images into your shapes palette.

Create a new field

Selection – Home

“Home”

Also create fields

True

TRUE

and

False

FALSE

  • Add Selection – Home to the Text shelf.
  • Change the mark type to Shape and select the ‘home’ shape from your custom shape palette.
  • Set to Entire View, then adjust the Label alignment to be bottom centre.
  • Uncheck the show tooltip option from the Tooltip dialog
  • Format the background of the worksheet to medium grey
  • Add True and False to the Detail shelf.
  • Name the sheet Home – Unselected or similar

Duplicate sheet and change the background colour to teal or similar. Name this sheet Home – Selected or similar.

Repeat the process building 2 sheets for each image – you’ll need to create a Selection– Category field, a Selection – Segment field and a Selection – State field.

Building the calcs for Dynamic Zone Visibility

In order to hide and show various content ‘on click’ we will be making use of dynamic zone visibility. For this we need several boolean fields created along with a parameter

pSelection

string parameter, defaulted to Home

We then need

Is Home Selected

[pSelection] =’Home’

Is Home Not Selected

[pSelection] <>’Home’

Is Category Selected

[pSelection] =’Category’

Is Category Not Selected

[pSelection] <>’Category’

Is Segement Selected

[pSelection] =’Segment’

Is Segement Not Selected

[pSelection] <>’Segment’

Is State Selected

[pSelection] =’State’

Is State Not Selected

[pSelection] <>’State’

Building the Dashboard

We need to make use of multiple (nested) containers in order to get all the content positioned in the right place. I’m not going to go through step by step which containers to place where, but just summarise the key points.

For the ‘navigator’ strip, all 8 sheets need to be placed side by side in a horizontal container, and should be ordered so the ‘home’ sheets are first, then the ‘category’ ones etc. I adjusted the padding around each object to be 1px, and obviously didn’t show the title.

For each sheet, determine whether it should display or not by using the control visibility using value option on the layout tab, and selecting the appropriate field based on which ‘page’ the sheet relates to , and whether it’s the ‘active’ / selected sheet or not.

Eg for the teal Home – Selected sheet, the control visibility using value option should be driven based on the value of the Is Home Selected field, while the grey Home – Unselected sheet should be based on the value of the Is Home Not Selected field.

If all these are set correctly, only 4 of the 8 sheets should be visible at any one time – 1 teal and 3 grey.

For the ‘pages’ ie the set of sheets visible based on the selection in the navigator, a Horizontal Container should be used which in turn consists of 1 vertical container (for the sheets relating to the Home page), 2 horizontal containers (1 containing the 2 sheets side by side for the Category page, and 1 containing the 2 sheets side by side for the Segment page), and finally the Sales by State sheet should be added to the main horizontal container.

The Sales by State sheet should be visible based on the Is State Selected field. Each of the other containers should be visible based on their relevant field.

When putting all this together, the dashboard might look crowded and disorganised, but once the settings have been applied, only 1 page’ should be visible and then you can tweak padding and positioning if need be.

Capturing the selection

We need parameter actions to determine which card should display

Select Home

This parameter action should be applied when the Home – Selected or Home- Unselected sheets are clicked on, and it should set the pSelection parameter, passing in the Selection – Home field.

Equivalent parameter actions should then be created for each of the other Selected/Unselected sheets, passing in the appropriate Selection – xxx field.

Finally to ensure the navigation options don’t remain ‘selected’ on click (the images look darker) we need to apply filter actions to set the true field to false on each of the navigation buttons – this means 8 filter actions, which should look similar to this…

The source sheet selected on the dashboard should target the actual sheet itself (not the one on the dashboard).

Add a title and any other content onto the dashboard. Finally to ensure the viz works properly on a mobile, delete the phone layout option that is automatically listed on the dashboard tab.

My published instance is here. Check out Kyle’s solution to see the 1-sheet navigator.

Happy vizzin’!

Donna

How is the Arctic ice melt trending?

Donna Coles3 Comments

This week’s #WOW2022 challenge required us to build an horizon chart. While I’m familiar with them, I haven’t had to build one before.

Luke provided references to a workbook and blog post as part of the challenge. I was also aware that Marc Reid had recently published this blog post on the subject and that in turn referenced this Tableau guest blog post by Yves Fornes. Reading through them both I hoped I’d have the clues I’d need to build the chart.

Note – the blogs referenced all give a good explanation on the process we’re trying to achieve with the horizon chart (divide into equal sized layers and ‘merge’), so I’m not going to spend time repeating that in this post.

Unfortunately, despite a good start, I couldn’t just couldn’t quite manage it – I tried both Marc’s and Yves techniques and also downloaded Joe Mako’s workbook and tried to follow his method, but I just couldn’t get the display to match up, and there were minimal clues in Luke’s published solution.

My fellow #WOW participant Rosaria Guana also pinged me, as she had got further than I had, but wasn’t matching Luke’s solution either. With some assistance from Rosario I managed to build the solution that matched hers and seemed the closest fit to Luke’s. As yet we can’t figure out where the discrepancy may lie… maybe we’ve overthought the issue… who knows.

For now though, I’ll step through the solution as per my published workbook.

Sorting out the dates

The data needs to be segregated into decades, so first I created

Decade

IF [Year]>=1990 AND [Year]<2000 THEN ‘1990s’

ELSEIF [Year]>=2000 AND [Year]<2010 THEN ‘2000s’

ELSEIF [Year]>=2010 AND [Year]<2020 THEN ‘2010s’

ELSEIF [Year]>=2020 THEN ‘2020s’

ELSE NULL
END

In the requirements, it mentions using data from the 1980s onwards, but the solution only presents 1990s onwards. I interpreted this as a typo in the requirements, so I added a Data Source Filter (right click data source > Edit Data Source Filters), and added Decade excludes Null to eliminate all the unnecessary rows.

The chart shows 10 years worth of data at the day level across the x-axis. We can’t just use the existing Date field to plot as this will extend for 30+ years. Instead we need to ‘baseline’ the dates to a fixed set of 10 years.

Firstly we need to know what year in the decade it is (ie the 1st, 2nd, 3rd year etc).

Year Index

RIGHT(STR([Year]),1)

This returns values of 0, 1, 2, 3… or 9

I then create the years I’ll baseline my dates against – this can start at any year. I chose 1980.

Baseline Year

INT(STR(198) + [Year Index])

So this will result in years 1980, 1981, 1982… up to 1989.

I can then create a new date which I’ll use to plot against

Baseline Date

MAKEDATE([Baseline Year], MONTH([Date]), DAY([Date]))

If we pop some of this data into a table, you can see how the Date field relates to this revised date

The 1st of January for the 1st year in each decade will all be plotted against 1st Jan 1980 etc.

Normalising the Arctic ice (NH) value

The NH values need to be adjusted so that they all sit on a scale of 0-1. This means the date with the lowest NH value across all the years will have a value of 0, and the date with the highest NH value across all the years will have a value of 1, and all the values in between will sit proportionally between. To compute this we need

Max NH

{FIXED : MAX([NH])}

Min NH

{FIXED : MIN([NH])}

Normalise NH

(AVG(NH) – SUM([Min NH])) / (SUM([Max NH]) – SUM([Min NH]))

take the difference between the NH value and the minimum as a proportion of the difference between the max and min values. This gives us the scale we need – the image below is sorted based on the Normalise NH value descending (so the dates aren’t in order)

Creating the Levels & the chart

All of the above I managed with no issue. It was this section that I struggled with 😦 As mentioned I tried all sorts of calculations, and after discussions with Rosario, the calcs I was using based on Yves blog post came closest…

We’re aiming to get bands which are 20% (0.2) in width.

Range 0.8-1

IF ([Normalise NH]) > 1 THEN 0.2
ELSE
IF ([Normalise NH])<0.8 THEN NULL
ELSE ([Normalise NH]) – 0.8
END
END

If the normalised value is over 1 then draw a mark at 0.2, our band size (there won’t be any values, but I did this for completeness). Otherwise if we’re below our lower threshold of 0.8, don’t draw anything, else as we’re within our specific range, draw a mark at the point which is the difference between our normalised value and our lower threshold of 0.8 ( ie a record with a normalised value of 0.91 will plot at 0.11).

On a new sheet add Baseline Date as a continuous exact date to Columns and Decade to Rows. Then add Range 0.8-1 to Rows too. Change to an Area chart.

Now let’s build the next level based on the same principals

Range 0.6-0.8

IF ([Normalise NH]) > 0.8 THEN 0.2
ELSE
IF ([Normalise NH])<0.6 THEN NULL
ELSE ([Normalise NH]) – 0.6
END
END

As you can see this is very similar to the above calculation, but this time, we will have values greater than the upper threshold of 0.8, which will all be plotted at 0.2.

Drag this field on the Range 0.8-1 axis and drop when you see the ‘two column’ symbol appear. This will make the chart a Combined Axis chart and Measure Names and Measure Values will automatically get added to the view. Colour the Measure Names accordingly, and up the transparency on the Colour shelf to 100%.

This looks very close to what we need, but you can see the axis is plotting values at >0.2. This is because the measures are stacked on top of each other. We need to turn this off via the Analysis > Stack Marks > Off menu.

When you do this, it’ll look like some marks have disappeared, but they are just hidden behind the other marks. Use the Measure Names colour legend on the right to drag and re-order the options listed.

Let’s now add the next level

Range 0.4-0.6

IF ([Normalise NH]) > 0.6 THEN 0.2
ELSE
IF ([Normalise NH])<0.4 THEN NULL
ELSE ([Normalise NH]) – 0.4
END
END

And drag the field into the Measure Values box. For the purposes of display at this point, I have coloured this measure a light grey, when it should be white (but that won’t show up at this point).

Now if we continue with the pattern and create similar calculated field for the next 2 ranges, we end up with something that isn’t right… too much red, and white in places we shouldn’t have white… 😦

This is where I started having discussions with Rosario. I could see by observing Luke’s solution that the dark reds were ‘inverted’ ie looked like humps coming ‘down’ from the top line, rather than humps ‘going up’ from the bottom line, but I was stumped where I needed to go next. Rosario confirmed this was her thinking too, and provided alternative computations for the light and dark red levels.

Range 0.2-0.4

IF [Normalise NH] < 0.2 THEN -0.2
ELSE
IF [Normalise NH]<0.4 THEN [Normalise NH] – 0.4
ELSE NULL
END
END

If we’re below the lower threshold, plot at -0.2 (minus 0.2) instead. If we’re within the lower and higher threshold, plot the difference between the Normalise NH value and 0.4 (which will be a negative number), otherwise plot nothing.

and finally for the last band

Range 0-0.2

IF ([Normalise NH]) < 0.2 THEN [Normalise NH]-0.2
ELSE NULL
END

Add this to the view, but you’ll need to change the order, so this measure is sitting above the Range 0.2-0.4 one.

Ok, so the shapes look like they might be correct, although we are missing some dark red in the 1990s compared to Luke’s solution, and as mentioned at the start, we don’t know where the discrepancy comes from.

So how do we make this all be ‘above the line’.

Drag another instance of Measure Values onto Rows so it’s sitting next to the existing one.

The make the chart dual axis BUT DON’T synchronise axis.

Instead, right click on the left axis to edit it, and fix the axis from 0-0.2

Then right click on the right axis to edit, and fix this from -0,2 to 0.

Set the colour of the Range 0.4-0.6 back to white, and you should have

Now you just need to

  • add tooltips
  • Edit the date axis and fix from 01/01/1980 to 31/12/1989
  • hide all 3 axis
  • remove row gridlines
  • remove column dividers
  • hide the null indicator
  • hide field labels for rows
  • adjust the font side and alignment of the Decade header

My published viz is here.

A few days later…

Since authoring this blog, Luke has made his workbook available to download. I have looked at it, and the calculations he uses are much much simpler. However I’m still struggling with the concept and not fully convinced with it all. I have played around with the workbook, and try to step through based on how the horizon chart is described in the various blogs, but I’m not entirely sure I know what the right answer might be in order to know whether what I’m seeing is actually correct or not. Feels like this is one I could do with a face to face discussion over, and ultimately is probably one for another day. ..

Happy vizzin’!

Donna

Can you visualise the Cholera Outbreak?

Donna ColesLeave a comment

Lorna delivered an exciting map based challenge this week, to recreate one of the most famous visualisations ever created. If you’re part of the data viz community and never heard of John Snow and his cholera map, then I suggest you go and google now 🙂

While I love map based challenges, as its an area I don’t get to use regularly, they’re also the ones that can give me the most frustrations/take the longest to complete, as the functions and concepts I need to use don’t come as readily as with other challenges. I had to refer back to my own blogs on previous map based challenges (specifically this one and this one), to help me out. However these were written before map layers were introduced, so not everything was applicable.

  • Modelling the data
  • Building the Avg Distance to Pump bar chart
  • Building the Total Deaths within Avg Distance bar chart
  • Building Map
  • Adding the dashboard interactivity

Modelling the data

3 sets of data was provided

  • Pumps – 1 row per pump with its location
  • Deaths – 1 row per death and its location (can be multiple rows for the same location)
  • Deaths aggregated – 1 row per location where at least 1 death was recorded, including the count of deaths at that location

This all needed to be ‘combined’ so we can work across all 3 sets of data and compare the locations. I used relationship calculations to create a relationship of 1=1 between the related data sets, which means all the aggregated deaths are mapped to each pump, and all the individual deaths are mapped to each pump too.

Building the Avg Distance to Pump bar chart

I chose to start building the bar charts first so I could get better acquainted with how the data was working together, and I knew the maps would take more effort.

To start we need to figure out the distance from each pump to each death, so we need to get the location of each pump, and the location of each death

Pump Location

MAKEPOINT([Pump Lat],[Pump Lon])

Individual Death Location

MAKEPOINT([Death Lat],[Death Lon])

These will both create calculated fields of a geographic data type as indicated by the globe icon to the left of the field.

And with these fields, we can then work out the distance between them in metres

Distance Pump to Death

DISTANCE([Pump Location], [Individual Death Location],’m’)

Now we want the average distance per pump

Avg Distance

{FIXED [Pump ID]: AVG([Distance Pump to Death])}

I formatted this to 0 dp and added a suffix of ‘m’

And with this, we can build the first bar chart – Pump ID on Rows and Avg Distance on Columns. You need to apply formatting

  • Change the font – I set the font to Times New Roman at the Workbook level, to make life easier (Format menu > Workbook).
  • Set the worksheet background colour. I used #f5f1f0
  • Add a black border to each bar (via the Colour shelf)
  • Show labels on bars (via Label shelf)
  • Hide Avg Distance axis (uncheck show header on the Avg Distance pill)
  • Remove all row & column borders
  • Set the Axes Ruler on Rows to black
  • Set the Tooltips

A parameter is needed to store the selected pump

pSelectedPump – an integer defaulted to 1

With this, we can then create a field to indicate which pump is selected

Is Selected Pump?

[Pump ID]=[pSelectedPump]

Add this field to the Colour shelf and set the True option to black and the false option to match the background colour you used.

Building the Total Deaths within Avg Distance bar chart

We now need to identify if the death occurred within the average distance that has been calculated.

Number Deaths within Avg Distance

SUM(INT([Distance Pump to Death] <= [Avg Distance]))

This is a bit of short hand instead of writing IF [Distance Pump to Death] <= [Avg Distance] THEN 1 ELSE 0, and summing up the result. It relies on the the fact that the equation [Distance Pump to Death] <= [Avg Distance] returns a boolean of true or false, which can be converted to an integer as true =1 and 0 = false.

At a row level, the field just returns 1 or 0, but just plot this field against Pump ID, you get the desired count

And the easiest way to build this bar chart, is to duplicate the one created above (on the worksheet tab, right click & duplicate sheet), then replace the Avg Distance pill with the Number Deaths within Avg Distance pill (simply drag the latter from the data pane and drop directly onto the former in the columns shelf). Then adjust tooltips etc to suit. Doing it this way preserves (most of) the formatting.

Building the Map

We’re going to use 4 layers in creating the map, and I’m documenting from the bottom upwards (although this isn’t the order in which I actually built the viz in practice – I shuffled things round afterwards).

We’ll start with the density layer – the blue coloured area on the map.

On a new sheet, add Individual Death Location and Death ID to the Detail shelf. This should automatically create a map view with the auto generated lat & long fields. Then

  • Change the mark type to Density
  • Change the colour to Density Blue-Teal Light
  • Alter the Intensity to 75%, the Opacity to 60%
  • Increase the Size to maximum
  • Delete the info in the tooltip

We need to set the background map

Via the Map > Map Layers menu option, set the Style to Normal and uncheck all options and set Streets, Highways, Routes

Then set the worksheet background to the colour we’ve used before

Onto Layer 2 – the pump buffer circle.

We’ve already identified the selected pump, but now we need to get it’s location, and for that we need the lat and long of the selected pump.

Selected Pump Lat

{FIXED: MIN(IIF([Is Selected Pump?],[Pump Lat],NULL))}

This essentially gets the latitude of the selected pump and sets that value across all the rows in the data.

Selected Pump Long

{FIXED: MIN(IIF([Is Selected Pump?],[Pump Lon],NULL))}

And with these we can get

Selected Pump Location

MAKEPOINT([Selected Pump Lat],[Selected Pump Long])

We also need to get the average distance of the selected pump spread across every row

Selected Pump Avg Distance

{FIXED :SUM( IF [Is Selected Pump?] THEN [Avg Distance] END)}

Now we have this, we can build the buffer field

Pump Buffer

BUFFER([Selected Pump Location],[Selected Pump Avg Distance],’m’)

Drag this Pump Buffer field onto the map and drop it onto the ‘Add a Marks Layer’ section that should appear. This will create an additional marks card.

Set the colour to the same background colour previously used, and drop the opacity to around 5%. Add Pump ID to Detail and Selected Pump Avg Distance to the Tooltip shelf. Adjust tooltip to match.

Onto Layer 3 – the pumps

Drag Pump Location onto the map and Add a Marks Layer.

Add Pump ID to Text and Location to Tooltip to the relevant marks card. Change mark type to circle, increase the size and change colour to red and add a dark grey border. Adjust text to be centred, and set the tooltips accordingly.

Onto the final layer now, the aggregated death locations. We need a further location field

Death Location

MAKEPOINT([DeathLat (deaths aggregate.csv)], [DeathLon (deaths aggregate.csv)])

Drag this onto the map to Add a Marks Layer then on the subsequent marks card add Loc ID to the Detail shelf. Change the mark type to circle, and add Deaths to the Size shelf – adjust accordingly. Change the colour to black with a white border. Adjust tooltip.

Adding the dashboard interactivity

Add the 3 sheets onto a new dashboard sheet. Create a new dashboard action to Change Parameter, that runs on select of any of the 3 charts, and targets the pSelectedPump parameter by setting it with the value from the Pump ID field.

Now if you select a pump on the map, or a bar on either of the bar charts, all the charts will reflect the selection made.

The other interactvity you may notice, is that on click, of the bars, or the pumps, the other data ‘fades’ (or the selected data is highlighted). We don’t want this.

To resolve, create a calculated field

True

True

and another

False

False

Add both these fields to the Detail shelf on both bar chart sheets and to the Detail shelf on the Pumps marks card.

Then on the dashboard, create a dashboard filter action which filters on selected fields setting True = False (see below). You’ll need to create one of these actions for each of the 3 sheets.

And hopefully, you now have a working viz. My published version is here.

Happy vizzin’! Stay Safe!

Donna

Can you compare a 3-day vs 14-day moving average and describe the latest trend?

Donna Coles1 Comment

This week for #WOW2020, Ann provided a table calculation feast of a challenge! This certainly is not for the faint-hearted! As well as cracking all the table calcs, the challenge features multiple views, measure swapping, parameters, BANs, filtering, sorting …. it’s got it all going on!

Ann hinted you’d probably want to start with the table, and even if there hadn’t been a table output in the display, this is what I would have done. If you’ve read enough of my blogs, you’ll know I often like to build up a ‘check data’ sheet, which just contains the data I need in tabular form as a quick reference. When working with table calculations this is an absolute must have!

So let’s build out that Check Data table to start with. I have a feeling this is going to be a lengthy blog 🙂

Initial Set up

First up, the requirements stated that the latest date would be 7 June, but I found records with a 8 June date. All the associated info for this date was null though, so I set a data source filter to exclude this. This means I wouldn’t get any issues if I needed to store the max date in a FIXED LoD calculation at any point.

I also found it easier to rename a couple of the measures provided to match the output, so rename PEOPLE_POSITIVE_NEW_CASES_COUNT to New Cases and PEOPLE_POSITIVE_CASES_COUNT to Reported Cases. I’ll refer to these renamed fields going forward.

Building all the Calculated Fields

To build out the table, we’re just going to focus on one State & County, as there’s a lot of data. So add Province State Name = Tennessee and County = Davidson to the Filter shelf.

Add Report Date (discrete exact date – blue pill) and New Cases & Reported to Rows. As you scroll down, you’ll see data starting to come in on 8 March.

We want to create our moving average calculations

3 Day Moving Avg

WINDOW_AVG(SUM([New Cases]), -2, 0)

14 Day Moving Avg

WINDOW_AVG(SUM([New Cases]), -13, 0)

Notice the number of rows to average over is 1 less than you might expect, as the current row is included, so the calculation is saying ‘current row’ and 2 | 13 previous rows.

Add these to the table, and adjust the table calculation so it is explicitly calculating by Report Date. This would have happened automatically, as the calculation would have been computing ‘down’ the table, but it’s best to fix the computation, so it doesn’t matter where the pill gets moved to in the view.

We now need to work out whether there is an increase or not between the 3-day and 14-day average.

Is Increase?

IF [3 Day Moving Avg] > [14 Day Moving Avg] THEN 1 ELSE 0 END

Is Decrease?

IF [3 Day Moving Avg] <= [14 Day Moving Avg] THEN 1 ELSE 0 END

I’m using 1s and 0s as it’s going to help with a later calculation.

NOTE – I’m assuming that if there is ‘no change’ it’ll be recorded as a decrease. This is how I interpreted the requirement, “ …whether it is an increase or a decrease (or no change)” and it wasn’t easy to find any matches anyway.

I also need some text to indicate the increase or decrease

Increase | Decrease

UPPER(IF [Is Increase?]=1 THEN ‘Increase’ ELSE ‘Decrease’ END)

The UPPER is used as that’s part of the tooltip formatting.

Let’s get these onto the view, always making sure the table calculations are set to Report Date.

We need to calculate the number of days that has been reported INCREASE in succession, and the number of days where successive DECREASE has been reported.

So first, let’s identify which rows match the previous row.

Match Prev Value?

LOOKUP([Is Increase?],-1) = [Is Increase?]

If the value of the Is Increase? field in the previous (-1) row is the same as the Is Increase? field in the current row, then this is true, else false.

Add to the view, and verify the table calculation for itself and all nested calculations being referenced, is set to Report Date.

We now have all the information we need to help us work out the number of days in the increase/decrease ‘trend’.

Days in Trend

IF (FIRST()=0) OR(NOT([Match Prev Value?])) THEN 1
ELSEIF [Increase | Decrease] = ‘INCREASE’ THEN ([Is Increase?]+PREVIOUS_VALUE([Is Increase?]))
ELSEIF [Increase | Decrease] = ‘DECREASE’ THEN ([Is Decrease? ]+PREVIOUS_VALUE([Is Decrease? ]))
END

If the row in the table is the very first entry (so there’s nothing previous to compare against), or the row in the table didn’t match it’s predecessor (ie there was a change), then we’re starting a new ‘trend run’, which obviously starts at 1.

Otherwise, if the current row we’re on indicates an increase, then we’ll add the value of the Is Increase? field (which is 1) to the previous value (which is also 1). PREVIOUS_VALUE works recursively though, so it essentially builds up a running sum, which gives our trend.

We ultimately do the same thing using the Is Decrease? column. This is why using 1 & 0s in the earlier calculation help.

Adding into the view, and setting the table calculation correctly, you should get something similar to this…

Finally, there’s one key field we need to add; something to help identify the latest row as we will need it for filtering in the table that’s displayed on the dashboard. Simply applying a standard ‘quick filter’ won’t work, as the table requires we show the 3-day & 14-day moving averages. A ‘quick filter’ to limit the data to the latest date (7th June), will show the wrong values, as the data related to the other days will be filtered out, so the table calc won’t have the information to correctly compute over.

We need to create another table calculation that we can use as a filter, and that due to Tableau’s ‘order of operations’ will apply later in the filtering process than a traditional quick filter.

Max Date

{FIXED : MAX([Report Date])}

The latest date in the whole data set.

Show Data for Latest Date

LOOKUP(MIN([Report Date]),0) = MIN([Max Date])

If the Report Date of the current row is the same as the maximum date in the whole data set, then return true.

We’ve now got all the core data components we need to create the various charts.

In the interest of time (my time in writing this out), I’m going to attempt not to describe the building of all the charts in too much detail, but just call out the useful bits you might need. If you’re attempting this challenge with the table calcs above, I’m assuming you know Tableau enough to not need everything defined to the lowest level.

The whole report is driven off a parameter which the user must enter a State – County combo.

You’ll need a calculated field to store the combo

State – County

[Province State Name] + ‘ – ‘ + [County]

and then create a parameter (State – County Parameter) off of this (right click, Create -> Parameter) which will create a string parameter with all the permutations.

When displaying on the dashboard, set this to be of type Type In

BAN

The BAN is a basic summary of the latest trend for the entered state county.

We need to filter the sheet to the value entered in the parameter

Is Selected State County?

[State – County Parameter] = [State – County]

Add this to the Filter shelf as true, along with the Show Data for Latest Date.

Add the relevant fields to the Text shelf to display the required text. The Report Date needs to be custom formatted to ddd, mmm d to get the Sun, Jun 7 display

Map

For the map, as well as filtering the latest date, we’re also going to need to filter just to the state only (not state & county) as above. So I created

Is Selected State?

LEFT([State – County Parameter], FIND([State – County Parameter],’-‘)-2) = [Province State Name]

This is unpicking the State – County combined string stored in the parameter, to just find the State part and compare to the Province State Name.

Build a filled map based on County and filter to the latest date and the selected state. I set the Map Layers to that below, which seems to match up

You’ll need to set both the Is Selected State County? and Increase|Decrease fields to the Colour shelf.

Bar & Line Chart

You’re going to need a few more calculated fields for this.

Moving Avg Selector

for the user to choose what the line should display. I’ve set it to an intger parameter that displays text

We then need a field to show on the display depending on what’s been selected in the parameter

Moving Avg to Display

If [Moving Avg Selector] = 3 THEN [3 Day Moving Avg] ELSE [14 Day Moving Avg] END

You’ll need a Dual Axis chart plotting New Cases and Moving Avg to Display against Report Date (continuous exact date)

The data only starts from 8th March, so I added Report Date to filter to start from 8th March. 8th March is also added as a constant reference line.

Table

Based on the State / County entered, the table is filtered to show the data for the latest date for all the counties in the state entered. Although not stated in the requirements, the first row is the county selected, with the rest ordered by Reported Cases.

You can get the selected county to the top, by adding Is Selected State County as a hidden field to the Rows, and moving ‘True’ to the top.

And that should be everything you need to build the dashboard, which is pretty much just stacking all the sheets one on top of each other in a single column.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna