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

Leave a Reply

Your email address will not be published. Required fields are marked *