Formula for First week of Timescale.


Hi Everyone,


Could someone please explain this formula in layman's language.


OFFSET(1, -1, 0) = 0




Best Answer

  • M.Kierepka
    Answer ✓

    Hi @zaibhamid81,

    You can check the documentation of OFFSET(WHAT, SHIFT, DEFAULT) function with examples here:

    Now to explain your exact use:
    First argument of OFFSET (which I would axplain as "WHAT", in this example it can be any number different than zero) tells Anaplan to give you value "1", but (because of second argument of function = -1, which you can imagine as "SHIFT") as if it was in previous period. So Anaplan tries it and does yield 1 for every period, but not the first one. Why not the first one? Because for the first one there is no "previous" period - so Anaplan uses third argument (which you can explain as "DEFAULT"). So OFFSET(1, -1, 0) will return 0 for first period, but 1 for every next - That's why you then compare it with "0", so you have TRUE for first period only, and FALSE for every other.

    FYI: almost all parts can be changed in this formula and it will behave in the same way, as long as Y is not equal to X: OFFSET(Y,-1,X)=X
    Now you should see that format of X and Y doesn't matter, so you can even simplify the formula to "OFFSET(FALSE, -1, TRUE)"