Functions & Formulas

Formulas let a semantic query compute values that are not stored directly in the model. You write a formula as an expression string — combining field references, operators, literals, and functions — and the Semantic Query API evaluates it as part of the query. Formulas power calculated dimensions and calculated measurements: a calculated dimension groups or filters rows by a computed value, while a calculated measurement aggregates a computed value.

There are two ways to bring a formula into a query:

• Model-defined calculated fields. The formula is authored once in the Semantic Data Model as a SemanticCalculatedDimension or SemanticCalculatedMeasurement, and the query references it by name through a semantic_field expression.
• On-the-fly calculated fields. The formula is supplied directly in the request as a calculated_field expression, without being persisted to the model. Use this for query-scoped computations.

An expression is written in one of two formula dialects, selected per calculated field with the syntax property:

• Tua ("Tua") — references fields with bracket notation [Table].[Field], and supports level-of-detail (LOD) expressions and table calculations.
• Salesforce ("Salesforce") — references fields with dotted notation table.field.

When a calculated field omits syntax, the default depends on how the model is supplied with the request: a request that carries an inline semanticModel defaults to Tua, while a request that references the model by ID (with no inline model) defaults to Salesforce.

Both dialects share the same function library and operator set. A calculated field in one dialect can reference a model-defined calculated field authored in the other dialect.

Calculated fields can be defined at authoring time as part of the Semantic Data Model, so their formulas are reusable across queries. A calculated dimension is authored as a SemanticCalculatedDimension; a calculated measurement is authored as a SemanticCalculatedMeasurement. Each carries an apiName, a dataType, an expression, a syntax, and (for measurements) an aggregationType and a level. For the full authoring field schema, see Semantic Calculated Dimension and Semantic Calculated Measurement in the Authoring API.

A model-defined calculated measurement:

1{
2  "apiName": "AverageSumOfSales_clc",
3  "label": "AverageSumOfSales",
4  "dataType": "Number",
5  "aggregationType": "UserAgg",
6  "decimalPlace": 2,
7  "expression": "WINDOW_AVG(SUM([Sales]), -3, 1)",
8  "level": "TableCalc"
9  // ...
10}

A query references a model-defined calculated field by name with a semanticField expression. The referenced formula is resolved from the model:

1{
2  "fields": [
3    {
4      "expression": {
5        "semanticField": {
6          "name": "AverageSumOfSales_clc"
7        }
8      },
9      "alias": "AverageSumOfSales_clc",
10      "rowGrouping": false,
11      "semanticAggregationMethod": "SEMANTIC_AGGREGATION_METHOD_USER_AGG"
12    }
13  ]
14}

To compute a value without persisting it to the model, place a calculated_field object inside a fields[] entry’s expression. A calculated_field has a name, an expression string, an optional syntax, and exactly one of calculated_measure_expression (for an aggregated numeric result) or calculated_dimension_expression (for a grouping value).

A calculated measurement declares its output type under calculated_measure_expression:

1{
2  "fields": [
3    {
4      "expression": {
5        "calculated_field": {
6          "name": "simple_power_calc",
7          "expression": "POWER(2,2)",
8          "calculated_measure_expression": {
9            "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER"
10          }
11        }
12      },
13      "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM",
14      "alias": "simple_power_calc"
15    }
16  ]
17}

A calculated dimension declares its output type under calculated_dimension_expression, using dimension_output_type:

1{
2  "calculated_field": {
3    "name": "simple-calc",
4    "expression": "DATETIMEVALUE(DATE_TRUNC('day', ssot__Account__dlm.ssot__LastModifiedDate__c))",
5    "calculated_dimension_expression": {
6      "dimension_output_type": "SEMANTIC_DIMENSION_TYPE_DATE_TIME"
7    }
8  }
9}

To group query rows by a computed dimension, set row_grouping to true on the field:

1{
2  "expression": {
3    "calculated_field": {
4      "name": "fiscal_quarter_on_the_fly_calculation",
5      "expression": "CONCAT('FY', TEXT(FISCAL_QUARTER(AccountSemanticLayer__dll.LastModifiedDate__c)), '-Q', TEXT(FISCAL_YEAR(AccountSemanticLayer__dll.LastModifiedDate__c)))"
6    }
7  },
8  "row_grouping": true
9}

A measurement calculated field may also specify decimal places under decimal_places_options:

1{
2  "calculated_field": {
3    "name": "table_calc_running_sum",
4    "expression": "{ PARTITION [AccountSemanticLayer__dll].[Name__c]: { ORDERBY [AccountSemanticLayer__dll].[KQ_Id__c] : RANK(SUM([AccountSemanticLayer__dll].[AnnualRevenue__c])) } }",
5    "calculated_measure_expression": {
6      "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER",
7      "decimal_places_options": {
8        "decimal_places": 2
9      }
10    },
11    "syntax": "Tua"
12  }
13}

Expressions combine values with arithmetic, comparison, and logical operators. Operators evaluate in the following order, from highest to lowest precedence:

1. Parentheses ( )
2. Unary minus -
3. Exponentiation ^
4. Multiplication, division, modulo * / %
5. Addition, subtraction + -
6. Comparisons > < >= <= = !=
7. IN
8. NOT
9. AND
10. OR

Use parentheses to make grouping explicit. Arithmetic combines measures and literals — this on-the-fly measurement adds a constant to a model-defined calculated measurement:

1{
2  "calculated_field": {
3    "name": "sf_calc_referring_to_tua_calc",
4    "expression": "tua_model_calc + 123",
5    "syntax": "Salesforce",
6    "calculated_measure_expression": {
7      "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER"
8    }
9  }
10}

Arithmetic can also combine field references directly:

1{
2  "calculated_field": {
3    "name": "nested_semantic_field_calc",
4    "expression": "[logical_table].[AnnualRevenue] + [SemanticContact_SDO].[MailingLatitude]",
5    "syntax": "Tua",
6    "calculated_measure_expression": {
7      "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER"
8    }
9  }
10}

Numeric functions operate on numbers and numeric expressions. POWER(base, exponent) raises a number to a power:

1{
2  "calculated_field": {
3    "name": "simple_power_calc",
4    "expression": "POWER(2,2)",
5    "calculated_measure_expression": {
6      "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER"
7    }
8  }
9}

ROUND(value, places) rounds to a given number of decimal places, and ABS(value) returns absolute value. This model-defined table calculation uses ABS in a period-over-period growth formula:

1{
2  "apiName": "Calculation18",
3  "dataType": "Number",
4  "aggregationType": "UserAgg",
5  "expression": "(ZN(SUM([Superstore_Orders].[Sales])) - LOOKUP(ZN(SUM([Superstore_Orders].[Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Superstore_Orders].[Sales])), -1))",
6  "level": "TableCalc"
7}

String functions build and transform text. CONCAT(...) joins strings, and TEXT(value) converts a value to text — combined here to format a fiscal-period label:

1{
2  "calculated_field": {
3    "name": "fiscal_quarter_on_the_fly_calculation",
4    "expression": "CONCAT('FY', TEXT(FISCAL_QUARTER(AccountSemanticLayer__dll.LastModifiedDate__c)), '-Q', TEXT(FISCAL_YEAR(AccountSemanticLayer__dll.LastModifiedDate__c)))"
5  }
6}

LEFT(text, count) returns the leftmost characters. STR(value) converts a value to a string, and text values concatenate with +. This model-defined calculated dimension formats a rounded score with a text suffix:

1{
2  "apiName": "Avg_Quality_Score_clc",
3  "dataType": "Text",
4  "expression": "IF [Quality_Score_Color_clc] = \"High\"\nTHEN STR(ROUND([Average_Quality_Score_clc],2)) + \" (High)\"\nELSEIF [Quality_Score_Color_clc] = \"Medium\"\nTHEN STR(ROUND([Average_Quality_Score_clc],2)) + \" (Medium)\"\nELSEIF [Quality_Score_Color_clc] = \"Low\"\nTHEN STR(ROUND([Average_Quality_Score_clc],2)) + \" (Low)\"\nELSE STR(ROUND([Average_Quality_Score_clc],2)) + \" (Very Low)\"\nEND"
5}

Date functions extract, truncate, and construct date and datetime values. DATE_TRUNC(part, datetime) truncates a datetime to a given part, and DATETIMEVALUE(...) produces a datetime value:

1{
2  "calculated_field": {
3    "name": "simple-calc",
4    "expression": "DATETIMEVALUE(DATE_TRUNC('day', ssot__Account__dlm.ssot__LastModifiedDate__c))",
5    "calculated_dimension_expression": {
6      "dimension_output_type": "SEMANTIC_DIMENSION_TYPE_DATE_TIME"
7    }
8  }
9}

DATEPART(part, date) returns a numeric component of a date, such as the year. This calculated dimension extracts the year from another calculated field:

1{
2  "calculated_field": {
3    "name": "datepartyear:F1",
4    "expression": "DATEPART('year', [ship_datetime_clc])",
5    "calculated_dimension_expression": {
6      "dimension_output_type": "SEMANTIC_DIMENSION_TYPE_NUMBER"
7    },
8    "syntax": "Tua"
9  }
10}

Fiscal date functions return fiscal-calendar components. FISCAL_YEAR(date) returns the fiscal year and FISCAL_QUARTER(date) returns the fiscal quarter for a date:

1{
2  "calculated_field": {
3    "name": "fiscal_quarter_on_the_fly_calculation",
4    "expression": "CONCAT('FY', TEXT(FISCAL_QUARTER(AccountSemanticLayer__dll.LastModifiedDate__c)), '-Q', TEXT(FISCAL_YEAR(AccountSemanticLayer__dll.LastModifiedDate__c)))"
5  }
6}

Conditional expressions branch on a condition. The IF ... THEN ... ELSEIF ... ELSE ... END form returns different values per branch; IN tests membership in a value list. This model-defined measurement returns an amount only for matching forecast categories:

1{
2  "apiName": "Open_Pipeline_clc",
3  "dataType": "Number",
4  "aggregationType": "Sum",
5  "expression": "IF [Opportunity26].[Opportunity_Forecast_Category110] IN (\"Pipeline\", \"Best Case\", \"Most Likely\", \"Commit\") \n    THEN [Opportunity26].[Total_Amount19]\nEND",
6  "level": "Row"
7}

IFNULL(value, replacement) substitutes a replacement when a value is null. This measurement sums two counts, treating null counts as zero:

1{
2  "apiName": "Test_clc",
3  "dataType": "Number",
4  "aggregationType": "UserAgg",
5  "expression": "IFNULL(COUNT([Opportunity26].[Opportunity_Id23]),0)+IFNULL(COUNT([Product2].[Product_Id75]),0)",
6  "level": "AggregateFunction"
7}

ISNULL(expression) returns whether a value is null, and ZN(expression) converts a null numeric result to zero. The growth formula above uses ZN so that missing sales values participate in arithmetic as zero:

1{
2  "apiName": "Calculation18",
3  "expression": "(ZN(SUM([Superstore_Orders].[Sales])) - LOOKUP(ZN(SUM([Superstore_Orders].[Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Superstore_Orders].[Sales])), -1))",
4  "aggregationType": "UserAgg",
5  "level": "TableCalc"
6  // ...
7}

Aggregation functions collapse many rows into a single measurement value. The library includes SUM, MIN, MAX, AVG, COUNT, COUNTD (distinct count), and MEDIAN. COUNTD counts distinct values of a field:

1{
2  "calculated_field": {
3    "name": "countd",
4    "expression": "COUNTD([SemanticAccount__dlm].[semantic__AccountSource__c])",
5    "calculated_measure_expression": {
6      "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER"
7    }
8  }
9}

When a calculated field wraps an aggregation, set the field’s aggregation method to SEMANTIC_AGGREGATION_METHOD_USER_AGG so the expression’s own aggregation is used rather than an outer one. Aggregate expressions also appear in Top-N criteria — here MEDIAN ranks dimension values:

1{
2  "operator": "AdvancedDimension",
3  "top_bottom_criteria": {
4    "top_bottom_limit": 10,
5    "expression": "MEDIAN([superstore_fixed_nulls].[Quantity150])"
6  }
7}

Table calculations compute across the ordered result set — running totals, moving averages, rankings, and offset lookups. A calculated measurement authored at level TableCalc carries a window function. WINDOW_AVG(expression, start, end) averages a measure over a sliding window of rows:

1{
2  "apiName": "AverageSumOfSales_clc",
3  "dataType": "Number",
4  "aggregationType": "UserAgg",
5  "expression": "WINDOW_AVG(SUM([Sales]), -3, 1)",
6  "level": "TableCalc"
7  // ...
8}

LOOKUP(expression, offset) returns a measure’s value from a row offset by a given number of positions — negative offsets look backward. The period-over-period formula uses LOOKUP(..., -1) to reference the prior row:

1{
2  "apiName": "Calculation18",
3  "expression": "(ZN(SUM([Superstore_Orders].[Sales])) - LOOKUP(ZN(SUM([Superstore_Orders].[Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Superstore_Orders].[Sales])), -1))",
4  "aggregationType": "UserAgg",
5  "level": "TableCalc"
6  // ...
7}

Table calculations use a PARTITION/ORDERBY window frame. The frame partitions rows by one set of columns, orders within each partition, and applies an analytics function. RANK(measure) assigns a rank within the ordered partition:

1{
2  "calculated_field": {
3    "name": "table_calc_running_sum",
4    "expression": "{ PARTITION [AccountSemanticLayer__dll].[Name__c]: { ORDERBY [AccountSemanticLayer__dll].[KQ_Id__c] : RANK(SUM([AccountSemanticLayer__dll].[AnnualRevenue__c])) } }",
5    "calculated_measure_expression": {
6      "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER",
7      "decimal_places_options": { "decimal_places": 2 }
8    },
9    "syntax": "Tua"
10  }
11}

The ORDERBY clause accepts sort direction (ASC / DESC) and null placement (NULLS FIRST / NULLS LAST). Table-calculation expressions can also be used inside filters — this filter selects rows by an ordered index computed with an ORDERBY frame:

1{
2  "unified_filter": {
3    "filter_logic": "1",
4    "filters": [
5      {
6        "fields": [
7          {
8            "expression": "{ORDERBY [Superstore_Orders].[Region] ASC NULLS FIRST : ([Row_Index_1_clc])}"
9          }
10        ],
11        "operator": "Between",
12        "values": [1, 2],
13        "include_nulls": false
14      }
15    ]
16  }
17}

Identity functions return values about the current user. USERID() returns the running user’s ID; combine it with LEFT to compare on a 15-character ID prefix. This model-defined calculated dimension marks rows owned by the current user:

1{
2  "apiName": "FFact_owner_opp_history_lv_clc",
3  "label": "FFact owner - opp history lv",
4  "dataType": "Boolean",
5  "expression": "LEFT([SW_FF_OPP_HIS_PA_lv].[Forecasting_Fact10_Owner], 15) = LEFT(USERID(), 15)"
6}

A formula can reference a model parameter. In Salesforce syntax, reference a parameter as Parameters.<name>; in Tua syntax, as [Parameters].[<name>]. This model-defined measurement multiplies a constant by a parameter value:

1{
2  "apiName": "formula_measurement_example",
3  "label": "formula_measurement_example",
4  "dataType": "Number",
5  "expression": "10 * Parameters.param1",
6  "syntax": "Salesforce"
7}

Each calculated field selects its dialect with the syntax property. The two dialects differ mainly in how they reference fields:

• Tua ("Tua"): bracket notation, [Table].[Field].
• Salesforce ("Salesforce"): dotted notation, table.field.

When syntax is omitted, the default is Tua if the request carries an inline semanticModel, and Salesforce if the model is referenced by ID with no inline model.

The same model can hold calculated fields in both dialects, and a formula in one dialect may reference a model-defined calculated field authored in the other. Here a Salesforce-syntax on-the-fly measurement references tua_model_calc, a model-defined measurement authored in Tua syntax:

1{
2  "structuredSemanticQuery": {
3    "fields": [
4      {
5        "expression": {
6          "calculated_field": {
7            "name": "sf_calc_referring_to_tua_calc",
8            "expression": "tua_model_calc + 123",
9            "syntax": "Salesforce",
10            "calculated_measure_expression": {
11              "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER"
12            }
13          }
14        }
15      }
16    ]
17  },
18  "semanticModel": {
19    "semanticCalculatedMeasurements": [
20      {
21        "apiName": "tua_model_calc",
22        "dataType": "Number",
23        "expression": "SUM([SemanticAccount__dlm].[semantic__Number__c])",
24        "syntax": "Tua"
25        // ...
26      }
27    ]
28  }
29}

The equivalent Tua-syntax reference uses bracket notation for the same field:

1{
2  "calculated_field": {
3    "name": "simple-calc",
4    "expression": "[SemanticAccount__dlm].[sdm__LastModifiedDate__c]",
5    "calculated_dimension_expression": {
6      "dimension_output_type": "SEMANTIC_DIMENSION_TYPE_DATE_TIME"
7    }
8  }
9}

You can annotate expressions with comments. Line comments (//) and block comments (/* */) are ignored during evaluation.

For the full request schema, see Request Reference.

• A calculated field is either a measurement or a dimension, not both. Supply exactly one of calculated_measure_expression or calculated_dimension_expression per calculated_field.
• Wrapped aggregations use USER_AGG. When an expression contains its own aggregation function (for example COUNTD(...) or a TableCalc-level formula), set the field’s aggregation method to SEMANTIC_AGGREGATION_METHOD_USER_AGG so the expression’s aggregation is honored.
• Table calculations require an ordering. Window and ranking functions evaluate over an ordered frame; a table-calculation formula specifies its PARTITION and ORDERBY (or window bounds) so row order is well-defined.
• Parameter reference notation is dialect-specific. Use Parameters.<name> in Salesforce syntax and [Parameters].[<name>] in Tua syntax.

• Semantic Calculated Dimension — Authoring API for model-defined calculated dimensions.
• Semantic Calculated Measurement — Authoring API for model-defined calculated measurements.
• Filtering — Use calculated fields and table-calculation expressions within filters.
• Grouping — Group query rows by a calculated dimension with row_grouping.
• Aggregation & Totals — How aggregation methods apply to calculated measurements.
• Request Reference — Complete field schema for calculated fields and expressions.

j

Get Started

Authoring API

Semantic Query API

Overview

Dimensions, Measurements, and Data Objects

Relationships and Joins

Parameters

Calculated Fields

Functions and Formulas

Filtering

Grouping

Aggregation and Totals

Sorting and Limiting

Predict and Forecast

Logical Views

Shared Dimensions

Currency and Fiscal Handling

SemanticQueryRequest Fields

Semantic Metric Query API