Shape · 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.

Also known as: Growth rate, year over year

How it works

Group by restarts the comparison per group and Order by is required. Pick the Column to measure, a Method (Diff or Pct Change) and the number of Periods to look back, 1 by default and never below 1. Diff writes current - previous; Pct Change writes (current - previous) / previous as a fraction, so 0.12 means 12 percent, and gives NULL when the previous value is 0. The first rows of each group, which have nothing to compare to, are NULL. The result is appended as column_diff or column_pct_change unless you set an Output column.

Inputs and outputs

Settings

{
  column: string
  periods: number
  method: "diff"|"pct_change"
  outputColumn?: string
  orderBy: [{ column, direction }]
  groupBy?: string[]
}

Example

Month-over-month growth per store: column sales, method Pct Change, periods 1, order by month, group by store. For year-over-year on monthly rows, set periods to 12.

More in 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.
• 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.