Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi,
Try this
sum(Aggr(if(CalendarMonthEnd>=[Hire Date] and CalendarMonthEnd <if(isnull([Inactive Date]),[Active Date],[Inactive Date]),1),EmployeeField))
Celambarasan
by look of it... i guess using avg or count can solve ur issue
Hi,
Try this
sum(Aggr(if(CalendarMonthEnd>=[Hire Date] and CalendarMonthEnd <if(isnull([Inactive Date]),[Active Date],[Inactive Date]),1),EmployeeField))
Celambarasan
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