Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Contributor III

## Calculate Count of Monthly active employees

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
Labels (5)

• ### Variables

2 Solutions

Accepted Solutions
Partner - Specialist

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:
[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,
134, HR, 6/3/2024,
135, Finance, 4/1/2020,
]
;

Employees:
[Emp_ID],
[Department],
[Hiring Date],
[Leaving Date],
[CurrentHiredFlag],
RESIDENT
TempEmployees
WHILE
;

DROP TABLE TempEmployees;```

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

Regards,

Mark Costa

Read more at Data Voyagers - datavoyagers.net
MVP

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.

6 Replies
Specialist

@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

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!
Partner - Specialist

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:
[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,
134, HR, 6/3/2024,
135, Finance, 4/1/2020,
]
;

Employees:
[Emp_ID],
[Department],
[Hiring Date],
[Leaving Date],
[CurrentHiredFlag],
RESIDENT
TempEmployees
WHILE
;

DROP TABLE TempEmployees;```

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

Regards,

Mark Costa

Read more at Data Voyagers - datavoyagers.net
MVP

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.

Contributor III
Author

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

Contributor III
Author

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

MVP