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:
Solved! Go to Solution.
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.
Original post edited to include blueprint.
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.
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.
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.
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