Aggregation and Totals

The following sections describe how to use Aggregation Types, Subtotals, Grand Totals, and Smart Totals in a Semantic Query.

The Aggregation Type defines how the Semantic Query will be calculated. Options include:

  • Sum
  • Average (Avg)
  • Minimum (Min)
  • Maximum (Max)
  • Median
  • Count Distinct
  • Standard Deviation (stddev)
  • Standard Population Deviation (stddevp)
  • Variance (var)
  • Variance in Population (varp)
  • Count
  • Auto
  • User aggregated (USER_AGG)
  • Count Distinct (UNIQUE_COUNT)

See the Salesforce Analytics SAQL Developer Guide to learn more about aggregate functions.

The Auto aggregation method (SEMANTIC_AGGREGATION_METHOD_AUTO) instructs Tableau Semantics to apply the default aggregation method defined for the field in the data model.

For example, when a calculated measurement references a semantic_field and specifies the Auto aggregation type, Tableau Semantics will automatically apply the aggregation method defined for that field in the model (e.g., SUM).

The Semantic Layer supports on-the-fly calculations, which are calculated dimension/measurement expressions defined only within the scope of a specific query and are not part of the semantic model. These temporary calculation provide additional flexibility without requiring any changes to the underlying model. For example:

The Semantic Layer uses the semantic aggregation method USER_AGG (SEMANTIC_AGGREGATION_METHOD_USER_AGG) to treat the formula as “already aggregated”. In other words, it indicates that the aggregation is defined inside the formula. User aggregation can be defined using either a calculated field or an alias. For example:

USER_AGG is also allowed for static numerical value expressions - i.e., formulas that evaluate to a static number and don’t contain any other field references. The Semantic Layer will treat the value as if it was already aggregated and will not apply any additional aggregation on the formula. Examples: “123”, “INT(1.0)”, “PI() + 4”

Subtotals calculate measurements according to the selected aggregation type. The calculation is based on the grouped dimensions and will be added as a row into the result set.

Grand Totals represent the aggregation method selected by the user, reflecting all values in the entire datasheet.

Example: If you choose to use subtotals for a summary report by Opportunity Owner, the report groups the accounts by Opportunity Owner, lists the number of opportunities owned by each user, and shows subtotals by Opportunity Owner for all the columns in the report. If you choose Grand Totals, then the total number of opportunities at the end of the results set is shown.