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

 

(2) Answers

Login