circular reference error with no time dimension used.

[MadhabikaM]

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

[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.

[DouglasEaton]

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

[MadhabikaM]

Thank you @DouglasEaton,

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

[DouglasEaton]

Happy to help