Dynamic headcount measure from employee table which respects time dimension

[Newbie777]

Hello,

I am trying to create dynamic headcount measure since one month ago from employee table (fact table, rather than dimension table with details of department changes with multiple hiring and leaving dates), but so far I have not been successful despite asking round in this forum.  So far, I have managed to create employee tenure measure, although this measure keeps number of days for employees who already left the company even after leaving dates.  I think the headcount measure can be cracked by somewhere along the line of tweaking this tenure length measure.  I tried to write measure using Aggr() function to generate array of count of employees at a point of time who joined after the selected point in time and have not left before the selected point in time.  However, Aggr () function does not seem to accept if formula nested in it.  Is this so?  How can I write a measure to count distinct number of employee names who have joined before the selected point in time (max(DateField)) and who have not left before the selected point in time?  

Any pointing in the right directions would be greatly appreciated.  

Thank you & best regards,

Newbie7

[Kushal_Chawda]

@Newbie777  It will be hard to provide solution without looking at your sample data. Please provide sample data with expected output

[Newbie777]

Re: Headcount measure using disassociated calendar... - Qlik Community - 1824149

I provided you the qvf file as requested similar to this one in another post more than a month ago. 

[Kushal_Chawda]

@Newbie777 

1) You may need to link your calendar to Hire Date as Island calendar table won't help you to create measure.

2) Create a flag for leaving date where it is blank as below

LOAD EmpID,
     [Hire Date],
      if(len(trim([Leaving Date]))=0,1,0) as Flag_Leave_Date
      ...
FROM EMP;

3) Create one variable as vMaxDate with below expression which will hold latest date based on period selections from calendar table

= date(max([Hire Date]))

4) Now, you can create below measure to count distinct number of employee names who have joined before the selected point in time and who have not left before the selected point in time

=Count(distinct {<YEAR,MONTH,WEEK,[Hire Date]={"<=$(vMaxDate)"},[Leave Date]={">=$(vMaxDate)"}>+<YEAR,MONTH,WEEK,[Hire Date]={"<=$(vMaxDate)"},flag={1}>}Emp_ID)

This measure counts either leaving date is blank means he is still employed or leaving date is higher than the selected period which means that employee was employed during the selected period.

[Newbie777]

Thanks for your time in answering my question.  I will following your instructions.  However, I have a feeling that linking hiring date with the calendar table will create an issue because I have not only hiring date but also leaving date in the employee table which should be treated as a date in the table. As I am new to this system and I don't know what flag is, but will study the method above. Thanks a lot. 

[Kushal_Chawda]

@Newbie777  Although you have different Dates in table it is always good to check with business that which date can be used as reporting period. If you want both date field as reporting period then it is better to create canonical date. You can find the article here

Once you have created common master calendar using both dates you can modify your measure according to it.

[Newbie777]

Hi 

My particular question relates to a headcount measure so there is not ambiguity regarding which date to use from my point of view.  I was able to do the tenure calculation respecting time dimension correctly without associating the dates to a calendar table, and I still think that associating hiring dates in the employee database to a calendar table will not solve the problem of headcount calculation, as it should not be filtered according to the calendar table for it to work when leaving dates are left unassociated with a calendar table.  

[Kushal_Chawda]

@Newbie777  How you are calculating tenure? 

[Newbie777]

Here is my tenure calculation which works mostly except for the fact that it shows the latest tenure number of days before leaving the company for the time period after employees left the company.  It works up to the date of their leaving and keeps showing the same figure even though it should be zero after they left because they are no longer part of the cumulative tenure of the employees of the company.  

[Kushal_Chawda]

@Newbie777  The tenure calculation is bit different then what you are asking. The sample which you shared, could you please tell me if you select particular period, let's say year or month, what could be the headcount you are looking for ne example, as to why you are counting it.?