Avoiding circular reference using daily workaround to simulate PREVIOUSITEM() function

cbrookes

I'm working on a tops down attrition allocation model. We want to be able to input an annual attrition number and allocate that down to the lowest level (Month, Country, Segment, Attrition Type, and Tenure). Given that attritions have to be whole numbers I've been using MOD() to carry forward the probability of an attrition. This approach works great for the most part but it's causing a circular reference in the last allocation because of the way we need the attrition forecast to interact with our tenure mix module (ie headcount with tenure as a dimension). Here's the planning units by whole curve thread where Jason Blinn outlines the MOD() approach:

We track Rep tenure with a list that increments by one from 0-19+. I used the daily workaround outline by @rob_marshall in this how to avoid circular reference thread to simulate a PREVIOUSITEM function. I'm also using the MOD() approach to allocate attrition to the tenure list, based on historical tenure attrition risk.

This all works flawlessly until I try to get it to interact with our tenure mix module (headcount by country, segment, tenure). Tenure mix forecast periods are calculated as follows:

Remaining Tenure Mix = Tenure Mix - Attrition Forecast

Tenure Mix = PREVIOUS(Remaining Tenure Mix) + Hires

I believe the model is throwing a circular reference error because the time range in the daily workaround module overlaps with the time range in the tenure mix module. I'm wondering if anyone has any clever workaround to avoid this circular reference. I'd also love to hear of other ways people have built tops down attrition models?

rob_marshall

@cbrookes

Ok, here you…in the module where you have your attrition percentages, that is my "Russ" module which will contain the final Attrition number when we are done. On the rows, will be your "tenure" list.

Create a SYS Time Days module dimensionalized by Time, at the Day level. We will use the day of the month as our tenure, in this case, 1-10, which will work until you have more than 28 tenures.

Create a module (Calc Russ). The only dimension/list I am missing from the module above is the Tenure list, but have replaced that with the Time, at the Day level. Why the Day level, because you have tenures more than 12 and we need to make sure we have encompassed all tenures. Also, I created a Time Range (One Year) so this only spans one year which will help with space and performance. Again, we are using the Time dimension to do the calcs and since each Native Time period is its own block of data, we will be able to use the Previous function.

Note, if you are using Polaris, you won't need to do that as Polaris will allow you to get the Previous/Next member of a list.

Data: Russ.data[LOOKUP: SYS Time Days.Month, LOOKUP: SYS Time Days.Tenure]
Cumulate: IF Data = 0 THEN 0 ELSE IF Data + PREVIOUS(Cumulate) >= SYS Global.CBrookes Threshold THEN Data ELSE Data + PREVIOUS(Cumulate)

Now that we have the data, we need to get it back to our "Russ" module, but we need to link the above module (dimensionalized by Day) to a module dimensionalized by Month. Before we do that, we need to create a couple of line items in our SYS Time Month module.

In your SYS Tenure (my SYS Test Upload Module) you will need to create a number representing the Tenure, we will use this later to represent the DAY.

Now, create a module dimensionalized by Time (Month) and Tenure with one line item.

Day: DATE('SYS Time - Month'.YearNum, 'SYS Time - Month'.MonthNum, SYS Test UpLoad.Number)

Back to our "Russ" module, create a line item (Attrition) that will get the Cumulate line item from the Calc module using the "mapping" module above. Remember, we can only use two formats in lookups, list formatted line items and Date/Time Periods.

Hope this helps and is clear,

Rob

rob_marshall

@cbrookes

Wow, you did your homework, but you always have to be careful following what @jasonblinn has to say. Obviously, I am kidding about Jason, just seeing if he will respond.

Without seeing your model, it is going to be hard to figure out, but there might be a chance to use a lookup for the previous member if it is stored in a SYS module.

You up for a call to discuss?

jasonblinn

@cbrookes I agree with Rob, I think this is one where without seeing it, I don't know of a great option off the top of my head. Is the circularity coming in when you reference in the Attrition Forecast into the Remaining Tenure mix Line item?

@rob_marshall Hah! If people need to be careful about my solutions, I will just tell them that I learned everything I know from you, so be careful what you wish for! 😂

rob_marshall

@jasonblinn well played, sir

cbrookes

@rob_marshall You are the man!!! Thanks for all your help over these last few days. The SYS Tenure by Month module was the missing piece that let me turn off the time summary in the daily module that was causing my circular reference 🙌.

Naturally, I'm still struggling with one piece of the calculations. I want to make sure I have a rep in a given tenure before assigning an attrition probability. I'm taking this one step at a time, so for now I just want to be able to multiply the attrition probability by 1 if there is a headcount to attrit and 0 if not. I get a circular reference when I try to bring the Tenure Mix (aka Headcount by Tenure) into the Daily tenure attrition allocation workaround module.

Attrition by month

*edited to insert correct screenshot here

Daily tenure attrition allocation workaround

Tenure Mix 1 or 0 will throw an error if I change the formula to IF Tenure Mix > 0 THEN 1 ELSE 0

SYS tenure and month to day mapping

Attrition by tenure and month

Headcount by tenure

I can't figure out what is causing this circular reference because the tenure mix forecast is referencing the previous(remaining tenure mix).

rob_marshall

@cbrookes

I could be wrong, but it could be the Previous in the ELSE statement. Previous works earlier because it is getting the previous Day/Cumulate value and I think you are using this previous statement to go up the Tenure hierarchy. Correct?

cbrookes

@rob_marshall The PREVIOUS() in the Tenure Mix Forecast formula is causing the circular reference. That formula is the crux of our whole tenure mix model though. The purpose of the formula is to carry forward headcount minus attrition and increment the tenure by 1. I did remove the IF statement and just left the part after ELSE and I'm still getting the circular reference.

Here's a simple diagram of what is going on in the tenure mix module without worrying about tenure:

I'm having trouble understanding why this whole thing works until I try to bring the Tenure Mix Forecast into the Daily Tenure allocation module. I think it must be something to do with the model thinking I'm causing a circular reference by carrying forward values in the daily module from one month to another even though that isn't really happening because we stop allocating on day 20.

I'm going to try moving all tenure mix calculations to the daily module. Will follow up in this thread once I give it a shot. Let me know if you have any other ideas 😁

cbrookes

Also, this is giving me more empathy for end users who submit incomplete requirements 😂. This is all info that would have been useful for me to share in the initial post.