Use a scaffold to solve the 2 date problem

My blog has moved to jimdehner.com 

Thank you for all the support over the past 2 years - I recently updated my blog to make it easier to read, improve the rendering of images and tie back to video presentations that are associated with the content - Check it out at  Jim's new blog  -   

The most frequently viewed workbook on my Tableau Public site is a solution to the classic 2 date problem.   –  The data have only a start and end date and you need to know how many programs are active on any date – Ultimately you want something like this

The business question can take many forms

•        Head count given only a hire and termination date
•        Account balance given deposit and withdrawal dates
•        Count of active (or inactive) customers

The file may look like this

But dates cannot be aligned along a single axis

To count the “active programs” we need a data structure where the account start date and end date can be aligned along a common axis

One solution is to create a date file – or scaffold – and attach the detail account data to the scaffold

The scaffold is a simple file of consecutive dates from the earliest date in our data to a minimum of the maximum date (or extended to include future date)

Load the scaffold into Tableau and join it to the detail data set.  Every record in the detail data has to join with each date on the scaffold –

Here is how it is done:

On the data source tab add the Date Scaffold and drag it to the canvas

Join the date scaffold and the detail file with a join clause of 1 = 1

Every record in the detail file is now connected to each date

The start and end dates are aligned with each date on the data scaffold

Add an expression that will count the dates from the date ladder that are between the start and end date

{ FIXED [Account ID],[Scaffold Date]: if min([Scaffold Date])>=min([Start Date]) and min([Scaffold Date])<max([End date]) then 1 end   }

And create the viz

The actual workbook with How To Instructions can be downloaded from the Tableau Public site at https://public.tableau.com/profile/jim.dehner#!/vizhome/Scaffold-2dateexamplev10_5/Overview

It is in a Tableau 10.5 format for your convenience - enjoy

Jim