2 Replies Latest reply on Jan 21, 2016 5:46 PM by Steve Mayer

    Summing hours worked from multiple users and calling out a benchmark?

    Derrick Lang

      Good Morning,


      I have a data source which reports in HR data from our employees. Our model is as such where we typically budget for one employee at one location for the main budget. As such, we often have to double or triple staff a location if volume dictates. Our previous model was up to the field manager's discretion - though now we are centralizing the process to streamline and to reduce waste of manpower. I have Tableau 9.0.


      I have attached both an example of the data source (xlsx) and my initial run at trying to condense the data (twbx). A challenege I am running into is having the benchmark to call out locations over the benchmark. The attached example, I would want each Location to have a benchmark of 10 hours. When I have taken a swing at it, it is either only looking at the individual employee or looking at the entire population.


      Based on the attached workbook, I would like a benchmark call out so that the Mauldin location (6+ hours worked) is under the benchmark but Rock Hill (12 hours) is above the benchmark, even though 2 employees worked that location.

        • 1. Re: Summing hours worked from multiple users and calling out a benchmark?
          Esther Aller

          Hey Derrick,


          I'm afraid I'm a little confused on what the final goal is. Do you want to filter the view to show only locations that are over/under 10 hours? Do you want to dynamically color the locations based on whether they are over/under 10 hours?


          Maybe you can make a mockup in Excel or Paint that shows how the final view should look?

          • 2. Re: Summing hours worked from multiple users and calling out a benchmark?
            Steve Mayer

            In the attached workbook, I created a calculated field called "Met Benchmark" and used it to color the text. This approach uses a table calculation to SUM all of the hours across all employees for each Location & Date. If you aren't familiar with table calculations, you'll want to spend some time understanding how they work.


            1. Create a calculated field called "Met Benchmark", which will return True if the total hours worked are less than or equal to 10:


            WINDOW_SUM(SUM([Time Worked])) * 24.0 <= 10


            2. Drag the calculated field to Color and set the "Compute Using"  to Employee, as shown below. This means the table calculation will be calculated across all Employees for each Location and Date.

            3. Set colors appropriately (I used black and red) and it should look something like this:



            Hope this helps,