Hi, I am in need of a solution to this dilemma and being new to Anaplan I can't think of way to write the formulas. The goal is to be able to pro-rate actual days work from days on leave. The PRO rate module has emp ID list and month time scale as the dimension and a whole host of line items to calculate start and end. And the Leave module that is used to contain all of the leave of absence records for each employee, this module does not contain a timescale. The Leave module gets populated by an external file. It contains the emp ID, first day leave, estimated last day, actual last day, a correction date.
The consultant group that help us built this, created some "time" line item by using Period/Day to get the months, count the number of days for etc..
Unfortunately, the data coming in is not as straight forward. ie an employee can have multiple records of leave in a month or leave that transcend multiple months.
Leave record could be:
Start 1/8/2021
Est. End 1/20/2021
Actual end: 6/10/2021
Start 1/22/2021
Est. end 3/10/2021
Actual end: 6/10/2021
Start 3/20/2021
Est End 6/10/2021
Actual end: 6/10/2021
Some records dont have an Actual End date.
As you can see the 3rd record doesnt show April and May as being the start and end month. I created a line item that takes the difference from the end date to the start date for each record. What I am thinking of doing is having a running sum for each emp days on leave.
Take that number bring to the PRO Rate module for each emp by month and take the difference for that month.
ie if the emp has a total of 100 days on leave, the PRO Rate module would that the # of days in a given month and take the difference from that 100 but the max it could take is based on the # of days in that month. If its Feb then the max is 28 days. The remainder will move forward to the next month until all the days of leave are gone. What I am stuck on is the running total and how to bring the balance forward.
