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

• Twitter
• LinkedIn
• Link
• YouTube

Latest YouTube video
This video tutorial steps through how to create a dot plot in Tableau.