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
- Headcount 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 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 dates)
Load the scaffold into Tableau and join it to the detailed 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
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-2dateexample/Overview
Jim
