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

    Grouping Different Time

    jitan.chainani

      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
          Paul Mathewson

          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
            jitan.chainani

            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
              Paul Mathewson

              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
                jitan.chainani

                Thank Paul!

                 

                It worked well without any issues.

                 

                Appreciate your help.

                 

                JC

                • 5. Re: Grouping Different Time
                  jitan.chainani

                  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
                    Tharashasank Davuluru

                    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
                      Tharashasank Davuluru

                      HI ,

                       

                      Please look at your your time filed whether it is properly mentioned are not. Please try this once from your end and check.

                       

                      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
                        jitan.chainani

                        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
                          Tharashasank Davuluru

                          Hi,

                          Post that custom query once.

                          • 10. Re: Grouping Different Time
                            jitan.chainani

                            Hey Tharashasank,

                             

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

                             

                            Thanks for your support,

                            JC

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

                              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