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?

Best Answer

  • aaron_overfors
    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.