Advanced Formula Calculation Accounts

Advanced formula calculations allow complex calculations, including built-in logic and formulas like average, period logic as well as multiplication and division. Advanced formula calculations are an integrated part of the consolidation process.

It is important to note that, which is also the case for calc accounts, multiplication and division must be used with care, if the value is analyzed by extended dimensions, as the resulting figure will be aggregated in a hierarchy. For best results, avoid division and multiplication in advanced formula calculation formulas calculated by extended dimensions, except for constants like division by 2 or multiplication by 150%. Instead, calculate different parts of a calculation independently. For example, the numerator and denominator in a division. Then perform the final calculation in the resulting report.

Note: You can use the different parts that are included in a calculation multiple times in a report, for example to calculate different key performance indicators.

Example

Imagine a simple product hierarchy with Vehicles as the top node, and with Bikes and Cars as the next level.

Figure 1. Simple product hierarchy
A simple product hierarchy with Vehicles as the top node, and with Bikes and Cars as the next level.

In this example, a certain calculation (KPI) is defined as C = A / B, where A is the numerator and B the denominator. You want C to be calculated on both Level 0 and Level 1. Calculating C with the support of the advanced formula calculation accounts could be done in two different ways:

  1. C is set up as an advanced formula calculation account at Level 1. A and B are normal accounts or advanced formula calculation accounts.
  2. C is calculated in the report. A and/or B are set up as advanced formula calculation accounts at level 1.

From a calculation point of view, you would have:

C = A / B (=1) and C = A / B (=0.1) respectively. If C is set up as an advanced formula calculation account, the system would automatically aggregate C to the next dimension level (Level 0). However, C is not defined as C + C (=1.1), but rather as C = A / B = (A + A) / (B + B) (=0.1818).

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 dimensions level, for example C dimension level 0 and C dimension level 1. Then, you need to use the appropriate advanced formula calculation account in your reports, that is, you need to be aware of that C level 1 will be aggregated to Level 0.

Scenario 2: If the calculation of C is done in the report instead, 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 they could also be reused in multiple KPI calculations in various reports.

Note that an advanced formula calculation performed on the top level only, that is, not analyzed by dimension, would succeed without the described numerator-denominator consideration.

Notes

  • You cannot link advanced formula calculation accounts to dimensions (use of linked structures).
  • Advanced formula calculation accounts are defined as statistical accounts.
  • You can use an advanced formula calculation account on another advanced formula calculation account.
  • If you create a ratio advanced formula calculation (key performance indicator) you need to define one advanced formula calculation account per dimension combination and you cannot drill on extended dimension levels.
  • Advanced formula calculation accounts can not be intercompany accounts. If the source is an intercompany account, the counterpart information will be removed.
  • You must define a dimension level for all advanced formula calculation accounts. The dimension level will decide at which level the calculation will occur. The dimension level must be the same or higher than the dimension level of the source accounts. If you do not want advanced formula calculations by dimensions, select level 0.
  • You can export or import advanced formula calculations as part of a structure. For more information, see Export Structures and Import Structures.