SECTIONSPlanning Units in Calculations
A plan cell can be assigned a Planning unit which describes the numeric value and provides additional meaning to it. For example, you might apply a currency or a time unit to a plan cell. For more information, see Planning Units and Applying a Planning Unit to a Plan Cell.
Depending on whether the plan is linked to a rate table and how the rate table is configured, calculations that involve units provide different results. Information about the most common cases is provided below. For more information about rate tables, see Rate Tables Overview.
Plans with a Single Unit
If your plan only contains a single unit, for example, all the values are in your corporate currency, the calculations are performed without any conversions. In this case, it isn't required for the plan to be linked to a rate table.
Cells Without a Unit
If your plan also contains cells that aren't assigned a unit, you can perform the following mathematical operations between a cell with a unit and a cell without a unit:
- Multiplication
- Division
- Exponentiation
If a cell doesn't contain a unit, you cannot add it to or subtract it from a cell that contains a unit.
Aggregate functions, such as SUM and AVG, cannot reference a mix of cells with a unit and cells without a unit. The only exception is the COUNT function. However, the cell that contains the COUNT function must not have a unit.
Plans with Multiple Units of the Same Type
If your plan contains values with multiple units of the same type, for example, multiple currencies, the plan must be linked to a rate table to perform any calculations between the different units. Before calculating the value, each operand is first converted into the unit of the cell that contains the calculation. The relevant conversion rate is retrieved from the rate table linked to the plan. If a relevant rate cannot be found for the combination of dimension values and specific unit, the value cannot be calculated and the cell displays a #UNIT error.
Aggregate functions, such as SUM and AVG, first convert each referenced value to the unit of the cell that contains the function using the relevant conversion rate for the referenced cell. The converted values are then aggregated.
References to Other Plans
Your plan might reference another plan that contains values in a different unit of the same type. For example, one plan might be in your corporate currency while the referenced plan is in a local currency for a specific geography. In this case, we recommend that you link both plans to the same rate table to ensure a consistent conversion. This is especially true when aggregating multiple values from the referenced plan using the SUM or AVG functions.
The aggregated values are calculated as follows:
- If the referenced plan is linked to a rate table, the relevant conversion rates are retrieved from that rate table.
- If the referenced plan isn't linked to a rate table, the relevant conversion rates are retrieved from the rate table linked to the plan that contains the aggregate function.
- If a specific conversion rate is not defined in the rate table linked to the referenced plan, the rate is retrieved from the rate table linked to the plan that contains the reference.
- In all other cases, the aggregate value is not calculated and the cell displays a #UNIT error.
Plans with Units of Different Types
If your plan contains cells with units of different types, for example, some values are in your corporate currency and other values are in hours, you cannot make calculations between them.