Active employee calculation

aykcos

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

 

TimothyThomas

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

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

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

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