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…
STR(DATETIME(INT([timestamp]) + (FLOOR(FLOAT([timestamp])%1*(1440/[p.Custom]) ) / (1440/[p.Custom]))))
ELSE // use the selected value…
STR(DATETIME(INT([timestamp]) + (FLOOR(FLOAT([timestamp])%1*(1440/[p.Time Calc]) ) / (1440/[p.Time Calc]))))
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.
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.