I’m new to KNIME and need some help on taking employee shift time durations and partitioning them into hourly intervals. I’d like to do this in order to capture hourly labor by employee and position, for comparing to sales and traffic. The attached spreadsheet serves as an example dataset. I imagine the solution requires a loop over the intervals of time that I"m attaching as well, along with several if/else statements.
There is a second layer of complexity with having overtime rates and pay. Perhaps the easiest solution is to not use the existing regular and overtime pay rates but rather create a new column that blends the rates into a per shift basis, as it’s difficult to track when an employee crosses into overtime.
If someone can help with a loop and if/else workflow, I greatly appreciate it!
Intervals of Time.xlsx (14.8 KB)
Labor Example.xlsx (157.5 KB)
Would you please explain your question a bit more? I’m not getting it, sorry. And what are the inHour and outHour columns in dataset?
Maybe it’ll help to attach the desired outcome. The question(s) I’m trying to answer by manipulating the originally attached datasets are:
-
Given an employee’s shift duration (Clock Out - Clock In) and given hourly time intervals (7:00 - 7:59, 8:00, 8:59, etc.) how can I partition the employee’s working time into the hourly intervals?
-
After knowing how many minutes an employee worked in each hour, I then want to determine the cost for the employee’s number of minutes worked in a given hour. A precursory step seems to be to take the employee’s (regular pay) + (overtime pay) and create a column for the aggregated rate of pay for the shift. Probably the easiest thing to do to capture any overtime accrued in a shift.
This aggregated rate would then be divided by 60 to yield a per minute rate, then the per minute rate is multiplied by each of the hourly intervals worked to get a per hour labor cost per employee. Example: John worked 60 minutes in the 7:00-7:59 interval, his aggregated shift rate was ($15/hour)/60 = $0.25/minute, so John made 60 minutes * $0.25/minute = $15 for minutes worked in the 7:00 hour interval.
See attached desired outcome for at least the end result step 1 needs to provide. This was built in Alteryx but I’m trying to transfer this to KNIME and finish step 2. THANKS!
AlteryxHourlyIntervalOutput.xlsx (28.2 KB)
Let me see if I’ve got your point.
You have several employees and they start working (inHour) in different times of the day (in shifts) and they leave the work at some time after working (outTime). Now you want to calculate the duration of their working time and based on the time and the duration, determine the hourly shifts he worked in.
For this you need to have the time in and out for each employee (e.g. in labor.xlsx you had inHOur and inMinute which I think means that one employee started working at for example 10:46 and the same for out time).
Then you want to put this working time in hourly shifts and say which shifts the employee has worked in and how long. Then based on a pay per minute method, you want to calculate the money that the employee should be paid.
Is that right?
Thank you for continuing to engage in this, but still not quite right.
Say I work for 2.5 hours, from 7:00 - 9:30. Say there are some sales in those 2.5 hours. The end goal is to know how much my labor cost per hour so this can be compared to the sales. Say my 2.5 hour shift included some overtime, so I had a blended rate for my shift of $15/hour. This means I cost $15 in 7:00, $15 in 8:00, and $7.50 in 9:00-9:30. I want to automate this calculation and I imagine the resulting workflow takes my single row of shift information and create multiple rows of my shift information for a each hour (example: my original row is that I worked from 7 - 9:30, the hourly partitioned output will result in me having 3 rows of data, two for each full hour and a third row with the half hour I worked.
You are correct on observing that the InHour and InMinute need to be combined into one column reflecting a time (HH:mm) and the same for the OutHour and OutMinute.
Thanks!
So you want to calculate the money paid to each employee in hourly basis (0.25$ per minute). For example an employee named John has worked from 10:45 to 1215. You want to have an output in which it has 3 rows for John for working from 10 to 11, 11 to 12 and 12 to 13. And it says how long (in minutes) he has worked in each hour and how much he should be paid.
I think it’s right now. If yes, I go on thinking to find the solution.
Yes, we are on the same page now! Awesome, any help you can provide is greatly appreciated.
OK, Now let me know how exactly the structure of your dataset is. Is the “Labor Example.xlsx” file, your true sample of the data you have? Do you have the date in a date/time column and the Hour and Minute in separate columns? I’m asking this because I have to transform the data and if your real dataset has the correct format already, It’s waste of time for me to do the job again. The data should contain for example 2 date/time columns, one for start date/time and one for end date/time. If no, I’ll work on that.
The data set is exactly as shared in the start of this post. There is a date of business column and then separate hour in, minute in, hour out, minute out columns. These last four columns need to be combined to be ‘clock in’ and ‘clock out’ time columns, I’m having trouble with this as well. Thank you!
Hi,
I’ve built a workflow (attached) which I think produces the output you are looking for.
I’ve put shifts as columns, so for each employee’s work day you have one record and it’s splitted into hourly shifts.
Take a look and then you can ask any further questions about it.
Best,
Armin
Labor Duration.knwf (92.5 KB)
Armin,
Thanks again for this. It gets me most of the way there, now I’m doing another loop to calculate the hourly dollars per employee and from there I think I need to transpose the data prior moving to visualization. Thanks again, I appreciate your support!
Tony
1 Like
Dear Tony,
I have improved the workflow and now I guess it does exactly what you asked:
Labor Duration.knwf (141.7 KB)
I also have explained the whole workflow in a video which may help you better:
Best,
Armin
1 Like
Armin,
Thanks for the update. I posted a new question here.
The labor data loop is working well. Now I’m looking to create a sales forecast by averaging together a few weeks of data.
date sales
week 1 123
week 2 354
week 3 569
week 4 734
I use the lag node to get several weeks on the same row:
date sales sales lag 1
week 1 123 n/a
week 2 354 123
week 3 569 354
week 4 734 569
What I’d like to do is take the average of these sales from one row and have KNIME generate a new row and continue the pattern for x rows:
- = forecasted data based on simple averaging
date sales sales lag 1
week 1 123 n/a
week 2 354 123
week 3 569 354
week 4 734 569
week 5* 652 734
week6* 693 652
and so on…
Can this be done?
I’ll post this same explanation in the actual other problem posting to respect the process.
Thanks,
