Cheat sheet

For every global, helper signature and token in one place, see the Formula API reference.

Dynamic column vs. aggregated field

	Dynamic column (+)	Aggregated field (Σ)
Runs	once per row	once per data source
{[Hours]} is	this row's value	the list of all values
Result	a column	one value (KPI, threshold)
Typical	{[Hours]} * {[Rate]}	Sum({[Hours]})
Uses an aggregated field?	becomes a late column	fine

Syntax

{[Field]}                       // a field (insert with click or @)
{[A]} * {[B]} + 1               // arithmetic
Math.round(x * 100) / 100       // round to 2 decimals
cond ? 'yes' : 'no'             // if / else
a === b   a !== b   && || !     // compare, and, or, not
{[X]} ?? 'default'              // fallback when empty
obj?.prop                       // safe property access
`${{[A]}} - ${{[B]}}`           // text template (double braces)
(() => { const x = 1; return x; })()   // multi-line logic

Dates (Luxon)

DateTime.fromISO({[Date]})                          // text -> date
DateTime.now()
DateTime.fromISO({[Date]}).toFormat('dd/MM/yyyy')
DateTime.fromISO({[Date]}).plus({ days: 30 })
Interval.fromDateTimes(start, end).length('days')

Aggregation helpers

Sum  Average  Median  Min  Max  Count  CountDistinct  First  Last
SumIf(values, criteriaValues, 'Open')
SumIf(values, criteriaValues, (v) => v !== 'Done')
AverageIf(values, criteriaValues, criterion)
CountIf(criteriaValues, criterion)

Reference only child "Tasks"

{[Tasks_MergedData_]}           // all task rows
{[Tasks_Hours]}                 // all task hours (list)
{[Tasks_<Total hours>]}         // the child's aggregated field (value)

Rows

Rows.find(rows, 'Field', value)        // first match or null
Rows.filter(rows, 'Field', value)      // all matches
Rows.count(rows, 'Field', value)
Rows.sum(rows, 'Field')   Rows.avg   Rows.min   Rows.max

// the pattern you'll use most:
Rows.sum(Rows.filter({[Tasks_MergedData_]}, 'ProjectNo', {[ProjectNo]}), 'Hours')

Remember

• Insert fields by clicking them. Dynamic and aggregated fields have generated internal names.
• Field names in Rows calls and inside rows are internal names in quotes: 'ProjectNo'.
• 1 ≠ "1": convert with Number() / String().
• Reference data is read-only: [...list].sort(...).
• Aggregated fields ignore dashboard filters.
• The editor previews aggregated fields over the preview rows only.