Rolling time series
Trailing-7d and trailing-28d revenue measures on top of an additive base.
Use case
Per-store revenue dashboards want a smoothed view: alongside the daily
revenue_usd, show its 7-day and 28-day trailing totals to wash out
day-of-week noise and surface real trend.
Two rolling measures wrapping the existing revenue_usd in
store_performance solve this without changing how callers query.
Data modeling
Add two rolling measures to store_performance. Both wrap the additive
revenue_usd base and roll along the ordered_at time dimension:
{
"measures": {
"revenue_usd": {
"sql": "sum(o.subtotal_usd)",
"type": "number",
"additivity": "additive"
},
"revenue_usd_trailing_7d": {
"behavior": "rolling",
"type": "number",
"base_measure": "revenue_usd",
"window": "7d",
"order_by": "ordered_at"
},
"revenue_usd_trailing_28d": {
"behavior": "rolling",
"type": "number",
"base_measure": "revenue_usd",
"window": "28d",
"order_by": "ordered_at"
}
}
}The base must be additive (rolling a ratio or distinct count is meaningless and is rejected at query time).
Query
{
"contract_name": "store_performance",
"query": {
"dimensions": [
{ "name": "store_name" },
{ "name": "ordered_at", "time_grain": "day" }
],
"measures": ["revenue_usd", "revenue_usd_trailing_7d", "revenue_usd_trailing_28d"],
"filters": [
{ "field": "store_name", "operator": "eq", "value": "Downtown" },
{ "field": "ordered_at", "operator": "gte", "value": "2026-04-01" },
{ "field": "ordered_at", "operator": "lt", "value": "2026-05-01" }
],
"sorts": [{ "field": "ordered_at", "direction": "asc" }]
}
}Result
{
"data": {
"pagination": {
"items": [
{ "store_name": "Downtown", "ordered_at": "2026-04-01T00:00:00", "revenue_usd": 4210.50, "revenue_usd_trailing_7d": 29840.75, "revenue_usd_trailing_28d": 118420.10 },
{ "store_name": "Downtown", "ordered_at": "2026-04-02T00:00:00", "revenue_usd": 3980.20, "revenue_usd_trailing_7d": 29515.45, "revenue_usd_trailing_28d": 117980.30 }
]
}
}
}Notes
The window has to be compatible with the requested time_grain. A 7d
window queried at month collapses to one bucket per month and is
rejected. Safe combinations:
| Window | Grains that work |
|---|---|
7d | hour, day |
4w | day, week |
3m | day, week, month |
Source code
store_performance ships in the sample dataset at
contracts/store_performance.json. The trailing measures above are an
extension shown in this recipe; add them to your own copy of the
contract before querying them.