cameronbenson
New Member
- Joined
- Jan 29, 2016
- Messages
- 5
Ok, so I have a data set that is extracted from our warehouse management software that shows each pick from stock that an employee does. i have the data dumped into a basic table that records the hour (=hour()) that the employee worked. This way, i can track how many picks the employee did each hour. But here's my problem:
I'm trying to figure out their weekly performance, but the employees bounce around to different tasks throughout the day. There is a goal per hour that they're supposed to hit, but if they spend 3 hours picking and then 2 hours doing put-away, they shouldn't be penalized for the 2 hours they weren't picking. I've been able to make a daily performance workbook, but I'm having trouble making one for the week. Specifically, if they picked 4 hours for the day, their goal for the entire day would be different than that of someone who picked 2 hours for the day.
In short, the daily goal should be based off how many hours each employee picked. In a perfect world, each employee would pick for 8 hours. So if you had 10 employees and your goal per hour was 20, then your daily goal would be 1600. But i need that goal to be flexible based on how many hours each person spent picking.
If possible, i'd like to have the following restrictions to the workbook. I understand if it's not possible.
1. While the report will only be run once a week, it's ok if the report takes some time to run. However, i'd like to make this as simple as possible because not all the Supervisors are a savvy at excel as they pretend to be.
2. The raw data has approx. 21,000 lines (picks) per week so I don't want the formulas to be too large and crash excel (our computers are only running dual cores).
3. I'd like to avoid pivot tables and macros. While I know how to use them and macros can be assigned to a button, you must not underestimate the laziness of some of these people.
This forum has helped me countless times in the past so I look forward to your responses. Unfortunately, I cannot provide the actual report due to security concerns. For ease of formula generation, I will be able to provide the following information:
Employee name is located: Data!Picking_Data[Picker]
Hour the pick was performed: Data!Picking_Data[Picking Hr '#]
Goal per hour is located: Dashboard!C9
Please let me know if you have any questions and I will do what I can to supply more information. I will be here for the next 3.5 hours. I will respond tomorrow if I miss any of your feedback. Again, thank you all so much for your help.![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
I'm trying to figure out their weekly performance, but the employees bounce around to different tasks throughout the day. There is a goal per hour that they're supposed to hit, but if they spend 3 hours picking and then 2 hours doing put-away, they shouldn't be penalized for the 2 hours they weren't picking. I've been able to make a daily performance workbook, but I'm having trouble making one for the week. Specifically, if they picked 4 hours for the day, their goal for the entire day would be different than that of someone who picked 2 hours for the day.
In short, the daily goal should be based off how many hours each employee picked. In a perfect world, each employee would pick for 8 hours. So if you had 10 employees and your goal per hour was 20, then your daily goal would be 1600. But i need that goal to be flexible based on how many hours each person spent picking.
If possible, i'd like to have the following restrictions to the workbook. I understand if it's not possible.
1. While the report will only be run once a week, it's ok if the report takes some time to run. However, i'd like to make this as simple as possible because not all the Supervisors are a savvy at excel as they pretend to be.
2. The raw data has approx. 21,000 lines (picks) per week so I don't want the formulas to be too large and crash excel (our computers are only running dual cores).
3. I'd like to avoid pivot tables and macros. While I know how to use them and macros can be assigned to a button, you must not underestimate the laziness of some of these people.
This forum has helped me countless times in the past so I look forward to your responses. Unfortunately, I cannot provide the actual report due to security concerns. For ease of formula generation, I will be able to provide the following information:
Employee name is located: Data!Picking_Data[Picker]
Hour the pick was performed: Data!Picking_Data[Picking Hr '#]
Goal per hour is located: Dashboard!C9
Please let me know if you have any questions and I will do what I can to supply more information. I will be here for the next 3.5 hours. I will respond tomorrow if I miss any of your feedback. Again, thank you all so much for your help.