Hello,
I've researched this topic but haven't been able to find a clear answer. I'm looking to create a headcount dashboard that tracks historical headcount by function over time (by month). It looks as though I'll need to combine an "IF THEN" and a "SUM" function. I am referencing a module that has:
Employee Department
Start Date
End Date
Transfer In/Out Dates
I am seeking to simply create a grid with Departments as Rows and months as Columns with the number of employees active as of the end of the month in each department. This would be a straightforward Countifs within Excel, but I am struggling to create this logic within Anaplan. Does anyone have any advice or similar experiences? Thanks so much!
Solved! Go to Solution.
Hi,
Just to piggyback on Anirudh: I'll usually label the module containing the Start/End dates as Employee Setup or Staffing Management, and then have a companion module (with months) where, based on the Employee Setup information, we calculate our monthly Headcount & Compensation amounts.
Anirudh's formula is a good introductary example for calculating month end headcount, however, Start & End Dates may be blank, so the formula probably needs to look at that.
I usually do something like:
If isblank(Join Date) then 0 Else if PERIOD(Join Date) <= ITEM(Time) AND (PERIOD(Leave Date) >= ITEM(Time) or Isblank(Leave Date)) THEN 1 ELSE 0
Note:
Cheers!
Paul