Headcount measure using disassociated calendar table

[Newbie777]

Hello,

I am trying to create headcount measure using the employee table and with the information of hiring date and leaving date and the calendar table which are disassociated with each other to enable dynamic calculation based on the different points in time dimension.  I used the following measure hoping to see the HC to be calculated flexibly with respect to different points in time dimension, but the outcome of the calculation is zero.  I would appreciate it if anyone can point me in the right direction where I am going wrong with this measure.  

Thanks a lot for your help.

[Kushal_Chawda]

@Newbie777  what is the expected output you need?

[Newbie777]

Thanks for your response.  I'd like to create a dashboard like below, but at the moment, I am focusing on the table headcount table at the bottom left.  I'd like the headcount to be calculated for all the selected years and months in the table flexibly using calendar table as the time dimension.  Once I get the measure expression to be working, I guess I can flexibly use it to apply to any visualization template in Qlik Sense, so I am not fixated with that particular table format though.  I just want the headcount measure to be working which calculates the headcount at any selected date using after hiring date and before leaving date if expression.   

[Newbie777]

Hi Kushal_Chawda,

I'd greatly appreciate if you could let me know where I am going wrong with the formula to get zero result.  FYI the calendar table might be mismatching with the date range in the sample data, but in my real data too, even though this is not happening, the formula doesn't work.  

Thanks for your help in advance. 

[Newbie777]

Hello Experts,

Please let me know how to fix the measure expression in my post above to get it working.  I haven't been able to resolve this issue yet.  Thanks a lot for your time in helping me solve this puzzle.  

[Kushal_Chawda]

@Newbie777  what is your logic to get the headcount?

[Newbie777]

Hi Kushal_Chawda,

My logic to get the headcount is that at any point in time headcount is the count of number of employees from the employee database who were hired before that selected point in time, and who have not left the company at that selected point in time.  By this way, we can get the number of headcount at any point in time in historical records if we maintain employee database properly.  I sometimes see headcount example with active (who are employed at one particular point in time) and not active (who have left at that time), but this kind of counting only is able to count employee at a particular point in time, and this is not what I want. I'd like my measure to be able to deal with the headcount counting at any selected point in time from the employee database which contains the information of hiring and leaving dates. 

Therefore, I use disassociated calendar table and greater than equal to (>=) hiring date and less than or equal to leaving date.  Please let me know if you need any further clarification.  I am keen to resolve this issue, but I am just a beginner Qlik Sense user and I don't know how to do this in Qlik Sense, which I am able to in Excel Power Pivot formula...