circular reference error with no time dimension used.

Hi All,

I have a module without time dimension,I am facing circular reference error here:

Line items used are:

Total = Sum (Lineitem1 + Lineitem2 +Lineitem3)

I need previous cell value of Lineitem Total for LineItem1
ie, Lineitem1 = previous(Total)
As I am not using time dimension, I cant use PREVIOUS function here.

Regards,

Madhu

Accepted answers

All comments

parmod.kumar2

Hello Madhu,

Please share more details along with a screenshot

regards,

Parmod

JaredDolich

@MadhabikaM

This is a great question and I think I know how you can solve this.

This use case sounds a lot like one that is used for versioning.

You have two choices on this.

1. You can create a version and save your total into that version (original, we'll call it). In the next version you can refer to the total. (Use SELECT)

2. You can create a "fake" version by using a list and save the total into that.

Either way, you should be able to refer to the previous total.

andrewtye

@MadhabikaM on top of what @JaredDolich has said you could always have a separate line item called Previous Total and have that being updated via an import process instead of using a version/fake list.

But to clarify line item 1 continues to increase/decrease based on changes to line item 2 and line item 3?

Everything starts as zero... then data is inputted into the other two line items which gives a total and becomes line item 1. Do you want it to continue adjusting infinitely or something else?

MadhabikaM

Hi @JaredDolich ,

Thanks for your quick response.

But I am bit confused here, not sure if I have understood this correctly. I still get the circular reference error for Line item1 while trying to refer to Total Original.

and also how can I refer to the previous cell value of Total

i,e I need Line item1 = previous(Total) provided Time dimension Is not used in this module.

Thanks & Regards

Madhu

Jitendra.Vishnolia

Hi @MadhabikaM ,

Since you are not using time dimension in the module and want to get the prev. Total into Line item 1, I would recommend to import the prev Total into Line Item 1 each time before the new calculations.

JaredDolich

@MadhabikaM

Three options:

- Create an "original" version and a "working plan" version. The WP version will be what you edit. Then import the WP editable fields into the original when you're satisfied you have the values you want. Then in your working plan version refer to the original plan version total value using SELECT

- Create a list that mimics versions (say WP and Original) and use that dimension the same as above. You'll import the data into the same module but point it to one of the new list items you just created. Use the LOOKUP or SUM function.

- Create a line item that is editable. Import the total value into that new line item when you have it where you want. Then just refer to it normally in formula.

Let me know if you want an example of each.

CommunityMember115881

Hi Madhu,

I have another solution to offer: simply map your custom list (which ever is applied to your line items 1-3) to real time periods.

All your have to do, is create the logic against real time (using SUM on a time period to allocate the line item value to a particular time period and LOOKUP that same time period to bring it back to your original module). See below print screens for details.

Let me know if that works for you,

Cheers

1 05022020.PNG 2 05022020.PNG 3 05022020.PNG 4 05022020.PNG

MadhabikaM

Thank you @CommunityMember115881,

This above solution works perfect for certain line items, but my few other scenarios
line items having similar issues, when I try to fix them this way.

The numbers are getting calculated by completely messed up, I believe this is because the Test Module2 using Time dimension and Test Module1 having no time.
I believe that have to apply SUM: function for all other line items in the test module2, even not having the circular reference issue.
Thanks you help...:)

Regards,
Madhu

CommunityMember115881

Happy to help

MadhabikaM

Hi @CommunityMember115881,

Thanks for the below help.

I tried the below solution, but I still get the circular reference error while trying to refer the previous line item value from Test module2 to test module1.

not sure If I am missing anything here..

Thanks & Regards,

Madhu

CommunityMember115881

Hi Madhu,

Without having a proper look at it it's a bit hard to tell. In theory it should work just fine.

I wonder if it doesn't have something to do with your formula for "Interest accretion at rate b/f - UPR" as it also references the first two line items.

Maybe try replacing that one by a input only and see if it solves the circular. If so we'll have to focus on that line item.

Cheers

dmitrii.mamaev

Hello Madhu,

Try to check summary method. I often catch bugs "reference error" caused by summary.

Hope it can help.

Dmitrii.

MadhabikaM

Hi @Douglas Eaton,

You are right, the formula for 'Interest accretion at rate b/f - UPR' also refers to the first two line items.

The line item are very much dependent on each other, your provided solution works fine when I create a test module and replicate the same solution as you provided in screenshot but not sure where it is breaking in case of my module.

I believe updating the line item by creating a temporary line item to hold the value and then populating the final by a process would be the best way in such scenario.

I was just trying to avoid to trigger a manual

Thanks & Regards,

Madhu

MadhabikaM

Hi @dmitrii. mamaev ,

Thank you...I did checked the summary method, they are all set to Sum.

which should be fine I believe.

Thanks & Regards,

Madhu

CommunityMember115881

if you give me the exact formula for 'Interest accretion at rate b/f - UPR' I can have another go at it.

Did you try removing the formula and putting in my fix?

MadhabikaM

Hi @CommunityMember115881

In first screenshot LM1 values populated in blue is what I need i,e prior cell value of Final rate.

CommunityMember115881

Ok so I tested it and the problem comes from the formula in your "interest accretion" line item.
The easiest way to solve it is to do all of your calculation against real time (any time periods will do, you just need to be able to use the PREVIOUS() function), map those to your fake time periods and bring the values back. s
It should be fairly straight forward.
Hope it solves it

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…