REBOOK
Basic Overview
AVAILABLE SINCE 5.3.0
Description | The REBOOK function can be used in all situations where value measures need to be “booked” to a different value in the same level. The FilterOperation allows further specifications on which level values should be rebooked. |
Signature | REBOOK('Node', "Level", "OldValue", "NewValue" <, "FilterOperation">) |
Parameters |
|
Limitations |
|
Explanations of FilterOperations:
EQ = The measures of the level value equal to the “OldValue” are rebooked to the “NewValue” (default)
NEQ = All measures of the level values not equal to the “OldValue” are rebooked to the “NewValue”
GT = All measures of the level values greater than the “OldValue” are rebooked to the “NewValue”
GTE = All measures of the level values greater than or equal the “OldValue” are rebooked to the “NewValue”
LT = All measures of the level values less than the “OldValue” are rebooked to the “NewValue”
LTE = All measures of the level values less than or equal the “OldValue” are rebooked to the “NewValue”
The “OldValue” is the leading indicator. As the dimension list is sorted in a lexicographic order, “greater” targets all levels that start with a letter that alphabetically comes after the first letter of the “OldValue”.
Example
Before:
Revenue:
Year | Segment | Revenue |
---|---|---|
2020 | MINI | 100 |
2020 | COMPACT | 50 |
2020 | LARGE | 10 |
After:
Example 1:
REBOOK('Revenue', “Segment”, “MINI”, “LARGE”) =
Year | Segment | Revenue |
---|---|---|
2020 | MINI | 0 |
2020 | COMPACT | 50 |
2020 | LARGE | 110 |
With this formula, the value measure of 'MINI' is rebooked to 'LARGE'.
As LARGE already had a revenue of '10', the rebooked value of '100' is added (=110).
Calculation:
Year | Segment | Revenue |
---|---|---|
2020 | MINI | 100 (-100) |
2020 | COMPACT | 50 |
2020 | LARGE | 10 (+100) |
Example 2:
REBOOK('Revenue', "Segment", [“MINI”, “COMPACT”], “LARGE”) =
Year | Segment | Revenue |
---|---|---|
2020 | MINI | 0 |
2020 | COMPACT | 0 |
2020 | LARGE | 160 |
With this formula, the value measures of 'MINI' and 'COMPACT' are rebooked to 'LARGE'.
The 'OldValue' measures are combined and add up to the measure of 'LARGE'.
Calculation:
Year | Segment | Revenue |
---|---|---|
2020 | MINI | 100 (-100) |
2020 | COMPACT | 50 (-50) |
2020 | LARGE | 10 (+100+50) |
Example 3:
REBOOK('Revenue', “Segment”, “MINI”, “LARGE”, “NEQ”)
Year | Segment | Revenue |
---|---|---|
2020 | MINI | 100 |
2020 | COMPACT | 0 |
2020 | LARGE | 60 |
As the FilterOperation is set to “NEQ”, all measures of the levels not equal to the “OldValue” (MINI) are rebooked to the “NewValue” (LARGE).
Calculation:
Year | Segment | Revenue |
---|---|---|
2020 | MINI | 100 |
2020 | COMPACT | 50 (-50) |
2020 | LARGE | 10 (+50) |