If you’ve been following the Tableau Desktop tutorials here at Playfair Data, it is likely that you also have a license to Tableau Prep Builder (just ask your license administrator to provide the activation key).  Combining Tableau Prep with Tableau Desktop makes it possible for you to be a self-sufficient, end-to-end analyst from data engineering to dashboard development.

One of infinite use cases you can solve on your own in Tableau Prep is preparing a data source to analyze employee utilization, even when the numerator and denominator for the formula are at different aggregations.  The “scaffolding” and join techniques shared in this post will be helpful any time you need to combine data sources at different levels for your analysis.

 

Using Tableau Prep to analyze employee capacity and utilization

By the end of this post, you will be able to recreate the following flow in Tableau Prep which combines timesheet data at an hourly level and capacity data at a pay period level.  This image will also be a helpful reference to see which Tableau Prep steps (Input, Join, Clean, Aggregate, Output) are required to prepare data at different levels of aggregation.

How to Use Tableau Prep to Combine Data at Different Levels

A common business need is analyzing employee utilization and time.  Employers want to know how often their employees are working and how that compares to the number of hours they want to work, or the number of hours they have available.  In most cases there are two main data sources that will be needed for this.

New to Playfair Data?

Watch our 90-second intro video and receive tips on making your data usable.
 
Watch Video

Timesheets – lists each employee and the date and times they clocked in or out.

Timesheets data set

Hours Wanted – lists each employee with the number of hours they would like to work or are available to work (by pay period).

Hours Wanted data set

The main problem in this data is that the timesheet data and hours wanted data are at two different levels.  Timesheets are recorded daily, and hours wanted is recorded by pay period.  Since these data sources are at two different levels, there needs be a way to figure out what days belong in each pay period.

To account for the two different date levels, create a third data source that provides a crosswalk or lookup for each day and pay period.

Calendar scaffold – lists every day of the year and the associated pay period.

Calendar scaffold

 

Working in Tableau Prep

Open Tableau Prep and use two Input steps to bring in the Timesheet and Calendar Scaffold data sources. Next, use a Join step to join the Timesheet data source to the calendar scaffold on Date.

Use a Join step to join the Timesheet data source to the calendar scaffold on Date

This join will bring the Pay Period numbers into the Timesheet data.

This join will bring the Pay Period numbers into the Timesheet data.

Some adjusting needs to be done with the dates and times which can be done by clicking the ellipsis on a field’s card from the Profile pane and choosing “Create Calculated Field”.  So far, the date field, clock_in, and clock_out times are in separate columns.

Create two datetime columns in order to calculate the number of hours worked on each day (Clock In Date/Time and Clock Out Date/Time).  Here’s how the Clock In Date/Time calculated field looks; you would also need to complete this step for the [clock_out] column.

Clock In Date/Time calculation

Next, create a calculated field that computes the number of hours between the newly created Clock In Date/Time and the Clock Out Date/Time fields.

Hours Worked calculation

Clean up any redundant fields to remove any clutter from the data.

Clean up any redundant fields to remove any clutter from the data.

Since the data sources are at two different levels, some sort of aggregation needs to be done so the timesheet and hours wanted are in alignment.  In this case, aggregate to the pay period level (the highest level in the data) by adding an Aggregate step in Tableau Prep.

Add an Aggregate step in Tableau Prep

Once the timesheet data is at the pay period level, you can join it to the Hours Wanted data source by Pay Period and Employee.

Join timesheet to the Hours Wanted data source by Pay Period and Employee.

As a last step, clean up any redundant fields that aren’t needed and use an Output step to export the data.  The only thing left from there is to visualize the combined data in Tableau!

Clean up any redundant fields that aren’t needed and use an Output step to export the data

Brittany Fong
Playfair Data Partner Consultant
[email protected]
 

Access Exclusive Benefits

Dashboard templates, digital credentials, and more.

Related Content