Calculate Count of Monthly active employees

[Rabia_Taimur]

I have a dataset of around 4000 employees in below format. I need to calculate count of active employees at the end of each month

Emp_ID	Department	Hiring Date	Leaving Date
123	IT	8/9/2021	5/8/2023
124	IT	11/1/2021
125	Finance	1/24/2023	1/14/2024
126	Finance	5/8/2023
127	IT	4/1/2023
128	Finance	2/1/2023
129	Finance	5/30/2023
130	HR	5/18/2023	5/14/2024
131	HR	1/19/2024
132	Admin	2/27/2024
133	Admin	3/3/2024
134	HR	6/3/2024
135	Finance	4/1/2020

[MatheusC]

@Rabia_Taimur 

try something like this example:

Sum(Aggr(If(Len(Trim([Leaving Date]))>0,0,1),[Emp_ID]))

You can use Month/Year as the chart dimension, MonthName.
Then you can insert a start and end point that is already filtered into the expression. This is an example, so adjust it according to your needs and the data available in your actual table.

You can also try adding a flag to your script indicating active and inactive

If(Len(Trim([Leaving Date]))>0,Inactive,Active) as Flag


- Matheus

[marksouzacosta · Accepted solution]

Hi @Rabia_Taimur,

I think you are looking for historical analysis of the hired employees. For instance, "how many employees I had in April 2020?". If that is the case, you have to create records for the periods between Hire Date and Leaving Date. Look at the code below:

TempEmployees:
LOAD 
    [Emp_ID], 
    [Department], 
    [Hiring Date], 
    MonthEnd(Date(If(Len(Trim([Leaving Date]))=0,Today(),[Leaving Date]))) AS [EndLoop],
    Date(If(Len(Trim([Leaving Date]))=0,Null(),[Leaving Date])) AS [Leaving Date],
    If(Len(Trim([Leaving Date]))=0,1,0) AS [CurrentHiredFlag]
INLINE [
Emp_ID, Department, Hiring Date, Leaving Date
123, IT, 8/9/2021, 5/8/2023
124, IT, 11/1/2021, 
125, Finance, 1/24/2023, 1/14/2024
126, Finance, 5/8/2023, 
127, IT, 4/1/2023, 
128, Finance, 2/1/2023, 
129, Finance, 5/30/2023, 
130, HR, 5/18/2023, 5/14/2024
131, HR, 1/19/2024, 
132, Admin, 2/27/2024,
133, Admin, 3/3/2024,
134, HR, 6/3/2024,
135, Finance, 4/1/2020,
]
;

Employees:
LOAD
    [Emp_ID], 
    [Department], 
    [Hiring Date], 
    [Leaving Date],
    [CurrentHiredFlag],
	MonthName(MonthEnd(AddMonths([Hiring Date],IterNo()-1))) AS [ActivePeriod]
RESIDENT
	TempEmployees
WHILE
	AddMonths(MonthEnd([Hiring Date]),IterNo()-1) <= [EndLoop]
;


DROP TABLE TempEmployees;

So, your chart just needs a dimension - ActivePeriod - and a measure: Count(Emp_ID).

Regards,

Mark Costa

[Vegar · Accepted solution]

I think @marksouzacosta solution will solve your issue, but you can also use interval match to solve this. I usually use choose that in situations like this.

[Rabia_Taimur]

Thank you for the detailed quick response, much appreciated!!

[Rabia_Taimur]

Thank you for the help. I'll definitely explore interval match function

[MarcoWedel]

maybe helpful:

Creating Reference Dates for Intervals - Qlik Community - 1463944