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.