Anaplan Offset Formula is looking for value outside timeranges

Question

Hi,

I'm using the OFFSET formula, where a calculated and number-formatted field determines how many previous weeks the formula should look back. Based on this value, it retrieves the corresponding week's data into the output line item.

The formula works correctly for some products; however, it fails for others. The issue occurs because, for certain products, the calculated offset points to a week that falls outside the available time range. As a result, the previous week line item attempts to reference a period that does not exist within the model's time settings, causing the formula to fail.

Thanks in Advance

raunak.singhai · Answer

Create a Boolean validation line item, such as Valid Offset?, to check whether the calculated offset is within the permitted week range, then control the calculation:

IF Valid Offset? THEN OFFSET(Data, -Offset Weeks, 0) ELSE 0

Keeping the validation in an intermediate line item makes the boundary logic easier to audit and troubleshoot.

priyanka1029 · Answer

Thanks,

I'm not using Strict.

can i use validation restrict logic in Offset formula or intermediate lineitem

SIVAPRASADPERAM · Answer

The OFFSET formula fails because some products have an offset value that goes beyond the model’s available weekly periods. Add a validation to restrict the offset to the available time range, or use a safe alternative such as LAG with appropriate boundary handling.