Aggregations and Calculation Policies

A measure summarizes the source facts at one row/column coordinate. Choose its input, null and invalid-value policies explicitly. A measure’s label, dataType and number format do not change those policies.

Recommended local configuration

Use a V2 report with createPivotStrictAggregators({ input: 'number', invalid: 'error' }) for numeric fields and input: 'scalar' for text/Boolean counts and typed distinct values. Both presets skip null and missing values. Persist aggregationSemantics: 'local' alongside the operations. This configuration requires matching current Pivot and Pro packages.

Validate the fixture after installing your package artifacts: the expected grand average is 5 and distinct customers 3. Compilation/import success alone does not prove policy execution. A package build that preprocesses compiled callbacks as default numeric IDs can instead return 3.3333333333333335 and 1; that build does not preserve the declared input policies. Keep the accepted report and use artifacts that pass these controls. An execution pin does not detect a duplicated aggregate-runtime identity.

The helper returns portable V2 declarations, not a native callback map. Execute the report with a V2 controller or compile it with compilePivotReport(report, runtime, { version: 2, source }) before assigning the issued configuration to grid.pivot.

// docs-check: complete
import {
  createPivotController,
  createPivotStrictAggregators,
  type PivotReportV2,
} from '@revolist/pivot/headless';

const report: PivotReportV2 = {
  kind: 'revogrid:pivot.report',
  version: 2,
  aggregationSemantics: 'local',
  fields: [
    { id: 'region', source: { kind: 'physical', prop: 'region' } },
    { id: 'amount', source: { kind: 'physical', prop: 'amount' },
      aggregators: createPivotStrictAggregators({ input: 'number', invalid: 'error' }) },
    { id: 'customer', source: { kind: 'physical', prop: 'customer' },
      aggregators: createPivotStrictAggregators({ input: 'scalar', invalid: 'error' }) },
    { id: 'row', dataType: 'number',
      source: { kind: 'expression', expression: { type: 'literal', value: 1 } },
      aggregators: createPivotStrictAggregators({ input: 'number', invalid: 'error' }) },
  ],
  rows: ['region'], columns: [], filters: [],
  measures: [
    { id: 'total', fieldId: 'amount', aggregator: 'sum' },
    { id: 'average', fieldId: 'amount', aggregator: 'avg' },
    { id: 'numericValues', fieldId: 'amount', aggregator: 'count' },
    { id: 'customerValues', fieldId: 'customer', aggregator: 'count' },
    { id: 'customers', fieldId: 'customer', aggregator: 'distinct' },
    { id: 'rows', fieldId: 'row', aggregator: 'count' },
  ],
  totals: { grandTotal: true, subtotals: true },
};
const source = [
  { region: 'North', amount: 10, customer: 'Alice' },
  { region: 'North', amount: null, customer: 'Bob' },
  { region: 'South', amount: 0, customer: '' },
];
const controller = createPivotController({ reportVersion: 2, report, source });
try {
  const outcome = await controller.refresh();
  if (outcome.status !== 'committed') throw new Error('Report did not commit');
  console.table(controller.getSnapshot().view?.model.pinnedBottomSource);
  // Grand total: sum 10; average 5; numeric values 2;
  // customer values 3; distinct customers 3; rows 3.
} finally {
  controller.dispose();
}

Interactive example

Aggregations

Use sum/min/max/avg/count and repeated source measures

The example loads when visible.

Reset

The preview demonstrates the configured measure layout. Its source/configuration is a separate example; use the explicit policy above when defining a report’s input contract.

Null, empty, invalid and zero

Input	input: 'number' preset	input: 'numeric-string' preset	input: 'scalar' preset
null, undefined, missing property	Skip	Skip	Skip
0	Eligible number	Eligible number	Eligible number
'', whitespace-only text	Invalid	Invalid	Eligible text; preserved exactly
'2', ' -2e3 '	Invalid	Eligible numeric value	Eligible text; preserved exactly
false, true	Invalid	Invalid	Eligible Boolean
NaN, Infinity, objects, Date, BigInt, Symbol	Invalid	Invalid	Invalid

Invalid values fail execution by default. Select invalid: 'skip' only when excluding those observations is intended. Numeric-string input accepts trimmed decimal/scientific syntax, including .5 and 1.; hexadecimal and nonfinite text are invalid. A strict numeric aggregate never calls application object coercion.

For individual operation declarations, operation.nulls: 'include' is supported with scalar input for count, distinct, first, last, mode and runtime callbacks. Null and missing then represent null occurrences; count includes each occurrence and distinct counts one null identity. Numeric aggregates do not support including null as a numeric observation. An absent row/column intersection can remain absent/blank rather than becoming an explicit zero-count bucket; an existing all-null bucket is a different case.

Count rows, values and distinct values

Question	Configuration	Meaning
How many facts?	Count the literal-one expression field in the complete example	Every contributing source occurrence, regardless of another field’s value
How many non-null scalar values?	Scalar count, nulls skipped	Includes empty strings, whitespace and Booleans
How many numeric values?	Numeric count	Counts only eligible numeric inputs under the selected invalid policy
How many distinct typed values?	Scalar distinct	1, '1' and true differ; 0 and -0 agree; text comparison is case-sensitive
How many SQL rows?	count on an authorized guaranteed-nonnull fact ID	The standard protocol uses a selector; count(amount) is a non-null amount count, not COUNT(*)

A constant row expression does not validate an unused amount field. Local typed distinct uses SameValueZero identity. SQL distinct uses the backend’s type/collation rules and must be tested separately.

Current defaults without an operation declaration

Omitted/empty dimensions[].aggregators uses commonAggregators. A nonempty map replaces that field’s registry; every requested operation must be present. V1 and V2 builtin declarations without operation use these callbacks. Physical dataType is descriptive and does not enable strict validation.

For the default numeric IDs sum, avg, min, max, median, range, variance, stdDev and distinct, native Pivot applies Number(value) and keeps finite inputs. Consequently null, empty/whitespace text and false become zero, true becomes one, and numeric strings become numbers. Undefined and other nonfinite conversions are discarded; conversion itself can throw or invoke an object’s coercion callback. Default count counts input occurrences, including null, missing and invalid values. Default numeric distinct does not count distinct customer names.

On [10, null], default AVG is 5; numeric preset AVG is 10. On the 24-fact fixture with sum 396 and six null amounts, these policies yield 16.5 and 22, respectively. Both are supported policies; choose and label the intended meaning.

ID preprocessing is based on the authored ID. An alias without an operation does not automatically receive its builtin’s preprocessing. A compiled operation selects by builtin identity, preserves its explicit policy and receives raw observations. Prefer the presets instead of relying on alias/coercion interactions.

For a newly added stock measure, a declared string, Boolean or date field defaults to count; numeric/unknown fields default to sum. A configured nonempty catalog instead selects its first operation. Numeric presets start with sum; scalar presets start with count. Specify each report measure’s aggregator explicitly.

Aggregate results at each grain

Operation with an explicit numeric/scalar contract	Empty eligible input	Result
sum, count, distinct	0	Eligible sum, occurrence count or typed distinct count
avg, min, max, median, range	null	Numeric statistic
variance, stdDev	null	Population statistic, ddof 0
varianceSample, stdDevSample	null	Sample statistic, ddof 1; at least two eligible observations
first, last, mode	null	Original eligible type/order; mode chooses the first source occurrence in a frequency tie

Leaves, subtotals and grand totals each aggregate their contributing original facts. They do not average displayed averages or sum distinct counts. For groups containing [10] and [20, 20, 20], the grand average is 17.5, not 15. Configure independent group/total operations only when those grains intentionally use different measures; those overrides are local-only.

Use createPivotWeightedAverage({ id, valueFieldId, weightFieldId }) for a paired weighted average. Add its returned fields and measures to a V2 report. Values [10, 20] with weights [1, 3] yield 70/4 = 17.5 at the total grain. Only jointly eligible pairs contribute to both numerator and denominator; missing pairs are excluded, zero/net-zero weights produce null when the denominator is zero, and nonfinite products are excluded. This recipe requires finite-number/null dependencies and is local-only. A general formula dividing two independent sums is not the same eligibility rule.

Percentages and running percentages

values[].calculation transforms an aggregate; it does not change fact eligibility. Portable field-based calculations use fieldId, while native configurations use field. Formula operands use stable measure IDs.

Calculation	Result
percentOfGrandTotal	Current aggregate / grand aggregate
percentOfRowTotal, percentOfColumnTotal	Current aggregate / its corresponding total
percentOfParentRowTotal, percentOfParentColumnTotal	Current aggregate / its immediate parent aggregate
percentDifferenceFrom	(Current − comparison) / comparison
percentRunningTotal	Prefix of ordered peer aggregates / sum of that peer sequence
index	Current × grand / (row total × column total)

Ratios are numeric values: 0.5 means 50%; the standard percentage formatter displays it as 50%. Missing/nonfinite inputs and zero denominators produce null. A missing comparison or a coordinate outside the selected field’s peer sequence also produces null. Running calculations follow the analytical peer order and hierarchy scope, not viewport pages; sparse descendant positions stay aligned. They do not sum duplicated subtotal rows into the peer sequence. For average measures, running percentage sums peer averages; it is not a cumulative average over raw facts.

Hidden total presentation does not remove the aggregate denominators required by calculations. Hiding/expanding a branch changes presentation, not fact scope. Source filters select contributing facts; result filters select aggregate groups and test the total independently. Do not assume the total is the sum of visible retained groups. See Totals.

A finite percentage difference between −1e308 and 1e308 is −2. Equal 1e308 peers have running percentages 0.5 and 1 even when the intermediate raw sum overflows. A plain unrepresentable difference or running total is null. No arbitrary-precision or exact cancellation guarantee is provided.

Execution pins and server support

aggregationSemantics: 'local' | 'server' is a top-level native configuration or V1/V2 report property. Plugin/controller preparation and report compilation/migration reject a pin that disagrees with the selected engine before adapter loading. Server mode, any adapter, or any remote store selects the server path; otherwise preparation selects local execution. Reports without a pin remain supported and unpinned.

A pin prevents accidental engine switching. It does not guarantee identical local/server results, change null/count policies, validate backend collation or certify an adapter. Persist both the pin and explicit operation declarations. Direct local model, cooperative-work and ClientPivotEngineAdapter entry points also enforce the pin before reading facts. These APIs validate their actual local execution path even when the configuration contains server-looking engine metadata.

The standard remote vocabulary is sum, min, max, avg, count, distinct, subject to the trusted field registry and advertised per-field capability profile. Median, population/sample statistics, versioned local operations, custom local callback semantics, independent group/total operations and paired weighted recipes are not standard server summaries. A standard ID does not promise that every backend implements it.

Post-aggregate server calculations require both the corresponding engine capability and an adapter/backend that implements the operation at complete analytical scope. calculations: true or an explicit calculation list advertises support; it does not run local calculation code over remote pages. The flat SQLite baseline rejects post-aggregate calculations. Its trusted hierarchy and analytical opt-ins now support their declared prefix/totals and calculation/ranking/sorting profiles; date grouping and independent local operations remain excluded. See current reference profiles. See Remote API and Backend validation.

Ordinary HTTP load, full-export forwarding and SQL planning reject unknown summary properties, including local operation policies and arbitrary metadata. The standard server adapter checks full-export summaries before calling an application store; the HTTP store checks them before transport. Backends must independently validate authorization, policies and supported calculations. See Complete export.

Precision, persistence and existing reports

Calculations use JavaScript IEEE-754 numbers, not decimal accounting or arbitrary precision. Strict aggregate nonfinite output fails execution; genuinely nonfinite derived results are null. Default callbacks can retain nonfinite output. CSV/XLSX and chart projection do not improve numerical precision. Custom callbacks retain their own arithmetic obligations.

Preserve existing policies when adding a pin to a saved report. Converting a configuration into a report does not convert its callbacks into strict operations. Review the report’s KPIs before deliberately replacing aggregate declarations. Persist a candidate only after successful execution; keep the accepted report when validation or calculation fails. V2 operation documents use explicit V2 parsing/import, while V1 saved-report helpers accept V1 only. Readers must support the pin property; readers with a closed schema that lacks it reject pinned documents. See Report specifications and Migrations.

Native callback maps

For a native PivotConfig, compile operation callbacks instead of assigning portable declarations to dimensions[].aggregators:

// docs-check: complete
import { compileAggregateOperation, type AggregateOperationV1 } from '@revolist/revogrid-pro/aggregations';
import type { PivotConfig } from '@revolist/pivot';

const operation: AggregateOperationV1 = {
  version: 1, input: { type: 'finite-number' }, nulls: 'skip',
  invalid: 'error', output: { type: 'finite-number' },
};
const pivot: PivotConfig = {
  aggregationSemantics: 'local', rows: ['region'], columns: [],
  dimensions: [
    { prop: 'region' },
    { prop: 'sales', aggregators: {
      sum: compileAggregateOperation({ builtin: 'sum', operation }),
      avg: compileAggregateOperation({ builtin: 'avg', operation }),
    } },
  ],
  values: [{ id: 'averageSales', prop: 'sales', aggregator: 'avg' }],
};
// Assign pivot to grid.pivot after installing PivotPlugin and the source records.

A nonempty callback map replaces built-ins for that field. Register every operation that its measures and authoring controls need. Use stable measure IDs when a source field appears more than once. Custom functions belong in the local map or a trusted runtime binding; a function name alone does not configure a server implementation.

Calculation FAQ

Why do two AVG configurations return 16.5 and 22?

The source sum is 396. Default numeric AVG treats six null facts as zero and divides by 24; the numeric preset skips those facts and divides by 18. Neither formatting nor an execution pin changes that policy. Choose it in the operation.

Does count mean rows?

Default count counts input occurrences. A numeric preset counts eligible numeric values; a scalar preset counts non-null scalar values, including empty text. Use the constant-one expression example for an explicit all-fact row count.

Can I move a saved local report to a server by changing its engine?

A matching execution pin rejects accidental rebinding. An intentional switch requires reviewing supported operations and the backend’s null/count/totals contract. It does not guarantee identical results. Keep the accepted report until the new candidate executes and its known KPIs are verified.

Can I compute totals or complete exports from displayed cells?

No. Recompute nonadditive totals from contributing facts at each grain and obtain complete server scope through the backend. A viewport is a window. Averages, distinct counts and paired weighted measures cannot be summed from displayed cells.