Headcount measure with multiple hiring and leaving dates

[Newbie777]

Hello,

I have been trying to figure out headcount measure for more than 2 months in Qlik Sense, but I have not managed to do it. Particular problem with my employee database is that multiple people keep coming back to the company sometimes more than 3 times, so instead of a simple headcount measure which is just the count of number of names at a selected time point where the employee was hired before that date and have not yet left, a single employee can have multiple hiring dates and leaving dates so this is making it more difficult to write this measure.  Anyway, I was able to write such measure in Power BI with correct outcome at any time periods, and I tried to replicate the measure in Qlik Sense as shown below, but the resultant table shows instead of the number of employees, it just shows the increment to the number of employees.  So when looking at the number at the end, it agrees, but the period in between shows only the movement of the headcount.  

I'd appreciate it if anyone could let me know how I can fix the formula above so that it counts the total number of employees at each period and not only the incremental changes. 

Thank you for your help.

[Vegar]

What if you do something like this, using  SET analysis?

Count({$<HiringDate = {">=$(=max(Date))"}, LeaveDate = {"<=$(=max(Date))"}>}DISTINCT EmployeeID)

[Newbie777]

Thanks for your suggestion.  I tried the formula above, and there is no error, but the output is showing null value.  I am wondering where I am going wrong.

Output table:

Thanks a lot for your help. 

[Vegar]

it looks like your set modifiers are wrong.  You need to adjust them to your data model. You might need to change the field name to the date field in your model. Make sure your Date field is a date and not a string. 

Count({$<HiringDate = {">=$(=max([Date-Posting Date]))"}, LeaveDate = {"<=$(=max([Date-StartDate]))"}>}DISTINCT EmployeeID)

[Newbie777]

Thanks a lot for your advice, but even though there was no error in the formula, the result produced was zero.  

I will explore the reason for this.  

But for now, tweaking of original formula is producing the correct answer which matched with Power BI calculated headcount result.  

So for now, I will use the formula above, but thanks for letting me know the alternative method, which I will explore in the future.  

Next, I'd like to suppress zero in the table of employee who already left or haven't joined the company at particular point in time.  I am wondering how I can suppress zero from the formula above.  

I'd greatly appreciate any advice. 

Thank you & best regards,

[Vegar · Accepted solution]

In some of the Qlik Sense objects it is possible to exclude zero values by a check box in the object property pane.