Show TOC Start of Content Area

This graphic is explained in the accompanying text Example of Exception Aggregation: Enhanced Counting  Locate the document in its SAP Library structure

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

 

 

End of Content Area