Skip to main content

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!

Hi,

 

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.


Reply