Inline Expressions

Dataset metrics can include an expression field containing a SQL expression that transforms source data before aggregation. This lets you define calculations like unit conversions, conditional logic, and rounding directly on a metric -- without creating a full calculated metric.

The optional format field controls display formatting -- either as a simple string shorthand (currency, percent, number) or a full object with properties like decimals, currency, and locale. When a user drags the metric onto a card, the format seeds the card's default display settings.

When to Use Inline Expressions vs Calculated Metrics

Expression Syntax

Two authoring styles are supported.

Plain field references:

expression: "amount_cents / 100"

Brace notation -- useful when field names contain special characters or for clarity:

expression: "{amount_cents} / 100"

Both produce the same result. At compile time, field names are resolved against the dataset's field inventory.

Referencing Other Metrics

Expression metrics can reference other metrics in the same dataset. If you have a metric amount_usd with expression amount_cents / 100, another metric can build on it:

metrics:
  - name: amount_usd
    expression: "amount_cents / 100"
    sourceField: amount_cents
    dataType: number
  - name: amount_with_tax
    expression: "amount_usd * 1.08"
    sourceField: amount_cents
    dataType: number

Supported SQL Constructs

• Arithmetic: +, -, *, /
• CASE WHEN: CASE WHEN status = 'active' THEN amount ELSE 0 END
• CAST: CAST(amount_cents AS DECIMAL) / 100
• Functions: ROUND(...), COALESCE(...), CONCAT(...), ABS(...), NULLIF(...)
• EXTRACT: EXTRACT(YEAR FROM order_date)
• Aggregate functions: SUM(...), COUNT(...), AVG(...) -- makes the expression pre-aggregated

Examples

Currency Conversion

metrics:
  - name: amount_usd
    label: "Amount (USD)"
    dataType: number
    sourceField: amount_cents
    expression: "amount_cents / 100"
    format: currency

Percentage Metric

metrics:
  - name: conversion_rate
    label: "Conversion Rate"
    dataType: number
    sourceField: conversions
    expression: "conversions / NULLIF(visits, 0) * 100"
    format: percent

Conditional Logic

metrics:
  - name: active_revenue
    label: "Active Revenue"
    dataType: number
    sourceField: amount
    expression: "CASE WHEN status = 'active' THEN amount ELSE 0 END"
    format: currency

Rounding

metrics:
  - name: amount_rounded
    label: "Amount (Rounded)"
    dataType: number
    sourceField: amount
    expression: "ROUND(amount, 2)"
    format: number

Pre-Aggregated Expression

When the expression already contains aggregate functions, Semaphor uses it as-is instead of wrapping it in SUM(...).

metrics:
  - name: avg_order_value
    label: "Average Order Value"
    dataType: number
    sourceField: amount
    expression: "SUM(amount) / NULLIF(COUNT(DISTINCT order_id), 0)"
    format: currency

Format Specification

The format field controls how a metric's values are displayed. It supports two forms: a string shorthand for common cases, and a full object for fine-grained control.

String Shorthand

For quick setup, pass a format type as a string:

metrics:
  - name: amount_usd
    expression: "amount_cents / 100"
    sourceField: amount_cents
    dataType: number
    format: currency

Supported string values:

Object Form

For more control, pass a format object with specific properties:

metrics:
  - name: revenue_eur
    expression: "amount_cents / 100"
    sourceField: amount_cents
    dataType: number
    format:
      type: currency
      currency: EUR
      decimals: 0
      locale: de-DE
      thousandsSeparator: true

metrics:
  - name: margin_pct
    expression: "profit / NULLIF(revenue, 0)"
    sourceField: profit
    dataType: number
    format:
      type: percent
      decimals: 1
      percentValueMode: fraction

Format Object Reference

Format Precedence

When a user drags a metric with a format onto a card, the format seeds the card's default display settings. The precedence order is:

1. User-set formatting (highest priority) -- any formatting the user explicitly configures on the card
2. Semantic format -- the format value from the metric definition
3. Visual defaults -- Semaphor's built-in defaults

Aggregation Behavior

Non-Aggregated Expressions

When an expression does not contain an aggregate function (SUM, COUNT, AVG, etc.), Semaphor automatically wraps it in SUM(...) when used as a metric. This mirrors the default behavior for regular numeric metrics.

# This expression:
expression: "amount_cents / 100"
 
# Becomes this in the generated SQL:
# SUM(amount_cents / 100)

Pre-Aggregated Expressions

If the expression already contains aggregate functions, Semaphor uses it as-is.

# This expression:
expression: "SUM(amount) / NULLIF(COUNT(DISTINCT order_id), 0)"
 
# Is used directly in the generated SQL:
# SUM(amount) / NULLIF(COUNT(DISTINCT order_id), 0)

Non-Numeric Expressions

For non-numeric data types (string, date), COUNT is applied instead of SUM.

Validation Errors

When you save a domain template with metric expressions, Semaphor validates them. Two error codes are specific to inline expressions:

Restrictions

• AS aliasing -- amount_cents AS usd is not supported. Use the metric's name field instead.
• Dimension expressions -- Only metrics support the expression field. Dimension transforms should use calculated dimensions.
• Cross-dataset references -- Inline expressions can only reference fields within the same dataset. For cross-dataset calculations, use calculated metrics.
• Braces in string literals -- String values containing { or } are not supported inside expressions.

Best Practices

• Prefer sourceField for simple metrics -- If a metric maps directly to a database column with no transformation, use sourceField without expression. Expressions are for when you need to transform the value.
• Use NULLIF to prevent division by zero -- Write amount / NULLIF(orders, 0) instead of amount / orders.
• Add a format hint for user convenience -- Format hints save dashboard users from manually configuring number formatting every time they use the metric.
• Keep expressions simple -- For complex multi-step calculations involving multiple datasets, use domain-level calculated metrics instead.
• Use descriptive label and description -- Help dashboard users understand what the transformed metric represents.