Tableau Software : Scaffold data with Tableau Prep to fill gaps in your data set

Here is what the finished Prep flow looks like. Start by bringing in your headcounts table-the table with one row per employee. In your first Clean step in Tableau Prep, create a dummy calculation:

This calculation will allow you to join to the date_dim table. Next, connect to the date_dim table and do the same thing-create a dummy key field to join your fields on.

Now you can join the two tables together. Since the field we are using as our join key is just the number 1, the resulting table is going to have a much higher row count: 3,145,728 to be exact. This duplication occurs because we are joining every row of our first table to every row of the calendar table.

This is helpful because we've changed the granularity of our date field and now have a record for every day that an employee worked at a company. This allows us to ask the more complex questions necessary for an accurate turnover rate. Now we can calculate measures like 'average daily headcount' rather than only being able to consider the headcount at the end of the year.

But, we are also left with many extraneous records.

Say you worked at a company for 30 days, but your date_dim table extended back to 1964, the year your company was founded. You'd have a record for every single day, running back to 1964, when you only need 30 records. Apply this to hundreds of employees, and you're left with a huge data set.

To correct this, in the next step, filter out any unnecessary records with a calculation:

This calculation has 3 parts to it:

1. Part one trims dates prior to an employee's hire date.
   [Calendar] >= [Hire Date]
2. Part two trims dates post termination date.
   ([Calendar])
3. Finally, part three trims out any dates that haven't happened yet. The date_dim table extends into the future, which is good. You don't have to manually add a record for every day.
   [Calendar]

Once we apply this using the 'Filter data' option in Prep, we're left with 12,186 rows instead of the 3,145,728 we had before.