A complex If() statement that uses OVER with Previous() or Next() is not working as expected, but individual components work.

A complex If() statement that uses OVER with Previous() or Next() is not working as expected, but individual components work.

A complex If() statement that uses OVER with Previous(), Next(), or other similar multiple-row functions is not working as expected, but individual components work.

The following is the "Insert Calculated Column" dialog's brief thumbnail description for the If() function:

-------------------------------- 
Returns Arg2 if Arg1=true, and Arg3 if Arg1=false.

Example: 
If(1 < 2, "Small", "Big")
--------------------------------


As designed, the first argument of an If() statement (Arg1) generates two data subsets: one for which the first argument evaluates to TRUE, and another for which it evaluates to FALSE.


The expression in the second argument of the If() statement (Arg2) uses only the rows for which the first argument (Arg1) evaluates to TRUE.

The expression in the third argument of the If() statement (Arg3) uses only the rows for which the first argument (Arg1) evaluates to FALSE.

Rows for which the If() statement's first argument (Arg1) evaluates to NULL (i.e., empty) are not used as input for either the second argument (Arg2) or the third argument (Arg3).


This means that an OVER structure, Previous() or Next() implemented in the If() statement's second argument (Arg2) or third argument (Arg3) will not use all of the rows in the data table.


If you want an OVER structure, Previous() or Next() to use all of the rows in the data table, it needs to be implemented in a separate calculated column, which can then be used as input in the second argument or third argument in the call to If().