If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Weekly Trends By Hour
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 |
#2
|
|||
|
|||
Weekly Trends By Hour
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. "CevinMoses" wrote: 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 |
#3
|
|||
|
|||
Weekly Trends By Hour
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" wrote: 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. "CevinMoses" wrote: 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 |
#4
|
|||
|
|||
Weekly Trends By Hour
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" wrote: 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" wrote: 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. "CevinMoses" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|