Period Comparisons

For Data Analysts

Period comparisons allow you to analyze how metrics change over time by comparing current values against historical periods. Olytix Core supports standard comparisons like year-over-year (YoY) and month-over-month (MoM), as well as custom period offsets.

Understanding Period Comparisons

Period comparisons answer questions like:

• "How does this month compare to last month?"
• "Are we ahead of last year's performance?"
• "What is our growth rate quarter-over-quarter?"

┌─────────────────────────────────────────────────────────────────────┐
│                    Period Comparison Example                         │
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│   Current Period         Prior Period           Comparison           │
│   ┌─────────────┐       ┌─────────────┐       ┌─────────────┐       │
│   │ March 2024  │       │ March 2023  │       │ YoY Growth  │       │
│   │ Revenue:    │  vs   │ Revenue:    │   =   │             │       │
│   │ $412,000    │       │ $350,000    │       │ +17.7%      │       │
│   └─────────────┘       └─────────────┘       └─────────────┘       │
│                                                                      │
│   ┌─────────────┐       ┌─────────────┐       ┌─────────────┐       │
│   │ March 2024  │       │ February    │       │ MoM Growth  │       │
│   │ Revenue:    │  vs   │ 2024        │   =   │             │       │
│   │ $412,000    │       │ $380,000    │       │ +8.4%       │       │
│   └─────────────┘       └─────────────┘       └─────────────┘       │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

Prior Period Functions

Prior Year

Compare against the same period in the previous year:

# metrics/revenue_comparisons.yml
metrics:
  - name: revenue_prior_year
    type: derived
    expression: orders.total_revenue
    description: "Revenue from the same period last year"
    meta:
      time_intelligence:
        type: prior_year
        time_dimension: orders.order_date

Query this metric alongside current values:

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

Response:

{
  "data": [
    {
      "orders.order_date.month": "2024-01",
      "revenue": 125000.00,
      "revenue_prior_year": 110000.00
    },
    {
      "orders.order_date.month": "2024-02",
      "revenue": 133000.00,
      "revenue_prior_year": 118000.00
    },
    {
      "orders.order_date.month": "2024-03",
      "revenue": 154000.00,
      "revenue_prior_year": 122000.00
    }
  ]
}

Prior Month

Compare against the previous month:

metrics:
  - name: revenue_prior_month
    type: derived
    expression: orders.total_revenue
    description: "Revenue from the previous month"
    meta:
      time_intelligence:
        type: prior_month
        time_dimension: orders.order_date

Prior Quarter

Compare against the previous quarter:

metrics:
  - name: revenue_prior_quarter
    type: derived
    expression: orders.total_revenue
    description: "Revenue from the previous quarter"
    meta:
      time_intelligence:
        type: prior_quarter
        time_dimension: orders.order_date

Prior Week

Compare against the previous week:

metrics:
  - name: revenue_prior_week
    type: derived
    expression: orders.total_revenue
    description: "Revenue from the previous week"
    meta:
      time_intelligence:
        type: prior_week
        time_dimension: orders.order_date

Year-Over-Year Analysis

Basic YoY Comparison

Create a complete year-over-year analysis with current, prior, and growth rate:

# metrics/yoy_analysis.yml
metrics:
  # Current period value
  - name: revenue_current
    type: simple
    expression: orders.total_revenue
    description: "Current period revenue"

  # Same period last year
  - name: revenue_yoy_prior
    type: derived
    expression: orders.total_revenue
    description: "Revenue from the same period last year"
    meta:
      time_intelligence:
        type: prior_year
        time_dimension: orders.order_date
        offset: 1
        offset_grain: year

  # Absolute difference
  - name: revenue_yoy_change
    type: derived
    expression: |
      orders.total_revenue - prior_year(orders.total_revenue)
    description: "Absolute change versus prior year"
    format: "currency"

  # Percentage growth
  - name: revenue_yoy_growth
    type: derived
    expression: |
      CASE WHEN prior_year(orders.total_revenue) > 0
        THEN (orders.total_revenue - prior_year(orders.total_revenue))
             / prior_year(orders.total_revenue) * 100
        ELSE NULL
      END
    description: "Year-over-year growth percentage"
    format: "percent"

Multi-Year Comparison

Compare against multiple prior years:

metrics:
  - name: revenue_2_years_ago
    type: derived
    expression: orders.total_revenue
    description: "Revenue from two years ago"
    meta:
      time_intelligence:
        type: prior_year
        offset: 2
        offset_grain: year

  - name: revenue_3_years_ago
    type: derived
    expression: orders.total_revenue
    description: "Revenue from three years ago"
    meta:
      time_intelligence:
        type: prior_year
        offset: 3
        offset_grain: year

Month-Over-Month Analysis

Complete MoM Metrics

# metrics/mom_analysis.yml
metrics:
  - name: revenue_mom_prior
    type: derived
    expression: orders.total_revenue
    description: "Revenue from the previous month"
    meta:
      time_intelligence:
        type: prior_month
        time_dimension: orders.order_date

  - name: revenue_mom_change
    type: derived
    expression: |
      orders.total_revenue - prior_month(orders.total_revenue)
    description: "Month-over-month change in revenue"
    format: "currency"

  - name: revenue_mom_growth
    type: derived
    expression: |
      CASE WHEN prior_month(orders.total_revenue) > 0
        THEN (orders.total_revenue - prior_month(orders.total_revenue))
             / prior_month(orders.total_revenue) * 100
        ELSE NULL
      END
    description: "Month-over-month growth percentage"
    format: "percent"

Sequential Month Comparison

Compare against multiple prior months for trend analysis:

metrics:
  - name: revenue_1_month_ago
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: prior_period
        offset: 1
        offset_grain: month

  - name: revenue_2_months_ago
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: prior_period
        offset: 2
        offset_grain: month

  - name: revenue_3_months_ago
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: prior_period
        offset: 3
        offset_grain: month

Quarter-Over-Quarter Analysis

# metrics/qoq_analysis.yml
metrics:
  - name: revenue_qoq_prior
    type: derived
    expression: orders.total_revenue
    description: "Revenue from the previous quarter"
    meta:
      time_intelligence:
        type: prior_quarter
        time_dimension: orders.order_date

  - name: revenue_qoq_growth
    type: derived
    expression: |
      CASE WHEN prior_quarter(orders.total_revenue) > 0
        THEN (orders.total_revenue - prior_quarter(orders.total_revenue))
             / prior_quarter(orders.total_revenue) * 100
        ELSE NULL
      END
    description: "Quarter-over-quarter growth percentage"
    format: "percent"

Custom Period Offsets

Flexible Offset Configuration

Use custom offsets for non-standard comparisons:

metrics:
  # Compare to 6 months ago
  - name: revenue_6_months_ago
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: prior_period
        offset: 6
        offset_grain: month

  # Compare to 52 weeks ago (same weekday last year)
  - name: revenue_52_weeks_ago
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: prior_period
        offset: 52
        offset_grain: week

  # Compare to 90 days ago
  - name: revenue_90_days_ago
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: prior_period
        offset: 90
        offset_grain: day

Comparison Output Options

Configure what values to return from comparisons:

metrics:
  - name: revenue_comparison
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: prior_year
        output:
          - current           # Current period value
          - prior             # Prior period value
          - absolute_change   # Current - Prior
          - percent_change    # (Current - Prior) / Prior * 100

Query with all outputs:

{
  "metrics": ["revenue_comparison"],
  "dimensions": ["orders.order_date.month"]
}

Response includes all calculated values:

{
  "data": [
    {
      "orders.order_date.month": "2024-03",
      "revenue_comparison.current": 154000.00,
      "revenue_comparison.prior": 122000.00,
      "revenue_comparison.absolute_change": 32000.00,
      "revenue_comparison.percent_change": 26.23
    }
  ]
}

Partitioned Comparisons

Compare metrics within segments using partition by:

metrics:
  - name: revenue_yoy_by_region
    type: derived
    expression: orders.total_revenue
    description: "Year-over-year comparison partitioned by region"
    meta:
      time_intelligence:
        type: prior_year
        time_dimension: orders.order_date
        partition_by:
          - orders.region

This calculates YoY for each region independently:

{
  "data": [
    {
      "orders.region": "North America",
      "orders.order_date.month": "2024-03",
      "revenue": 95000.00,
      "revenue_yoy_by_region": 82000.00
    },
    {
      "orders.region": "Europe",
      "orders.order_date.month": "2024-03",
      "revenue": 59000.00,
      "revenue_yoy_by_region": 40000.00
    }
  ]
}

Handling Edge Cases

Incomplete Periods

Handle comparisons when prior period data is missing:

metrics:
  - name: revenue_yoy_safe
    type: derived
    expression: |
      CASE
        WHEN prior_year(orders.total_revenue) IS NULL THEN NULL
        WHEN prior_year(orders.total_revenue) = 0 THEN NULL
        ELSE (orders.total_revenue - prior_year(orders.total_revenue))
             / prior_year(orders.total_revenue) * 100
      END
    description: "YoY growth with NULL handling for missing data"
    format: "percent"

New Product Launches

Mark metrics where prior period comparison is not meaningful:

metrics:
  - name: revenue_yoy_with_flag
    type: derived
    expression: orders.total_revenue
    meta:
      time_intelligence:
        type: prior_year
        null_if_incomplete: true  # Return NULL if no prior data

Cube-Level Measures

Define comparison measures 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: total_revenue_prior_year
        type: sum
        sql: order_amount
        meta:
          time_intelligence:
            type: prior_year

      - name: revenue_yoy_growth_pct
        type: number
        sql: |
          CASE WHEN {total_revenue_prior_year} > 0
            THEN ({total_revenue} - {total_revenue_prior_year})
                 / {total_revenue_prior_year} * 100
            ELSE NULL
          END
        format: "percent"

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

Best Practices

1. Use Consistent Naming

Adopt a naming convention for comparison metrics:

# Pattern: {measure}_{comparison_type}_{period}
- name: revenue_prior_year
- name: revenue_prior_month
- name: revenue_yoy_growth
- name: revenue_mom_change

2. Document Comparison Logic

Explain the exact comparison being made:

- name: revenue_same_day_last_year
  description: |
    Compares revenue to the same calendar date in the prior year.
    Note: Does not account for day-of-week differences.
    For same-weekday comparison, use revenue_52_weeks_ago.

3. Handle Division by Zero

Always guard against division by zero in growth calculations:

expression: |
  CASE
    WHEN prior_year(orders.total_revenue) = 0 THEN NULL
    WHEN prior_year(orders.total_revenue) IS NULL THEN NULL
    ELSE (orders.total_revenue - prior_year(orders.total_revenue))
         / prior_year(orders.total_revenue) * 100
  END

4. Consider Seasonality

For seasonal businesses, use appropriate comparison periods:

# Retail: Compare to same week last year
- name: sales_52_weeks_ago
  meta:
    time_intelligence:
      type: prior_period
      offset: 52
      offset_grain: week

# Monthly recurring: Compare to prior month
- name: mrr_prior_month
  meta:
    time_intelligence:
      type: prior_month

Next Steps

• Rolling Windows — Moving averages and window calculations
• Time Intelligence Overview — Complete capabilities reference
• Querying — Execute time-intelligent queries