The following scenario shows how you can count the results of differently calculated key figures in the BEx Query Designer. The data is taken from the following example and extended: Counting Exception Aggregations.
You have loaded the following data into your InfoCube:
Country |
Region |
Customer |
Revenue |
---|---|---|---|
USD |
|||
US |
NY |
A |
400.000 |
B |
200.000 |
||
C |
50.000 |
||
CA |
A |
800.000 |
|
C |
300.000 |
In the query you now want to determine not only the number of customers where the revenue is under 1,000,000 USD but also calculate the the following values:
Number of customers with revenue between 100,000 and 1,000,000
Number of customers with revenue between 100,000 and 1,000,000 in at least one region
To be able to calculate these values, you must create the following calculated key figures:
F1: Customers with Revenue <= 1,000,000
Tab page General: Formala Definition: Revenue <= 1,000,000
Tab page Aggregation: Exception Aggregation: Total; Reference Feature: Customer
F2: Customers with revenue between 100,000 and 1,000,000
Tab page General: Formula Definition: (Revenue >= 100,000) AND (Revenue <=1,000,000)
Exception Aggregation: Total; Reference Feature: Customer
F3: Customers with revenue between 100,000 and 1,000,000 in at least one region
For this key figure you need the help key figure H1 (to aggregate the regions with revenue between 100,000 and 1,000,000 USD)
Tab page General: Formula Definition: (Revenue >= 100,000) AND (Revenue <=1,000,000)
Tab page Aggregation: Exception Aggregation: Maximum; Reference Feature: Region
The help key figure (H1) is used in F3:
Tab page General: Formala Definition: Help Key Figure (H1)
Tab page Aggregation: Exception Aggregation: Total; Reference Feature: Customer
The query would then deliver the following result:
Country |
Region |
Customer |
Revenue |
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 grand total of the calculated key figure F2 is calculated in the following way:
Revenue Customer A (1,200,000) does not fulfill the condition (Revenue between 100,000 and 1,000,000), the result is 0,
Revenue Customer B (200,000) does fulfill the condition, the result is 1,
Revenue Customer C (350,000) does fulfill the condition, the result is 1;
when totalled together the grand total for F2 is 2.
The grand total of the calculated key figure F3 is illustrated by the following view:
Customer |
Country |
Region |
Revenue |
F1 |
F2 |
F3 |
H1 |
---|---|---|---|---|---|---|---|
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 |