Jump to content
We've recently updated our Privacy Statement, available here. ×

OVER function not working correctly inside IF function


Luke George 2

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...