Group Data by Time Period 1

[capronton]

Any ideas about how I might develop a report by Time Period? For example

Morning Peak – 6am to 9am Ontime Late Early
Morning Midday – 9am to noon Ontime Late Early
Afternoon Midday – noon to 3pm Ontime Late Early
Evening Peak – 3pm to 6pm Ontime Late Early

Is it possible to group the data as shown above?
Is it possible to create a Time Period parameter with the time ranges above?

One problem is that all my date fields include date and time. See data set below:

incLogID tDate incTime schTime bus driver rsaStatus service blk rte
153061791 2/17/15 6:33:38 6:33 2055 21470 OnTime WKDAY 300032 300
153071385 2/17/15 6:43:28 6:43 2055 21470 OnTime WKDAY 300032 300
153082779 2/17/15 6:55:22 6:54 2055 21470 OnTime WKDAY 300032 300
153088269 2/17/15 7:01:12 6:58 2055 21470 OnTime WKDAY 300032 300
153097603 2/17/15 7:11:06 7:10 2055 21470 OnTime WKDAY 300032 300
153107619 2/17/15 7:21:48 7:22 2055 21470 OnTime WKDAY 300032 300
153085919 2/17/15 6:58:42 6:58 2215 29580 OnTime WKDAY 7009 7
153095179 2/17/15 7:08:32 7:08 2215 29580 OnTime WKDAY 7009 7
153107461 2/17/15 7:21:42 7:19 2215 29580 OnTime WKDAY 7009 7
153111975 2/17/15 7:26:22 7:23 2215 29580 OnTime WKDAY 7009 7
153129087 2/17/15 7:44:26 7:35 2215 29580 Late WKDAY 7009 7
153139371 2/17/15 7:55:16 7:47 2215 29580 Late WKDAY 7009 7
153140045 2/17/15 7:55:54 7:55 2055 21470 OnTime WKDAY 300032 300

Thanks for any assistance.

 

[Charliy]

Once you use the HOUR function, like Skip explained, then it's easy to group by hour.

You'll have a formula {@time hour} that is HOUR({table.your_date_time_field})

Then set up another formula {@hour breakout} like:

if {@time hour} in 6 to 8
then "Morning Peak"
else
if {@time hour} in 9 to 11
then "Morning Midday'
else
if {@time hour} in 12 to 14
then "Afternoon Midday"
else
if {@time hour} in 15 to 17?
then "Evening Peak"
else "After Hours"

Why did I end the first range at 8 when you specified 6 AM to 9 AM? The way I did it the first range is 6:00 to 8:59
then the second range starts at 9:00. You don't want any time to fall into two groupd, so this is the cleanest way to break it out.

Once tat's in place you can refer to that second formula in other formulas or Running Totals

 

[capronton]

This is great! Thanks!

-ron

 

[pmax9999]

Hi Ron

Charliy gave you a good solution, as have a number of people to the other questions you have posted in the past.

I would suggest you use the Great post? Star it! (with the purple star) button as a way of showing your appreciation for the assistance given, and to show others that the solution offered solved your problem.


Regards
Pete