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.
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.
