React Embedded AnalyticsExpressions

Expressions allow new columns to be generated off of the source data.

const expressionFields = [
    {
        id: 'revenue',
        isMeasure: false,
        expression: {
            operator: 'multiply',
            inputs: [
                { id: 'sales.unitPrice' },
                { id: 'sales.quantity' },
            ],
        },
    },
];

Expression fields are defined on the Data Source as an array of AgExpressionFieldDefinitions. Each expression field consists of an field definition (similar to a normal Field Definition), along with the expression itself.

See Below for the expression field API.

Calculated Columns & Measures Copy Link

An Expression Field can produce two different outputs:

Calculated Column - For example Profit = Revenue - Cost.
Measure - For example Total Profit = SUM(Revenue - Cost).

Calculated Columns Copy Link

Calculated columns produce multiple outputs for multiple inputs, and can therefore be aggregated in the UI.

You must specify isMeasure: false for Calculated Columns.

const expressionFields = [
    {
        id: 'profit',
        isMeasure: false,
        expression: {
            operator: 'multiply',
            inputs: [
                {
                    operator: 'subtract',
                    inputs: [
                        { id: 'sales.unitPrice' },
                        { id: 'sales.unitCost' },
                    ],
                },
                { id: 'sales.quantity' },
            ],
        },
    },
    // ...
];

Measures Copy Link

Measure columns produces a single output when given multiple inputs and therefore cannot be aggregated in the UI.

You must specify isMeasure: true for Measures.

const expressionFields = [
    {
        id: 'totalProfit',
        isMeasure: true,
        expression: {
            operator: 'subtract',
            inputs: [
                { id: 'sales.unitPrice', aggregation: 'sum' },
                { id: 'sales.unitCost', aggregation: 'sum' },
            ],
        },
    },
    // ...
]

Expression Types Copy Link

An expression is of one of three types:

Function - A function applies an operator to one or more inputs.
Value - A value is a fixed value (e.g. number, string, etc.).
Field - A field refers to another field (either in the source data, or another expression field).

Function Expression Copy Link

const functionExpression = {
    operator: 'multiply',
    inputs: [
        { id: 'sales.unitPrice' },
        { value: 100 },
    ],
};

AgFunctionExpression

A function expression applies an operator to one or more inputs. Each input is another expression - a function, value or field.

See Below for the list of function expression operators.

Value Expression Copy Link

const valueExpression = {
    type: 'number',
    value: 100,
};

AgValueExpression

A value expression is a fixed value (e.g. number, string, etc.).

Field Expression Copy Link

const fieldExpression = {
    id: 'sale.profit',
    aggregation: 'sum',
};

AgFieldExpression

A field expression refers to another field (either in the source data, or another expression field).

API Copy Link

Expression Field Definition Copy Link

TFormat

The format type of the field (provides default formatting, etc.). If not provided, will be inferred from the expression.

boolean

Whether this expression creates a Measure or a Calculated Column. A Calculated Column produces a list of values, e.g. quantity × cost, even without a grouping. A Measure produces a single value e.g. SUM(quantity).

AgExpression<TRegistry>

The expression for the field.

string

Field ID.

string

Display name.

string

Field description. Displayed in the Field Panel

boolean

Set to true to hide from being selected in the UI. Field can still be used for joins.

boolean | AgFieldEditableKey[]

Controls whether the field can be edited in the UI.

AgFieldSerializer<InferDataTypeFromFormat<TRegistry, TFormat>>

Optional. How the field values will be serialized into state. Defaults to format serializer.

AgFieldDeserializer<InferDataTypeFromFormat<TRegistry, TFormat>>

Optional. How the field values will be deserialized from state. Defaults to format deserializer.

AgFieldValueFormatterFactory<InferDataTypeFromFormat<TRegistry, TFormat>, TFormatOptions>

Optional. Build a value formatter bound to the field's format options and the runtime API. Defaults to format factory.

string

Optional. How blank values will be displayed. Defaults to format blank value.

TFormatOptions

Optional. Will be passed to the value formatter.

Function Expression Operators Copy Link

AgFunctionExpression<TRegistry, 'add'>

Add two values together.

['number', 'number'] => 'number'

['string', 'string'] => 'string'

AgFunctionExpression<TRegistry, 'subtract'>

Subtract the second value from the first.

['number', 'number'] => 'number'

AgFunctionExpression<TRegistry, 'multiply'>

Multiply two values together.

['number', 'number'] => 'number'

AgFunctionExpression<TRegistry, 'divide'>

Divide the first value by the second.

['number', 'number'] => 'number'

AgFunctionExpression<TRegistry, 'modulo'>

Remainder of the first value divided by the second.

['number', 'number'] => 'number'

AgFunctionExpression<TRegistry, 'equals'>

Are the two values equal?

['number', 'number'] => 'boolean'

['date', 'date'] => 'boolean'

['datetime', 'datetime'] => 'boolean'

['boolean', 'boolean'] => 'boolean'

['string', 'string'] => 'boolean'

AgFunctionExpression<TRegistry, 'notEqual'>

Are the two values not equal?

['number', 'number'] => 'boolean'

['date', 'date'] => 'boolean'

['datetime', 'datetime'] => 'boolean'

['boolean', 'boolean'] => 'boolean'

['string', 'string'] => 'boolean'

AgFunctionExpression<TRegistry, 'lessThan'>

Is the first value less than the second?

['number', 'number'] => 'boolean'

AgFunctionExpression<TRegistry, 'greaterThan'>

Is the first value greater than the second?

['number', 'number'] => 'boolean'

AgFunctionExpression<TRegistry, 'lessThanOrEqual'>

Is the first value less than or equal to the second?

['number', 'number'] => 'boolean'

AgFunctionExpression<TRegistry, 'greaterThanOrEqual'>

Is the first value greater than or equal to the second?

['number', 'number'] => 'boolean'

AgFunctionExpression<TRegistry, 'and'>

Are both the values true?

['boolean', 'boolean'] => 'boolean'

AgFunctionExpression<TRegistry, 'or'>

Are either of the values true?

['boolean', 'boolean'] => 'boolean'

AgFunctionExpression<TRegistry, 'not'>

Negates the value.

['boolean'] => 'boolean'

AgFunctionExpression<TRegistry, 'negate'>

Negates the value.

['number'] => 'number'

AgFunctionExpression<TRegistry, 'if'>

If the first value is true then return the second value, else return the third value.

['boolean', 'string', 'string'] => 'string'

['boolean', 'number', 'number'] => 'number'

['boolean', 'boolean', 'boolean'] => 'boolean'

['boolean', 'date', 'date'] => 'date'

['boolean', 'datetime', 'datetime'] => 'datetime'

AgFunctionExpression<TRegistry, 'in'>

Is the first value in any of the subsequent values?

['string', ...'string'] => 'boolean'

['number', ...'number'] => 'boolean'

['boolean', ...'boolean'] => 'boolean'

['date', ...'date'] => 'boolean'

['datetime', ...'datetime'] => 'boolean'

AgFunctionExpression<TRegistry, 'isTrue'>

Is the value true?

['boolean'] => 'boolean'

AgFunctionExpression<TRegistry, 'isFalse'>

Is the value false?

['boolean'] => 'boolean'

AgFunctionExpression<TRegistry, 'isNull'>

Is the value null?

['string'] => 'boolean'

['number'] => 'boolean'

['boolean'] => 'boolean'

['date'] => 'boolean'

['datetime'] => 'boolean'

AgFunctionExpression<TRegistry, 'isNotNull'>

Is the value not null?

['string'] => 'boolean'

['number'] => 'boolean'

['boolean'] => 'boolean'

['date'] => 'boolean'

['datetime'] => 'boolean'

AgFunctionExpression<TRegistry, 'datediff'>

Return the number of units defined by the first value that are between the second and third values.

['string', 'date', 'date'] => number

['string', 'datetime', 'datetime'] => number

Supported units:

Milliseconds - 'millisecond' | 'ms'

Seconds - 'second' | 'ss' | 's'

Minutes - 'minute' | 'mi' | 'n'

Hours - 'hour' | 'hh'

Days - 'day' | 'dy' | 'y'

Weeks - 'week' | 'ww' | 'wk'

Weekdays - 'weekday' | 'dw' | 'w'

Months - 'month' | 'mm' | 'm'

Quarters -'quarter' | 'qq' | 'q'

Years - 'year' | 'yyyy' | 'yy'

Days of year - 'dayofyear'

AgFunctionExpression<TRegistry, 'dateAdd'>

Add a signed integer interval to a date or datetime.

['date', 'number'] => 'date'

['datetime', 'number'] => 'datetime'

AgFunctionExpression<TRegistry, 'dateFromParts'>

Construct a date from integer year, 1-based month, and day components. Spine-internal - not valid in calendar range expressions.

['number', 'number', 'number'] => 'date'

AgFunctionExpression<TRegistry, 'dateTrunc'>

Truncate a date or datetime to the start of a given unit.

['string', 'date'] => 'date'

['string', 'datetime'] => 'datetime'

Supported units: 'year' | 'quarter' | 'month' | 'week' | 'day' (and 'hour' for datetime only)

AgFunctionExpression<TRegistry, 'dateEnd'>

Return the end of a date or datetime period for a given unit.

['string', 'date'] => 'date'

['string', 'datetime'] => 'datetime'

Supported units: 'year' | 'quarter' | 'month' | 'week' | 'day' (and 'hour' for datetime only)

AgFunctionExpression<TRegistry, 'dateExtract'>

Extract a single integer component from a date or datetime.

['string', 'date'] => 'number'

['string', 'datetime'] => 'number'

Supported units:

'year' | 'isoYear' | 'quarter' | 'month' | 'week' | 'day' | 'hour' | 'dayOfYear' | 'dayOfWeek' | 'monthOfQuarter' | 'weekOfMonth' | 'weekend' | 'timeOfDay' | 'minute' | 'second' | 'dayOfMonth'

AgFunctionExpression<TRegistry, 'percentiles'>

Compute multiple percentiles of the grouped values of the target numeric field. The query compiler fans this out into one output column per p value. Planning-level pseudo-function - never evaluated directly.

First input: the source numeric field

Remaining inputs: percentile values (0-1), one per desired output column

AgFunctionExpression<TRegistry, 'percentile'>

Compute the value at percentile p (0-1) of the grouped values of the target numeric field. This is a planning-level pseudo-function: the query compiler rewrites it into a groupSorted + percentileOf pair before execution. It is never evaluated directly.

['number', 'number'] => 'number'

AgFunctionExpression<TRegistry, 'median'>

Shorthand for percentile(field, 0.5). Planning-level pseudo-function.

['number'] => 'number'

AgFunctionExpression<TRegistry, 'currentDate'>

Returns the current date as a DATE value. Session-constant: all rows in a single query receive the same value.

[] => 'date'

AgFunctionExpression<TRegistry, 'currentTimestamp'>

Returns the current date and time as a TIMESTAMP value. Session-constant: all rows in a single query receive the same value.

[] => 'datetime'

AgFunctionExpression<TRegistry, 'abs'>

Returns the absolute value of a number.

['number'] => 'number'

AgFunctionExpression<TRegistry, 'mod'>

Returns the remainder when the first number is divided by the second (modulo). The sign of the result follows the sign of the dividend.

['number', 'number'] => 'number'

AgFunctionExpression<TRegistry, 'floor'>

Returns the largest integer less than or equal to the given number.

['number'] => 'number'

AgFunctionExpression<TRegistry, 'ceiling'>

Returns the smallest integer greater than or equal to the given number.

['number'] => 'number'

AgFunctionExpression<TRegistry, 'round'>

Rounds a number to the nearest integer at the specified scale. Uses round-half-away-from-zero semantics.

['number', 'number'] => 'number'

['number'] => 'number' (scale defaults to 0)

AgFunctionExpression<TRegistry, 'truncate'>

Truncates a number to the specified scale toward zero.

['number', 'number'] => 'number'

['number'] => 'number' (scale defaults to 0)

AgFunctionExpression<TRegistry, 'power'>

Raises the base to the power of the exponent.

['number', 'number'] => 'number'

AgFunctionExpression<TRegistry, 'exp'>

Returns e raised to the power of the given number.

['number'] => 'number'

AgFunctionExpression<TRegistry, 'sin'>

Returns the sine of a number in radians.

['number'] => 'number'

AgFunctionExpression<TRegistry, 'cos'>

Returns the cosine of a number in radians.

['number'] => 'number'

AgFunctionExpression<TRegistry, 'tan'>

Returns the tangent of a number in radians.

['number'] => 'number'

AgFunctionExpression<TRegistry, 'asin'>

Returns the arcsine of a number (inverse sine) in radians. Result is in the range [-π/2, π/2]. Input must be in the range [-1, 1].

['number'] => 'number'

AgFunctionExpression<TRegistry, 'acos'>

Returns the arccosine of a number (inverse cosine) in radians. Result is in the range [0, π]. Input must be in the range [-1, 1].

['number'] => 'number'

AgFunctionExpression<TRegistry, 'atan'>

Returns the arctangent of a number (inverse tangent) in radians. Result is in the range [-π/2, π/2].

['number'] => 'number'

AgFunctionExpression<TRegistry, 'atan2'>

Returns the arctangent of the quotient of two numbers (two-argument arctangent) in radians. Result is in the range [-π, π].

['number', 'number'] => 'number'

AgFunctionExpression<TRegistry, 'ln'>

Returns the natural logarithm (base e) of a number.

['number'] => 'number'

AgFunctionExpression<TRegistry, 'log'>

Returns the logarithm of the second number to the base specified by the first number.

['number', 'number'] => 'number'

AgFunctionExpression<TRegistry, 'log10'>

Returns the base-10 logarithm of a number.

['number'] => 'number'

AgFunctionExpression<TRegistry, 'greatest'>

Returns the maximum value of two or more numbers. Returns NULL if any argument is NULL.

['number', ...'number'] => 'number'

AgFunctionExpression<TRegistry, 'least'>

Returns the minimum value of two or more numbers. Returns NULL if any argument is NULL.

['number', ...'number'] => 'number'

AgFunctionExpression<TRegistry, 'upper'>

Converts a string to uppercase.

['string'] => 'string'

AgFunctionExpression<TRegistry, 'lower'>

Converts a string to lowercase.

['string'] => 'string'

AgFunctionExpression<TRegistry, 'substring'>

Extracts a substring from a string.

['string', 'number'] => 'string' (FROM start position to end of string)

['string', 'number', 'number'] => 'string' (FROM start position FOR length)

Positions are 1-indexed. A start position less than 1 is treated as 1.

AgFunctionExpression<TRegistry, 'position'>

Returns the 1-indexed position of the first occurrence of needle in haystack.

['string', 'string'] => 'number'

Returns 0 if needle is not found, 1 if needle is empty. NULL on either argument yields NULL.

AgFunctionExpression<TRegistry, 'ltrim'>

Removes the specified characters from the left side of a string.

['string', 'string'] => 'string'

Second argument is a set of characters to strip from the left side.

AgFunctionExpression<TRegistry, 'rtrim'>

Removes the specified characters from the right side of a string.

['string', 'string'] => 'string'

Second argument is a set of characters to strip from the right side.

AgFunctionExpression<TRegistry, 'btrim'>

Removes the specified characters from both sides of a string.

['string', 'string'] => 'string'

Second argument is a set of characters to strip from both ends.

AgFunctionExpression<TRegistry, 'overlay'>

Replaces a substring with another string at a specified position.

['string', 'string', 'number'] => 'string' (FROM start, replace to end of replacement)

['string', 'string', 'number', 'number'] => 'string' (FROM start FOR length)

Start position is 1-indexed. When length is omitted, defaults to the replacement length.

AgFunctionExpression<TRegistry, 'sign'>

Returns the sign of a number (-1, 0, or 1).

['number'] => 'number'

Returns -1 for negative, 0 for zero, 1 for positive. NULL input yields NULL.

Getting Started

Connecting Data

Fields & Formatting

Appearance & Behaviour

Widgets

AI Assistant

User Guide

React Embedded AnalyticsExpressions

Calculated Columns & Measures Copy Link

Calculated Columns Copy Link

Measures Copy Link

Expression Types Copy Link

Function Expression Copy Link

Value Expression Copy Link

Field Expression Copy Link

API Copy Link

Expression Field Definition Copy Link

Function Expression Operators Copy Link