Avoiding a Circular Reference

neg177
Regular Contributor

Avoiding a Circular Reference

Hi,

 

Having a problem avoiding a circular reference when it comes to this Anaplan Formula. 

 

 

neg177_0-1621553703345.pngneg177_1-1621553930039.png

The circular refence comes up in the Line Item's  "Excess Funding Sweep" when trying to make equal to the "Calculated MCA Sweep to Excess Funding", because the "Calculated MCA Sweep to Excess Funding" references the "Securitization Master Account Closing" Line Item, which is a sum that includes the "Excess Funding Sweep" Line Item. 

 

Please advise on how to avoid the circular reference, 

 

Thank you 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHeathcote
Community Boss

Re: Avoiding a Circular Reference

@neg177 

Trying reducing all references to the line item driving the circular reference to the calculation used to derive that line item. 

This should break the chain and ensure the formulas work in parallel rather than in series which is why it is causing the circular reference. 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

3 REPLIES 3
ChrisHeathcote
Community Boss

Re: Avoiding a Circular Reference

@neg177 

Trying reducing all references to the line item driving the circular reference to the calculation used to derive that line item. 

This should break the chain and ensure the formulas work in parallel rather than in series which is why it is causing the circular reference. 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

neg177
Regular Contributor

Re: Avoiding a Circular Reference

Hi, 

 

Still having a problem getting around this circular reference. The formula I'm using is below. The Opening Balance Line Item is causing the circular reference because it is being called upon twice. 

 

Formula For Line Item that is saying Circular Reference 

IF 'S01 Time Settings'.'10th of every Month?' THEN Borrowing Base Adjustment[LOOKUP: 'S01 Time Settings'.Last day of Previous Month] + Cash Collected[LOOKUP: 'S01 Time Settings'.Last day of Previous Month] + '(Cash Sweep to Wells)'[LOOKUP: 'S01 Time Settings'.Last day of Previous Month] + 'BALANCING FIGURE FOR x5000'[LOOKUP: 'S01 Time Settings'.Last day of Previous Month] + 'Securitization Master Acct (x5000 Opening)'[LOOKUP: 'S01 Time Settings'.Last day of Previous Month] ELSE 0

 

Formula that is also calling the Opening Balance 

Borrowing Base Adjustment + Cash Collected + '(Cash Sweep to Wells)' + 'BALANCING FIGURE FOR x5000' + 'Securitization Master Acct (x5000 Opening)' + Excess Funding Sweep

 

If you need more explanation I can try and be more specific. 

 

Thank you 

 

 

ChrisHeathcote
Community Boss

Re: Avoiding a Circular Reference

@neg177 

If you are trying to force a closing value based on a balancing figure then you may not be able to avoid the circular reference by continuing to reference the offending line item.

 

The only other method available to you is to use an action to effectively cut and paste the balancing figure.

Create a new line item which contains no formula. Import into that new line item the calculated balancing figure and then use this in all downstream calculations.  

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA