# OVER function not working correctly inside IF function

Hi there,

I'm trying to generate some calculated columns that I need for some futher calculations. Below is an example where

CalcCol1 = Sum([RowId()]) OVER (LastPeriods(3,[RowId()]))

&

CalcCol2 = Sum([RowId()]) OVER (LastPeriods(2,[RowId()]))

When I subtract CalcCol2 from CalcCol1, I basically get the value two rows previous in the RowId() column.

I can combine this arithmetic into a single calculated column and everything still works fine, i.e.,

Sum([RowId()]) OVER (LastPeriods(3,[RowId()])) - Sum([RowId()]) OVER (LastPeriods(2,[RowId()]))

However, when I try placing the above formula inside a simple IF statement, i.e.,

If([RowId()]>5,Sum([RowId()]) OVER (LastPeriods(3,[RowId()])) - Sum([RowId()]) OVER (LastPeriods(2,[RowId()]))),

I now get two zeros showing up in rows 6 and 7 of the new column before the formula starts working correctly, as you can see in the "attempt" column below.

Why does this IF formula do this? What am I missing?

 RowId() CalcCol1 CalcCol2 CalcCol1-CalcCol2 Attempt 1 1 1 0 2 3 3 0 3 6 5 1 4 9 7 2 5 12 9 3 6 15 11 4 0 7 18 13 5 0 8 21 15 6 6 9 24 17 7 7 10 27 19 8 8