I am relatively new to Qlik and am struggling to figure out how to calculate available working days for employees.
I need to be able to calculate the available working days between the later of a fixed point - i.e. start of year and a hire date and also the earlier of a fixed date - i.e. end of year and a termination date. I need to do this for each employee and also run the calculation into the future. So for example, if I wanted to calculate this for an employee a who started in December and another one (b) who started on Jan 6th, and make it effective for January MTD through the 15th, employee day would have 11 working days but b would have only 8.
I tried doing this as a measure and can calculate total working days using Networkdays between the hire date and term date but can't then factor in other time period limits. I received a suggestion to instead create a calculated data field called WorkingDay that recorded a 1 for each day the employee was available and stored that by date so that I could apply normal set analysis for YTD etc. against it.
Does anyone have examples of how they have done this and how I can best set this up?
@ellys You can check hire date or year start date & termination date or year end date. So for a year if hire date is grater than year start date then your start date will be hire date else year start date and for end date if termination date is less than year end date then your end date will be termination date else year end date. Then use networkdays function to calculate workdays.
