Count of Employees by month

[hopkinsc]

Hi All,

I am trying to count employees by month but unsure on how to do this.

My fields are

Employee Id

Start Date

End Date

Month

If an employee starts in Jan and finishes in April, i would want this employee counted from Jan-April but not after

So eg

Employee ID	Employee Name	Start Date	End Date
1	Person A	01/01/2014
2	Person B	15/01/2015	21/05/2014
3	Person C	01/02/2014
4	Person D	01/06/2014
5	Person E	01/07/2014
6	Person F	15/07/2014
7	Person G	15/07/2014	01/12/2014
8	Person H	01/10/2014	01/12/2014
9	Person I	01/11/2014
10	Person J	15/12/2014

So in these examples, my counts would be..

Jan - 2

Feb - 3

Mar - 3

Apr - 3

May - 3

Jun - 3 (Person B left in May)

Jul - 6

Aug - 6

Sep - 6

Oct - 7

Nov - 8

Dec - 9

Does that make sense?

I have attached an example but unsure if i am going down the right path.

[Anonymous]

i think this works with 2 small modifications...

1. in your excel table, i think the second row start date is supposed to be 1/15/2014 and that 2015 is just a typo...correct?

2. in your load statement for Employee table, replace [End Date] with the following:

if(len([End Date]) < 2, Date($(vMaxDate)), [End Date]) as [End Date]

the reason i think you were not getting the correct results is because when the end date is null, the calendar field "Date" was not being assigned to the employee.

Optional change: in your Calendar table load statement, replace Month(Date) as Month to MonthName(Date) as Month

Now...the table you need is a straight table with Month as dimension and count(distinct [Employee ID] as expression. suppress values when null in dimensions tab checked.

see attached!

[fred_s]

Hi Hopkinsc,

See attachment.

Person B is out because your startdate is 2015.

Grtz Fred