testing for a time period before the first model time period

[dan_ossmann]

I want to average quarter values for the last several years. i use (offset(lineitem,-x,0) + offset(lineitem,-x+1,0)) / 2. when the offset runs off the beginning end of my time period, i would like to do something different with the formula. How do i test for this condition when i refer to an invalid time period or date?

[aaron_overfors · Accepted solution]

Hi Dan, You can add a conditional check to the logic, that would look like this: IF offset(lineitem,-x,0) = 0 THEN Alternate calculation ELSE (offset(lineitem,-x,0) + offset(lineitem,-x+1,0)) / 2 This will perform the alternate calculation for x and prior to the start of the timescale since a line item will be read as null or zero when it does not exist on the timescale. Let me know if this achieves what you are looking for. Thanks, Aaron

[dan_ossmann]

very nice and clear. thank you for the insight. Dan Ossmann