Jim Dehner 

 Tableau Visionary and Ambassador

Tableau How to’s,  Use Cases, and Forums Questions

Hey #DataFam

Let’s Talk

Open for

Post Links

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

Leave a Reply

Your email address will not be published.