Given a column or expression, along with a width and sort expression list, this Ordered Analytical function derives a new column for each expression giving the moving difference of the expression when the rows are sorted by the sort expression list. The moving difference is calculated as the difference between the current value and the nth previous value of the expression, where N equals the width. The moving difference is NULL if there is no Nth-preceding row in the table or group.
In Teradata V2R4.1, this function is implemented using an enhanced version of MDIFF, a non-standard Teradata specific function. MDIFF may not be mixed in the same analysis with aggregation functions such as average, and partitioning is not supported. The SQL generated takes the form MDIFF (expression, width, sort expression list). The enhancement is the ability to compute the moving difference of a date expression, generating MDIFF (expression - DATE '1900-01-01', width, sort expression list).
In Teradata V2R5.0 and later releases, an equivalent version of Moving Difference is generated using the standard ordered analytical function SUM. In this case, Partition Columns may be specified, as with other standard ordered analytical functions. Note that the non-standard expression MDIFF (expression, width, sort expression list) is the same as the following.
expression - SUM(expression) OVER (ORDER BY sort expression list ROWS BETWEEN width PRECEDING AND width PRECEDING)
When dragging a Moving Difference function into a variable, the following tree element is created.
Sort expressions can be built up in the Sort Expressions folder, and if the system is V2R5.0 or later, Partition Columns can be built up in that folder (with V2R4.1 systems, Partition Columns are ignored). Columns and/or other expressions can be moved into the (empty) branch of the tree. The Width is specified using the Properties panel. Double-click on Moving Difference, or highlight it and click Properties.
The default Width of 1 can be updated here.