Formulas

A formula item computes its value from an expression written in the XCubes formula language. Formulas are attached to individual items in a dimension; the engine evaluates them when the cube is computed.

Basic expressions

A formula references sibling items by their code. Codes that start with a letter and contain only letters, digits, and underscores can be written bare. Numeric codes and codes with special characters must be wrapped in double quotes.

Revenue - COGS
"620200" + "22520"
"R&D" + "G&A"

The standard arithmetic operators +, -, *, / work as expected. Parentheses control precedence.

Summing multiple items

To sum a fixed list of items, write an explicit + chain:

ProductSales + ServiceSales + OtherRevenue

This is clearer and more predictable than SetSum(), which takes wildcard patterns (SetSum("Sales*")) and matches by code prefix. Reserve SetSum() for true wildcard aggregations ("everything starting with 6") rather than enumerated lists.

The `self` keyword

Some time-series functions accept the keyword self as their first argument — it refers to this same item at an adjacent period, so you don't repeat the item's own code. self is supported by Prior, Grow, Feed, FeedFrom, and YoY. For example, Prior(self; 1) reads this line's previous period, and Grow(self; 0.05) grows it 5% each period.

Functions that accumulate or read from the current period's own value — YTD, RollingSum, RollingAvg, Lag, PctChange — reject self, because referring to the value being computed would create a cycle. For those, pass the item's own code (e.g. YTD(Revenue)), or express same-line lookback with Prior(self) / Grow(self; …).

Function arguments use semicolons

Arguments to all functions are separated by semicolons (;), not commas:

Lag(Revenue; -1)
If(Condition; TrueValue; FalseValue)

(Lag's shift is signed — -1 is the previous data period, +1 the next.)

Item codes vs. reference names

Plain identifiers in a formula refer to sibling items in the same dimension. The functions Reference(), Prior(), PriorDelta() and Delay() take a reference name (a string in double quotes) that is resolved through the cube's registered cross-cube references — they do not refer to items. See Cube references.

How formulas behave at totals

When a formula item meets a total of another dimension — a quarter or year column, a scenario sum like ACT + PLAN, a subtotal row — the value in that cell depends on what kind of formula it is:

Additive formulas (Revenue - COGS, A + B + C) behave exactly as you'd expect: the total of the formula equals the formula of the totals. Both readings give the same number, so there is nothing to remember.
Ratio formulas (anything containing a division — margins, percentages, prices) are recomputed at the total from the totals of their inputs. Margin = GM / Revenue at the FY column is FY-GM ÷ FY-Revenue — the ratio of the sums — not the sum of the twelve monthly margin cells. For a margin or a percentage this is almost always the number you want; it just isn't the sum of the cells displayed above it.

Two related rules:

Hierarchy roll-ups only happen on hierarchical dimensions. A parent item on a freeform dimension never aggregates its children — give it a formula (ChildA + ChildB + …) or convert the dimension to hierarchical.
Plain (non-formula) items crossing a total follow their per-item aggregation setting: sum (default), average, weighted, opening, closing, or none. Use weighted for prices and rates so their totals stay meaningful; closing for a balance or a headcount (a quarter shows its last month) and opening for the opening line of a roll-forward (a quarter shows its first month's opening), so Opening + movements = Closing ties at every subtotal.

Declaring how a formula row rolls up

The rules above are a guess from the formula's shape, and the guess is wrong for an amount that merely carries a division: Salary / 12 * Months at the year total reads (year's Salary ÷ 12) × year's Months, not the sum of the months. A formula item's Roll-up at totals setting (in the item editor, alongside the formula) replaces the guess with a declaration:

Roll-up at totals	What the crossing total shows	Use it for
Automatic (the default)	the rules above: a formula with a `/` is recomputed from totals, any other adds up	formulas whose shape already says what they are
Add up the rows	the sum of the crossing's members, in every dimension, whatever the formula contains	an amount computed with a division — a monthly salary, a depreciation charge, a fee from a rate
Recompute from totals	the formula re-evaluated from its inputs' totals, in every dimension — including at a parent of a hierarchy, which otherwise always sums its children	a true ratio of two totals — a margin, a conversion rate, churn over an opening base

The other aggregation settings (average, none, closing, opening) work on a formula item too; use average or none for a per-row factor or rate that has no meaningful total. "Different by dimension" lets one row add up over entities and recompute over time.

Before any number looks wrong. The rule in short: a formula containing a division is recomputed at a total from the totals of its inputs, so that total is not the sum of the rows above it. To make a total add up the rows instead, declare Add up the rows on the formula row, or express the total as a hierarchy (a hierarchy parent always adds up its children). The calculation order (solve order) does not change this. Whenever an undeclared formula that divides meets a formula total of another dimension, such as TOTAL = SetSum() over a list of employees, the cube editor shows an amber notice naming the row and the total, with Add up the rows and It's a ratio (which declares Recompute from totals for that dimension and keeps the numbers as they are). Either choice records your intent and clears the notice. The cube's diagnostics list the same finding as RATIO_CROSSING_REEVALUATED.

When the guess is caught out. After a recompute, XCubes compares every automatically recomputed crossing with the members it stands for. A total that lies outside the range of its members — 900 over twelve cells that each read 200 — cannot be any weighted reading of them, so the automatic rule was wrong for that row. Such a cell carries an amber corner in the grid: hover it for the finding, click it to declare Add up the rows or Leave total blank (the aggregation setting none). The card does not offer Recompute from totals, because that is the reading the check just disproved; if the row really is a ratio over an opening or closing balance, declare it in the item editor instead. The same finding is listed by the cube's diagnostics and counted by check_assertions, so an agent sees it without opening the grid. A row that is already declared is never second-guessed.

If you genuinely need "the sum of the computed cells" for a ratio-like item, declare Add up the rows on it rather than modelling a second total.

Full function list

XCubes ships with functions for aggregation, math, logic, time-series navigation, and cross-cube lookups. The complete list with signatures and examples is in the Formula function reference.