Time Intelligence Overview
Time intelligence enables powerful temporal analytics without complex SQL. Olytix Core provides built-in functions for period-to-date calculations, prior period comparisons, rolling windows, and growth rates.
What is Time Intelligence?
Time intelligence transforms raw time-series data into meaningful business insights:
┌─────────────────────────────────────────────────────────────────────┐
│ Time Intelligence Functions │
├─────────────────────────────────────────────────────────────────────┤
│ │
│ Period-to-Date Comparisons Rolling Windows │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ YTD │ │ Prior Year │ │ 7-day avg │ │
│ │ QTD │ │ Prior Month │ │ 30-day sum │ │
│ │ MTD │ │ Prior Qtr │ │ 90-day avg │ │
│ │ WTD │ │ YoY Growth │ │ Custom │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
│ │
│ Cumulative Growth Rates │
│ ┌─────────────┐ ┌─────────────┐ │
│ │ Running Tot │ │ MoM % │ │
│ │ Cumulative │ │ QoQ % │ │
│ │ Sum │ │ YoY % │ │
│ └─────────────┘ └─────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────┘
Supported Time Functions
Period-to-Date Functions
Calculate cumulative totals from the start of a period to the current date:
| Function | Description | Example Use Case |
|---|---|---|
ytd | Year-to-date | Annual revenue tracking |
qtd | Quarter-to-date | Quarterly sales targets |
mtd | Month-to-date | Monthly budget monitoring |
wtd | Week-to-date | Weekly performance review |
fytd | Fiscal year-to-date | Fiscal year reporting |
fqtd | Fiscal quarter-to-date | Fiscal quarter analysis |
Prior Period Comparisons
Compare current values against historical periods:
| Function | Description | Example Use Case |
|---|---|---|
prior_period | Previous period at same granularity | Day-over-day comparison |
prior_year | Same period last year | Year-over-year analysis |
prior_quarter | Same period last quarter | Quarterly trend review |
prior_month | Same period last month | Monthly variance analysis |
prior_week | Same period last week | Weekly trending |
Rolling Window Functions
Calculate aggregations over sliding time windows:
| Function | Description | Example Use Case |
|---|---|---|
rolling_7d | 7-day rolling window | Weekly smoothing |
rolling_14d | 14-day rolling window | Bi-weekly trends |
rolling_30d | 30-day rolling window | Monthly smoothing |
rolling_90d | 90-day rolling window | Quarterly trends |
rolling_365d | 365-day rolling window | Annual smoothing |
rolling_custom | Custom window size | Flexible analysis |
Growth Rate Functions
Calculate percentage changes between periods:
| Function | Description | Example Use Case |
|---|---|---|
yoy_growth | Year-over-year growth | Annual growth tracking |
mom_growth | Month-over-month growth | Monthly momentum |
qoq_growth | Quarter-over-quarter growth | Quarterly growth |
wow_growth | Week-over-week growth | Weekly momentum |
pod_growth | Period-over-period growth | Flexible comparison |
Configuring Time Intelligence
Basic Configuration
Enable time intelligence on a metric:
# metrics/revenue.yml
metrics:
- name: revenue_ytd
type: derived
expression: orders.total_revenue
time_grain: day
meta:
time_intelligence:
type: year_to_date
time_dimension: order_date
Period-to-Date Example
Calculate month-to-date revenue:
metrics:
- name: revenue_mtd
type: derived
expression: orders.total_revenue
description: "Revenue accumulated from the first of the month"
meta:
time_intelligence:
type: month_to_date
time_dimension: orders.order_date
Prior Period Example
Compare current revenue against the same period last year:
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
offset: 1
offset_grain: year
Growth Rate Example
Calculate year-over-year growth percentage:
metrics:
- name: revenue_yoy_growth
type: derived
expression: |
(orders.total_revenue - prior_year(orders.total_revenue))
/ prior_year(orders.total_revenue) * 100
format: "percent"
description: "Year-over-year revenue growth rate"
meta:
time_intelligence:
type: year_over_year_growth
time_dimension: orders.order_date
Querying Time Intelligence
API Query
Query time-intelligent metrics via the API:
{
"metrics": ["revenue_ytd", "revenue_mtd"],
"dimensions": ["orders.order_date.month"],
"filters": [
{
"member": "orders.order_date",
"operator": "inDateRange",
"values": ["2024-01-01", "2024-12-31"]
}
]
}
Response Format
{
"data": [
{
"orders.order_date.month": "2024-01",
"revenue_ytd": 125000.00,
"revenue_mtd": 125000.00
},
{
"orders.order_date.month": "2024-02",
"revenue_ytd": 258000.00,
"revenue_mtd": 133000.00
},
{
"orders.order_date.month": "2024-03",
"revenue_ytd": 412000.00,
"revenue_mtd": 154000.00
}
]
}
Time Dimension Requirements
Time intelligence requires a properly configured time dimension:
# cubes/orders.yml
cubes:
- name: orders
sql: "SELECT * FROM {{ ref('fct_orders') }}"
dimensions:
- name: order_date
type: time
sql: order_date
granularities:
- day
- week
- month
- quarter
- year
Supported Granularities
| Granularity | SQL Function | Example Output |
|---|---|---|
second | DATE_TRUNC('second', ...) | 2024-03-15 14:30:00 |
minute | DATE_TRUNC('minute', ...) | 2024-03-15 14:30:00 |
hour | DATE_TRUNC('hour', ...) | 2024-03-15 14:00:00 |
day | DATE_TRUNC('day', ...) | 2024-03-15 |
week | DATE_TRUNC('week', ...) | 2024-03-11 |
month | DATE_TRUNC('month', ...) | 2024-03-01 |
quarter | DATE_TRUNC('quarter', ...) | 2024-01-01 |
year | DATE_TRUNC('year', ...) | 2024-01-01 |
Fiscal Calendar Support
Configure fiscal calendars for non-standard fiscal years:
# olytix-core_project.yml
fiscal_calendars:
- name: retail_fiscal
fiscal_year_start_month: 2 # February
week_start_day: 1 # Monday
retail_calendar: "445" # 4-4-5 retail calendar
Reference the fiscal calendar in metrics:
metrics:
- name: revenue_fytd
type: derived
expression: orders.total_revenue
meta:
time_intelligence:
type: fiscal_year_to_date
fiscal_calendar: retail_fiscal
Best Practices
1. Name Metrics Descriptively
Include the time function in the metric name:
# Clear naming
- name: revenue_ytd # Year-to-date
- name: revenue_mtd # Month-to-date
- name: revenue_rolling_7d # 7-day rolling
- name: revenue_yoy_pct # Year-over-year percentage
2. Document Time Logic
Explain what the metric calculates:
- name: revenue_qtd
description: |
Cumulative revenue from the first day of the current quarter
through the reporting date. Resets at the start of each quarter.
3. Use Appropriate Granularity
Match granularity to your reporting needs:
| Report Type | Recommended Granularity |
|---|---|
| Daily dashboard | day |
| Weekly report | week |
| Monthly review | month |
| Quarterly earnings | quarter |
4. Handle Incomplete Periods
Configure behavior for partial periods:
meta:
time_intelligence:
type: month_to_date
null_if_incomplete: true # Return NULL if month is incomplete
Common Patterns
Executive Dashboard Metrics
metrics:
# Current period
- name: revenue_mtd
expression: orders.total_revenue
meta:
time_intelligence:
type: month_to_date
# Comparison
- name: revenue_prior_month
expression: orders.total_revenue
meta:
time_intelligence:
type: prior_month
# Growth rate
- name: revenue_mom_growth
expression: |
(revenue_mtd - revenue_prior_month) / revenue_prior_month * 100
format: "percent"
Trend Analysis Metrics
metrics:
- name: orders_rolling_7d_avg
expression: orders.order_count
meta:
time_intelligence:
type: rolling_avg
window_size: 7
window_grain: day
- name: orders_rolling_30d_avg
expression: orders.order_count
meta:
time_intelligence:
type: rolling_avg
window_size: 30
window_grain: day
Next Steps
Explore specific time intelligence capabilities:
- Period Comparisons — Prior period and year-over-year analysis
- Rolling Windows — Moving averages and window calculations
- Fiscal Calendars — Custom fiscal year configuration