The formula language

Halo's formula syntax in full: fields, values, operators, types, missing values, aggregates, parameters and every function.

A formula reads like a spreadsheet formula, refers to a dataset's fields and report parameters, and compiles to your source's SQL. It needs no SQL permission, and runs the same on PostgreSQL, Redshift and Dremio, and in DuckDB for extracts and uploaded files. Other sources say "Formulas can't run on … sources yet" until they're supported.

SUM([Revenue]) / SUM([Orders])
IF([Region] = 'North', 'Northern', 'Elsewhere')
IF(WEEKDAY([Order date]) >= 6, 'Weekend', 'Weekday')
SUM([Revenue]) / @target

Fields

Refer to a field by its label in brackets or by its name.

• [Average order] finds the field labeled Average order. Labels always go in brackets.

• revenue finds the field named revenue. A name of letters, digits and underscores that doesn't start with a digit needs no brackets; any other name does, as in [2026 target].

• Case doesn't matter when only one field fits, so Revenue finds revenue.

• Names come first. If one field is named region and another is labeled Region, [Region] is the one named region.

• If two fields share a label, the editor asks you to write the one you mean by name.

• Inside brackets, ]] writes a ].

Saving stores each reference by name and Halo Data shows it with the current label, so renaming a label never breaks a formula. Completion (Ctrl+Space) matches labels and names.

A field's value is its value in each row: [Revenue] - [Cost] subtracts row by row. A field that's already an aggregate, such as another aggregate formula, is that aggregate.

Values

Text next to a date is read as a date, so [Order date] >= '2026-06-01' works. Every value in a formula reaches the source as a bind parameter, never as SQL text.

// starts a comment that runs to the end of the line.

Operators

From loosest to tightest:

Parentheses group. Comparisons take two values at a time. / always gives a number, never whole-number division.

+ works on numbers only. Join text with CONCAT, move dates with DATE_ADD and count between them with DATE_DIFF.

Conditions

IF(condition, then, else), IFNULL, COALESCE and NULLIF are functions. CASE is syntax, in two forms:

CASE WHEN [Orders] >= 1000 THEN 'Busy'
     WHEN [Orders] >= 100 THEN 'Steady'
     ELSE 'Quiet' END

CASE [Region] WHEN 'North' THEN 'Northern' ELSE 'Elsewhere' END

All results share one type.

Types

Formulas use the field types, in words: number, whole number, text, true/false, date, and date and time. Whole numbers widen to numbers and dates to dates and times where they meet, so IF(x, 1, 2.5) is a number. A formula field's type is its formula's: Halo Data shows it as you type, and saving sets it.

Missing values

A missing value is NULL in a formula and shows as a dash in a chart or table.

• Aggregates skip it: SUM, AVG, MIN, MAX, MEDIAN, COUNT and COUNT_DISTINCT ignore missing values. COUNT_ROWS() counts every row.

• Dividing by zero gives a missing value, not an error, so SUM([Revenue]) / SUM([Orders]) needs no guard.

• = NULL is never true, so it's an error that suggests IS NULL.

• IF takes its else when the condition is missing.

• CASE without an ELSE is missing when nothing matches.

• IFNULL(value, fallback) and COALESCE(a, b, …) swap in a value. NULLIF(value, other) makes one missing, such as an empty string.

Aggregates and row formulas

• A formula with an aggregate function (SUM, AVG, MIN, MAX, COUNT, COUNT_DISTINCT, COUNT_ROWS, MEDIAN, PERCENTILE) is a measure. It has no aggregate of its own, charts can't re-aggregate it, and it can't be a dimension.

• A formula without one is a row formula: a dimension, or a measure that charts aggregate (sum by default for numbers, count distinct otherwise).

• Aggregates and row values can't mix, and aggregates can't nest. Literals and parameters fit either.

Parameters

A parameter is a value reports set, written @name, such as SUM([Revenue]) / @target. Declare them on the dataset's Fields tab under Report parameters, each with a label, a name (lowercase letters, digits and underscores), a type and a default. A report's value takes over from the default, and a value that doesn't fit the type is an error.

Parameters work in row formulas and aggregates alike. See Filters and parameters for the report side.

Dates and time zones

Dates and times reach formulas as wall-clock time in the report's time zone, so HOUR([Shipped at]) is the local hour. TODAY() and NOW() are the report's today and now.

DATE_TRUNC follows your organization's calendar: 'week' starts on its week start, 'fiscal_quarter' and 'fiscal_year' on its fiscal year, and 'iso_week' on Monday. WEEK() is always the ISO week and WEEKDAY() runs from 1 for Monday to 7 for Sunday. See Dates, time zones and fiscal calendars.

When a formula has a mistake

Formulas are checked as you type. Each mistake is underlined and says what to do:

• "SUM needs a number; Region is text." with "To count text values, use COUNT or COUNT_DISTINCT."

• "No field is named revnue. Did you mean revenue?"

• "DATE_TRUNC doesn't know the unit months. Did you mean 'month'?"

• "These formulas use each other in a loop: Margin → Cost ratio → Margin. Break the loop in one of them."

A formula that refers to a broken one says which field to fix. Saving a definition with a broken formula fails with the same message.

Functions

Every function, with its signatures and examples. Search by name or by what it does.