Creating Calculated Key Figures with Exception Aggregation

In a query, it is possible to choose how key figure values are aggregated. You can use different characteristics and characteristic values in your aggregation definition. To calculate the value of each key figure in the query, the data from the InfoProvider must be aggregated to the detail level of the query. Formulas may also need to be calculated.

Configuring Exception Aggregation

There are two approaches to defining exception aggregation:

1. In the modeling settings of a key figure InfoObject.

   You can configure exception aggregation during modeling of key figures if the exception aggregation is going to be applied to all queries.

   For Non-Cumulative key figures, you must configure exception aggregation (for example, Last Value) to avoid incorrect aggregation over time characteristics.

2. In the properties of formulas or calculated key figures in SAP BW Query Designer in SAP HANA Studio.

   Add or change exception aggregation settings for each query by creating a formula object, adding operands, and defining the type of exception aggregation for the reference characteristics.

In an exception aggregation, you can use up to five reference characteristics, so that the aggregation rule will be applied in a single calculation step that combines all of the reference characteristics. For example, you want to define a calculated key figure, Sales per Employee and Product and Year.

Define multi-dimensional aggregation by defining a single calculated key figure that contains all three characteristics as reference characteristics.

Exception aggregation rules are performed on single records, without the need to calculate the sub-steps, improving performance.

With multi-dimensional exception aggregation, it is also possible to create new analyzes - for example, to calculate average values for the combination of characteristics.

Nested exception aggregation is necessary when you want to use an exception aggregation for a value that already has been calculated with an exception aggregation.

For example, you want to display the average number of products that have been sold for each customer:

1. To count the number of products per customer, create a formula Count of Products with exception aggregation as count and the reference characteristic as product. This will provide the number of products for each customer in your result.

2. To calculate the average number of products per customer, create a second formula, and use the formula Count of Products as the operand.

3. Define an exception aggregation average with the reference characteristic customer.

This is a nested exception aggregation that will return the average number of products per customer.