Period over period

You want each month’s web revenue next to the previous month’s, or the growth between them, without writing a self-join. The temporalize decorator on a measure asks the planner for the same measure shifted by one period. It needs a date projection truncated to the matching grain in the same request, because the shift is expressed on that date. Reference: Projections and decorators.

Transforms

JSON transformshorthandpair with
year_over_yearyoy(x), yoy_pct(x)year(date)
quarter_over_quarterqoq(x), qoq_pct(x)quarter(date)
month_over_monthmom(x), mom_pct(x)month(date)
week_over_weekwow(x), wow_pct(x)week(date)
day_over_daydod(x), dod_pct(x)day(date)

"percent_change": true (the _pct forms) returns the relative change instead of the prior-period value. The derived alias is LM(Web Net Paid), LY(…), LQ(…), LW(…) or D/D(…), prefixed with % when percent_change is set. Set your own alias to override it.

Month over month with a measure filter

Previous month’s web revenue per month, for three categories, keeping only months above 100.

{"spec": {
  "projections": [
    {"field": "date", "order_by": "asc", "decorators": [{"type": "truncate", "grain": "month"}]},
    {"field": "ws_net_paid", "decorators": [{"type": "temporalize", "transform": "month_over_month"}]}
  ],
  "filters": [
    {"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""},
    {"field": "ws_net_paid", "predicate": "greater_than", "value": "100"}
  ]
}}
zsql sql --expr "month(date) asc, mom(web net paid), category in (men, children), web net paid > 100"
WITH ag47a9128558e109f9cf0b93c01992e0e7 AS (
SELECT
	(DATE_TRUNC('month', T1."d_date")::DATE + '1 month'::interval) AS "dim5b35e57",
	sum(T0."ws_net_paid") AS "__msrlm_51898eff8"
FROM
	web_sales T0
	JOIN date_dim T1
		ON T0.ws_sold_date_sk = T1.d_date_sk
	JOIN item T2
		ON T0.ws_item_sk = T2.i_item_sk
WHERE
	LOWER(T2."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
	(DATE_TRUNC('month', T1."d_date")::DATE + '1 month'::interval)
HAVING
	sum(T0."ws_net_paid") > 100
)
SELECT
	A0.dim5b35e57 AS "Month(Date)",
	A0.__msrlm_51898eff8 AS "LM(Web Net Paid)"
FROM
	ag47a9128558e109f9cf0b93c01992e0e7 A0
ORDER BY
	A0.dim5b35e57 asc

Why the SQL looks like this

  • A shifted-date aggregation CTE. ag47a9… groups web revenue by DATE_TRUNC('month', d_date) + '1 month'. The sum for January is stored under February’s key, so when the final SELECT reads the row for February it gets January’s value: that is LM(Web Net Paid).
  • The date projection supplies the grain. The truncate on date is what the shift is applied to. Without a month-truncated date projection there is nothing to shift by.
  • HAVING stays inside the CTE. The measure filter ws_net_paid > 100 is a filter on an aggregate, so it is HAVING sum(…) > 100 in the node that groups, which is the shifted CTE. The dimension filter on category is a WHERE in the same node. See Filters and top-n for the placement rule.
  • Display-only transform. Only the shifted measure is projected, so the final SELECT reads the CTE directly and orders by the month. Project the plain ws_net_paid too and the planner also needs the unshifted aggregation; the two are joined on the month key so current and prior sit on one row.

Variations

  • Year over year, as a percentage. zsql sql --expr "yoy_pct(net paid), year(date)", or in JSON {"field": "net_paid", "decorators": [{"type": "temporalize", "transform": "year_over_year", "percent_change": true}]} with {"field": "date", "decorators": [{"type": "truncate", "grain": "year"}]}. The shifted CTE adds one year instead of one month, and the final SELECT computes the change relative to the prior value, and the derived alias gets a % prefix.
  • Prior and current together. Projections month(date), web net paid, mom(web net paid) give three columns per month. In the shorthand: zsql sql --expr "month(date) asc, web net paid, mom(web net paid)".
  • Keep the filter off the comparison. Move web net paid > 100 out of filters and apply the threshold in your own code if you want the prior-month column to include months under 100. The HAVING applies to the shifted node, so it drops prior months by their own value, not by the current month’s.

Next steps