Count sum of employee per month

[borndy2904]

Hi everyone!

I have fields,

a. Date (from table master calendar)

b. start_date (from table employee)

c. resign_date (from table employee)

d. name (from table employee)

I want to create a measure to be added in the chart that can count sum of employee that still exist (exist mean : start_date<=Date and resign_date>=Date). And I want it to be able to calculate sum of employee per month in each year.

Can any of you help me? Thank you

[agigliotti]

Hi @borndy2904 ,

Which are your chart dimensions?

[Anil_Babu_Samineni]

Perhaps this

Sum(Aggr(Count(TOTAL <Month> {<Date={"=Date>=start_date and Date<=resign_date"}>} Employee), Year))

[borndy2904]

Hi @agigliotti ,

The chart dimension is month from field Date.

[borndy2904]

Hi @Anil_Babu_Samineni,

Thank you for your answer.

I've tried the expression you gave and there are no errors with the expression, but the output that appears is not as expected. The output that appears is '0' in every month.

[Anil_Babu_Samineni]

We are here just to give hints on your data model not for final solution 🙂 anyway, can we have some data where you are saying that it is not working?

[agigliotti]

maybe this:

=count( DISTINCT if( start_date<=Min(Date) and resign_date>=Max(Date), EmployeeID ))

I hope it can help.
Best Regards
Andrea