4 Replies Latest reply on Feb 9, 2017 7:49 PM by Andrew Robinson

    Grouping times into three time periods

    Andrew Robinson

      Hello,

       

      I am putting together a worksheet that has one column called incident number and a second column that shows the incident time (in hours and seconds) that the incident was created.

       

      I want group/display the times into three time column headings: 00:00 - 08:00; 08:01 - 16:00 and 16:01 - 23:59 and count each instance. So if the incident time is 00:35 it would fall into the 00:00 - 08:00 and the new row value would equal 1. A time of 03:23 would fall into the 00:00 - 08:00 section and the new row value would be 2 and so on for all sections.

       

      I'm sorry I don't have a worksheet to show. I'm still pulling the information. I can do this in excel, I am only hoping there is a way to do it inside Tableau and to learn more about these types of calculations.

       

      Thanks in advance for any help you can provide.

       

      Andrew

        • 1. Re: Grouping times into three time periods
          Deepak Rai

          You need to create three separate measures using the format:

           

          If

          [Time Interval] >=00:00 and [Time Interval]<=08:00

          Then

          1

          Else

          0

          END

           

          Secondly,

          If

          [Time Interval] >=08:01 and [Time Interval]<=16:00

          Then

          1

          Else

          0

          END

           

           

          Similarly for third one. Bring these measures into view and make them discrete and Aggregate them. Hope it helps!!

          Thanks

          Deepak

          • 2. Re: Grouping times into three time periods
            Andrew Robinson

            Hi Deepak,

             

            Thanks for the help. Perhaps I'm doing something wrong because I keep getting an error in the calculation. It doesn't seem to like the colon in the times. I've attached a worksheet I tried it out on.

             

            Thanks

             

            Andrew

            • 3. Re: Grouping times into three time periods
              Deepak Rai

              Hi Andrew,

              It is Done. Let me explain. The problem was that you had Time as String in your data as well as I could not edit your data source . so what I did.

               

              1. I copied your data to clipboard in new sheet and that gave me [Time] as Time data type. From there I created another copy of it and did some formatting to get it in hh:mm:ss format using Custom time format because I wanted to use colon (:) as Delimiter.

               

              2. I split this new field Time(copy) into three separate splits using : as delimiter and then used those splits in calculations. Since you do not have any seconds in your time so I used first and second split to create the calculated fields and converted all my results into minutes as per following to get a measure New Time in  minutes.

               

              Then using the New Time, I created Three Measures

               

              Between 0:00 and 8:00, Between 8:01 and 16:00 and Between 16:01 and 23:59

               

              For Example, I used following for Between 0:00 and 8:00

              If

              [New Time] >=00 and [New Time]<=480

              Then

              1

              Else

              0

              END

               

              and using those measures I could get these views

               

              and then your final view :

               

              I am attaching the workbook. Let me know.

              Hope it Helps!!!

              Thanks

              Deepak

              1 of 1 people found this helpful
              • 4. Re: Grouping times into three time periods
                Andrew Robinson

                Deepak,

                 

                I truly appreciate your time and effort. I will be using what you have taught me very often.

                 

                Thanks again.

                 

                Andrew