Calculated fields

Add a number or grouping your source doesn't have, written as a formula that runs on your database.

A calculated field is a field written as a formula over other fields, such as revenue per order. Once saved, it works like any other field in charts, filters and reports. You don't need SQL or the custom SQL permission to write one.

Add a calculated field

1. Open the dataset's Fields tab and choose Calculated field.

2. Give it a Label, such as Average order value.

3. Write the Formula. Refer to fields by label in brackets, like [Revenue]. Ctrl+Space lists fields and functions.

4. Read the summary under the editor and the Preview of its first values. Mistakes are underlined as you type, with a fix where there is one.

5. If the formula doesn't aggregate, choose its Role above the editor: Dimension to group by it, or Measure to add it up.

6. For a number, pick a Format. Labels that say share, margin, percent or a rate such as conversion rate start as Percent.

7. Choose Add field, then Save new version.

Not sure how to write it? Write with Halo above the editor takes a sentence, such as "average order value in dollars", and suggests a formula with an explanation. It's checked like any other, and you decide whether to use it.

Try one

These editors work like the one in Halo Data, over sample fields: Revenue, Orders, Cost, Customer, Region, Channel, Order date and Shipped at. Change a formula and see what the editor says.

A ratio across groups

Average order value divides the totals, not the rows. Because it uses aggregate functions, it's a measure, and every chart gets the right number at any level of grouping.

Dividing by zero gives an empty value rather than an error, so you don't need to guard against it.

Margin as a percentage

Parentheses group as you'd expect. Give this one the Percent format, so 0.34 shows as 34%.

A grouping from a condition

This one has no aggregate, so it's a row formula: Halo works it out for each row, and you can make it a dimension.

Bands with CASE

CASE takes the first condition that holds. Without an ELSE, anything left over is empty.

A value readers can change

A parameter is written @name. Here @target is a revenue target that reports can set, so a reader can try different targets without editing anything.

Add parameters under Report parameters on the Fields tab, each with a type and a default.

Row formulas and aggregates

Whether a formula has an aggregate function decides what kind of field it is.

The two kinds can't mix. In SUM([Revenue]) / [Orders], [Orders] has no single value per group, so the editor says it isn't aggregated and suggests aggregating it too. Aggregates can't nest either: SUM(SUM([Revenue])) is an error.

A rate is usually an aggregate: SUM([Revenue]) / SUM([Orders]) is correct at any level, while averaging a row-level ratio gives small orders the same weight as large ones.

Renaming and changing later

Halo stores formulas with field names and shows them with labels, so renaming a label never breaks a formula. To change a formula, open More settings on its row. Saving a definition with a broken formula fails with the same message the editor shows.

Using SQL instead

Members with the Write custom SQL permission also see Write it as, with Formula and SQL. A SQL field is an expression in the source's own dialect, such as a function your database has and formulas don't. A SQL measure should already aggregate, like sum(orders.amount).

Use a formula when you can. Formulas compile for each source and for extracts, while SQL runs as written, so a SQL field may need rewriting if the dataset moves to another source or fails on an extract that doesn't understand it.

The formula language covers the syntax, types and every function.