Calculated fields

A field whose value is computed rather than read from the row. There is no separate type and no registry — it is an ordinary PivotField carrying five extra members, pushed into the same fields array as everything else.

The compiler is free; the designer modal is Enterprise. If you are defining fields in code, everything on this page works in the Community package.

Row-level or summary-level — decide first

This is the one decision that matters, and getting it wrong gives you a column of plausible-looking wrong numbers.

A ratio or a percentage must be summary-level. A row-level margin computes per-row percentages and then adds them together, because the engine's default summaryType is sum. EMEA's 60% and APAC's 37.5% become 97.5%.

In code

import { compileSafeFormula } from '@kanunilabs/pivotgrid-core';
import type { PivotField } from '@kanunilabs/pivotgrid-react';

const revenue: PivotField<Sale> = {
  id: 'revenue', dataField: 'revenue', caption: 'Revenue',
  dataType: 'number', area: 'data', areaIndex: 0, summaryType: 'sum',
};

// Row-level: a plain function, aggregated afterwards like any measure.
const profit: PivotField<Sale> = {
  id: 'profit', caption: 'Profit', dataType: 'number',
  area: 'data', areaIndex: 1,
  summaryType: 'sum',          // state it — the default is 'sum' either way
  isCalculated: true,
  calculateExpression: (row) => row.revenue - row.cost,
  format: { style: 'currency', currency: 'USD' },
};

// Summary-level: the correct shape for a ratio.
const MARGIN = '([Revenue] - [Cost]) / [Revenue] * 100';
const fieldMap = new Map([['Revenue', 'revenue'], ['Cost', 'cost']]);

const margin: PivotField<Sale> = {
  id: 'margin', caption: 'Margin %', dataType: 'number',
  area: 'data', areaIndex: 2,
  isCalculated: true,
  formulaType: 'summary',
  formulaString: MARGIN,
  calculateSummaryExpression: compileSafeFormula(MARGIN, fieldMap)!,
  format: { style: 'decimal', maximumFractionDigits: 2 },
};

Give every field an id equal to its dataField. Row formulas bind on dataField || id, summary formulas bind on id. Keeping them identical means one fieldMap serves both, and moving a formula between the two levels stops being a rewrite.

formulaString and formulaType are what gets recompiled on reload — a bare calculateExpression function does not survive a page refresh. Supply both if the field should persist.

The formula language

The whole grammar:

numbers        1  2.5  100
field refs     [Revenue]        — by CAPTION, in square brackets
operators      +  -  *  /  %
grouping       (  )

That is all of it. There are no functions — no SUM, IF, ABS, ROUND, MIN, MAX — no comparisons, no ternaries, no strings, no dates, and no way to reference another cell. It is a hand-written recursive-descent parser compiled to a closure tree, so nothing goes near eval or new Function.

Validate before you commit a formula:

import { testSafeFormula } from '@kanunilabs/pivotgrid-core';

try {
  testSafeFormula(input, fieldMap);   // throws with a message
} catch (err) {
  setError((err as Error).message);
}

compileSafeFormula returns null instead of throwing — use it when you have already validated, and testSafeFormula when a human is typing.

The designer modal (Enterprise)

Passing renderCalculatedFieldModal is what makes the footer's calculator button appear. EnterprisePivotGrid does this for you.

import { EnterprisePivotGrid } from '@kanunilabs/pivotgrid-react-enterprise';

<EnterprisePivotGrid
  licenseKey={key}
  data={data}
  initialFields={fields}
  onFieldsChange={(next) => {
    // The only signal that a user added, edited or deleted a calculated field.
    persist(next.filter((f) => f.isCalculated));
  }}
/>

onSave hands you a finished PivotField with isCalculated, formulaString and the compiled function already in the right slot.

What this does not do

• A saved report destroys every calculated field. Reports are persisted by JSON.stringify, which drops the compiled functions, and loading one never recompiles from formulaString. The column comes back as zeros, with no error. And it is not opt-in: a default report is loaded after the state store has correctly rehydrated, so a user who set one loses their calculated fields on every page load. Persist formulaString + formulaType yourself from onFieldsChange if these must outlive the browser.
• Formulas bind by CAPTION. Renaming a field silently invalidates every formula that references it, and the field settings dialog lets a user rename anything with no cross-check.
• A typo reports the wrong error. An unknown caption comes back as "Formula contains unsafe characters", not "unknown field".
• Everything non-numeric becomes zero. Every field read is Number(x) || 0, so null, missing and text all collapse to 0 rather than to a blank cell. Division and modulo by zero also yield 0.
• The modal never sets summaryType. A ratio created through the designer is therefore summed. Create ratios in code, or edit the field afterwards.
• A summary expression cannot read another summary expression. The context is a snapshot taken before any expression writes to it.
• Row-level expressions run on the main thread. They are evaluated during column encoding, before the data is handed to the worker — functions cannot be serialised across that boundary. A heavy row expression is felt directly.
• Only data-area fields are visible to a summary expression. Row and column fields are not in the context.

Without a licence key the modal still works, watermarked.