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
Answers
-
Hello Madhu,
Please share more details along with a screenshot
regards,
Parmod
0 -
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.
0 -
@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?
1 -
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
0 -
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.
0 -
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.
2 -
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
2 -
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,
Madhu0 -
Happy to help
0 -
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
0 -
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
1 -
Hello Madhu,
Try to check summary method. I often catch bugs "reference error" caused by summary.
Hope it can help.
Dmitrii.
1 -
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
0 -
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
0 -
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?
0 -
In first screenshot LM1 values populated in blue is what I need i,e prior cell value of Final rate.
0 -
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 it0
