Tracking Resource Capacity & Utilization over time in Qlik Sense

[leftfield78]

I'm relatively new to Qlik and am creating an app to track my department's resource capacity and utilization.  We need to account for each person's start and end date for the year.   Originally, I used the min(Date) and max(Date) for each resource to set these boundaries.  The 'Date' comes from each instance where someone logs their time for the week.  Ideally, everyone would log their time each week - however this is not always so.  If I use the min(Date) and max(Date) I don't accurately capture everyone's full capacity since someone may not have logged time until February but were available to work starting on 1/1/2017.  That being the case, I'm wanting to incorporate the 'EmployeeHireDate' field to capture everyone's start date (I'm only looking at 2017 so if they were hired before this year, I'll use 1/1/2017 as the start), 'EmployeeTerminationDate' for those no longer in the department, and the Today function for everyone else since we want to capture capacity and utilization up to the current day.

I have a need to report on utilization by month for 2017 so i created a bar chart with 'Month' as the dimension and the following expression for my measure:

(Sum(TimeCardHours)) / ((Sum(Aggr((networkdays(min(Date), max(Date), '1/2/2017', '5/29/2017', '7/4/2017', '9/4/2017', '11/23/2017',

'11/24/2017', '12/25/2017'))*[DailyWorkHours]), EmployeeID,Month))))

'DailyWorkHours' establishes each employee's total hour capacity per work day.

This does provide me a utilization % per month but, again, the problem is that the department's total capacity is not yet being captured accurately so I'm getting numbers that are not quite accurate.  So my questions are:

1.  How could I properly establish the appropriate date ranges?  I'm trying to figure out how I can express this either in the load script or in the measure expression.  I need conditions such as -  if(EmployeeHireDate<min(Date),EmployeeHireDate,min(Date)) - however I keep getting en error saying this expression is invalid.  There are also other if statements I need to use but am not sure if they should be nested.  I can't reference the 'Date' field more than once in a table load or I get an error.

2.  Am I on the right track or do I need to approach this problem differently?  I feel that I'm very close.

Any help anyone could provide would be very much appreciated.

Thanks!

[JustinDallas]

Halloween is over, but that Measure function you have is scary!  Do you mind explaining what the dates

ays(min(Date), max(Date), '1/2/2017', '5/29/2017', '7/4/2017', '9/4/2017', '11/23/2017',

are for?

[leftfield78]

Hahaha!  Indeed it is.  The networkdays function is eliminating weekends from our capacity calculation, the min(Date) and max(Date) are capturing the earliest and most recent timelogging instances per resource respectively, and the individual dates listed are holidays recognized by my company.