MovingDifference

Returns the moving difference between current and preceding values. The position of the preceding row used in the calculation is defined in the function arguments. The calculations can be restarted based on attributes set in the function parameters. This is an OLAP function.

Syntax

MovingDifference <BreakBy, SortBy> (Argument, WindowSize)

Where:

• BreakBy is the attribute indicating where the calculation restarts.
• SortBy is the attribute or metric by which the data is sorted.
• Argument is a metric representing a list of numbers.
• WindowSize is a positive integer indicating the range of values used to compute each difference.

Expression

Where:

• yi = metric value at the ith row
• m = window size
• n = number of rows/metric values

Example

This example illustrates how the MovingDifference function subtracts the value of a preceding row from the value of the current row and returns the difference. In this case, the window size is set to 3, meaning that the value two rows previous to the current row is subtracted from the current row. In other words, there are 3 rows in the window, the current row is 3, the row immediately preceding it is 2, and the row preceding that is 1; therefore, the calculation for the moving difference of row 3 is (row 3 - row 1).

The value in the MovingDifference column represents the result of the calculation for every window of three values. The calculation is also shown.

Values	MovingDifference
400
500
700	300 (700-400)
300	-200 (300-500)
600	-100 (600-700)
800	500 (800-300)
200	-400 (200-600)