Skip to main content

Semi-Additive Metrics

Semi-additive metrics are those whose values can be summarized for some dimensions of a model, but not all. Ratios such as Average are also considered semi-additive metrics.

For example, suppose you have a column that tracks employee headcount. The headcount yesterday was 10 employees, today it is 11, so does this make the total for the month 21? Definitely not! This is an example of a semi-additive metric. While headcounts of two regions or two departments can be summed up, the changes across time for a single region or department should not be summed up. So, headcount is a semi-additive metric across time. In this example, we want to look at the last non-empty value we have in a given month to determine our total headcount.

For this use case, you'd define the dimensions over which the values should not be summarized (usually a date or time dimension). Instead, the query engine returns either the First Non-empty or Last Non-empty value in the result set when the results are sorted by time. For more information, see Add Semi-Additive Metrics.

note

In Tableau, semi-additive metrics will appear as CNTD (Count Distinct) aggregations in the client application if the return value is an integer and AVG if the value is not an integer. However, the actual query will use whatever aggregate calculation you specified when the metric was defined in AtScale. This is because SQL clients do not understand the concept of semi-additive metrics, and this is how AtScale informs the client that the memetricasure is non-additive. Users should not change the aggregation function on the client-side in Tableau for these semi-additive metrics.

note

To configure the default totaling behavior for semi-additive metrics, you can use the query.planning.semiAdditive.defaultAggregationWhenOutOfScope setting. When this setting is set to True (default), the first/last non-empty subtotal or grand total is shown only when the query contains one of the metric's configured semi-additive dimension attributes. When this setting is False, the first/last non-empty subtotal or grand total is shown when the query does not contain a configured semi-additive dimension attribute.

You do not need to restart the engine after changing this setting. For more information on engine settings, see Configuring Global Settings.

Limitations on Embedded Dimensions in Semi-Additive Metrics​

For semi-additive metrics that contain embedded dimensions, the following restrictions apply to the non-additive component of first non-empty/last non-empty value metrics:

  • Only the leaf levels of the embedded dimension hierarchies can be referenced. Note that this does not restrict the ability to query at higher levels of the embedded dimension hierarchy.
  • Embedded dimensions cannot be referenced via many-to-many relationships.
  • Embedded dimensions cannot be referenced through a path that involves role-playing.