testing for a time period before the first model time period
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?
RE: testing for a time period before the first model time period
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.