Show TOC

Extended Counting of Exception AggregationsLocate this document in the navigation structure

Use

The following scenario shows how you can use the query definition to count the results of differently calculated key figures. The data is taken from the following example and enhanced: Counting Exception Aggregations.

You have loaded the following data into your InfoProvider:

Country

Region

Customer

Sales

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 the number of customers where the revenue is under 1,000,000 USD and calculate 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

    • Formula definition: Revenue <= 1,000,000

    • Exception aggregation: Total, reference characteristic: Customer

  • F2: Customers with revenue between 100,000 and 1,000,000

    • Formula definition: (Revenue >= 100,000) AND (Revenue <=1,000,000)

    • Exception aggregation: Total, reference characteristic: 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)

    • Formula definition: (Revenue >= 100,000) AND (Revenue <=1,000,000)

    • Exception aggregation: Maximum, reference characteristic Region

    The help key figure (H1) is used in F3:

    • Formula definition: Help key figure (H1)

    • Exception aggregation: Total, reference characteristic: Customer

  • The query would then deliver the following result:

    Country

    Region

    Customer

    Sales

    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 meet the condition (revenue between 100,000 and 1,000,000) - the result is 0,

    Revenue customer B (200,000) meets the condition - the result is 1,

    Revenue customer C (350,000) meets 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

    Sales

    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