Formula Syntax

What the formula editor is for

The formula editor lets you build a custom calculation from typed tokens — operators, numbers, and references to rows, drivers, sub-positions, and KPIs. Same editor, same syntax, on every Formula method anywhere in the app: Planning rows, Planning drivers, KPIs, and custom dashboards.

TL;DR

Type a number or operator to drop a token. Type a name to autocomplete a reference. Each reference carries an optional time offset (this month / previous month / 1 year ago / Custom) and, with the Departments add-on, a source scope for cross-department refs.

Tokens

A formula is a flat sequence of tokens. The grid renders each token as a chip; Backspace on an empty input removes the last chip.

Numbers

Type any number (100, 0.05, -1.2) and press Enter to drop it as a number token.

Operators

The five binary operators and parentheses:

+   -   *   /   ^   (   )

Type one with the input empty and the editor inserts it immediately. Type a sequence like (price * volume) / 12 and press Enter on each token.

Functions

Three functions are available. Type the name and the editor drops a function skeleton for you to fill in:

  • abs( ) — absolute value of a single expression, e.g. abs(Revenues - Plan).
  • min( , ) — the smaller of two expressions, e.g. min(Cash, 0).
  • max( , ) — the larger of two expressions, e.g. max(EBT, 0).

abs takes one argument; min and max take exactly two and compare values in the same period (they are not windowed).

References

Start typing a position, sub-position, account, driver, or KPI name. The autocomplete dropdown narrows on every keystroke; press Enter to insert the top match, or click any result to insert it directly. Inside the formula, the reference renders as a chip with the row label.

References are picked from a browse picker grouped by source: P&L, BS, CF, KPIs, plus drivers and helper sub-positions. With the Departments add-on, an additional source tab appears (see Cross-department references below).

Time offsets

Each reference carries a time offset — the period the reference reads from, relative to the current cell.

Click the offset pill on any reference chip to pick:

  • this month — month(0) (default)
  • previous month — month(-1)
  • 1 year ago — month(-12)
  • Custom — opens a modal where you set a month count and a direction (next / previous), so e.g. month(-3) or month(+6) are reachable

Time offsets are resolved at evaluation time: Revenues@month(-12) reads the Revenues value twelve months before the cell being computed.

Aggregation windows

Besides a time offset, each reference can carry a trailing-window aggregation — instead of reading a single period, the reference reads a rolling window and reduces it to one value. Click the aggregation control on a reference chip to pick:

  • Sum — totals the reference over the window (renders as e.g. Σ 12M).
  • Average — the mean of the reference over the window (renders as e.g. avg 3M).

The window length is L3M (last 3 months), L6M, L12M, or Custom (set your own month count). A Sum over L12M gives a trailing-twelve-month total; an Average over L3M smooths a noisy monthly series. Aggregation combines with the time offset — the window is measured back from the offset period.

Cross-department references

In contexts that span multiple departments (Departments add-on), the picker exposes an additional source tab above the reference list:

  • Departments — pick a specific department’s slice as the source. Cross-dept refs render with a short-code prefix on the chip so you can see which department is being read.
  • All departments — an aggregate option that sums the reference across every department.

The chip surfaces the source short-code only; the source tab in the picker is where you change it.

Editor controls

Inside the editor:

  • Enter — insert the typed token (number / operator / top autocomplete match), or commit the formula when the input is empty
  • Escape — discard the current edit
  • Backspace on empty input — remove the last chip

Validation

statycs validates formulas continuously. Errors surface as a red ! badge next to the formula, with a tooltip describing the cause:

  • Circular reference — the formula references its own row, directly. Transitive cycles (A → B → A across positions) are not detected today.
  • Broken reference — the row, sub-position, or driver the formula points at no longer exists. Open the formula and remove or replace the chip.
  • Division by zero — the denominator evaluates to zero in one or more periods. Wrap the denominator in a guard or pick a different reference.
  • Offset outside dataset — a time offset (e.g. month(-12)) points at a period before the start of the available data. Surfaces as a warning; either narrow the offset or accept the missing periods.

Errors don’t block saving. Affected cells evaluate to zero until the error is fixed, so a partially broken formula won’t stop you from continuing — it just won’t contribute a value.