Grouping Data into Custom Time Bins

For a recent project I had to group my data in to custom time bins, for example, 30 mins, 60 mins, 240 mins etc. In this blog I show two methods in which you can do this:

  • Using predefined Tableau groups
  • Using a calculation

I also also look at how you can also split the data out into different days or combining all days. The example below will use tweet data that includes a date time field of the tweet, the bars are number of records. The data is not important, however, as I just needed a time field to work with.

Below is the final product:

Method 1: Using Predefined Tableau Groups

This was my first attempt to solve the problem and is a bit convoluted as well as not being dynamic, in that you can’t select a non-predefined time bucket. I’ve included it here for completion in case it’s of interest, but I would recommend using the second solution further down.

Create Tableau Groups from the Time Field

First I created a custom date from my timestamp field on Hour using Date Part:

Then I created several Tableau Groups on that custom date – 2 hours, 4 hours etc.

Here is the 4 Hour Group with the 8 – 12 bucket expanded:

Create a Parameter to Select a Time Bucket

The parameter allows the user to select a time bucket to use. I’ve used the integer data type and displayed a more descriptive name:

Create a Calculation to use the Time Groups

The below calculation has two parts. If the 30 min group is selected in the parameter, a string is created by concatenating the hour of the timestamp and either “:00” or “:30” as appropriate depending on the time.

The second part uses the groups created earlier based on the parameter selection:

Below is the view for method 1:

Method 2: Using a Calculation

After creating the first solution above I wasn’t really happy with it as it was fixed to the predefined groups only. I wanted to use a calculation that was flexible and could be used for any time group (5 mins, 10 mins, 38 mins, N mins).

Thinking on how to do this reminded me of a blog post I read a while back by Bona Wang on the topic of How to round time in Tableau. Below is the calculation Bona used (see the linked blog for details):

 DATETIME( INT ([DateTime]) + (ROUND( FLOAT([DateTime])%1*1440 ) /1440) ) 

This gets you to the minute, so with a few adjustments I could make it work for any value of minute bin. I swapped the ROUND to a FLOOR so the time would always round down and then divided the 1440 (the number of minutes in a day) by the minutes bin selected, which could be preset (15, 30, 60 etc.) or a custom value (I restricted this to 5 to 240 but it doesn’t have to be):

The code is below in case you want to copy it. Note, because my timestamp is a full date time field and I only want to show the hours and minutes components, I used some regex to extract these parts.

IF [p.Time Calc] = 1 THEN    // Use custom value…

REGEXP_EXTRACT(
STR(DATETIME(INT([timestamp]) + (FLOOR(FLOAT([timestamp])%1*(1440/[p.Custom]) ) / (1440/[p.Custom]))))
,'(\d\d:\d\d):\d\d')

ELSE // use the selected value…

REGEXP_EXTRACT(
STR(DATETIME(INT([timestamp]) + (FLOOR(FLOAT([timestamp])%1*(1440/[p.Time Calc]) ) / (1440/[p.Time Calc]))))
,'(\d\d:\d\d):\d\d')

END

There are two parameters: p.Custom can be any value between 5 and 240 in 5 minute increments, and p.Time Calc are the preset values (similar to the parameter used in method 1).

Below is the final view for method 2:

Split by Days

Finally, I added a simple calculation, controlled by a parameter, to allow the day component of the timestamp to split the view into days or to have all days be grouped. The screenshot for method 1 above shows with days grouped, and method 2 with days broken out.

Workbook

The workbook is available on Tableau Public here.

Thanks for reading and thanks to Bona for writing his blog post with the time calculation: How to round time in Tableau.

Thanks for reading!
Marc

One thought on “Grouping Data into Custom Time Bins

Add yours

  1. Hi Marc, this is awesome. Thanks for posting.
    It’s super useful in a highlight table (days of the week vs. months) in e.g., detecting potential patterns in homicides.
    In other words, making each day of the week more granular (i..e., Monday 22:00 – 02:00, 02:00 – 07:00 …).
    Thanks!
    Franco

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑