Highlighted
Occasional Contributor

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.

 

image.png

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:

image.png

 

6 REPLIES 6
Highlighted
Contributor

Re: Circular Reference when using a line item as input

Please share the Blueprint view of module
Highlighted
Occasional Contributor

Re: Circular Reference when using a line item as input

Original post edited to include blueprint.

Highlighted
Occasional Contributor

Re: Circular Reference when using a line item as input

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.

 

 

Highlighted
Contributor

Re: Circular Reference when using a line item as input

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.

    

Highlighted
Occasional Contributor

Re: Circular Reference when using a line item as input

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.

Highlighted
Community Boss

Re: Circular Reference when using a line item as input

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