KATER__PERIOD_SHIFT

Family: Calendar comparisons

Syntax

1
KATER__PERIOD_SHIFT(measure, by => date, unit => unit, offset => integer [, coverage => 'matching_elapsed' | 'full_period'])

Arguments

Required

Optional

Returns

A number. The field menu writes the @format of the field it reads beside it, or @format number(2) where that field has none and can have decimals.

Usage notes

• It works the measure out again over the source rows of the earlier period. It does not read an earlier row of the result, so a period with no row still has a value.
• The unit fits the axis’s grain. A day axis takes any unit, a week axis takes week, a month axis takes month, quarter or year, a quarter axis takes quarter or year, and a year axis takes year.
• A date filter on the axis needs fixed dates at both ends. A moving end, such as CURRENT_DATE, is refused.
• For the change, subtract the call from EVAL(measure). For the percent change, give EVAL(measure) and the call to KATER__PERCENT_CHANGE.
• In a query, declare it with CUSTOM_CALCULATION.

Example

This Sandbox query reads the sample shop’s orders. The table shows the rows that Kater gave.

1
SELECT
2
  KATER__MONTH(orders.order_date) AS "Order Month",
3
  EVAL(orders.sum__quantity) AS "Units",
4
  CUSTOM_CALCULATION(
5
    'period_shift__sum__quantity',
6
    KATER__PERIOD_SHIFT(orders.sum__quantity, by => KATER__MONTH(orders.order_date), unit => 'month', offset => -3)
7
  ) AS "Units 3 Months Before"
8
FROM kater_dataset.orders AS orders
9
WHERE
10
  KATER__IS_BETWEEN(orders.order_date, DATE '2026-05-01', DATE '2026-09-30')
11
GROUP BY
12
  KATER__MONTH(orders.order_date)
13
ORDER BY
14
  1

See also

• Period comparisons
• Other functions in Calendar comparisons: KATER__PREVIOUS_PERIOD, KATER__SAME_PERIOD_LAST_YEAR, KATER__SAME_WEEKDAY_LAST_WEEK, KATER__PERCENT_CHANGE