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

# Grouping hours

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_DATETIME VALUE 06/03/2016 00:00:00 356 06/03/2016 00:01:00 500 06/03/2016 00:14:00 800 06/03/2016 00:24:00 356 06/03/2016 00:30:00 754 06/03/2016 00:31:00 234 06/03/2016 00:38:00 555 06/03/2016 01:00:00 245 06/03/2016 01:06:00 769 06/03/2016 01:08:00 578

thanks very much

• ###### 1. Re: Grouping hours

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

Thanks!

• ###### 3. Re: Grouping hours

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?]')