Active employee calculation

aykcos
aykcos
in Modeling

Hi Anaplan community,

 

I am facing a problem and need help. I created a line item (Active?) to define whether the employee is active or not. The formula that I used is:

 

IF PERIOD('DAT04 Employee Data'.Leave Date) < ITEM(Time) THEN FALSE ELSE IF PERIOD('DAT04 Employee Data'.Start Date) <= ITEM(Time) THEN TRUE ELSE FALSE

 

I get no error message but in some cells, I should see checked booleans. But none of the booleans are checked. The formula and the module that holds the data can be seen in the following screenshots.

 

Any help is appreciated.

Thanks in advance,

 

Aykut

 

1.PNG2.PNG

Tagged:

Best Answers

  • TimothyThomas
    TimothyThomas
    Answer ✓

    Sorry, forgot end parentheses. Should read as: IF 'DAT04 Employee Data'.Leave Date < 'SYS00 Time Settings'.Last Day of Month THEN FALSE ELSE IF 'DAT04 Employee Data'.Start Date <= TIMESUM('SYS00 Time Settings'.Current Period Start) THEN TRUE ELSE FALSE

  • TimothyThomas
    TimothyThomas
    Answer ✓

    Hi @aykcos ,

     

    No problem, glad that helped! Sure, to do that I would create a simple line item in your same module you have there that is: IF Active? THEN 1 ELSE 0.

     

    From there, it'll depend on the dimensions of the reporting module you're pulling into. For example, if the reporting module is location based, you could use a SUM based on the employee's region.

     

    Let me know if additional help is needed!

    Thanks,

    Timmy

Answers

  • TimothyThomas
    TimothyThomas

    Hi @aykcos ,

     

    I think the issue here is that the model is trying to compare the start date to the native timescale of the model, but in this specific case of Keith Nicholson, his start date is prior to the first period of the model itself.

     

    To get around this, I would create a Time Settings module with Time as the only dimension. Create a line item called Current Period Start, formatted as a date with the formula CURENTPERIODSTART(). Create another line item called Last Day of Month, formatted as a date with the formula END().

     

    From there, switch your formula to: IF 'DAT04 Employee Data'.Leave Date < 'SYS00 Time Settings'.Last Day of Month THEN FALSE ELSE IF 'DAT04 Employee Data'.Start Date <= TIMESUM('SYS00 Time Settings'.Current Period Start THEN TRUE ELSE FALSE.

     

    That should take care of instances where either (or both) the employee start date or leave date are outside of the time scope of the model.

     

    Hope that helps!

    Thanks,

    Timmy

  • aykcos
    aykcos

    Thanks Timothy. I really appriceate that.

     

    Another issue that I am facing with is I am trying to create a line item or module that calculates "headcount". To do that I am trying to sum/aggregate the total amount of "Active?" line item.

     

    Do you have any suggestion? Any formula?

     

    Thanks in advance.

    Aykut 

Categories