testing for a time period before the first model time period

dan_ossmann
dan_ossmann
in Modeling

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?

Tagged:

Best Answer

  • aaron_overfors
    aaron_overfors
    Answer ✓
    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

Answers

Categories