Creating Calculated Key Figures

Objectives

After completing this lesson, you will be able to:

  • Create a query with calculated key figures

Calculated Key Figures

Complicated calculations for information analysis are necessary in reporting. This requires various mathematical functions such as percentage functions and totals functions.

You can take basic key figures, restricted key figures, or calculated key figures to define new calculated key figures. For example, use the restricted key figures for two specific years to calculate the difference between the net order amounts of the years in question.

You can define calculated key figures at InfoProvider level and at query level. If you create a calculated key figure at InfoProvider level, you can use it in all queries that are based on the same InfoProvider. At query level, the calculated key figure is valid only for the query in question.

Functions for Calculated Key Figures

The following functions are available as operators when you are defining a calculated key figure:

  • Percentage functions

  • Data functions

  • Mathematical functions

  • Trigonometric functions

  • Boolean Operators

Percentage Functions

The following table describes most of the percentage functions that are available as operators for calculated key figures.

Function

Formula

Description

Percentage Variance

<Operand 1> %

<Operand 2>

Calculates the percentage variance of operand 1 from operand 2. Planned Net order amount % Actual Net order amount calculates the percentage by which the actual net order amount exceeds the planned sales volume.

Percentage Share

<Operand 1> %A

<Operand 2>

Calculates operand 1 as a percentage share of operand 2. Net order amount % A Incoming Orders calculates the percentage share of net order amount that is made up of incoming orders.

Percentage Share of Result

%CT <Operand>

Calculates how high the percentage share is in relation to the result where result means the result of aggregation at the next level (interim result).

%CT Incoming Orders calculates the share of incoming order values of each individual characteristic value, for example, each customer, in relation to the characteristic’s result, for example, customer of a division.

Percentage Share of Overall Result

%GT <Operand>

Calculates how high the percentage share is in relation to the overall result. The overall result means the result of aggregation at the highest level in the list.

Dynamic filters are included in the calculation of the overall result.

Percentage Share of the Report Result

%RT <Operand>

This is similar to %GT. Unlike the process for calculating the percentage share of the overall result, dynamic filters are not included in the calculation of the report result.

  

The calculation always normalizes to the same value, irrespective of the dynamic filter status and the navigational state.

Percentage Share of Results along the Columns

%XT <Operand>

Calculate the percentage based on the next total level on the X axis.

Percentage Share of Results along the Row

%YT<Operand>

Calculate the percentage based on the next total level on the Y axis.

Data

The following table describes the most important data functions that are available for calculated key figures.

Function

Formula

Description

Counts

COUNT (<Expression>)

Delivers the value 1 if the expression named in <Expression> is not equal to 0. Otherwise, the function delivers the value 0.

NDIV0 (x): Is equal to 0 with division by 0, otherwise x.

NDIV (<Expression>)

Delivers 0 if the expression named in <Expression> gives a division by 0 in the calculation. Otherwise, the result is the value of the expression.

This function is used to avoid the output of an error message or to continue calculating with a defined result.

Result

SUMCT <Operand>

Delivers the result of the operand in all rows or columns.

Overall Result

SUMGT <Operand>

Delivers the overall result of the operand.

NODIM

NODIM <expression>

Returns purely numerical values of <expression> and suppresses units and currencies

Report Result

SUMRT <Operand>

Delivers the report result of the operand.

Note
Key figures always keep their units when a query is executed. This means that the formula is semantically incorrect if you add a currency unit - for example, EUR, to a unit of weight, for example, kg. If you want to calculate values without using units, use the Value Without Dimension (NODIM) data function.

Mathematics

Mathematical functions that are available for calculated key figures include the following:

  • Maximum

  • Minimum

  • Absolute value

  • Smallest integer value larger than operand

  • Division (integers)

Calculated Key Figures at the Query Level

To define calculated key figures at the query level:

  • Include key figures needed for the calculation in the query definition.
  • Choose New Formula from the context menu of the header of the key figure structure.
  • As of SAP BW/4HANA 2.0 SP04, such a formula can be restricted in a further selection. This restricted selection can only be used in the context of the current query, that means, it cannot be saved as a reusable restricted key figure at InfoProvider level.

Calculated Key Figures at the InfoProvider Level (Reusable Component)

When you create a calculated key figure at the InfoProvider level, you can reuse it in all queries that are based on the same InfoProvider.

To define a calculated key figure at the InfoProvider level:

  • In the InfoProvider tab: Reusable ComponentsCalculated Key FiguresNew Calculated Key Figure.

  • Alternatively, in the Project Explorer tab: in the BW Repository, right-click the InfoProvider, and select NewCalculated Key Figure.

  • You can include basic key figures, restricted key figures, and calculated key figures that have been defined at the InfoProvider level.

  • Drag the calculated key figure to the section where it should be used, because it is not automatically included in your query.

  • You can reuse the calculated key figure in all queries on the same InfoProvider.

  • Changes made later to the calculated key figure affect all queries in which it is used.

  • You cannot Dereference the reused calculated key figure.

How to Define Calculated Key Figures

Launch the following demo to learn how to define calculated key figures at InfoProvider level and at query level.

How to Use Data Function within a Calculated Key Figure

In the following demo you will learn how to use a data function within a calculated key figure.

Log in to track your progress & complete quizzes