How to count the number of employees in different months based on the start and end times

  • 31 August 2023
  • 1 reply

Userlevel 2
Badge +7

I currently have an Employee Dimension, consisting of four columns:

Employee IDOfficeContract Start DateContract End Date

I would like to use this to calculate the number of employees in each month, grouped by different offices, as shown in the following figure.


The pseudocode would be something like: 

Count(Employee.EmployeeID, Date.Month > Employee.ContractStartDate && Date.Month < Employee.ContractEndDate)

Do you know how to implement this calculation?

Thank you in advance!


Best answer by Nathan 31 August 2023, 14:15

View original

1 reply

Userlevel 6
Badge +12



In Pigment, you can use the PRORATA() function to do this calculation.


Then you can aggregate the result to the offices using BY modifier.


This is covered in the base training in the academy, I encourage you to follow it.