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.
Answers
-
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:
Tues 1 Weds 1 Thurs 1 Fri 1 Sat 1 Sun 2 Mon 3 Tues 1 Weds 1 etc. 0
