Rolling Windows

For Data Analysts

Rolling windows calculate metrics over a sliding time period, smoothing out daily fluctuations to reveal underlying trends. Olytix Core supports rolling sums, averages, and custom aggregations over configurable window sizes.

Understanding Rolling Windows

Rolling windows apply aggregations over a fixed-size window that moves forward through time:

┌─────────────────────────────────────────────────────────────────────┐
│                    7-Day Rolling Average Example                     │
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│   Day 1   Day 2   Day 3   Day 4   Day 5   Day 6   Day 7   Day 8     │
│   100     120     90      110     130     95      105     115       │
│                                                                      │
│   ├───────────────── Window 1 ─────────────────┤                    │
│   │  Avg: (100+120+90+110+130+95+105)/7 = 107  │                    │
│                                                                      │
│           ├───────────────── Window 2 ─────────────────┤            │
│           │  Avg: (120+90+110+130+95+105+115)/7 = 109  │            │
│                                                                      │
│   As each day passes, the window slides forward, dropping the       │
│   oldest value and including the newest.                            │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

Built-in Rolling Windows

Olytix Core provides pre-configured rolling windows for common use cases:

Function	Window Size	Use Case
rolling_7d	7 days	Weekly trends, smoothing daily noise
rolling_14d	14 days	Bi-weekly patterns
rolling_30d	30 days	Monthly trends
rolling_90d	90 days	Quarterly trends
rolling_365d	365 days	Annual trends, year-over-year smoothing

7-Day Rolling Window

Rolling Sum

Calculate 7-day rolling revenue:

# metrics/rolling.yml
metrics:
  - name: revenue_rolling_7d
    type: derived
    expression: orders.total_revenue
    description: "Sum of revenue over the past 7 days"
    meta:
      time_intelligence:
        type: rolling_sum
        window_size: 7
        window_grain: day
        time_dimension: orders.order_date

Rolling Average

Calculate 7-day rolling average:

metrics:
  - name: revenue_rolling_7d_avg
    type: derived
    expression: orders.total_revenue
    description: "Average daily revenue over the past 7 days"
    meta:
      time_intelligence:
        type: rolling_avg
        window_size: 7
        window_grain: day
        time_dimension: orders.order_date

Query Example

{
  "metrics": ["revenue", "revenue_rolling_7d_avg"],
  "dimensions": ["orders.order_date.day"],
  "filters": [
    {
      "member": "orders.order_date",
      "operator": "inDateRange",
      "values": ["2024-03-01", "2024-03-31"]
    }
  ]
}

Response:

{
  "data": [
    {
      "orders.order_date.day": "2024-03-01",
      "revenue": 5200.00,
      "revenue_rolling_7d_avg": 4850.00
    },
    {
      "orders.order_date.day": "2024-03-02",
      "revenue": 4100.00,
      "revenue_rolling_7d_avg": 4720.00
    },
    {
      "orders.order_date.day": "2024-03-03",
      "revenue": 6300.00,
      "revenue_rolling_7d_avg": 4890.00
    }
  ]
}

30-Day Rolling Window

Monthly Trend Analysis

metrics:
  - name: revenue_rolling_30d
    type: derived
    expression: orders.total_revenue
    description: "Sum of revenue over the past 30 days"
    meta:
      time_intelligence:
        type: rolling_sum
        window_size: 30
        window_grain: day
        time_dimension: orders.order_date

  - name: revenue_rolling_30d_avg
    type: derived
    expression: orders.total_revenue
    description: "Average daily revenue over the past 30 days"
    meta:
      time_intelligence:
        type: rolling_avg
        window_size: 30
        window_grain: day
        time_dimension: orders.order_date

  - name: orders_rolling_30d_count
    type: derived
    expression: orders.order_count
    description: "Number of orders over the past 30 days"
    meta:
      time_intelligence:
        type: rolling_sum
        window_size: 30
        window_grain: day

Comparing Short and Long Windows

Analyze trends by comparing different window sizes:

metrics:
  - name: revenue_7d_vs_30d_ratio
    type: derived
    expression: |
      revenue_rolling_7d_avg / NULLIF(revenue_rolling_30d_avg, 0)
    description: |
      Ratio of 7-day average to 30-day average.
      Values > 1 indicate recent acceleration.
      Values < 1 indicate recent deceleration.
    format: "decimal"

Custom Window Sizes

Configuring Custom Windows

Define any window size for your analysis:

metrics:
  # 14-day window
  - name: revenue_rolling_14d
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: rolling_sum
        window_size: 14
        window_grain: day

  # 90-day window
  - name: revenue_rolling_90d
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: rolling_sum
        window_size: 90
        window_grain: day

  # 52-week window (annual)
  - name: revenue_rolling_52w
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: rolling_sum
        window_size: 52
        window_grain: week

Window Grain Options

Specify the unit of the window:

Grain	Description	Example
day	Daily granularity	7-day, 30-day windows
week	Weekly granularity	4-week, 13-week windows
month	Monthly granularity	3-month, 12-month windows

metrics:
  # 4-week rolling average (weekly granularity)
  - name: revenue_rolling_4w
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: rolling_avg
        window_size: 4
        window_grain: week

  # 3-month rolling sum (monthly granularity)
  - name: revenue_rolling_3m
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: rolling_sum
        window_size: 3
        window_grain: month

Rolling Window Aggregations

Available Aggregation Types

Type	Function	Use Case
rolling_sum	SUM	Total over window
rolling_avg	AVG	Average over window
rolling_min	MIN	Minimum in window
rolling_max	MAX	Maximum in window
rolling_count	COUNT	Count over window
moving_average	AVG	Alias for rolling_avg

Example: Multiple Aggregations

metrics:
  - name: revenue_rolling_7d_sum
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: rolling_sum
        window_size: 7
        window_grain: day

  - name: revenue_rolling_7d_avg
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: rolling_avg
        window_size: 7
        window_grain: day

  - name: revenue_rolling_7d_min
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: rolling_min
        window_size: 7
        window_grain: day

  - name: revenue_rolling_7d_max
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: rolling_max
        window_size: 7
        window_grain: day

Advanced Rolling Window Options

Include or Exclude Current Period

Control whether the current period is included in the window:

metrics:
  # Include current day (default)
  - name: revenue_rolling_7d_inclusive
    meta:
      time_intelligence:
        type: rolling_sum
        window_size: 7
        include_current_period: true  # Default

  # Exclude current day (trailing window)
  - name: revenue_trailing_7d
    meta:
      time_intelligence:
        type: rolling_sum
        window_size: 7
        include_current_period: false

Offset Windows

Calculate windows with an offset from the current period:

metrics:
  # 7-day sum, but from 8-14 days ago
  - name: revenue_prior_week_rolling
    meta:
      time_intelligence:
        type: rolling_sum
        window_size: 7
        window_grain: day
        offset: 7  # Start 7 days before current

Handle Incomplete Windows

Configure behavior for periods where the full window is not available:

metrics:
  - name: revenue_rolling_7d_strict
    meta:
      time_intelligence:
        type: rolling_avg
        window_size: 7
        null_if_incomplete: true  # Return NULL if < 7 days available

Partitioned Rolling Windows

Calculate rolling windows within segments:

metrics:
  - name: revenue_rolling_7d_by_region
    type: derived
    expression: orders.total_revenue
    description: "7-day rolling revenue calculated per region"
    meta:
      time_intelligence:
        type: rolling_sum
        window_size: 7
        window_grain: day
        partition_by:
          - orders.region

Query result shows independent rolling calculations per region:

{
  "data": [
    {
      "orders.region": "North America",
      "orders.order_date.day": "2024-03-15",
      "revenue_rolling_7d_by_region": 245000.00
    },
    {
      "orders.region": "Europe",
      "orders.order_date.day": "2024-03-15",
      "revenue_rolling_7d_by_region": 178000.00
    }
  ]
}

Cube-Level Rolling Measures

Define rolling calculations directly on cubes:

# cubes/orders.yml
cubes:
  - name: orders
    sql: "SELECT * FROM {{ ref('fct_orders') }}"

    measures:
      - name: total_revenue
        type: sum
        sql: order_amount

      - name: order_count
        type: count

      - name: revenue_rolling_7d
        type: number
        sql: |
          SUM(order_amount) OVER (
            ORDER BY order_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
          )
        description: "7-day rolling revenue sum"

      - name: avg_order_value_rolling_30d
        type: number
        sql: |
          AVG(order_amount) OVER (
            ORDER BY order_date
            ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
          )
        description: "30-day rolling average order value"

    dimensions:
      - name: order_date
        type: time
        sql: order_date

Common Rolling Window Patterns

Trend Detection

Identify acceleration or deceleration:

metrics:
  - name: revenue_7d_avg
    meta:
      time_intelligence:
        type: rolling_avg
        window_size: 7
        window_grain: day

  - name: revenue_28d_avg
    meta:
      time_intelligence:
        type: rolling_avg
        window_size: 28
        window_grain: day

  - name: trend_indicator
    type: derived
    expression: |
      CASE
        WHEN revenue_7d_avg > revenue_28d_avg * 1.1 THEN 'accelerating'
        WHEN revenue_7d_avg < revenue_28d_avg * 0.9 THEN 'decelerating'
        ELSE 'stable'
      END
    description: "Trend based on short vs long rolling average"

Smoothed Growth Rate

Calculate growth on smoothed values:

metrics:
  - name: revenue_7d_avg
    meta:
      time_intelligence:
        type: rolling_avg
        window_size: 7
        window_grain: day

  - name: revenue_7d_avg_prior_week
    meta:
      time_intelligence:
        type: rolling_avg
        window_size: 7
        window_grain: day
        offset: 7

  - name: smoothed_wow_growth
    type: derived
    expression: |
      (revenue_7d_avg - revenue_7d_avg_prior_week)
      / NULLIF(revenue_7d_avg_prior_week, 0) * 100
    format: "percent"
    description: "Week-over-week growth using 7-day smoothed averages"

SLA Compliance Monitoring

Track rolling compliance percentages:

metrics:
  - name: orders_delivered_on_time_30d
    type: derived
    expression: orders.on_time_delivery_count
    meta:
      time_intelligence:
        type: rolling_sum
        window_size: 30
        window_grain: day

  - name: orders_total_30d
    type: derived
    expression: orders.order_count
    meta:
      time_intelligence:
        type: rolling_sum
        window_size: 30
        window_grain: day

  - name: on_time_delivery_rate_30d
    type: derived
    expression: |
      orders_delivered_on_time_30d / NULLIF(orders_total_30d, 0) * 100
    format: "percent"
    description: "30-day rolling on-time delivery rate"

Best Practices

1. Choose Appropriate Window Sizes

Match window size to your analysis needs:

Analysis Type	Recommended Window
Daily operations	7-day
Weekly trends	14-day or 4-week
Monthly reporting	30-day
Quarterly trends	90-day
Annual patterns	365-day

2. Document Window Behavior

Clearly describe what the window includes:

- name: revenue_rolling_7d
  description: |
    Sum of revenue for the current day plus the previous 6 days.
    Includes: today, yesterday, ..., 6 days ago (7 days total).
    Window slides forward daily.

3. Handle Missing Data

Consider NULL values in your calculations:

metrics:
  - name: avg_order_value_7d
    type: derived
    expression: |
      CASE
        WHEN orders_rolling_7d > 0
        THEN revenue_rolling_7d / orders_rolling_7d
        ELSE NULL
      END
    description: "7-day average order value, NULL if no orders"

4. Use Consistent Granularity

Ensure time dimension granularity matches window grain:

# Correct: Daily granularity with day-based window
dimensions:
  - name: order_date
    type: time
    sql: order_date
    granularities: [day]

metrics:
  - name: revenue_rolling_7d
    meta:
      time_intelligence:
        window_grain: day  # Matches dimension granularity

Next Steps

• Period Comparisons — Prior period and year-over-year analysis
• Time Intelligence Overview — Complete capabilities reference
• Pre-aggregations — Optimize rolling window performance