Time is the Hardest Dimension¶

Time dimensions are special: they're naturally ordered, and they need an aggregation granularity (daily, weekly, monthly). This makes them the foundation for a family of transforms that are simple to express but non-trivial to implement in SQL.

This notebook demonstrates SLayer's time-related features with working code.

Time Dimensions and Granularity¶

A time dimension truncates a date/timestamp column to a given granularity. SLayer supports day, week, month, quarter, and year.

Let's start with monthly revenue:

In [2]:

Copied!





result = engine.execute_sync(
    query={
        "source_model": "orders",
        "time_dimensions": [{"dimension": "ordered_at", "granularity": "month"}],
        "measures": ["order_total:sum"],
        "order": [{"column": "ordered_at", "direction": "asc"}],
        "limit": 12,
    }
)

print(f"{'Month':<12} {'Revenue':>14}")
print("-" * 28)
for row in result.data:
    month = str(row["orders.ordered_at"])[:7]
    rev = row["orders.order_total_sum"]
    print(f"{month:<12} ${rev:>13,.2f}")
result = engine.execute_sync(
    query={
        "source_model": "orders",
        "time_dimensions": [{"dimension": "ordered_at", "granularity": "month"}],
        "measures": ["order_total:sum"],
        "order": [{"column": "ordered_at", "direction": "asc"}],
        "limit": 12,
    }
)

print(f"{'Month':<12} {'Revenue':>14}")
print("-" * 28)
for row in result.data:
    month = str(row["orders.ordered_at"])[:7]
    rev = row["orders.order_total_sum"]
    print(f"{month:<12} ${rev:>13,.2f}")

Month               Revenue
----------------------------
2023-05      $    15,102.20
2023-06      $    23,098.27
2023-07      $    29,880.94
2023-08      $    38,628.40
2023-09      $    44,370.20
2023-10      $    47,540.71
2023-11      $    71,315.94
2023-12      $    96,590.09
2024-01      $   101,618.39
2024-02      $   101,344.30
2024-03      $    94,641.51
2024-04      $   100,594.15

Month-over-Month Change¶

The change() transform computes the difference from the previous period. It needs no extra arguments — the time dimension and granularity are already known from the query. Internally it uses a self-join CTE (not LAG), so it handles time gaps correctly: the previous period is matched on the calendar (e.g., the month before), not on the previous row in the result set. The very first period in the window still has no prior period to compare against, so its change is NULL (the first row below shows -); subsequent periods are gap-safe.

change_pct() gives the percentage change:

In [3]:

Copied!





result = engine.execute_sync(
    query={
        "source_model": "orders",
        "time_dimensions": [{"dimension": "ordered_at", "granularity": "month"}],
        "measures": [
            "order_total:sum",
            {"formula": "change(order_total:sum)", "name": "mom_change"},
            {"formula": "change_pct(order_total:sum)", "name": "mom_pct"},
        ],
        "order": [{"column": "ordered_at", "direction": "asc"}],
        "limit": 12,
    }
)

print(f"{'Month':<12} {'Revenue':>14} {'MoM Change':>14} {'MoM %':>8}")
print("-" * 52)
for row in result.data:
    month = str(row["orders.ordered_at"])[:7]
    rev = row["orders.order_total_sum"]
    chg = row["orders.mom_change"]
    pct = row["orders.mom_pct"]
    chg_str = f"${chg:+,.0f}" if chg is not None else "-"
    pct_str = f"{pct:+.1%}" if pct is not None else "-"
    print(f"{month:<12} ${rev:>13,.2f} {chg_str:>14} {pct_str:>8}")
result = engine.execute_sync(
    query={
        "source_model": "orders",
        "time_dimensions": [{"dimension": "ordered_at", "granularity": "month"}],
        "measures": [
            "order_total:sum",
            {"formula": "change(order_total:sum)", "name": "mom_change"},
            {"formula": "change_pct(order_total:sum)", "name": "mom_pct"},
        ],
        "order": [{"column": "ordered_at", "direction": "asc"}],
        "limit": 12,
    }
)

print(f"{'Month':<12} {'Revenue':>14} {'MoM Change':>14} {'MoM %':>8}")
print("-" * 52)
for row in result.data:
    month = str(row["orders.ordered_at"])[:7]
    rev = row["orders.order_total_sum"]
    chg = row["orders.mom_change"]
    pct = row["orders.mom_pct"]
    chg_str = f"${chg:+,.0f}" if chg is not None else "-"
    pct_str = f"{pct:+.1%}" if pct is not None else "-"
    print(f"{month:<12} ${rev:>13,.2f} {chg_str:>14} {pct_str:>8}")

Month               Revenue     MoM Change    MoM %
----------------------------------------------------
2023-05      $    15,102.20              -        -
2023-06      $    23,098.27        $+7,996   +52.9%
2023-07      $    29,880.94        $+6,783   +29.4%
2023-08      $    38,628.40        $+8,747   +29.3%
2023-09      $    44,370.20        $+5,742   +14.9%
2023-10      $    47,540.71        $+3,171    +7.1%
2023-11      $    71,315.94       $+23,775   +50.0%
2023-12      $    96,590.09       $+25,274   +35.4%
2024-01      $   101,618.39        $+5,028    +5.2%
2024-02      $   101,344.30          $-274    -0.3%
2024-03      $    94,641.51        $-6,703    -6.6%
2024-04      $   100,594.15        $+5,953    +6.3%

Year-over-Year with time_shift¶

time_shift(measure, offset, granularity) shifts the time dimension by the given offset before applying the query's granularity. This is implemented as a calendar-based self-join CTE — semantically simple, but the SQL is non-trivial.

For example, time_shift(order_total_sum, -1, 'year') gives "same month, previous year":

In [4]:

Copied!





import datetime as dt

# Use last full calendar year so the example stays in the demo's data window
# (the loader shifts dates so MAX(ordered_at) == today, with ~4 years of history).
last_year = dt.date.today().year - 1
date_range = [f"{last_year}-01-01", f"{last_year}-12-31"]

result = engine.execute_sync(
    query={
        "source_model": "orders",
        "time_dimensions": [
            {
                "dimension": "ordered_at",
                "granularity": "month",
                "date_range": date_range,
            }
        ],
        "measures": [
            "order_total:sum",
            {"formula": "time_shift(order_total:sum, -1, 'year')", "name": "prev_year"},
        ],
        "order": [{"column": "ordered_at", "direction": "asc"}],
    }
)

print(f"{'Month':<12} {'Revenue':>14} {'Prev Year':>14}")
print("-" * 42)
for row in result.data:
    month = str(row["orders.ordered_at"])[:7]
    rev = row["orders.order_total_sum"]
    prev = row["orders.prev_year"]
    prev_str = f"${prev:>13,.2f}" if prev is not None else f"{'n/a':>14}"
    print(f"{month:<12} ${rev:>13,.2f} {prev_str}")
import datetime as dt

# Use last full calendar year so the example stays in the demo's data window
# (the loader shifts dates so MAX(ordered_at) == today, with ~4 years of history).
last_year = dt.date.today().year - 1
date_range = [f"{last_year}-01-01", f"{last_year}-12-31"]

result = engine.execute_sync(
    query={
        "source_model": "orders",
        "time_dimensions": [
            {
                "dimension": "ordered_at",
                "granularity": "month",
                "date_range": date_range,
            }
        ],
        "measures": [
            "order_total:sum",
            {"formula": "time_shift(order_total:sum, -1, 'year')", "name": "prev_year"},
        ],
        "order": [{"column": "ordered_at", "direction": "asc"}],
    }
)

print(f"{'Month':<12} {'Revenue':>14} {'Prev Year':>14}")
print("-" * 42)
for row in result.data:
    month = str(row["orders.ordered_at"])[:7]
    rev = row["orders.order_total_sum"]
    prev = row["orders.prev_year"]
    prev_str = f"${prev:>13,.2f}" if prev is not None else f"{'n/a':>14}"
    print(f"{month:<12} ${rev:>13,.2f} {prev_str}")

Month               Revenue      Prev Year
------------------------------------------
2025-01      $   234,932.57 $   101,618.39
2025-02      $   213,342.52 $   101,344.30
2025-03      $   197,498.69 $    94,641.51
2025-04      $   211,484.49 $   100,594.15
2025-05      $   250,840.99 $   118,106.62
2025-06      $   320,144.10 $   154,699.94
2025-07      $   355,408.18 $   168,960.69
2025-08      $   372,860.67 $   180,589.12
2025-09      $   365,526.70 $   177,773.88
2025-10      $   386,828.05 $   187,103.17
2025-11      $   391,355.79 $   184,622.43
2025-12      $   408,238.49 $   181,767.65

time_shift vs lag¶

Without a granularity argument, time_shift(x, -1) is equivalent to lag(x, 1) — it uses SQL's LAG() window function to look at the previous row. This is more efficient but blindly walks rows: if the result has a gap (e.g., a missing month), the previous row is the wrong period.

With a granularity argument (like 'year' above), time_shift uses a calendar-based self-join: gap-safe (it matches on the actual calendar date, so a missing intermediate month produces NULL rather than the wrong value), and the shift can be any amount (not just a multiple of the query granularity). Both forms still return NULL at the start of the window when there's no prior period in the data to match.

The last() Transform¶

last(measure) returns the most recent time period's value, broadcast to every row. This collapses the time dimension — useful for comparing each period to the latest one, or for filtering.

For example, revenue by store alongside each store's most recent month's revenue:

In [5]:

Copied!





result = engine.execute_sync(
    query={
        "source_model": "orders",
        "time_dimensions": [{"dimension": "ordered_at", "granularity": "quarter"}],
        "measures": [
            "order_total:sum",
            {"formula": "last(order_total:sum)", "name": "latest_quarter"},
        ],
        "dimensions": ["stores.name"],
        "order": [{"column": "ordered_at", "direction": "desc"}],
        "limit": 10,
    }
)

print(f"{'Quarter':<12} {'Store':<20} {'Revenue':>14} {'Latest Qtr':>14}")
print("-" * 62)
for row in result.data:
    qtr = str(row["orders.ordered_at"])[:7]
    store = row["orders.stores.name"]
    rev = row["orders.order_total_sum"]
    latest = row["orders.latest_quarter"]
    print(f"{qtr:<12} {store:<20} ${rev:>13,.2f} ${latest:>13,.2f}")
result = engine.execute_sync(
    query={
        "source_model": "orders",
        "time_dimensions": [{"dimension": "ordered_at", "granularity": "quarter"}],
        "measures": [
            "order_total:sum",
            {"formula": "last(order_total:sum)", "name": "latest_quarter"},
        ],
        "dimensions": ["stores.name"],
        "order": [{"column": "ordered_at", "direction": "desc"}],
        "limit": 10,
    }
)

print(f"{'Quarter':<12} {'Store':<20} {'Revenue':>14} {'Latest Qtr':>14}")
print("-" * 62)
for row in result.data:
    qtr = str(row["orders.ordered_at"])[:7]
    store = row["orders.stores.name"]
    rev = row["orders.order_total_sum"]
    latest = row["orders.latest_quarter"]
    print(f"{qtr:<12} {store:<20} ${rev:>13,.2f} ${latest:>13,.2f}")

Quarter      Store                       Revenue     Latest Qtr
--------------------------------------------------------------
2026-04      New Orleans          $    46,679.30 $    46,679.30
2026-04      Philadelphia         $    61,992.41 $    61,992.41
2026-04      Brooklyn             $   105,664.69 $   105,664.69
2026-04      Chicago              $    93,307.77 $    93,307.77
2026-04      San Francisco        $    84,370.64 $    84,370.64
2026-01      New Orleans          $    93,931.43 $    46,679.30
2026-01      Brooklyn             $   305,553.50 $   105,664.69
2026-01      Chicago              $   267,679.39 $    93,307.77
2026-01      San Francisco        $   244,693.18 $    84,370.64
2026-01      Philadelphia         $   186,458.15 $    61,992.41

Composing Transforms in Filters¶

Transforms can be composed and used in filters. For example, last(change(order_total_sum)) < 0 keeps only rows where the most recent period's change was negative — i.e., revenue declined in the latest period.

This is a powerful pattern: "show me all stores, but only those whose most recent quarter-over-quarter revenue went down":

In [6]:

Copied!





result = engine.execute_sync(
    query={
        "source_model": "orders",
        "time_dimensions": [{"dimension": "ordered_at", "granularity": "quarter"}],
        "measures": [
            "order_total:sum",
            {"formula": "change(order_total:sum)", "name": "qoq_change"},
        ],
        "dimensions": ["stores.name"],
        "filters": ["last(change(order_total:sum)) < 0"],
        "order": [{"column": "ordered_at", "direction": "desc"}],
        "limit": 10,
    }
)

print("Stores where latest QoQ revenue declined:")
print(f"{'Quarter':<12} {'Store':<20} {'Revenue':>14} {'QoQ Change':>14}")
print("-" * 62)
for row in result.data:
    qtr = str(row["orders.ordered_at"])[:7]
    store = row["orders.stores.name"]
    rev = row["orders.order_total_sum"]
    chg = row["orders.qoq_change"]
    chg_str = f"${chg:+,.0f}" if chg is not None else "-"
    print(f"{qtr:<12} {store:<20} ${rev:>13,.2f} {chg_str:>14}")
result = engine.execute_sync(
    query={
        "source_model": "orders",
        "time_dimensions": [{"dimension": "ordered_at", "granularity": "quarter"}],
        "measures": [
            "order_total:sum",
            {"formula": "change(order_total:sum)", "name": "qoq_change"},
        ],
        "dimensions": ["stores.name"],
        "filters": ["last(change(order_total:sum)) < 0"],
        "order": [{"column": "ordered_at", "direction": "desc"}],
        "limit": 10,
    }
)

print("Stores where latest QoQ revenue declined:")
print(f"{'Quarter':<12} {'Store':<20} {'Revenue':>14} {'QoQ Change':>14}")
print("-" * 62)
for row in result.data:
    qtr = str(row["orders.ordered_at"])[:7]
    store = row["orders.stores.name"]
    rev = row["orders.order_total_sum"]
    chg = row["orders.qoq_change"]
    chg_str = f"${chg:+,.0f}" if chg is not None else "-"
    print(f"{qtr:<12} {store:<20} ${rev:>13,.2f} {chg_str:>14}")

Stores where latest QoQ revenue declined:
Quarter      Store                       Revenue     QoQ Change
--------------------------------------------------------------
2026-04      New Orleans          $    46,679.30       $-47,252
2026-04      Philadelphia         $    61,992.41      $-124,466
2026-04      Chicago              $    93,307.77      $-174,372
2026-04      San Francisco        $    84,370.64      $-160,323
2026-04      Brooklyn             $   105,664.69      $-199,889
2026-01      New Orleans          $    93,931.43       $+53,759
2026-01      Chicago              $   267,679.39       $-27,440
2026-01      San Francisco        $   244,693.18       $-23,744
2026-01      Brooklyn             $   305,553.50       $-54,661
2026-01      Philadelphia         $   186,458.15       $-36,020

Summary¶

Each of these concepts is simple and natural at the semantic level, but requires non-trivial SQL (CTEs, self-joins, window functions) to implement. SLayer offloads that effort.

Transform	What it does	SQL implementation
`change(x)`	Difference from previous period	Self-join CTE (gap-safe; first period is NULL)
`change_pct(x)`	Percentage change from previous period	Same as change, divided
`time_shift(x, -1, 'year')`	Same measure, shifted in time	Calendar-based self-join CTE
`lag(x)` / `lead(x)`	Previous/next row's value	SQL LAG/LEAD window (gap-blind)
`last(x)`	Most recent period's value, broadcast	Sub-query with FIRST_VALUE
`cumsum(x)`	Running total over time	SUM window function

All transforms compose: cumsum(change(x)), last(change(x)) in filters, etc.

See the Formulas docs for the full reference.