shift function

The shift function shifts values by a specified value.


shift(column, shift, group, cyclic)




Returns the value of the corresponding row of the specified column. Column is a mandatory parameter.


The number of rows downward by which the value must be shifted. Shift is a mandatory parameter.


The results can be grouped according to a specified attribute.


If True, any missing initial rows will be filled with values taken from the end of the dataset in a cyclical manner. By default, this value is set to False.

Example - shift(column, shift, group)

The following examples use the Oranges dataset.



Our dataset contains the prices of oranges on a daily basis in 3 different farmers markets.

If we create a new attribute called Yesterday’s cost, and enter the formula shift($"Cost of oranges today", 1) the cost of oranges from the previous row will be displayed.

However, the results contain the values from the previous row. but not from the previous day, as there is an entry for every day for each farmer's market, so if we need to take the results from the previous row for the same market, using the group parameter.

So we’ll instead enter the formula shift($"Cost of oranges today", 1, $"Market").

Example - shift(column, shift, group, cyclic



You’ll have noticed that there are missing values for the first rows in the dataset, as there are no previous rows to retrieve values from.

If it makes sense logically, you can use retrieve values from the final rows of the dataset in a cyclical manner, so there are no missing values.

To do this, you must set the Cyclic parameter to True - by default it is False, so if not specified, the function will not automatically fill empty initial rows.

The corresponding formula will be: shift($"Cost of oranges today", 1, $"Market), True)