11 Replies Latest reply on Apr 21, 2016 8:09 AM by jitan.chainani

# Grouping Different Time

Hi Guys,

I have a data set with bunch of time slots and need to group them into multiple category.

Can you please suggest on how to put them in multiple groups?

I looked at the grouping on tableau and it was too manual to select and add them to a group.

Is there an alternate way to add this into group?

Thank,

JC

• ###### 1. Re: Grouping Different Time

Hey JC,

You can change your time field to a date time format then group each of those in to separate hours by selecting a discrete or continuous hour.  Depending on the logic of your grouping, you could also use the datepart function to create a calculated field that groups your time based on logic.

For example,

if DATEPART('hour',[Time SCL]) = 1 then "1st" elseif

DATEPART('hour',[Time SCL]) = 2 then "2nd" END

This would classify anything during 1AM as "1st", 2AM as "2nd" and everything else as null.

You can also you the minute date part to group at a more granular level.

Hope this helps!

Paul

InterWorks

1 of 1 people found this helpful
• ###### 2. Re: Grouping Different Time

Thanks Paul!

I am looking for something like below -

9AM to 4 PM -->Morning/Afternoon

4PM to 6 PM -->Evening

6 PM to 11 PM  --> Night

11PM to 8 AM  --> Late Night/Earl Morning

Thanks,

JC

• ###### 3. Re: Grouping Different Time

You can certainly do that!

Here's one example:

if DATEPART('hour',[Time SCL]) >=9 AND

DATEPART('hour',[Time SCL]) <16 then "Morning/Afternoon"

Elseif DATEPART('hour',[Time SCL]) >=16 AND

DATEPART('hour',[Time SCL]) <18 Then "Evening"

Elseif DATEPART('hour',[Time SCL]) >=18 AND

DATEPART('hour',[Time SCL]) <24 then "Night"

else "Early morning" END

Also attached a workbook with it for you to reference.

Cheers,

Paul

2 of 2 people found this helpful
• ###### 4. Re: Grouping Different Time

Thank Paul!

It worked well without any issues.

JC

• ###### 5. Re: Grouping Different Time

Hey Paul,

Quick question -

I am doing a customized query and the formula below is giving me an error -

if DATEPART('hour',[Timescl]) >=9 AND

DATEPART('hour',[Timescl]) <16 then "Morning"

Elseif DATEPART('hour',[Timescl]) >=16 AND

DATEPART('hour',[Timescl]) <18 Then "Evening"

Elseif DATEPART('hour',[Timescl]) >=18 AND

DATEPART('hour',[Timescl]) <21 then "Prime"

Elseif DATEPART('hour',[Timescl]) >=21 AND

DATEPART('hour',[Timescl]) <22 then "Late Night"

else "Early Morning" END

Error MSG - DatePar is called (string,string) Did you mean (string,date)

How do I resolve this? I tried adding int and str before date part and still getting issues.

Let me know...

Thanks,

JC

• ###### 6. Re: Grouping Different Time

Hey Jitan ,

The formula what you posted is working fine at our end. no error.Please have a  look at this picture

1 of 1 people found this helpful
• ###### 7. Re: Grouping Different Time

HI ,

if DATEPART('hour',[Time SCL]) >=9 AND

DATEPART('hour',[Time SCL]) <16 then "Morning"

Elseif DATEPART('hour',[Time SCL]) >=16 AND

DATEPART('hour',[Time SCL]) <18 Then "Evening"

Elseif DATEPART('hour',[Time SCL]) >=18 AND

DATEPART('hour',[Time SCL]) <21 then "Prime"

Elseif DATEPART('hour',[Time SCL]) >=21 AND

DATEPART('hour',[Time SCL]) <22 then "Late Night"

else "Early Morning" END

2 of 2 people found this helpful
• ###### 8. Re: Grouping Different Time

Hi Tharashasank,

The formula was working fine before. I had joined the tables on data source before but for some reason I had to use a customize query option and after that it has started giving me an error. The date field in customize query has a data type of date and still giving me an issue.

Thanks,

JC

• ###### 9. Re: Grouping Different Time

Hi,

Post that custom query once.

• ###### 10. Re: Grouping Different Time

Hey Tharashasank,

I figured it out..... There was an issue in the formula that I was missing.

JC

1 of 1 people found this helpful
• ###### 11. Re: Grouping Different Time

Hi Paul,

I have submitted another response "Combining 2 views in one chart" and would need your suggestion.

Can you please let me know if you have any ideas?

Thanks,

Jitan