OVER function not working correctly inside IF function

[Luke George 2]

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

[Gaia Paolini]

Hi

With the IF you are restricting the calculation to when rowid>5, so you cannot see the previous 2-3 rows during the calculation. The easiest thing would beto create some kind of mask e.g.

[mask]=If([RowId]

[Luke George 2]

The 'IF rowid>5' will restrict the function from calulating a result for when rowid<=5, but are you saying that even the OVER function will not "see" any rows above rowid=5 and therefore the function returns 0 That seems counterintuative to me. 

 

I want to try and produce the calculated column withou using CalcCol1 or CalcCol2, and I also want to try and avoid using any other intermediate calculated columns in this instance because my actual case requires me to nest a number if IF functions.

[Gaia Paolini]

You can create and multiply by the mask function directly in the expression this way:

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

there is no intermediate column here.

Gaia