Weekly Trends By Hour

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I work in a factory as a foreman. I am building a Dashboard-style Report,
and one of the graphs I want to include is a look at whether or not we have
better results on certain days of the week or not. We work a 6 day work
week, 3 shifts, and pieces either come out Perfect, Fixable, or Junk. The
machines are shut down on the 7th day, and I want to know, by hour, what our
trend is like during the week. I have used a crossdata query to group the
data by weekday, then by hour, with column heading of the grading, and the
value is a count of the grading. This serves to combine data on Monday from
the first week with the data from Monday from the second week, etc.

The data in the query is exactly how I want it, but I can't seem to get it
to graph correctly because the weekday and the hour are still two different
fields. I want the X axis of the graph to be a by-hour listing from midnight
on Sunday morning to midnight Saturday night, by I can only get it to give me
either by day or by hour, not both. Do I need to run a seperate query first
to somhow combine these two fields, or is there another way to structure my
query to better facilitate getting the graph I want?

-Cevin
 
I would suggest calculating an hourInTheWeek field using the Day and Hour
fields. Then it will be straightforward to get the hourInTheWeek on the
X-axis.
 
I've combined them into one field :
HourofWeek: [Day] & ", "[Hour]

This sorts my data the way I want it. My only problem know is that it
returns the weekdays as a number rather than the name of the day, or the 3
letter abbreviation for the day. Also, the time comes back as a 2 digit
number. For example, Sunday 2:00 pm comes back "0, 14"

How can I format this so that it is recognizable on the graph?

Thanks for the help.

-Cevin
 
You may have to keep your current HourOfWeek field to maintain the sorting
but then add another field that will contain the value you want for display
using WeekDayName([Day],True) or Format([Day], "ddd" ) to get an abbreviated
day name and Format([Hour],"hhhh") to get military time.

CevinMoses said:
I've combined them into one field :
HourofWeek: [Day] & ", "[Hour]

This sorts my data the way I want it. My only problem know is that it
returns the weekdays as a number rather than the name of the day, or the 3
letter abbreviation for the day. Also, the time comes back as a 2 digit
number. For example, Sunday 2:00 pm comes back "0, 14"

How can I format this so that it is recognizable on the graph?

Thanks for the help.

-Cevin

Bruce Meneghin said:
I would suggest calculating an hourInTheWeek field using the Day and Hour
fields. Then it will be straightforward to get the hourInTheWeek on the
X-axis.
Click to expand...
Click to expand...
 
Back
Top