About
The consolidations operator define the consolidation path and can be used in formulas.
It defines how Essbase rolls up data for each member in a branch to the parent.
Article Related
List of operator
Operator | Representation | Description |
---|---|---|
+ | plus sign representing addition | The default operator. Essbase adds the member to the result of previous calculations performed on members of the branch. |
– | dash representing subtraction | Essbase multiplies the member by ‑1 and then adds the product to the result of previous calculations performed on members of the branch. |
* | asterisk representing multiplication | Essbase multiplies the member by the result of previous calculations performed on members of the branch. |
/ | forward slash representing division | Essbase divides the member into the result of previous calculations performed on members of the branch. |
% | percent sign representing percentage | Essbase divides the member into the sum of previous calculations performed on members of the branch. The result is multiplied by 100. |
~ | tilda representing no consolidation | Does not use the member in the consolidation to its parent. |
^ | Does not use the member in any consolidation in any dimension. |
Example
Consolidation operators define how Essbase rolls up data for each member in a branch to the parent.
For example, using the default addition (+) operator, Essbase adds 100‑10, 100‑20, and 100‑30 and stores the result in their parent, 100, as shown below in the TBC Product Dimension.
The Product dimension contains mostly addition (+) operators, which indicate that each group of members is added and rolled up to the parent.
Diet has a tilde (~) operator, which indicates that Essbase does not include the Diet member in the consolidation to the parent, Product. The Diet member consists entirely of members that are shared. The TBC product management group wants to be able to isolate Diet drinks in reports, so TBC created a separate Diet member that does not impact overall consolidation.
Precedence
Operator Precedence: Calculating Members with Different Operators (Effect of Position)
Essbase calculates the data of a branch in top-down order. For example, if you have, in order, two members tagged with an addition (+) operator and a third member tagged with a multiplication (*) operator, Essbase adds the first two and multiplies that sum by the third.
Because Essbase always begins with the top member when it consolidates, the order and the labels of the members is important.
When siblings have different operators, Essbase calculates the data in top-down order. The following section describes how Essbase calculates the members listed below:
Parent1
Member1 (+) 10
Member2 (+) 20
Member3 (-) 25
Member4 (*) 40
Member5 (%) 50
Member6 (/) 60
Member7 (~) 70
Essbase calculates Member1 through Member4 as follows:
(((Member1 + Member2) + (-1)Member3) * Member4) = X
(((10 + 20) + (-25)) * 40) = 200
If the result of this calculation is X, Member5 consolidates as follows:
(X/Member5) * 100 = Y
(200/50) * 100 = 400
If the result of the Member1 through Member4 calculation is Y, Member6 consolidates as follows:
Y/Member6 = Z
400/60 = 66.67
Because Member7 is set to No Consolidation(~), Essbase ignores Member7 in the consolidation.