Formula basics
Formulas define how a node is calculated in a model. They combine node references, operators, and functions to calculate values across time and other business dimensions.
Learn the core syntax here before diving into the full Operators reference and the Function catalog.
The building blocks
- Node references
- Wrap node names in single quotes:
'Revenue'. Typing a'in the Function Editor lists the available nodes, including nodes from submodels. - Constants
- Write numbers directly in formulas:
0,1.2,100000. Numbers have no dimensions; they are a single value. - Operators
- Arithmetic
+,-,*,/; comparisons=,!=,<,<=,>,>=; and boolean logicAND,NOT,OR,XOR,TRUE,FALSE. Operators can be nested and combined with functions. - Functions
- Functions are named operations that transform, filter, or compare values. Names are case-sensitive and written in uppercase, for example
EXPAND,FILTER,IF, take comma-separated arguments inside parentheses, and are evaluated inside out. - Dimensions, levels,
and level values - Dimensions and levels describe which business axes a value is defined on and at what detail. Use double quotes for dimension names, level names, and level values:
"Time","Year","2026". - Value lists
- Use square brackets for lists of values:
["EMEA", "APAC"]. - Project variables
- Use project variables as placeholders in functions. Type
$to select a variable from the list, for example$FORECAST_START. - Comments
- Use
#to add comments in the function field.
Editor assistance
The Function Editor actively helps you write correct formulas.
Context-aware autocomplete. As you type, the editor suggests the values that make sense in that position, following the dimension → level → level value hierarchy. Beyond node references, it now completes dimensions, levels, level values, data sources, tables and measures, models, and project variables — each labeled with where it comes from, for example “Level in dimension” or “Measure in table”.
Inline signature help. While your cursor is inside a function’s parentheses, the editor shows that function’s parameters, so you can see what each argument expects as you type.
Human-readable error messages. When a formula has a problem, the editor explains it in plain language instead of a generic syntax error, for example:
- Unknown functions, with a “did you mean …?” suggestion for a close match.
- Unbalanced brackets or parentheses — a missing or extra
]or). - Unclosed quotes — a missing closing
"or'. - Missing separators, such as a comma between arguments.
- Unknown dimensions, levels, nodes, data sources, or project variables, with hints when a name needs single instead of double quotes.
Common formula shapes
Arithmetic
Combine node values directly with operators.
'Revenue' - 'COGS'
Data reference
Pull uploaded or source data into a node.
DATA("ERP", "Actuals", "Volume")
Reshaping
Add dimensional detail with one or more levels.
EXPAND('Sales', "Product")
Filtering
Keep only the rows that match a set of level values.
FILTER('Sales', "Region", ["EMEA", "APAC"])
Conditional logic
Return one value where a condition holds, another where it does not.
IF('Margin' < 0, 0, 'Margin')
Related
- Operators: arithmetic, comparisons, and boolean logic used in formulas
- Function catalog: all functions (A-Z and browse by category)
- Troubleshooting guide: wrong shape, missing values, and common pitfalls