The scenario below shows you how to count the results of different calculated key figures in the BEx Query Designer. The data from the following example is enhanced for the purposes of this example: Example of Exception Aggregation: Counting.
You have loaded the following data into your InfoCube:
Country |
Region |
Customer |
Sales Volume |
|
|
|
USD |
US |
NY |
A |
400,000 |
B |
200,000 |
||
C |
50,000 |
||
CA |
A |
800,000 |
|
C |
300,000 |
In the query, you do not want to determine the number of customers for which the sales volume totals less than 1,000,000 USD (see Example of Exception Aggregation: Counting) - you want to determine the following values instead:
○ Number of customers with sales volume between 100,000 and 1,000,000
○ Number of customers with sales volume between 100,000 and 1,000,000 in at least one region
To be able to calculate these values, you have to create the following calculated key figures:
F1: Customers with sales volume <= 1,000,000
○ General tab page: Formula definition: Sales Volume <= 1,000,000
○ Aggregation tab page: Exception Aggregation: Total, Ref. Characteristic: Customer
F2: Customers with sales volume between 100,000 and 1,000,000
○ General tab page: Formula definition: (Sales Volume >= 100,000) AND (Sales Volume <=1,000,000)
○ Aggregation tab page: Exception Aggregation: Total, Ref. Characteristic: Customer
F3: Customers with sales volume between 100,000 and 1,000,000 in at least one region
For this key figure, you require the secondary key figure S1 (for the aggregation of regions with a sales volume between 100,000 and 1,000,000 USD).
○ General tab page: Formula definition: (Sales Volume >= 100,000) AND (Sales Volume <=1,000,000)
○ Aggregation tab page: Exception Aggregation: Maximum, Ref. Characteristic: Region Code
The secondary key figure (S1) is used in F3:
○ General tab page: Formula definition: Secondary key figure (S1)
○ Aggregation tab page: Exception Aggregation: Total, Ref. Characteristic: Customer
This query would deliver the following result:
Country |
Region |
Customer |
Sales Volume |
F1 |
F2 |
F3 |
|
|
|
USD |
|
|
|
US |
NY |
A |
400,000 |
1 |
1 |
1 |
B |
200,000 |
1 |
1 |
1 |
||
C |
50,000 |
1 |
0 |
0 |
||
Result |
650,000 |
3 |
2 |
2 |
||
CA |
A |
800,000 |
1 |
1 |
1 |
|
C |
300,000 |
1 |
1 |
1 |
||
Result |
1,100,000 |
2 |
2 |
2 |
||
Overall result |
1,750,000 |
2 |
2 |
3 |
The overall result of the calculated key figure F2 is calculated as follows:
Sales volume of customer A (1,200,000) -> does not fulfill condition (sales volume between 100,000 and 1,000,000) -> 0; sales volume of customer B (200,000) fulfills condition -> 1; sales volume of customer C (350,000) fulfills condition -> 1. When totaled, this gives 2 as the overall result for F2.
The overall result of the calculated key figure F3 is clarified by the table below:
Customer |
Country |
Region |
Sales Volume |
F1 |
F2 |
F3 |
S1 |
|
|
|
USD |
|
|
|
|
A |
US |
NY |
400,000 |
1 |
1 |
1 |
1 |
CA |
800,000 |
1 |
1 |
1 |
1 |
||
Result |
1,200,000 |
0 |
0 |
1 |
1 |
||
B |
US |
NY |
200,000 |
1 |
1 |
1 |
1 |
Result |
200,000 |
1 |
1 |
1 |
1 |
||
C |
US |
NY |
50,000 |
1 |
0 |
0 |
0 |
CA |
300,000 |
1 |
1 |
1 |
1 |
||
Result |
350,000 |
1 |
1 |
1 |
1 |
||
Overall result |
1,750,000 |
2 |
2 |
3 |
0 |