Employee Headcount

[#1]

I am trying to do an employee headcount based on a hire date and inactive date

This is the formula I am using. Note: Active Date is just today() * 2 to put it into the future.:

sum(if(CalendarMonthEnd>=[Hire Date] and CalendarMonthEnd <if(isnull([Inactive Date]),[Active Date],[Inactive Date]),1))

My dimension is CalendarMonthEnd.

My issue is that it is summing it for every day in the month. So Month ending 10/31/2011 for 1 employee should return 1. It currently returns 31. How can I go abount doing this? Would Aggr work?

[#2]

by look of it... i guess using avg or count can solve ur issue

[CELAMBARASAN · Accepted solution]

Hi,

     Try this

     sum(Aggr(if(CalendarMonthEnd>=[Hire Date] and CalendarMonthEnd <if(isnull([Inactive Date]),[Active Date],[Inactive Date]),1),EmployeeField))

Celambarasan

[#4]

This was correct, I tried that just before you posted and it returned the right number.

On another note, this is a pivot with the month end along the top. Any idea how I can change just the total field to show the average month head count?

Nevermind, found the secondarydimensionality ( ) function