How to Analyse Overlapping Time Periods in Alteryx

The aim of this blog is to demonstrate a popular method for analysing problems involving overlapping time periods using Alteryx. To begin, we will establish the context by discussing the common use cases for analysing such problems. Next, we will illustrate the approach for solving overlapping time problems through a case study. 

 

I. The Use Cases:

Many business operations and decisions, such as scheduling, resource allocations, and time-based usage/cost analysis all involve overlapping time period calculations: 

• At what time does our system reach the max capacity limit when all users are using the system together? 
• Which systems tend to operate at the same time? 
• What is the total number of days that two pieces of machinery were leased out at the same time?

 

II. The Case Study

Let’s go through a case study based on Alteryx Challenge #192.

Goal: Find the number of days both cranes have been leased at the same time

 

Step 1: Converting relevant fields to DateTime (Date) format

Here, I will use the Multi-Field Formula Tool and the DateTimeParse function (in the expression box) to convert both Start Date and End Date fields from string to date format. Another option is to use the DateTime Tool to parse these date fields.

 

Step 2: Generating all dates between Start and End Dates

In this step, we will use the Generate Rows Tool to generate a new field called “Date” that creates every single day between each date in the Start and End Date fields, starting from the earliest Start Date (2014-07-15) all the way through to the latest End Date (2019-12-23).

The logic of the expression is as follows:

 

Step 3: Filtering based on Date fields to get the relevant data (problem specific)

This challenge requires the Crane Status field to equal “Leased” and years between 2016 – 2018 only, so we will use the Filter Tool accordingly. Clearly, this step is very problem dependent and should be tailored to your specific problem.

 

Step 4: Counting the number of instances or occurrences of each day in the Date field

In this step, we will use the Summarize Tool to count the number of times that each day occurs in the Date field. If the two cranes were leased on the same day (an overlap), then the count will be 2 (because there will be 1 count for each crane leased on that day, which sum up to 2 if both cranes are leased), otherwise the count will just be 1. 

 

Step 5: Filtering to keep overlapping days only

As mentioned in step 4, when count equals 2 it means that both cranes were leased on the same day. Therefore in this step, we will use the Filter Tool to keep the overlapping days only by setting the filtering condition to [Count] >= 2.

 

Step 6: Counting the number of records where there’s an overlap in lease dates

Finally, we will use the Count Records Tool to count the number of records where there’s an overlap in lease dates (the records that were “True” from step 5’s filter).

 

Done!

Excited? You should be! We conquered this challenge using just 6 tools! 

Now that you know how to deal with overlapping time periods, why not consolidate your new skills with this similar challenge? Challenge #106