Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Hours Per Shift

MD2050
8 - Asteroid

Hello Everyone-

I am trying to create a workflow which will help capture the time hours worked by an employee in a specific shift.

 

Scenario-

We are a manufacturing facility and production goes on in all 3 shifts (1st Shift 6 am-3 pm, 2nd Shift 3 pm-1 am, 3rd shift 10 pm- 6 am). 

Challenge is on how to accumulate the time (work hours) of an employee who punches in at the start of the shift and keep accumulating the hours until the end of the shift.

For example: if an employee starts at 6 am then every time the workflow executes it should accumulate the hours worked since he punched in to the time workflow executed, so if the workflow executed at 7:45am it should show 1 hour and 45 mins as accumulated time in case when the employee is still clocked in. The work flow will execute every 30 mins so the work hours will accumulate from the start of the shift until the end and will reset at the shift change. When the employee clocks out then the accumulated hours should be [Clock In Time]-[Clock Out Time].

Same logic has to be replicated for 3 shifts and they should hold the accumulated hours per shift independently meaning the accumulated hours should reset to zero once a new shift clocks in.

Another challenge is to capture the worked hours past midnight, meaning if a 2nd shift employee clocks in at 3 pm on 11/21 and clocks out at 1am on 11/22 then his worked hours should be captured under 2nd shift for 11/21.

I recently started to work with Alteryx and this is the first project I am working on since last week. I read some of the articles at the community section and have been trying to figure out how to tackle this situation. I will really appreciate if I can get some direction.

Please find in attached the punch in data.

 

5 REPLIES 5
MSalvage
11 - Bolide

@MD2050,

 

So i started working on a solution for this and got relatively far along then realized I didn't know how to allocate hours for the overlapping shifts(2nd and 3rd).

 

If an employee clocks in at 9pm(2nd shift technically) and out at 5am(3rd shift) how would you want that split? 1hr 2nd, 7hrs 3rd? Or 4 hrs 2nd, 4hrs 3rd?

 

I think the way to go with this one is to set up partitions based on the shifts/your judgement and generate rows based on 15 minute intervals. Then taking the difference between the max and min within the partition. I have attached the workflow I built you will just have to work on the logic for partitioning(shifts).

 

Best,

MSalvage

MD2050
8 - Asteroid

Hello MSalvage –

Thank you very much for the reply.

If the employee clocks in at 9 pm and clocks out at 5 am, his hours will go entirely in 2nd shift.

 

Also, if a person who started in first shift and is continuing to work in second shift his hours can be ignored after the second shift kicks-in to remove the complexity of shift overlap. The challenge is to capture the 3rd shift time which starts on 11/21 but end on 11/22, those hours’ needs to be accumulated under 3rd shift for 11/21.

 

Purpose of this information is to display a real time KPI of units produced per man hour by department [total units produced /total man hours] at an interval of 30 mins. We do not want to save the accumulated data meaning every when the workflow executes the old data will get overwritten by the new data.

For example: We have 2 employee started on 11/22 (shown below)  one employee already clocked out at 2 pm while the other one is still working .what we want is if the workflow executes at 3 pm CST then total hours for the 2 employee should be [10+8=18] ; 8 hours is for the second employee who didn’t clocked out yet and his worked hours are calculated by taking difference of the time-stamp at 3 pm to the time he clocked in which is 3 pm-7 am= 8 hours

EMP ID

Time In

Time Out

1

600

1400

2

700

 

Further, when the same workflow executes 30 mins later at 3:30 pm CST then then total accumulated hours should be [10+8.5=18.5] , and so on. Every time when the workflow runs it will overwrite the old data.

For every employee who is still working his worked hours will be the difference between the time when the workflow ran to the time when he clocked-in and when an employee clocks out his worked hours will be [clock-out punch]- [clock-in punch] .

Further, the total hours will reset when the shift is over and the same logic will start accumulating hours for the new shift.

Thank you.

 

jrgo
14 - Magnetar

@MD2050,

 

See if the attached satisfies your requirements. Wasn't sure of the output you wanted so there's two options for you.

image.png

 

The tools are annotated so I won't go into specifics here, however, feel free to reach out if you have any questions!

 

Best,

 

Jimmy

MSalvage
11 - Bolide

@MD2050,

 

I think I understand now. @jrgo's workflow solved the "not clocked out yet" issue. Although I now think that for what you want there is no need to generate rows. You simply need to concatenate the shift and the Punch_In_date to get the a "Shift_Date" group by that and Sum the hours worked. The remaining issue(I think) is that if a person starts at 1am on 11-22-2017 you want the Shift_Date to be "11-21-2017 3rd Shift".

 

Using mostly @jrgo's work I think the attached workflow should be your desired output.

DateTime for MD2050.PNG

 

 

 

 

Best, 

MSalvage

MD2050
8 - Asteroid

Good Morning-

Thank you very much MSalvage and Jrgo for helping me with the logic. 

Sincere apologies for late reply. I was able to get around the issue when an employee clocks in past midnight.

I was also able to complete the workflow to calculate the units per man hour workflow using your logic for capturing man hours.

 

Thank you very much for your help.

Labels