Highlighted
Certified Master Anaplanner

Circular Reference with OFFSET function

I'm getting a circular reference while using the OFFSET function, but I don't understand why.

 

(1) IF offset_value = -12 THEN OFFSET(Gross Revenue, -12, 0) ELSE OFFSET(Gross Revenue, -11, 0)

vs.

(2) OFFSET(Gross Revenue, offset_value, 0)

 

Dimension is Months and 'offset_value' always equals either -12 or -11 based on a formula. Why does (1) work but (2) is a circular reference?

 

Sample below:

Offset - Circular Reference.PNG

2 REPLIES 2
Highlighted
Master Anaplanner/Community Boss

Re: Circular Reference with OFFSET function

You could set the offset value to cause PY Gross to look at itself even though you know it can never be. Theoretically it could at some point and the system wont allow a user to create a circular reference. With a constant in the formula that can never happen.
Hope that helps.
Highlighted
Contributor

Re: Circular Reference with OFFSET function

Ryan,


Feel free to use a strict lag/lead in order to avoid having to use the if statement as a solution. 

 

Lag Function

 

Strict.jpg