8 Replies Latest reply on May 4, 2011 2:35 PM by jessica sufit

# Group by Hour and Minute

I currently have a datetime data field as a dimension in my dataset and I would like to be able to create a grouping by time only.  I would like to have date on the x axis and time groupings on the y axis (5:30am - 6:00am, 6:00am - 7:00am, 7:00am - 8:00am, 8:00am - 8:30am) so I can do sums/averages by each time grouping for each date.  Any suggestions for how to accomplish this?

• ###### 1. Re: Group by Hour and Minute

I would look to use round or int as per this post: http://www.tableausoftware.com/support/forum/topic/sum-data-using-6-hour-time-interval

Post back if you get stuck. Welcome to the forums.

• ###### 2. Re: Group by Hour and Minute

Thanks.  The time groupings I want to use are not uniform (some are a half hour, some an hour, some an hour and a half), can i do conditional rounding?

• ###### 3. Re: Group by Hour and Minute

Then you'll have to use this method - you just need to work out what the multiplier for the round is - 24 for hour, 48 for half hour, 96 for quarter, so on)

• ###### 4. Re: Group by Hour and Minute

If some are an hour and some are a half hour, how do I indicate that if the time is 5:45, i want it rounded to the half hour but if its 6:15 i want it rounded to the hour?

• ###### 5. Re: Group by Hour and Minute

Ah, the times are different during the day - then you may have to use a case statement - if you post some data and examples of times I can help

• ###### 6. Re: Group by Hour and Minute

A case statement might work.  I attached some sample data and an example of the time intervals.  Thanks again.

• ###### 7. Re: Group by Hour and Minute

Attached workbook - had to use if statements as case needs an exact match not a range. Also, the xls file showing how the times are actually decimal numbers if you reformat them.

1 of 1 people found this helpful
• ###### 8. Re: Group by Hour and Minute

Thanks Alex.  That makes sense.  The data i'm using is actually stored in SQL Server.  Any chance you know why my Time field (datatype Time(7)) is not showing up in the fields list when I connect to this datasource?