Show TOC

# Extended Counting of Exception Aggregations

Use

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.

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

Note

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