Active employee calculation

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

Accepted answers

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

All comments

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

TimothyThomas

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

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

Quick Links

Can you help?

Unanswered questions

Best way to Architect Financial Statements in Anaplan for Scalability
I have seen two methods used to build financial statements and I am wondering what others have experienced in scalability/maintenance between the two or if there is a better approach. Assume that we have a flat data source for our general ledger within Anaplan. Our line item we are focused on within the source data is the…
Curious Comparison: Forecast Modeling for Unusual Products
I've been experimenting with some forecast modeling techniques in Anaplan recently and decided to test them on a few quirky, offbeat product categories just for fun. One of the categories I played around with was love dolls—yes, you read that right. It actually turned out to be more interesting than expected. Unlike…
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled