Lexis diagrams with Tableau

Jan 2022

Background

I'm moving to a new role soon and the team use Tableau - hooray! It's been a few years since I've used Tableau in production so I spent some time diving back into the product via various personal viz challenges.

For the curious, going back to a BI tool after time away is really NOT like riding a bike. It turns out your brain needs to re-learn, particularly as products get updated (or you get old like me).

As part of this challenge I saw an intriguing chart on DataWrapper called a Lexis Diagram.

It's traditionally used by demographers for cohort analysis. It shows birth dates (a timeline on the x axis) vs age on the y axis. Lexis diagrams feel like the love child of a Gantt and Column Chart.

I challenged myself to recreate one and got help from a workbook by Ludovic Tavernier. This is the replica I ended up with... (pretty darn close to the original)

Tableau how to...

Follow along with these instructions to recreate the chart above in Tableau.

  • Download the source data - european-leaders.xlsx

  • Open a new Tableau workbook and connect to this file

  • Drag european-leaders sheet from the left into your Data area on the right

  • Select the start and end date columns and pick Pivot from the dropdown (this gives us two rows per leader and an effective start and end point to draw the diagonal lines which make a Lexis diagram)

  • Go to Sheet 1 and set up the four calculations shown below:

// Start date

{FIXED [Row Id]:

MIN(IF [Pivot Field Names]="Startdate" THEN [Pivot Field Values] END)

}


// End date

{FIXED [Row Id]:

MAX(IF [Pivot Field Names]="Enddate" THEN IIF(ISNULL([Pivot Field Values]), TODAY(), [Pivot Field Values]) END)

}


// X

IF [Pivot Field Names] = "Startdate" THEN [Start Date]

ELSE [End Date]

END


// Y

IF [Pivot Field Names]="Startdate" THEN 0

ELSE ([End Date] - [Start Date]) / 365.25

END


  • If they appear in blue (discrete) use the drop down menu on each to switch them to continuous (green)

  • Drag X to columns and change date type to Month (e.g. May 2015)

  • Drag Y to rows

  • Switch chart type from Automatic to Line via the drop-down on the Marks card

  • Drag Pivot Field Name to Path

  • Drag Row Id to Detail

You're basically done! All that's left is some styling and polishing up...

  • Create a new calculation "Leader Status" (we'll use this to colour the lines)

// Leader Status

IF [Exit] = "Still in Office" THEN IIF([Gender] = "F", "Current leader (female)", "Current leader (male)", "Former leader (male)")

ELSE IIF([Gender] = "F", "Former leader (female)", "Former leader (male)", "Former leader (male)")

END

  • Drag the new calculation "Leader Status" to Colour on the Marks card

  • If you'd like the same colours as DataWrapper, set them via the Colour button as follows:

    • Current leader (female) = #CB0077

    • Current leader (male) = #18A1CD

    • Former leader (female) = #C8859C

    • Former leader (male) = #C4C4C4

  • Click the Size button and move the slider left to make the lines thinner

  • Tidy up the axes. Right click Y-axis and click "Edit Axis", set the Range to Fixed then Fixed start = 0 and Automatic for the end

If you got this far, you're finished

BI use cases

I persevered with this chart because I think it has some real world BI use cases.

I like visualisations that allow you to see high level patterns while maintaining the grain of your data. Scatter graphs, beeswarms and jitter plots are all fine examples of this in action. In a BI tool, these chart types allow you to interactively explore individual data points as part of a high level summary. They're very engaging!

Scatter graphs, beeswarms and jitter plots

Scatter graphs, beeswarms and jitter plots preserve the grain of your data

Lexis diagrams allow us to see the density of activity over a time series while comparing the duration of events.

In the real world this would fit well when analysing customer tenure, the volume and resolution time of complaints, onboarding waits for new customers or handling times in a call centre. I'm sure you'll have loads more ideas.

By way of example, here's a sample dashboard for call handing...

In this example we're getting a lot of insight, some of which poses further business questions...

  • The mornings are our busiest time, evenings less so

  • There aren't many priority calls after 6pm. Is it worth running this service?

  • Duration of call bears little relation to priority. Why are we spending so long on low priority calls?

  • Was there an outage just before 6pm, or did everyone take a tea break?

By drilling interactively into priority status and agents, we uncover further insight and can quickly see both standard call patterns and outliers.

It's a lot of information in a small space - I love it.

Lexis Comets!

If you've followed along with the instructions you can add a final flourish by adding a Comet effect to your Lexis lines.

  1. Move the Y calculation to Size on the Marks card

  2. Ensure the aggregation on the pill is SUM(Y)

  3. Click Size on the Marks card and adjust the slider right to enlarge your comet as desired

I found the effect pure fun and in light of recent news (for future readers, Britain's PM is embroiled in a lockdown party scandal) I used it on this topical example about British Prime Ministers...

I hope you found this introduction to Lexis Diagrams as useful as I did - they seem like another great visualisation for your arsenal. Have fun!