Show TOC

Extended Counting of Exception AggregationsLocate this document in the navigation structure

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.

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

    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