Running total, No Timescale

Options
ntrddragn
ntrddragn Member, ALL USERS Posts: 5 New Contributor
in Planning & Modeling

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. 

 

Tagged:

Comments

  • ntrddragn
    ntrddragn Member, ALL USERS Posts: 5 New Contributor

    Hi @anikdas thanks for the response. I can only see 2 screenshot. Can you repost the blue print? Thanks

  • ntrddragn
    ntrddragn Member, ALL USERS Posts: 5 New Contributor

    Hi @anikdas , why is your Start() and End () retuning 1/3/2021 and 2/6/2021 instead of 1/1/2021 and 1/31/2021? 

    Also, in my company, emp 1 would have multiple records ie. 2/10/221-4/18/2021, 4/19/2021-6/19/2021 and 6/22/2021- 3/31/2022. 

     

    I tried to replicate your logic with my data but I am show 0 Leave Days for 6/22/2021 - 2/28/2022

Categories