Shape · Time series

Shift / Lag

Copies a value from N rows earlier or later onto each row, so you can compare a row with the one before it. For example, last month's sales next to this month's.

Also known as: Lag, lead, previous row

How it works

Group by restarts the shift per group. Order by is required: it is the row order the shift walks along. Each shift row names a column, a number of periods (1 by default), an optional fill and an output column. A positive number copies the value from that many rows earlier, a negative one from rows later. Rows with no earlier (or later) row get the fill value, or NULL when the fill is blank. The output is appended as column_lag_N or column_lead_N unless you name it.

Inputs and outputs

Settings

{
  shifts: [{ column: string, periods: number, fillValue?: string, outputColumn?: string }]
  orderBy: [{ column, direction }]
  groupBy?: string[]
}

Example

You have monthly sales per store and want last month next to this month. Order by month, group by store, and add a shift of sales by 1 period with output prev_sales.

More in Time series

• Diff / Pct Change — Compares each row with the one N periods earlier and writes the difference or percent change. For example, month-over-month growth per store.
• Rolling Window — Adds moving statistics over the last N rows: sum, average, standard deviation, min, max, count or median. For example, a 7-day moving average.
• Resample — Rolls a time series up into regular buckets, from minutes to years, with a total or other summary per bucket. Empty buckets can be left out, carried forward or set to zero.
• Exponential Weighted Mean — Smooths a number column so recent rows count more than older ones. Gives a weighted mean, standard deviation or variance.
• Forecast — Forecasts the next periods of a measure from a dated series, one forecast per group, with a lower and upper band. It can also backtest the recent past to show how accurate the forecast would have been.
• Sessionize — Groups events into sessions: a new session starts when the gap since the last event is longer than you set, such as 30 minutes.