Calculate headcount by time

edited December 2022 in Modeling

I'm having issues counting the current headcount by month. I have employee hire date, leave date, and projected leave date.


I've attached snap shots of my modules. I haven't figured out how to include "projected leave date" into my formula yet. I'm stuck on trying to get the current employees based on historical leave date correct first.


Thanks in advance for the guidance.

Best Answer

  • rob_marshall
    edited December 2022 Answer ✓

    @andrewtye @BryanByrnes 


    You might want to tap those breaks...A couple of things to look at:

    • The Employee Lookup module is dimensionalized by Employee - all good...but the Headcount module is not.  This isn't bad, but you don't have sums turned on in the Employee module.  I bring this up for a couple of reasons:
      • you are not using a sum to get the data, you will need to because, at the top of the house for Employee, you will not get any data because the sums of the line items are off.
      • if you do a SUM (the aggregate function), sum on hire date
    • Also, in your formula, you are looking to see if the hire date is blank or not.  When would the hire date be blank?  I guess for future hires, but if you are doing the sum on hire date, that is baked in as it won't find it
    • In order to do the sum, you will need line items in the Employee Lookup module defining the Functions and Pay Types, are those in there?


    Hope this helps,




  • andrewtye
    edited December 2022

    Hi @BryanByrnes 

    Couple of things:

    1. Put the period into the Employee Lookup table - will make things easier on the calcs

    2. For the future leavers put into the Employee Lookup table a combined leave date of those that have left and those that will leave again makes things simpler

    It's likely to be something to do with the time periods - so worth having in the employee lookup something along the lines of if hire date <= first time period (lookup module) then first time period and that way Anaplan will know that they're live


  • Ah yes - either dimension module two by employee or do sum on the dimensions in module one.

    Probably easier to do the first one on balance as that would give the phasing of live employees.