6 Replies Latest reply on Mar 7, 2016 12:32 PM by Simon Runc

    How to calculate hours worked (per month) based on start and stop times

    James Keuning

      I have activity data and timekeeping data. The two tables are in Access, I am blending them using a left join (I think that is how I describe the data source).

       

      Using the data I can calculate the hours worked on a daily level, but whenever I try to do anything else my time calculation gets wonky. I understand what is happening, that the sum is being calculated at a different level than I expect, but I do not know what to do about it.

       

      Ultimately what I want is to show a count of each action per user and a count per hour of each action per user.

       

      I understand that I need either an LOD or a table calculation, I just cannot figure out how. The views will always have a date range filter.

       

      (One thing to note is that I have timekeeping data in the tblLog source but I do not want to use it, I included it for quality control, I am trying to calculate times from tblTimeCardExport)

       

        • 1. Re: How to calculate hours worked (per month) based on start and stop times
          Simon Runc

          hi James,

           

          Is this what you mean?...attached.

           

          I've changed your [ActionsPerHour] calc to

           

          SUM([Number of Records])

          /

          MIN([TimeWorked])

           

          bear in mind that, and assuming I've understood your requirements!!), this works as the Level of Detail of the Viz (having userID and Action) is such that you can take the MIN (so will just be a singleton value). If you were to remove, say Actions) from the Viz, it would take the MIN([TimeWorked]) from the entire userID so would calculate differently. As an aggregate calculation, it is dependent on the VizLoD...If you wanted to have it calculated (off canvas) like a Row Level calc then you'd need to use a LoD or Table Calc.

           

          Hope this helps, but please post back if not (or it doesn't make sense)

          • 2. Re: How to calculate hours worked (per month) based on start and stop times
            James Keuning

            I've taken a look at MIN([TimeWorked]) and what I still need to figure out is how to calculate an accurate total of hours worked for a period when there is no date field on a any shelf.

             

            So, in the view I show in this screenshot, I want to pull the Day of Date off and add up the hours for U03 (13+21+19+1) and add up each action for the period.

             

            (It also occurs to me that I should not use MIN because I might have data where a person has two starts and stops on the same day - a person works two short shifts or punches our for lunch.)

             

            • 3. Re: How to calculate hours worked (per month) based on start and stop times
              Simon Runc

              hi James,

               

              Apologies I hadn't looked at the grain of the data!...and hadn't also known your final use-case wouldn't have the same VizLoD (i.e. by TimeIn-TimeOut) as the example.

               

              So yes a LoD is required (we could use a Table Calc, but LoD is simpler and will work irrelevant of the VizLoD).

               

              So the new formula to get the (13+21+19+1) for U3 would be

               

              [TimeWorked - FIXED LoD SR]

              {FIXED [Userid]: SUM({FIXED [Userid],[Time In1],[Time Out1]: MIN(DATEDIFF("hour",[Time In1], [Time Out1]))})}

               

              so the middle FIXED LoD {FIXED [Userid]: SUM({FIXED [Userid],[Time In1],[Time Out1]: MIN(DATEDIFF("hour",[Time In1], [Time Out1]))} gets the hours between each session (by session I means the combination of [Userid] & [Time In1] & [Time Out1]). As this is repeated for each Action, we only want to take a single value (so I've used MIN...but MAX, AVG, ATTR...etc. would work equally well). We then want, for each [Userid] to add these up. This is what the outer part of the FIXED LoD does.

               

              We can then use this new calculated field in the 'ActionPerHour' calculation

              [ActionPerHour - SR]

              SUM([Number of Records])

              /

              SUM([TimeWorked - FIXED LoD SR])

               

              In the attached I've left the [Time In1] & [Time Out1] in the VizLoD, so you can see what's going on, but the calculation will work once this is removed, see the 'Using LoD - SR' tab.

               

              Hope I've got it this time, but let me know if not!

              1 of 1 people found this helpful
              • 4. Re: How to calculate hours worked (per month) based on start and stop times
                James Keuning

                Wow, that's it. Thanks a bunch. You enabled me to move on from hours of failing.

                 

                My LOF-fu is super-weak and I had no idea that we could use multiple fields when defining the level.

                • 5. Re: How to calculate hours worked (per month) based on start and stop times
                  James Keuning

                  One more question - can we make the LOD calculation respond to the date filter?

                   

                  EDIT: I think I solved this by changing the date filter to a Context filter.

                  • 6. Re: How to calculate hours worked (per month) based on start and stop times
                    Simon Runc

                    ...glad it did the trick!

                     

                    For getting going with LoDs...I'd start with this great TTD Video

                     

                    TDT: LoD of the Rings - May 21, 2015

                     

                    and then look at these 15 great examples on different ways to use them

                     

                    https://www.tableau.com/about/blog/LOD-expressions?signin=ef097e747837b26390ce4b8bb61f15ab

                     

                    Yes FIXED are calculated at the same point in the pipeline as calculated sets, so the only way to make them 'filter responsive' is to make the filter 'in context' (if you are interested in the geekier side of tableau, and it's order of filter operations check out this blog...Vizible Difference: Evolution of the Order of Operations Diagram which explains why this is the case). There is a performance cost to using context filters (unless they are filtering out 80%+ of the data, when there's a performance boost!...apparently), but unless your data is very complicated and/or 10M+ rows, it'll be fast enough. You can use INCLUDE/EXCLUDE LoDs, which are filter responsive (and more efficient!), but they are (in part) dependent on your VizLoD, so your exact solution would be hard for me to guess at (unless I know the final use VizLoD)...they are also more complicated (as you need to think about the VizLoD). The 2 vids above will explain how they work, far better than I can!!