Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Newbie7
Partner - Creator II
Partner - Creator II

Headcount measure with multiple hiring and leaving dates

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.  

Newbie7_0-1631973354396.png

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.

1 Solution

Accepted Solutions
Vegar
MVP
MVP

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

View solution in original post

5 Replies
Vegar
MVP
MVP

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

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

Newbie7
Partner - Creator II
Partner - Creator II
Author

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.

Newbie7_0-1632020730662.png

 

Output table:

Newbie7_1-1632021034015.png

Thanks a lot for your help. 

 

Vegar
MVP
MVP

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)

Newbie7
Partner - Creator II
Partner - Creator II
Author

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

Newbie7_0-1632036122861.png

I will explore the reason for this.  

Newbie7_1-1632036588673.png

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

Newbie7_2-1632036659835.png

Newbie7_3-1632036793564.png

 

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
MVP
MVP

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