Avoiding a Circular Reference when pulling the previous day from the same LI

neg177
Super Contributor

Avoiding a Circular Reference when pulling the previous day from the same LI

Hi, 

I'm trying to find a way to pull for the previous day, while skipping over weekends and holiday's, for the same LI. Below is an example of the formula I'm trying to do, but I get a circular reference. This formula works if the LI is from a different module, but I need to be able to pull the previous day for the same LI using a formula like this. 

neg177_0-1657907852470.png

 

1 REPLY 1
ryan_kohn
Certified Master Anaplanner

It sounds like you could solve this using the LAG function with the STRICT keyword. Using the STRICT keyword tells the engine that you will never be referring to the current cell, so it can guarantee that there will not be a circular reference and thus allow you to use the formula on the same line item.

 

You will need to calculate how many periods back you want for each value. I would recommend storing that result in a separate line item.

 

So, for your offset values, you likely end up with something like this:

Tues1
Weds1
Thurs1
Fri1
Sat1
Sun2
Mon3
Tues1
Weds1
etc.