Calculate Single Values As... 
Use
You use this function to recalculate the single values displayed in a report according to particular criteria. These local calculations include only those numbers in the calculation that appear in the current view of the report. In this way, you override the standard OLAP processor calculations.
You can use this function in the following areas:
Query Designer: In structural components in the dialog box Selection/Formula Properties.
Web Applications: in the context menu for structural components (see Context Menu Functions)
BEx Analyzer: In the context menu for the filter cells of the result area or in the BEx symbol bar via OLAP Functions for Active Cells.
Features
You can choose from the following settings:
(Nothing Defined)
This setting displays the data from the OLAP processor.
Normalization
You can normalize the query data for a key figure for different results of the key figure – the data is displayed as a percentage of the result.
Normalization of Result
All values are normalized for the result. The result equals the total result if there is only one characteristic in the drilldown.
Normalization of Overall Result
All values are normalized to the overall result. If there are several characteristics in the drilldown, there are different results that are combined to form an overall result.
Normalization of Query Result
All values are normalized to the query result. 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.
The values of the results row and the overall results row are not displayed as percentages but as absolute values.
Ranked List
The characteristic values are sorted according to the key figure you have selected, and given a ranking. The order of the ranked list relates to the size of the characteristic value, whereby the largest value has rank 1 and the smallest has the lowest rank.
If a value appears more than once, the corresponding characteristic values have the same rank. In a basic ranked list, the next smallest value has a rank that is increased by 1.
Example: A ranked list of products, according to sales.
A 100 = Rank 1
B 50 = Rank 2
C 50 = Rank 2
D 20 = Rank 3
Ranked List (Olympic)
In the olympic ranked list, the next smallest value when a value appears more than once, is not given the rank increased by 1, but the rank that corresponds to the number of previous characteristic values (including the current value).
Example: There are three products with a higher rank than product D, therefore D has rank 4 and rank 3 is not given 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
Maximum
You can calculate the minimum key figure values for a characteristic locally.
Minimum
You can calculate the minimum key figure values for a characteristic locally.
Count all values
You can count all values.
Count all values <> 0
You can count all values, excluding zero values.
Average of all values
You can calculate the average of all values locally.
Average of all values <> 0
You can calculate the average of all values, excluding zero values locally.
Suppress Single Values
The single values are not displayed. Only the results are displayed.
Examples
The following table gives an overview of the functions Normalize to Result, Normalize to Overall Result and Normalize to Query Result:
|
Customer |
Sales |
Normalization of Result |
Normalization of Overall Result |
Normalization of Query 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 gives an overview of the Ranked List and Ranked List (Olympic) functions:
|
Customer |
Sales |
Ranked List |
Ranked List (Olympic) |
|
Customer 1 |
10 |
4 |
5 |
|
Customer 2 |
0 |
6 |
7 |
|
Customer 3 |
17 |
2 |
2 |
|
Customer 4 |
8 |
5 |
6 |
|
Customer 5 |
15 |
3 |
3 |
|
Customer 6 |
22 |
1 |
1 |
|
Customer 7 |
15 |
3 |
3 |
The following table gives an overview of the functions Maximum, Minimum, Count All Values and Count All Values <> 0:
|
Customer |
Sales |
Maximum |
Minimum |
Count all values |
Count all values <> 0 |
|
Customer 1 |
10 |
10 |
10 |
1 |
1 |
|
Customer 2 |
0 |
10 |
0 |
2 |
1 |
|
Customer 3 |
17 |
17 |
0 |
3 |
2 |
|
Customer 4 |
8 |
17 |
0 |
4 |
3 |
|
Customer 5 |
15 |
17 |
0 |
5 |
4 |
|
Customer 6 |
22 |
22 |
0 |
6 |
5 |
|
Customer 7 |
15 |
22 |
0 |
7 |
6 |
The following table gives an overview of the functions Average of All Values, Average of All Values <> 0 and Suppress Single Values.
|
Customer |
Sales |
Average of all values |
Average of all values <> 0 |
Suppress Single Values |
|
Customer 1 |
10 |
10,00 |
10,00 |
0 |
|
Customer 2 |
0 |
5,00 |
10,00 |
0 |
|
Customer 3 |
17 |
9,00 |
13,50 |
0 |
|
Customer 4 |
8 |
8,75 |
11,66 |
0 |
|
Customer 5 |
15 |
10,00 |
12,50 |
0 |
|
Customer 6 |
22 |
12,00 |
14,40 |
0 |
|
Customer 7 |
15 |
12,43 |
14,50 |
0 |
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) 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 figures (for example, sales), the calculation begins at the top left and goes 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 Normalization of 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 function Ranked List:
|
Customer |
Germany |
England |
France |
|
BMW |
6 |
3 |
1 |
|
VW |
8 |
4 |
3 |
|
Mercedes |
5 |
2 |
7 |
The following table shows the query with the function Ranked List (Olympic):
|
Customer |
Germany |
England |
France |
|
BMW |
7 |
3 |
1 |
|
VW |
9 |
5 |
3 |
|
Mercedes |
6 |
2 |
8 |
The following table shows the query with the function Maximum:
|
Customer |
Germany |
England |
France |
|
BMW |
10 |
17 |
35 |
|
VW |
10 |
17 |
35 |
|
Mercedes |
12 |
22 |
35 |
The following table shows the query with the function Minimum:
|
Customer |
Germany |
England |
France |
|
BMW |
10 |
10 |
10 |
|
VW |
0 |
0 |
0 |
|
Mercedes |
0 |
0 |
0 |