Aggregations (statistical accounts)

When working with statistical accounts, you need to be aware of the numerator-denominator behavior as described in the example.

Imagine a simple product hierarchy with Vehicles as the top node (0) and with Bikes and Cars as the next level (1). In this example, a certain calculation (KPI) is defined as C = A / B, where A is the numerator and B the denominator. C is to be calculated on both Level 0 and Level 1.

Figure 1. Aggregating statistical accounts
In a product hierarchy, vehicles is the top node (0). Bikes and cars are at the next level (1). The A values are 1 for bikes and 1 for cars. The B values are 1 for bikes and 10 for cars.

Calculating C with the support of the advanced formula calculation accounts could be done in two different ways:

  • C is set up as an advanced formula calculation account at Level 1, and the division is part of the formula. A and B could be normal accounts or AFC/statistical accounts.
  • C is calculated in the report. A and/or B are set up as advanced formula calculation/statistical accounts at level 1.

From a calculation point of view:

Formula Results
CBikes = ABikes / BBikes CBikes = 1/1 = 1
CCars = ACars / BCars CCars = 1/10 = 0.1

If C is set up as an advanced formula calculation account, the FAP cube would automatically aggregate C to the next dimension level (Level 0). However, CVehicles is not defined as CBikes + CCars, but rather as CVehicles = AVehicles / BVehicles = (ABikes + ACars) / (BBikes + BCars.

Default aggregation for C on Level 0:

  • CVehicles = CBikes + CCars = 1 + 0.1 = 1.1

Expected result for C on Level 0:

  • CVehicles = AVehicles / BVehicles = (ABikes + ACars) / (BBikes + BCars = (1+1) / (1+10) = 2/11 = 0.1818

When defining these types of calculations you must be aware of this behavior. However, there are at least two possible alternative ways of handling this:

  • Scenario 1: If you want the calculation of C to be set up as an advanced formula calculation account, then different advanced formula calculation accounts should be created by dimension level. For example, C dimension level 0 and C dimension level 1. You then need to use the appropriate advanced formula calculation account in your reports; that is, you need to be aware of that C on level 1 will be aggregated to Level 0, and not be meaningful on this level.
  • Scenario 2: If the calculation of C is done in the report for all levels, no such aggregation would occur. A and/or B can be set up as advanced formula calculation accounts, use the built-in formulas and period logic, and be re-used in multiple KPI calculations in various reports.
Note: Please note that an advanced formula calculation performed on the top level only would work without the numerator-denominator consideration.