5 Replies Latest reply on Apr 17, 2018 9:18 AM by Jonathan Entwisle

    Utilization of hours for date range (missing dates)

    John Bryant

      Hi -

       

      I'm fairly new to Tableau and self taught. I'm trying to generate a report that shows the hours logged between a filtered time frame and calculate the percentage of hours based upon business days. For instance, resource Abington logged 195 hours in January 2018. There were 23 business days (M thru F) in January (I'm including holidays, as we log PTO time). 23 days = 184 hours. (Hours Logged / Business Day Hours) * 100 = Utilization Percentage. So in this scenario; (195 / 184) * 100 = 105.98%.

       

      I am able to calculate business days and hours based upon some calculations I found in the forums:

       

      Business Days

      COUNTD(IF DATEPART('weekday', [Work Date]) > 1 AND DATEPART('weekday', [Work Date]) < 7

      THEN DATETRUNC('day', [Work Date])

      END)

       

      Business Days Hours

      [Business Days] * 8

       

      These work just fine when filtering by Work Date and using the calculated fields in the Measure Values

      I soon realized that my data is missing dates for some associates, i.e. resource Albright logged 182.5 hours across 22 days in January.  Reason being I'm using [Work Date] as part of my calculation.

      Is there a way to keep my Business Days and Business Days Hours static for a date range? I assume the answer is no, since I am using Work Date, and Albright does not have an entry for a business day within Work Date (i.e. he/she did not log time on 1/1/18).

       

      If I'm unable to keep this calculation static, is it possible to include missing dates by resource?

       

      My data consists of multiple work log entries per day. In the Albright scenario, the resource did not log time on 1/1/2018 (i.e. PTO time). But I have other scenario's in which associates will log time for a week on 1 day.