13 Replies Latest reply on Aug 27, 2009 9:46 AM by . roberthday

    Actual hours vs. available hours in time tracking

    . roberthday

      Hello:

       

      I am attempting to create a calculation that provides the percentage of hours that an employee has logged in our Time Tracking database.  It’s quite easy to calculate the total hours recorded but it is difficult to calculate the available hours within various timeframes.  Here are the key components and challenges:

      1)  We can assume the allotted time for each weekday is 8 hours and only weekdays need to be used for the calculation.  I could try weeks (40) but if I am reporting for a specific month, the month will not likely start on a Sunday and end on a Saturday.

      2)  We can NOT assume each employee will enter time for every day (some have every other Friday off).

      3)  Employees often enter multiple entries for different activities/projects during the day so there are often multiple entries with the same date.

      4)  Results can be shown day by day but will also need to be rolled up for weekly and/or monthly percentages (also for groups of employees).

      5)  Key fields are [Date] and [Hours Logged] with [Number of Records] likely coming into play.

       

      Thanks for any ideas!

      Bob

        • 1. Re: Actual hours vs. available hours in time tracking
          James Baker

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

          • 2. Re: Actual hours vs. available hours in time tracking
            . roberthday

            The assumption is 5 working days for each week as even holidays should have time entered as "Holiday".

            • 3. Re: Actual hours vs. available hours in time tracking
              Joe Mako

              Bob,

               

              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)?

              • 4. Re: Actual hours vs. available hours in time tracking
                . roberthday

                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!

                • 5. Re: Actual hours vs. available hours in time tracking
                  Richard Leeke

                  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.

                   

                  http://www.tableausoftware.com/forum/how-combine-expressions-different-levels-aggregation

                   

                  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.

                  • 6. Re: Actual hours vs. available hours in time tracking
                    Joe Mako

                    Bob,

                     

                    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

                    • 7. Re: Actual hours vs. available hours in time tracking
                      Richard Leeke

                      Joe:

                       

                      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.

                      • 8. Re: Actual hours vs. available hours in time tracking
                        . roberthday

                        Joe:

                         

                        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.

                         

                        - Bob

                        • 9. Re: Actual hours vs. available hours in time tracking
                          Richard Leeke

                          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])

                           

                          [person_weekday]

                          // 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]

                          ELSE

                            NULL

                          END

                           

                          [person_nonworkday]

                          // 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]

                          ELSE

                            NULL

                          END

                           

                          [Available Hours]

                          (COUNTD([person_weekday]) - COUNTD([person_nonworkday])) * 7.5

                          • 10. Re: Actual hours vs. available hours in time tracking
                            . roberthday

                            Richard,

                             

                            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?

                             

                            Thanks again,

                            Bob

                            • 11. Re: Actual hours vs. available hours in time tracking
                              Andrew Kritzer

                              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

                              • 12. Re: Actual hours vs. available hours in time tracking
                                Richard Leeke

                                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.

                                • 13. Re: Actual hours vs. available hours in time tracking
                                  . roberthday

                                  Kritzer,

                                   

                                  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.

                                   

                                  Thanks,

                                  Bob