Calculated Fields

Overview

Calculated fields let you create custom metrics and dimensions directly in the Explorer. Derive new insights from your data without writing database queries or modifying your underlying data source.

Two types of calculated fields:

• Calculated Metrics: Combine existing metrics using operations like sum, difference, or ratio
• Calculated Dimensions: Create custom groupings using SQL expressions

Example: If you have 1,000 order rows:

• A calculated dimension like CASE WHEN {Amount} > 100 THEN 'Large' ELSE 'Small' END evaluates each of the 1,000 rows to categorize them
• A calculated metric like SUM(Revenue) / SUM(Cost) first computes SUM(Revenue) and SUM(Cost) across all rows, then divides the two results

Calculated Metrics

Create derived metrics by combining existing fields with mathematical operations.

Supported Operations

Aggregation Functions

Choose how each input field is aggregated before the calculation:

• SUM - Total of all values (default for numeric fields)
• AVG - Average value
• COUNT - Number of rows
• COUNT DISTINCT - Number of unique values
• MIN - Minimum value
• MAX - Maximum value

Creating a Calculated Metric

1. Open the Explorer and click + Add Calculated Field
2. Select Calculated Metric
3. Enter a name (e.g., "Profit Margin")
4. Choose an operation (e.g., Ratio)
5. Select fields for Input A and Input B
6. Choose aggregation functions for each input
7. Click Create Metric

Example: Average Order Value

To calculate average order value:

• Operation: Ratio
• Input A: Amount with SUM aggregation
• Input B: Order ID with COUNT DISTINCT aggregation
• Result: SUM(Amount) / COUNT(DISTINCT Order ID)

Calculated Dimensions

Create custom groupings and categories using SQL expressions. Reference existing fields using token syntax: {FieldName}.

Expression Syntax

Use curly braces to reference fields in your expression:

CASE WHEN {Amount} > 100 THEN 'Large' ELSE 'Small' END

The {Amount} token references the Amount field from your dataset. Semaphor resolves these tokens to the actual column names when executing the query.

Creating a Calculated Dimension

1. Open the Explorer and click + Add Calculated Field
2. Select Calculated Dimension
3. Enter a name (e.g., "Order Size Bucket")
4. Write your SQL expression using {FieldName} tokens
5. Click Validate Expression to check for errors
6. Select the output type (String, Number, or Date)
7. Click Create Dimension

Common Expression Patterns

Categorize values into buckets:

CASE
  WHEN {Amount} > 1000 THEN 'Large'
  WHEN {Amount} > 100 THEN 'Medium'
  ELSE 'Small'
END

Extract date parts:

DATE_TRUNC('month', {Order Date})

Combine text fields:

CONCAT({City}, ', ', {State})

Handle null values:

COALESCE({Discount}, 0)

AI-Powered Expression Generation

Describe what you want in plain English, and Semaphor generates the SQL expression for you.

Using the AI Generator

1. Click Generate with AI in the expression editor
2. Describe your calculation in natural language
3. Review the generated expression
4. Click Use Expression to apply it, or Regenerate for a different approach

Example prompts:

• "Create buckets for order amount: small under 100, medium 100-500, large over 500"
• "Extract the quarter from the order date"
• "Calculate profit as revenue minus cost"

Expression Validation

Semaphor validates your expressions to catch errors before you save.

What Gets Validated

• Field references: All {FieldName} tokens must match available fields
• SQL syntax: Expression must be valid SQL for your database dialect
• Self-references: Cannot reference the field being edited (prevents circular references)
• Function support: Functions must be supported by your database

Validation Feedback

After clicking Validate Expression, you'll see:

• Tokens found: Which fields were detected and resolved
• Errors: Issues that must be fixed (unknown fields, syntax errors)
• Warnings: Suggestions for improvement (missing ELSE clause, division by zero risk)
• Inferred output type: The detected data type of the result

Example validation result:

Tokens Found:
  {Amount} -> orders.amount (number)
  {Quantity} -> orders.quantity (number)

Inferred Output Type: number

Common Validation Errors

Scope Options

Control where your calculated field is available.

Card Scope (Default)

The field is only available in the current card. Use this for one-off calculations specific to a single visualization.

Dashboard Scope

The field is available to all cards in the current dashboard. Use this when multiple cards need the same derived metric or dimension.

Domain Scope

Save the calculated field to your semantic domain, making it available across all dashboards that use that domain. This is ideal for standardized business metrics and commonly-used dimensions that should be consistent everywhere.

Permissions by User Type

When to use each scope:

What can be saved to domain scope:

Best Practices

Naming Conventions

• Use descriptive names that explain what the field represents
• Include units when relevant (e.g., "Revenue per Order (USD)")
• Follow your organization's naming standards for consistency

Expression Design

• Keep expressions simple: Break complex calculations into multiple calculated fields
• Handle edge cases: Use NULLIF to prevent division by zero, COALESCE for null handling
• Consider performance: Complex expressions on large datasets may impact query speed

Division by zero protection:

{Revenue} / NULLIF({Orders}, 0)

Null-safe calculations:

COALESCE({Discount}, 0) * {Amount}

Documentation

When saving to domain scope, write clear descriptions:

For metrics:

• What the metric measures
• How it's calculated
• When to use it
• Any caveats or limitations

For dimensions:

• What the dimension represents
• How values are categorized or derived
• When to use this grouping
• Dependencies on specific datasets or fields

Examples

Profit Margin Percentage

Type: Calculated Metric Operation: Ratio Inputs:

• A: Profit (SUM)
• B: Revenue (SUM)

Formula: SUM(Profit) / SUM(Revenue) Format: Percentage with 2 decimals

Customer Segment

Type: Calculated Dimension Expression:

CASE
  WHEN {Total Spend} > 10000 THEN 'Enterprise'
  WHEN {Total Spend} > 1000 THEN 'Mid-Market'
  ELSE 'Small Business'
END

Output Type: String

Year-over-Year Growth

Type: Calculated Metric Expression (custom):

(SUM({Current Year Revenue}) - SUM({Previous Year Revenue}))
/ NULLIF(SUM({Previous Year Revenue}), 0)

Format: Percentage

Fiscal Quarter

Type: Calculated Dimension Expression:

CONCAT('Q', EXTRACT(QUARTER FROM {Order Date}), ' ', EXTRACT(YEAR FROM {Order Date}))

Output Type: String