#2 seems like the sticking point to me. How do we know what days an employee is supposed to work? What about holidays?
Is there some way you can get this information into your data? Ideally as a column called "Expected hours" with an entry for each day, but at the minimum it just seems important to know *which* days they are supposed to work.
The assumption is 5 working days for each week as even holidays should have time entered as "Holiday".
Could you provide the structure (all the field names) of your data source?
When you say "percentage of hours that an employee has logged in our Time Tracking database", do you mean the ratio of hours that an employee has logged v.s. an 8 hour workday (and the ability to aggregate this ratio to day/week/month or some category level)?
If you expect 5 work days for every week for each employee, how do you know what days or how many days an employee should be working on the partial weeks (first and last week of your month of data)?
Attached is one month of data for a particular employee. The key fields are HOURS_WORKED and DATE_WORKED.
>>> When you say "percentage of hours that an employee has logged in our Time Tracking database", do you mean the ratio of hours that an employee has logged v.s. an 8 hour workday (and the ability to aggregate this ratio to day/week/month or some category level)? YES
>>> If you expect 5 work days for every week for each employee, how do you know what days or how many days an employee should be working on the partial weeks (first and last week of your month of data)? IF I AGGREGATE BY WEEKS I WOULD ENSURE THE WEEKS WERE INCLUDING THE FULL 7 DAYS. IF AGGREGATING BY MONTH, I WOULD NOT BE BASING THE PERCENTAGE BY WEEK BUT BY MONTH.
Sorry for the all caps ... not yelling ... just making it easier to pick out my response. :)
Thanks for you interest and help!
TimeTracking_Format.xls 23.0 KB
This is a very similar thread to one I started a while back on the same problem - have a read of this for some more suggestions that various folk provided.
The way that I handle the "available vs actual" hours question is something like the following (it's a while since I did it and can't quite remember the details - also this may not work for you depending on your rules for time recording).
1) If the day is a weekend day or the staff member records "leave" or "public holiday" for that day the day is not treated as an available working day, otherwise it is. (This depends on staff having to record *something* for every weekday.)
2) Calculate the total "available hours" as the number of "available days" x "standard working day" (7.5 hours in our case).
3) Calculate the total hours worked over the period of interest.
I was trying to calculate the "application rate" as the ratio of the above two numbers as a calculated field that I could use in aggregates. Unfortunately this is not a well behaved aggregate for Tableau drill-down reporting. As you drill down by some classification of the recorded hours, any day when no time was recorded against that particular type of activity drops right out of the available hours used so the ratio is wrong.
What I ended up doing was representing it as a bar chart with actual hours overlaid over a wider available hours background. See attached clip.
I still think it would be good to be able to combine expressions at different levels of aggregation - but I can't imagine what the user interface would have to be like to avoid it being a real source of confusion.
time_reporting.PNG 20.4 KB
Would it be a true statement to say, if the employee did not enter anything for a day, that they were not supposed to work on that day, and therefor be zero working hours for that day for that employee?
Also for the reverse of that, if the employee did enter something for a day, that that day should be looked as having 8 working hours for that day for that employee?
If they are true, then in your example data:
Inclusive Date Ranges that have 0 working hours per day:
1 Aug - 2 Aug
7 Aug - 9 Aug
15 Aug - 16 Aug
21 Aug - 23 Aug
Inclusive Date Ranges that have 8 working hours per day:
3 Aug - 6 Aug
10 Aug - 14 Aug
17 Aug - 20 Aug
24 Aug - 26 Aug
The trap to watch out for with that could be that if people record worked hours on weekends or holidays the day can end up being treated as 8 available working hours - when it shouldn't really be. All depends how you are thinking of doing it - that was certainly a problem in my case.
The big problem here is that folks have not been keeping up with their time tracking. Unfortunately, to get paid, they simply need to fill out their paper timesheet every two weeks. We're working to combine the two processes but, for now, I need to determine who is keeping up with their electronic time tracking and who isn't. So, I can't assume that if someone didn't enter time for a particular day, that day was a workday for the individual ... they may simply have forgotten/avoided entering time for that day. This is why I'm keeping the potential work days (every M-F) as a constant and measuring logged workdays against that. Also, for vacation/leave I do include these hours as I can highlight them in the reports as such.
Thanks guys for the help so far. I will look at Richards thread and see if that helps.
BTW - I don't think the earlier thread actually spelt out the detail of how I was counting available days. Here are a few snippets that might help.
I defined a series of calculated fields, as follows (obviously my data structure and my requirements are different from yours - but this might just give you some ideas). Note that this relies on COUNTD() - so only works against a datasource that supports that (i.e. you need to create an extract if you're using a text data source).
[Day of Week]
DATEPART('weekday', [Timesheet Item - Date])
// concatenating date and employee number allows this to be used used in a "count distinct" across
// a range of dates and a group of employees to derive total person-days
IF (([Day of Week] >= 2) AND ([Day of Week] <= 6)) THEN
STR([Timesheet Item - Date]) + ", " + [Timesheet - Employee Number]
// as above but to count weekdays that are not available work days
// ('LWOP' means "Leave Without Pay")
IF ((([Day of Week] >= 2) AND ([Day of Week] <= 6))
AND (([Timesheet Item - Activity Type] = 'LWOP') OR ([Timesheet Item - Activity Type] = 'Public Holiday'))) THEN
STR([Timesheet Item - Date]) + ", " + [Timesheet - Employee Number]
(COUNTD([person_weekday]) - COUNTD([person_nonworkday])) * 7.5
This is really good stuff ... I am using a number of your calcs and am getting very close ... thanks! My last real hiccup in the calculations comes when an employee doesn't enter any time for a day that is supposed to be logged.
For example, say someone only enters time on Monday-Thursday, forgetting about Friday. In the calculation, the time logged should show up as 80%. However, if Friday doesn't show up as a record and count as one of the unique days, the calculation would show that the employee had entered 100% of their time because there would only be the four distinct days. Is there a way to calculate the available hours without having the Friday record in the data?
I used a calendar table outer joined to the hours worked based (accounting for the day someone started working). That way you have a dummy record for each day. It's must easier to create the correct aggregation/records on the server/backend and then manipulate it in Tableau.
We ended up having one table with a single line for each employee for each day, and then did a sheet link on employee number and day to get specific data for a given day in a 'detail' table.
Each day for an employee looks like this:
EmployeeID Work Date hours_avail billed_hrs
10600 2009-01-19 8 9.80
The master calendar looks like this:
date_id calendar_date day_of_week holiday workday
1 2008-01-01 00:00:00.000 Tuesday 1 1
2 2008-01-02 00:00:00.000 Wednesday0 1
3 2008-01-03 00:00:00.000 Thursday 0 1
4 2008-01-04 00:00:00.000 Friday 0 1
5 2008-01-05 00:00:00.000 Saturday 0 0
6 2008-01-06 00:00:00.000 Sunday 0 0
7 2008-01-07 00:00:00.000 Monday 0 1
8 2008-01-08 00:00:00.000 Tuesday 0 1
9 2008-01-09 00:00:00.000 Wednesday0 1
10 2008-01-10 00:00:00.000 Thursday 0 1
11 2008-01-11 00:00:00.000 Friday 0 1
I agree - having that calendar would make things much simpler - so go that way if you can. The reason I don't is that time recording is done in an externally managed service so we have no control over the data structure - and I didn't want to introduce an onerous additional administrative step in order to produce some monthly reports.
That's a great point. I was originally generating a rollup table of available hours based on a calendar table. However, that table was in Access and my core Time Tracking data was coming from Oracle. When I would post to our Tableau Server, the passwords wouldn't work and performance was brutal. I just submitted a request to our DBA's to add the calendar table to Oracle. Hopefully, this will allow me to be able to set it up as you suggested.