Hi there,
I have a business case, where I need to build a operations model calculating people per work shifts.
Let's say we have schedule of machines arrivals and type of service to be done:
| Arrival | Service type |
|---|
| 2016-01-01 15:15:00 | A |
| 2016-01-01 15:18:00 | B |
| 2016-01-02 06:55:00 | A |
| 2016-01-02 17:40:00 | A |
| 2016-01-04 19:30:00 | C |
| 2016-01-07 01:45:00 | B |
Then to each Service type we have number of workers required and typical service duration:
| Service type | Workers | service duration (minutes) |
|---|
| A | 5 | 15 |
| B | 10 | 20 |
| C | 1 | 7 |
| D | 3 | 10 |
The service is being performed 24h, all week long by 3 work shifts. Machines have different time of departure, but for now lets assume, that we always have up to 30 minutes to perform service after machine's arrival. Also lets assume that they are always serviced in order of arrival.
What I need to do is to perform a model of how many workers are required to meet those 30 minutes. For example: if machine arrives at 15:15 and needs service A, which engages 5 workers for 15 min., the service will be finished at 15:30. But when next machine arrives at 15:18 for service B, which takes 20 min., if the workers start at 15:30, the job won't be done on time, so I need 10 more people, because those previous 5 are still busy. It means we need at least 15 people. And so on.
Do You have any idea how to make such shedule (timestamp, workers needed)? I've tried with some intervalmatch and then sum workers for each timestamp, but for some reason it didn't work.
Thex step would be to manage starting shifts in order to minimalise global number of workers, but I think I can handle that by =Max(aggr(sum([workers_needed]),[shift_number])) or something like that.
It doesn't have to be much effective solution - I have Yearly shedule of 100 000 machine arrivals, 5 service types and shifts schedule should be fracted to maybe 5 min (that is 105 120 rows), the result is more important.
Thank You in advance for any hint, that brings me closer to the solution.
Wojtek
