Running total, No Timescale

ntrddragn

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. 

 

anikdas

Hi @ntrddragn ,

 

First of all, thanks for an interesting problem statement. Your thinking was in the right direction, however we can achieve it by using few IF statements.

 

I am attaching the screenshots and blueprints, hopefully should be  self explanatory.

Input:

Calc:

 

ntrddragn

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

anikdas

Hi @ntrddragn 

Blueprints are to the right hand side of the previous post.

ntrddragn

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

anikdas

@ntrddragn 

My model timescale is not set up as a monthly model, but is a 4-4-5 weekly model. I showed the start and end to show that it's not 1st and last day of month for me.

This should work for multiple records as well, as the calc module is dimensionalized by the records and not by employees.

The formulas for start? and end? needs to be modified if it spans across years. But it should not return 0.

Start? --> MONTH(Leave Record Details.Start Date) = MONTH(START()) AND YEAR(Leave Record Details.Start Date) = YEAR(START())
End? --> MONTH(Leave Record Details.Final End Date) = MONTH(START()) AND YEAR(Leave Record Details.Final End Date) = YEAR(START())

 

Here is what I am seeing: