Circular Reference when using a line item as input

I have a formula that needs to look a specific number of months back. The formula works when I type in the number of months as a hard code in the formula, but not when a use an line item containing the number of months.

A screenshot is below.

The Maturing Balance formula works. The Retained formula is the Maturing Balance line item x 97%. However, if I change the formula in Maturing Balance from LAG(Retained, 1 , 0) to LAG(Retained, Maturity months,0), I get a circular reference. I can't see any reason why replacing the hard code 1 with a line item containing a hard coded 1 would give a circular reference.

For this example, the module is dimensioned with a list containing only one item and no top level, and I have set all summary methods to none.

Can anyone help me out?

Edit: Blue print view below:

Accepted answers

CommunityMember131269

This has been driving me up the wall for two days, but I have solved it.

If I change the formula to LAG(Retained, Maturity months, 0, STRICT) then it works. It looks like the STRICT term tells Anaplan that there is no danger of creating a circular reference.

Leaving this here in case anyone else has this issue.

All comments

Ankitjain

Please share the Blueprint view of module

CommunityMember131269

Original post edited to include blueprint.

CommunityMember131269

This has been driving me up the wall for two days, but I have solved it.

If I change the formula to LAG(Retained, Maturity months, 0, STRICT) then it works. It looks like the STRICT term tells Anaplan that there is no danger of creating a circular reference.

Leaving this here in case anyone else has this issue.

Ankitjain

Your formula worked because STRICT parameter ignore negative & 0 as offset value and return the Fill value.

In your case when you use STRICT then your formula has to never work within particular time dimension member.

Hence, there will no Circular Reference.

CommunityMember131269

Slightly misleading of Anaplan though. The formula isn't actually circular, but potentially circular. And I am unclear why STRICT wouldn't be the default mode for the LAG function.

ArunManickam

This is excellent finding @CommunityMember131269 . Anaplan help does not describe the STRICT like how you avoided the circular reference, it may be intended for other purpose.

Kudos to solving my long standing problem. Great solution.

Thanks

Arun

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…
Exports with anaplan using informatica connector
I'm trying to execute an Action to export data with the use of a Mapping in Informatica. But it gives us null rows .If we export directly from Anaplan , the export file has all the rows filled. Both exports have same number of rows but in the informatica export the values appear as blank. This issue is with only some…