How to avoid Circular Reference

ahowe2

I am looking for a solution on how to write a formula that takes the value for 1 and Jan 21 and adds that value to the answer to the formula in cell 2 and Jan 21. 

 

The formula I have currently is the below. What is in bold is what I need to figure out. 

IF Financial Projection Time = Financial Projection Time.'1' THEN Sales Premium * Sales to Inforce Timing ELSE Sales Premium * Sales to Inforce Timing + the previous row value in that column

 

The 1-4 is representing a list of values from 1-256. They represent calendar months while the Jan-21, Feb 21 etc represent sales month. 

 

Does anyone have any suggestions here? I get a circular reference each time I enter the line item name. I also tried to make a Previous Financial Projection time line item and enter that but I get a circular reference as well. 

 

 

 

rob_marshall

@ahowe2 

 

I got you covered and you are correct now that I see what you are needing, RankCumulate will not work.  But, that doesn't mean it can't be done.  I am not going to say this is easy, but it does work.

 

 

 

Now, how did I get there?  Buckle in because this is going to be fun...but long-winded.  Also, in the link I linked above about the block structure, the block structure is the reason why we have to do this work around.  So, at a high level, we are doing the following:

• have to use Time (at the day level) to get the previous value - due to the block structure.
• but we can't have time on both dimensions, so we have to create a "Fake Time" or Custom Time list

 

so, here we go...

The setup...

Create a list, I called it Fake Months, with all of the months in your time calendar (your model calendar).  It is best to name them the exact same as they appear as it will help the Finditem() to be much easier.

 

Create a SYS Months Property module, dimensionalized by Time (native time).  Create two line items:

• item Txt:  NAME(ITEM(Time))
• Link to Fake Months: FINDITEM(Fake Months, Item Txt)

 

Now, we need to also do the opposite, map the "fake months" to the real.  Create a SYS Fake Months with two line items:

• Item Txt: NAME(ITEM(Fake Months))
• Time List: FINDITEM(Time, Item txt)

 

 

 

Create a module SYS Filter Days which is dimensionalzed only by Time.  Now, we only need 256 days, so if your model calendar is greater than one year, I suggest creating a Time Range with only one year.  And honestly, the beginning year really doesn't matter because we are only using this for calculation purposes.

 

 

Now, the SYS Filter Days module, create a line item called Date (format of Date) with the formula START() and also change the Timescale to Day as well as change the Time Range to what you defined above (the one year time range).

 

 

Create a SYS Index properties module (this is the 1-256 dimension you have).  I called my Row Count, but please use yours.  Within this, create a line item name Days, formatted as Date.  This is where we are going to link the data.  Now, in your previous module, SYS Filter Days, copy the data in the Date line item and paste it into the Days column...This will be used for mapping.

 

 

 Now the fun part...

 

Create a CALC module, I called my CALC Circular dimensionalized by Fake Months and Time (native time).  In the blueprint, change the Timescale to Day, change the time range to One Year (the time range you defined above) and add the following line items:

• Sales Premium
• Sales to Info
• Result
• Persistency Result - although I think can be from the original module but not exactly sure what it does.  Mine is hardcoded to .9957.

The formulas go back to my original module (the first picture in this post, the module is named Circular) where I am inputting the Sales to Info.  Here are my formulas:

• Sales Premium: Circular.Sales Premium[LOOKUP: SYS Fake Months Circular.Time list, LOOKUP: 'SYS Filter Days - Circular'.Row Count List]
  This is getting the Sales Premium data from the first module, but I am having to lookup on the mapping modules that you have already created.
• Sales to Info: Circular.Sales to Info[LOOKUP: SYS Fake Months Circular.Time list, LOOKUP: 'SYS Filter Days - Circular'.Row Count List]
• Result: IF 'SYS Filter Days - Circular'.First Member? THEN Sales Premium * Sales to Info ELSE Sales Premium * Sales to Info + PREVIOUS(Result) * Persistency Result

 

 

The above is working because the time (in the rows) is a separate block of data, therefore you will not get a circular reference when you use the Previous() function.

 

Now, back to the first module, the "input" if you will.  It is dimensionalized by native time (at the month level) and your index (my row count).  I have 3 line items:

• Sales Premium: hardcoded to .47, but you can enter what you want
• Sales to Info: data input
• Result: CALC Circular.Result[LOOKUP: SYS Row Count Circular.Day, LOOKUP: 'SYS Months - Circular'.Link to Fake Months]

 

 

Now, the Sales Premium and the Sales to Info can come from a different module, but I was just going off what you had.  But you can see, if I change any of the values in Sales Info, it will automatically calculate correctly.

 

Let me know if you have questions or need further guidance.

 

Rob

ryan_kohn

I believe RANKCUMULATE does what you're looking for. I mocked up an example below.

 

 

 

 

The circular reference you were experiencing is because Anaplan doesn't naturally understand the sequence of your list items without explicit guidance. This is where the RANK portion of the function and the Sequence line item come into play -- this tells Anaplan the order of your items. Note that the Sequence should be configured in a System module (you can learn more about the DISCO standard in this 6 min lesson).

 

Note that some special list types in Anaplan actually do have an ordering built in and have specific functions that can be leveraged. Time and Versions lists are the special lists that can leverage these relationships, and example functions include PREVIOUS(), NEXT(), PREVIOUSVERSION(), YEARTODATE(), etc.

 

rob_marshall

@ahowe2 

 

@ryan_kohn is correct, RankCumulate() is what you are wanting to use.  As for the reason, your list members, while on the same level, are actually stored in the same "block" of data.  Native Time and Versions are the only "lists" that store data at the most granular level (months or weeks or days) in separate blocks.  This is why previous(), next(), lag(), and others work without getting a circular reference.  More information about blocks can be found here: link

 

Rob

ahowe2

Thank you both for your quick responses. I am still running into some issues so I will provide some more detail to see if we can get this corrected. I am hoping we are close. 

 

The full formula needed is IF Financial Projection Time = Financial Projection Time.'1' THEN Result ELSE Result + the previous cell in that column * Persistency Rate Lookup

 

where Result = Sales Premium*Sales to Inforce Timing

 

For RANKCUMULATE I currently have

IF Financial Projection Time = Financial Projection Time.'1' THEN Result ELSE Result + RANKCUMULATE(Result, Convert Text to Num) * Persistency Rate Lookup

 

I think the problem is that it does not need to cumulate the result of RANK, it needs to cumulate the result of rank+ the previous cumulate value*persistency rate lookup but I get a circular reference when I do RANKCUMULATE(RANKCUMULATE, Convert Text to Num) due to using the name of the row in the formula. 

 

Is there a clean workaround for this?

 

For example) 

For line 4 it should be .47*.97 +0*.9957 = .4559

For line 5 it should be .47*0 +.4559*.9957 = .4539

 

 

ryan_kohn

Based on your further description, I'm not sure RANKCUMULATE actually accomplishes what you're looking for. With your additional example, I think I found a solution that may work.

 

I believe that you can stage the first part of your calculation result, and then carry that forward based on some logic to tell Anaplan what the "previous" Financial Projection Time is.

 

I have used some logic based on the Financial Project Time code since it is conveniently sequential. This wouldn't work if your code was non-sequential or non-numerical, but you could also just manually load in what the previous item is based on any other logic that determines that relationship. Note that you should define that relationship in a System module rather than in the main calculation module, per our DISCO standard.

 

This is what the calculation looks like now, along with the associated blueprint.

 

 

 

 

And here is the System module, along with the blueprint:

 

 

rob_marshall

@ahowe2 

 

And of course, I left out a key piece, other line items  on the SYS Filter Days module.

ahowe2

You are my hero. Thank you so much!!!

ahowe2

@rob_marshall 

 

Hi friends, 

 

It is me again. Now I need to sum each quarter's data. So for example) Jan21, Feb 21, and Mar21 Fake Months Columns need to be summed into Q1FY21 in the second module. 

 

I took the Fake Months Circular module and added the quarter mapping to each month 

And then I looked up the quarter mapping in the main calculation module as seen below 

But I cannot get the summation to work. I am thinking I need additional mapping with days due to the days time dimension but each day has multiple values going across it's row that correspond to different fake months, so I am not exactly sure. I was thinking map days to fake months to months to quarter but there is not a day that corresponds to a singular fake month as multiple fake months have values for any given day. 

 

Any suggestions?

rob_marshall

@ahowe2 

 

Can you not just sum the data from the main module (the one with the results with real months) up to quarters?

ahowe2

The module that has the calculations done has days as the column and fake months as the row. We took data from the module that was dimensionalized by the financial projection time list (1,2,3,4,..) and time in months and put it in the calc module to avoid the circular reference. Do I need to make additional line items in the original module to bring the calculations back in? And map fake months to real months there? 

rob_marshall

@ahowe2 

 

I will DM you, check your Community email.

 

Rob

medaganeshamanikanta

Hi @rob_marshall 
Is it possible to share full screenshot of "SYS Filter Days- Circular" Module.
I could able to see only Date line item. But in the formulas you have used some more line items like

First Member?, Row Count List.

 

ahowe2

@medaganeshamanikanta Here is the full module. It is embedded in the middle of Rob and I's string. 

rob_marshall

@medaganeshamanikanta 

 

Yes, I had forgotten that, look at the last post which has what I think you are looking for.