# 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 |