Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Tracking Resource Capacity & Utilization over time in Qlik Sense

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!

2 Replies
Highlighted
Specialist II
Specialist II

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?

Highlighted
Contributor
Contributor

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.