Create Aggregations for Tables

In your SAP Analytics Cloud table, you can use the Calculation Editor to create aggregations.

Prerequisites

A table must be selected. The data source must contain key figures.

Note

SAP HANA models prior to version 122.14 do not support aggregations.

However, you can use models that have data from an acquired data source.

Context

Calculations can be created from aggregations such as sum, count, average, and so on. When you create an aggregation, you can also choose what conditions are required for the aggregation to be applied, and when the conditions are required. For example, you can create an aggregation to count the number of sales per store, when the store carries a certain product.

Procedure

Select a table and then select the Builder.
- 1. Under Rows or Columns, select Add Dimensions and add the Account dimension or the Cross Calculations dimension.
  2. Select either the Account or Cross Calculations dimension and then select Add Calculation.
Note
The option to create a new calculation may not appear if calculations are not possible for the model.

The Calculation Editor appears.
Select Aggregation from the list.
Enter a name for the aggregation.

Select an aggregation type.

The following aggregation types are supported:

Type	Description
SUM	The sum of the measure’s values across all leaf members of the selected dimensions.
COUNT	The number of leaf members of the selected dimensions with values for a specific measure, including null values. Empty values for this measure are not counted. Note Although NULL and NO_DATA cells are visually identical, the application still distinguishes them in the backend. NULL cells are retrieved directly from the database, and can also be created using formulas. For example, IF( cost > 1000; NULL; 1). NO_DATA cells are empty cells that are use jointly with the unbooked mode for example, or to create a cross-join of the rows and columns axis. The COUNT function only accounts for NULL cells in the aggregation, and discards the NO_DATA cells.
COUNT DIMENSIONS	The number of leaf members of the selected dimensions that have at least one measure value. This aggregation doesn't count members that have an empty value for all measures. (COUNT DIMENSIONS is not valid for the Cross Calculations dimension.)
COUNT excl NULL	The number of values, excluding null values.
COUNT excl 0, NULL	The number of values, excluding zero and null values.
MIN	The smallest value across the selected dimensions.
MAX	The largest value across the selected dimensions.
AVERAGE	The average of the measure’s values across the selected dimensions, including null values. Note Although NULL and NO_DATA cells are visually identical, the application still distinguishes them in the backend. NULL cells are retrieved directly from the database, and can also be created using formulas. For example, IF( cost > 1000; NULL; 1). NO_DATA cells are empty cells that are use jointly with the unbooked mode for example, or to create a cross-join of the rows and columns axis. The AVERAGE function only accounts for NULL cells in the aggregation, and discards the NO_DATA cells.
AVERAGE excl NULL	The average of the measure’s values, excluding null values.
AVERAGE excl 0, NULL	The average of the measure’s values, excluding zero and null values.
FIRST	Shows the first (oldest) value in the selected time period: for example, show the number of employees on the first day of a month.
LAST	Shows the last (most recent) value in the selected time period: for example, show the number of employees on the last day of a month.
STANDARD DEVIATION	Calculates how much the value varies from the average value in the series.
MEDIAN	The median (middle) value (half of the data lies below the median value, and half lies above).
MEDIAN excl. NULL	The median (middle) value (half of the data lies below the median value, and half lies above), ignoring null values.
MEDIAN excl. 0, NULL	The median (middle) value (half of the data lies below the median value, and half lies above), ignoring null and zero values.
FIRST QUARTILE	Calculates the first quartile value (25% of the data is less than this value).
FIRST QUARTILE excl. NULL	Calculates the first quartile value (25% of the data is less than this value), ignoring null values.
FIRST QUARTILE excl. 0, NULL	Calculates the first quartile value (25% of the data is less than this value), ignoring null and zero values.
THIRD QUARTILE	Calculates the third quartile value (75% of the data is less than this value).
THIRD QUARTILE excl. NULL	Calculates the third quartile value (75% of the data is less than this value), ignoring null values.
THIRD QUARTILE excl. 0, NULL	Calculates the third quartile value (75% of the data is less than this value), ignoring null and zero values.

In the Measure section, select a measure from the list.

Note
Measures do not have to be selected for Count Dimensions.
In the Aggregation Dimensions section, select one or more dimensions to apply the aggregation to. For example, if you select the Date and Product dimensions, values for each product at the smallest time granularity in the model will be aggregated.

Use (Add) to add additional dimensions.

(Optional) Select Use conditional aggregation.

Conditional aggregation allows you to specify when the aggregation is applied and what conditions are required for the aggregation to be applied.

Note

Conditional aggregation is not applicable to Count Dimensions.

In the Aggregate when aggregation dimensions section, choose Have Measure values for Conditions, or Do not have Measure values for Conditions.
In the Conditions section, under Dimension, select one or more dimensions to apply conditions to.

Use (Add a Condition) to add additional dimensions.

Beside each dimension, under Dimension Values or Input Controls, select Click to Select Values, and then choose an option from the list:

Option	Description
Select by Member	Select values for the condition from the list of available members. If you select Exclude selected members, all members except the ones selected will be applied to the aggregation condition. You can use (Search) to find specific values. When you expand the list beside the search icon, you can choose to view the member Description, ID and Description, or ID. The members you choose appear in the Selected Members list. When this list is complete, select OK.
Select by Range	Enter a start value and end value for the range of values for the condition. Select Add a New Range to add additional ranges to the aggregation condition. Note This option appears only if dimension values are numerical or date based. If the dimension is date based, you can also select quarter, month, or year from the slider that appears. Ranges can be fixed or dynamic; for example, you could choose the fixed range January 2017 to December 2017. If this story is opened in 2018, the story will still show 2017 data. For dynamic date ranges, in addition to the above granularities, these granularities are also available: current year, current quarter, and current month. For more information, see Story and Page Filters. When you have configured the ranges of values, select OK.
Create a New Calculation Input Control	This option is available when adding an aggregation to a table. It allows table viewers to select their own values for the aggregation condition from a list of values that you specify. Enter a name for the input control, and then select Click to Add Values. Choose how to add values to the input control: Select by Member: Select values from the list of available members. If you select Exclude selected members, all members except the ones selected will be used in the condition for the aggregation. You can use (Search) to find specific values. When you expand the list beside the search icon, you can choose to view the member Description, ID and Description, or ID. Expand the Settings for Users section, and then choose whether users can do the following in the input control: Single Selection, Multiple Selection, or Multiple Selection Hierarchy. Select OK. Select by Range: Choose a start value and end value for the range. Select Add a New Range to add additional ranges to the input control. Expand the Settings for Users section, and then choose whether users can do the following in the input control: Single Selection, Multiple Selection, or Single Value Slider. Select OK. Select OK.

Option

Description

Select by Member

Select values for the condition from the list of available members. If you select Exclude selected members, all members except the ones selected will be applied to the aggregation condition. You can use (Search) to find specific values. When you expand the list beside the search icon, you can choose to view the member Description, ID and Description, or ID.

The members you choose appear in the Selected Members list.

When this list is complete, select OK.

Select by Range

Enter a start value and end value for the range of values for the condition. Select Add a New Range to add additional ranges to the aggregation condition.

Note

This option appears only if dimension values are numerical or date based. If the dimension is date based, you can also select quarter, month, or year from the slider that appears.

Ranges can be fixed or dynamic; for example, you could choose the fixed range January 2017 to December 2017. If this story is opened in 2018, the story will still show 2017 data. For dynamic date ranges, in addition to the above granularities, these granularities are also available: current year, current quarter, and current month. For more information, see Story and Page Filters.

When you have configured the ranges of values, select OK.

Create a New Calculation Input Control

This option is available when adding an aggregation to a table. It allows table viewers to select their own values for the aggregation condition from a list of values that you specify.

Enter a name for the input control, and then select Click to Add Values.
Choose how to add values to the input control:
- Select by Member:
  
  Select values from the list of available members. If you select Exclude selected members, all members except the ones selected will be used in the condition for the aggregation. You can use (Search) to find specific values. When you expand the list beside the search icon, you can choose to view the member Description, ID and Description, or ID.
  
  Expand the Settings for Users section, and then choose whether users can do the following in the input control: Single Selection, Multiple Selection, or Multiple Selection Hierarchy. Select OK.
- Select by Range:
  Choose a start value and end value for the range. Select Add a New Range to add additional ranges to the input control.
  
  Expand the Settings for Users section, and then choose whether users can do the following in the input control: Single Selection, Multiple Selection, or Single Value Slider. Select OK.
Select OK.

Select OK.