Data Scaffolding in Tableau (Calculating a Series of Values Based on Only a Start and End Date)

By: Eric Parker

Pro Headshot.jpg

Eric lives in Seattle and has been teaching Tableau and Alteryx for 5 years. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email.

Data scaffolding allows you to generate missing data for visualization purposes.

Imagine you are doing analysis for your company’s projects and want to know how many total hours are being devoted to each project and how those hours trend over the course of the project. Each row of data in your data source represents how many daily hours an individual plans to work on a given project including their start date and end date.

98-1.png

If all you needed to know was total hours per person, you could calculate the difference between the start and end dates and multiply that number of days by the number of hours.

If only life was that easy. Instead, let’s say these employees only work Monday through Friday so we only want to count the hours for those days. Things just got more complicated.

What we’ll want to do is create a second table of data that contains only the valid weekdays that fall between the earliest start date and the latest end date.

98-2.png

By combining these two tables we can create a data set that contains a record for every person and every day they worked (and the number of hours they will work on that day).

The next step is to create a join in Tableau where the Start Date is less than or equal to the Date field (from the new table) and the End Date is greater than or equal to the Date field.

98-3.png

Notice in the above example how Frederick Titterington’s row of data has now been multiplied into numerous rows of data, one for each valid date from the “Date” field.

After that join is complete, it becomes easy to visualize hours by project and trends over time.

98-4.png

 If you want help with your own data scaffolding (or other Tableau/data) problem, feel free to check out my office hours!