Circular Reference for rollforward calc


I have a circular reference issue that I have not previously seen. The screenshot below shows a relatively simple example of a roll forward calculation where balances are carried forward. The issue I am having is that I would like the 'Out' line item to be dependent on the 'In' line item and simply take data from the 'In' line item and post the negative of it to future periods. I've hard coded the formula to post it 10 periods in the future (days in my example) but if I instead reference any line item to use as the length I get a circular reference, even if the '10' in this example is not dim'd by anything. I get a circular reference issue when using Lag, Offset, Lead, and Profile.


Any thoughts would be appreciated.





Best Answer


  • I thought I had it by using a duplicate line item but suffered the circular reference error. Then I tried using a time period line item and lookup against it. Brick Wall.

    Like you I don't understand why it works with a hard coded number but doesn't against a line item with the same hard-coded number.

    Wonder if @DavidSmith or @rob_marshall might be able shed some light on the situation?

  • Hi,
    I have overcome circular references by always taking the data from the source. Try copying the formula from the line item, 'In' directly into the, 'Out' line item.
    Alternatively, create a section in the module where you pull the source data and reference these line items in all subsequent calculations.
  • On further reflection I think your issue is with line item, 'Ending,' specifically the reference to the 'Beginning,' line item. 

    Try deleting this formula to see if this resolves the circular reference.

    If so, replace each element of the calculation with the formula from each of the respective line items.


  • David - including the STRICT mode did the trick!


    Many thanks and to others with their suggestions.

  • @DavidSmith - knocking it out of the park as always! #EverydayLearningDay

  • "it never leaves you"