3 Replies Latest reply on Dec 21, 2017 2:57 AM by Beth Daniel Lindsay

    Grouping hours

    Eleanor Green

      Hello

       

      First question I have ever asked here - I hope it is not too stupid but the other answers don't quite seem to be the right thing for me

       

      I have data based on an event based date and time - ie its regularity varies.

       

      I would like to group the data just to show the average value in off-peak hours - which are hours 0-6 AND 20-23. I have read this Sum data using a 6-hour time interval  but I can't quite work out how to make this work for a group of hours that is not continuous.

       

        

      APPLIES_TO_DATETIMEVALUE
      06/03/2016 00:00:00356
      06/03/2016 00:01:00500
      06/03/2016 00:14:00800
      06/03/2016 00:24:00356
      06/03/2016 00:30:00754
      06/03/2016 00:31:00234
      06/03/2016 00:38:00555
      06/03/2016 01:00:00245
      06/03/2016 01:06:00769
      06/03/2016 01:08:00578

       

       

      thanks very much

        • 1. Re: Grouping hours
          Ivan Young

          Hi Elanor,

          I would recommend creating a calculated field(dimension) that you can use for grouping or filtering.  The formula would be something like what I have below.

           

          Good luck,

          IVan

           

           

          IIF((DATEPART('hour',[APPLIES_TO_DATETIME] ) >= 0 and DATEPART('hour',[APPLIES_TO_DATETIME] ) <= 6) or

          (DATEPART('hour',[APPLIES_TO_DATETIME] ) >= 20 and DATEPART('hour',[APPLIES_TO_DATETIME] ) <= 23), 'offpeak', 'peak')

          • 2. Re: Grouping hours
            Eleanor Green

            Thanks!

            • 3. Re: Grouping hours
              Beth Daniel Lindsay

              Would it be possible to do something like this but to have more than 2 categories? For example, I'd like to have morning, afternoon, evening, late night and closed hour groupings.

               

              So, for example:

               

              IIF((DATEPART('hour',[APPLIES_TO_DATETIME] ) >= 2 and DATEPART('hour',[APPLIES_TO_DATETIME] ) <= 7), 'closed', '[what to put here?]')

               

              Another field would be:

               

              IIF((DATEPART('hour',[APPLIES_TO_DATETIME] ) >= 8 and DATEPART('hour',[APPLIES_TO_DATETIME] ) <= 11), 'morning', '[what to put here?]')

               

              Thanks for your help!