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

# Utilization of hours for date range (missing dates)

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:

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

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

END)

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.

• ###### 1. Re: Utilization of hours for date range (missing dates)

In my view a small description and an attached workbook would let help running to you.

• ###### 2. Re: Utilization of hours for date range (missing dates)

Hi -

Thanks for the suggestion, was having issues attaching file. But have now figured that out.

The utilization is displaying as so:

Ambati is missing work entries in January, February and March. Albright is missing a work entry in January. Abington is not missing any work entries.

My expected result is:

Thanks, John

• ###### 3. Re: Utilization of hours for date range (missing dates)

I solved my issue with a work around. It isn't to the detail I would like (date range), but it works for the time being (by month).

• I updated my spreadsheet with a new column called work month year (i.e. the month/year the time was logged)
• I then created a new sheet on the excel file having the # of business days and hours for a month.
• I joined the two excel sheets by month/year.
• Created a Expected Hours calc:

{FIXED [Monthly Working Hrs] : MIN([Monthly Working Hrs])}

• Created a utilization calculation

SUM([Hours Logged]) / SUM([Expected Hours])

• Changed filter to only allow user to select by month.
• ###### 4. Re: Utilization of hours for date range (missing dates)

Any other ideas?

I am curious about this also. In the past when working with days, I accounted for missing dates for a given entity level by joining on a date dimension table that had one row per day. This ensured that every entity level had at least one row for every day and in SQL I simply injected a 0 when null.

I'm now trying to do this, but for every hour of every day. Curious if there is a more efficient way to inject 0s for missing datetimes in Tableau.

• ###### 5. Re: Utilization of hours for date range (missing dates)

I did a bit more digging. It appears what I have done in the past for days is suggested by others for datetimes also.