KATER__SAME_PERIOD_LAST_YEAR

Family: Calendar comparisons

Syntax

1
KATER__SAME_PERIOD_LAST_YEAR(measure [, by => date] [, 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 axis is a date at a grain of day, month or quarter.
• On a day axis, a day with no match a year earlier, such as 29 February, gives NULL.
• 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. As a MEASURE, it needs by.

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
    'same_period_last_year__sum__quantity',
6
    KATER__SAME_PERIOD_LAST_YEAR(orders.sum__quantity, by => KATER__MONTH(orders.order_date))
7
  ) AS "Units Year 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_WEEKDAY_LAST_WEEK, KATER__PERIOD_SHIFT, KATER__PERCENT_CHANGE