Calculate Single Values As...
Use
You use this function to recalculate single values displayed in the BI application according to specific criteria. These local calculations include only those numbers in the calculation that appear in the current view of the BI application. You thus override the standard calculations in Analytic Engine.
You can use this function in the following areas:
Tool/Application |
Details |
BEx Query Designer |
For structure elements in the selection or formula properties More information: Selection/Formula/Cell Properties |
BEx Web Applications |
● In the context menu of structure elements ● In the properties of structure elements and data cells More information: |
BEx Analyzer |
In the key figure properties More information: Key Figure Properties |
The Calculate Single Values As... function is not supported for active display hierarchies.
Note that the local calculations only change the display of the values. With subsequent calculations, the system does not use this value changed for the display, but rather the original value specified by the analytic engine.
More information: Interpretation of Results Rows
Features
Note that the features outlined below are available in Web applications only; the BEx Query Designer and the BEx Analyzer supports only some of these functions.
You can choose from the following settings:
Settings |
Description |
(Nothing Defined) |
With this setting, the data provided by the analytic engine is displayed. No local calculation of single values takes place. |
Minimum |
The system displays the minimum key figure value for a characteristic. |
Minimum of Values That Are Not Zero, Null, or Error |
Excluding the values that are equal to zero, the system displays the minimum key figure value for a characteristic. |
Moving Minimum Value |
The system displays the smallest value available up to this point. |
Maximum |
The system displays the maximum key figure value for a characteristic. |
Maximum of Values That Are Not Zero, Null, or Error |
Excluding the values that are equal to zero, the system displays the maximum key figure value for a characteristic. |
Moving Maximum Value |
The system displays the largest value available up to this point. |
Sum |
You use this function to cumulate the individual cells of an area. This means the first value is added to the second value. The result is added to the third value, and so on. In the columns, the cells are cumulated from top to bottom, and in the rows, the cells are cumulated from left to right. With blocks of single values, a drilldown in both the rows and the columns, the values are cumulated from top to bottom and from left to right. |
Summation of Rounded Values |
The system displays the running sum of the rounded values. |
Counter for All Detailed Values |
All values for a characteristic are counted and numbered. |
Counter for All Detailed Values That Are Not Zero, Null, or Error |
Excluding the values that are equal to zero, all values for a characteristic are counted and numbered. |
Moving Average |
The system calculates the average of all values. |
Moving Average That Is Not Zero, Null, or Error |
Excluding the values that are equal to zero, the system calculates the average of all values. |
Normalize According to Next Group Level Result |
The data is displayed as a percentage of the result of the next group level. The values of the results row and the overall results row are not displayed as percentages but as absolute values. If there is only one characteristic in the drilldown, the result of a group level is the same as the overall result. |
Normalize According to Overall Result |
The data is displayed as a percentage of the overall result. The values of the results row and the overall results row are not displayed as percentages but as absolute values. If there are multiple characteristics in the drilldown, there are different results, which are combined to form an overall result. |
Normalize According to Unrestricted Overall Result |
The data is displayed as a percentage of the unrestricted result (query result). The values of the results row and the overall results row are not displayed as percentages but as absolute values. Each key figure for a query has a query result. This is the result of the key figure that results from the aggregation of all characteristics for the query. The overall result and the query result are identical to one another, provided that none of the characteristics are filtered at runtime. |
Rank Number |
The characteristic values are sorted according to the selected structure element and are given a ranking. The order of the ranked list is based on the size of the value for the structure element, where the largest value has rank 1 and the smallest value has the last rank. If a value occurs more than once, the corresponding characteristic values are assigned the same rank. In a basic ranked list, the next smallest value is assigned this rank incremented by one. You can create a ranked list of products according to sales. A 100 = Rank 1 B 50 = Rank 2 C 50 = Rank 2 D 20 = Rank 3 |
Olympic Rank Number |
The Olympic ranked list differs from the basic ranked list as follows: In the Olympic ranked list, when a value occurs more than once, the next smallest value is not assigned the rank incremented by one, but the rank that corresponds to the number of previous characteristic values (including the current value). There are three products with a higher rank than product D, therefore D has rank 4 and rank 3 is not assigned since B and C have the same rank (2). A 100 = Rank 1 B 50 = Rank 2 C 50 = Rank 2 D 20 = Rank 4 |
Hide |
The system only displays the results; the single values are suppressed. |
Examples
The following table provides an overview of the functions Normalize According to Next Group Level Result, Normalize According to Overall Result, and Normalize According to Unrestricted Overall Result.
Customer |
Sales Volume |
Normalize According to Next Group Level Result |
Normalize According to Overall Result |
Normalize According to Unrestricted Overall Result |
Customer 1 |
10 |
10.86% |
0.97% |
0.12% |
Customer 2 |
0 |
00.00% |
0.00% |
0.00% |
Customer 3 |
17 |
18.47% |
1.66% |
0.21% |
Customer 4 |
8 |
08.69% |
0.78% |
0.10% |
Customer 5 |
15 |
16.30% |
1.46% |
0.19% |
Customer 6 |
22 |
23.91% |
2.15% |
0.28% |
Customer 7 |
15 |
16.30% |
1.46% |
0.19% |
Result |
92 |
|
|
|
Overall Result |
1023 |
|
|
|
Query Result |
7812 |
|
|
|
The following table provides an overview of the Counter for All Detailed Values, Counter for All Detailed Values That Are Not Zero, Null, or Error, Rank Number, and Olympic Rank Number functions.
Customer |
Sales Volume |
Counter for All Detailed Values |
Counter for All Detailed Values That Are Not Zero, Null, or Error |
Rank Number |
Olympic Rank Number |
Customer 1 |
10 |
1 |
1 |
4 |
5 |
Customer 2 |
0 |
2 |
1 |
6 |
7 |
Customer 3 |
17 |
3 |
2 |
2 |
2 |
Customer 4 |
8 |
4 |
3 |
5 |
6 |
Customer 5 |
15 |
5 |
4 |
3 |
3 |
Customer 6 |
22 |
6 |
5 |
1 |
1 |
Customer 7 |
15 |
7 |
6 |
3 |
3 |
The following table provides an overview of the Maximum, Moving Maximum Value, Minimum, and Moving Minimum Value functions:
Customer |
Sales Volume |
Maximum |
Moving Maximum Value |
Minimum |
Moving Minimum Value |
Customer 1 |
10 |
22 |
10 |
0 |
10 |
Customer 2 |
0 |
22 |
10 |
0 |
0 |
Customer 3 |
17 |
22 |
17 |
0 |
0 |
Customer 4 |
8 |
22 |
17 |
0 |
0 |
Customer 5 |
15 |
22 |
17 |
0 |
0 |
Customer 6 |
22 |
22 |
22 |
0 |
0 |
Customer 7 |
15 |
22 |
22 |
0 |
0 |
The following table provides an overview of the Moving Average, Moving Average That Is Not Zero, Null, or Error, and Hide functions:
Customer |
Sales Volume |
Moving Average |
Moving Average That Is Not Zero, Null, or Error |
Hide |
Customer 1 |
10 |
10.00 |
10.00 |
|
Customer 2 |
0 |
5.00 |
10.00 |
|
Customer 3 |
17 |
9.00 |
13.50 |
|
Customer 4 |
8 |
8.75 |
11.66 |
|
Customer 5 |
15 |
10.00 |
12.50 |
|
Customer 6 |
22 |
12.00 |
14.40 |
|
Customer 7 |
15 |
12.43 |
14.50 |
|
The system always calculates from the top in the direction of the drilldown. In this case (characteristics in the rows and key figures in the column), this is from top to bottom. If the key figures are in the rows and the characteristics are in the columns, the calculation direction is from left to right.
If a query contains characteristics in the rows and in the columns and the query is filtered by a key figure (for example, sales), the calculation begins at the top left and works down to the bottom right.
Example query with Sales in the filter and Customer and Region in the drilldown:
Customer |
Germany |
England |
France |
Result |
BMW |
10 |
17 |
35 |
62 |
VW |
0 |
15 |
17 |
32 |
Mercedes |
12 |
22 |
3 |
37 |
Result |
22 |
54 |
55 |
131 |
The following table shows the query with the function Normalize According to Overall Result:
Customer |
Germany |
England |
France |
BMW |
7.63% |
12.97% |
26.71% |
VW |
0.00% |
11.45% |
12.97% |
Mercedes |
9.16% |
16.79% |
2.29% |
The following table shows the query with the Rank Number function.
Customer |
Germany |
England |
France |
BMW |
6 |
3 |
1 |
VW |
8 |
4 |
3 |
Mercedes |
5 |
2 |
7 |
The following table shows the query with the Olympic Rank Number function.
Customer |
Germany |
England |
France |
BMW |
7 |
3 |
1 |
VW |
9 |
5 |
3 |
Mercedes |
6 |
2 |
8 |