mapProjectionValues
¶
This function is used to map data from one or more projections in …
… while optionally shifting data in time or altering the valuation date. Many combinations of the above are possible.
Signature¶
mapProjectionValues(
VarName=varName,
ValnType=valnType,
hLevel=hLevel,
ValnDate=valnDate,
timeShift=0,
resolveMulti="sum"
)
Arguments¶
VarName
- The name of the variable to get. It defaults to the same name of the variable for which this function is applied.
ValnType
- The valuation type to reference. Default is to reference the same valuation type.
hLevel
- The hierarchy level from which to get the value(s). The default is to link to the same hierarchy level. Can
reference absolute levels, e.g. 1,2,5 or relative levels, e.g.
hLevel-1
,hLevel-2
, etc. ValnDate
- The valuation date from which to get the value. Defaults to the Valuation date of the Projection in which the
variable is defined. Global defined dates and hierarchy properties are often used as the date to reference. It is
recommended to use
the
EOMONTH
function to move between valuation dates. timeShift
- How many months the Projection Date should "shift". A time shift of
T
means that timet
in the projection node containing this formula will get results from timet+T
in the precedent projection node. E.g. to reference a value from 1 year ago, use-12
. resolveMulti
-
Determines how values from multiple projection nodes are combined into a resulting value. Defaults to
"sum"
. The following behaviours can be chosen when referencing a lower hierarchy level which may have more than one sub node:"sum"
- Adds the values in all lower hierarchy levels
"average"
- Take the average of values in lower hierarchy levels
"min"
- Takes the minimum value
"max"
- Takes the maximum value
"firstSeen"
- Takes the first hierarchy subnode in the hierarchy tree
"lastSeen"
- Takes the last hierarchy subnode in the hierarchy tree
Examples¶
Note
In the examples below, whenever a valuation date is mentioned, we only use the month name, for brevity. Instead
of February
, you would write something like DATE(2021,2,28)
or EOMONTH(DATE(2021,2,1),0)
.
The examples below are all demonstrated in this example workbook.
The provided model has the following company hierarchy:
- RootNode (
hLevel=3
)- NodeA (
hLevel=2
)- NodeA1 (
hLevel=1
) - NodeA2 (
hLevel=1
)
- NodeA1 (
- NodeB (
hLevel=2
)- NodeB1 (
hLevel=1
) - NodeB2 (
hLevel=1
)
- NodeB1 (
- NodeA (
There are two valuation types, valnTypeA
and valnTypeB
.
The model is configured to start on the projection node associated with NodeA
, with valuation type valnTypeA
and
valuation date January
.
The first time step (t=1
) in this projection node is December
(of the preceding year). This happens when there is a
formula that reads from a precedent which has an earlier valuation date than the projection node containing
the mapProjectionValues
call. See the valuation date example
below for a more detailed explanation.
Another valuation type¶
Variable MpvValuationType
demonstrates how to map data from another valuation type. It has the following formula:
mapProjectionValues(
"VarA",
"valnTypeB",
hLevel,
valnDate,
0,
"sum"
)
Say variable VarA
with valuation type valnTypeB
on NodeA
at valuation date January
has the following values:
t | Date | varA |
---|---|---|
1 | January | \(x_1\) |
2 | February | \(x_2\) |
3 | March | \(x_3\) |
4 | April | \(x_4\) |
Then MpvValuationType
with valnTypeA
on NodeA
will have the following values:
t | Date | MpvValuationType |
---|---|---|
1 | December | N/A |
2 | January | \(x_1\) |
3 | February | \(x_2\) |
4 | March | \(x_3\) |
Another valuation type with time shift¶
Variable MpvValuationTypeTimeShift
is similar to MpvValuationType
above. It reads from the same precedent, but it
shifts the data by the given number of time steps. Its formula is:
mapProjectionValues(
"VarA",
"valnTypeB",
hLevel,
valnDate,
-1,
"sum"
)
The results will be:
t | Date | MpvValuationTypeTimeShift |
---|---|---|
1 | December | N/A |
2 | January | N/A |
3 | February | \(x_1\) |
4 | March | \(x_2\) |
Another valuation type with valuation date shift¶
It's also possible to change the valuation date. This is not a time shift. Rather, it changes the precedent from which
data is mapped. Variable MpvValuationTypeValuationDate
demonstrates this. Its formula is:
mapProjectionValues(
"VarA",
"valnTypeB",
hLevel,
EOMONTH(valnDate, -1),
0,
"sum"
)
Now instead of reading from a precedent with valuation type valnTypeB
on NodeA
at valuation date January
, it will
read from a precedent with valuation type valnTypeB
on NodeA
at valuation date December
. Say the values in the
precedent look something like this:
t | Date | varA |
---|---|---|
1 | December | \(y_1\) |
2 | January | \(y_2\) |
3 | February | \(y_3\) |
4 | March | \(y_4\) |
Then MpvValuationTypeValuationDate
with valnTypeA
on NodeA
will have the following values:
t | Date | MpvValuationTypeValuationDate |
---|---|---|
1 | December | \(y_1\) |
2 | January | \(y_2\) |
3 | February | \(y_3\) |
4 | March | \(y_4\) |
Although the projection node containing the call to mapProjectionValues
has a valuation date of January
, its start
is brought forward to December
to allow us to see more of the data.
A different hierarchy level¶
It's possible to map data from other nodes in the company hierarchy. When mapping data from a higher hierarchy level, you are accessing data from a single ancestor of the current node. When mapping data from a lower hierarchy level, you are accessing data from one or more descendents of the current node.
Note
To map from hierarchy nodes which are not ancestors or descendents of the current node,
use mapExternalProjectionValues()
.
A higher hierarchy level¶
Variable MpvHigher
demonstrates how to map from the parent hierarchy node. Its formula is:
mapProjectionValues(
"VarA",
valnType,
hLevel+1,
valnDate,
0,
"sum"
)
This will map data from a precedent with valnTypeA
on RootNode
. Say the precedent has the following values:
t | Date | varA |
---|---|---|
1 | January | \(z_1\) |
2 | February | \(z_2\) |
3 | March | \(z_3\) |
4 | April | \(z_4\) |
The results for variable MpvHigher
will be:
t | Date | MpvHigher |
---|---|---|
1 | December | N/A |
2 | January | \(z_1\) |
3 | February | \(z_2\) |
4 | March | \(z_3\) |
Note that only the current branch (i.e. ancestors of the node containing the mapProjectionValues
call) is considered.
If there were another node on level 3, it would not be used. If this is not what you want, consider
using mapExternalProjectionValues()
instead.
All nodes of a lower hierarchy level¶
Variable MpvLower
demonstrates how to map from the children of the current hierarchy node. Its formula is:
mapProjectionValues(
"VarA",
valnType,
hLevel-1,
valnDate,
0,
"sum"
)
This will map data from precedents with the same valuation type, but on hierarchy nodes NodeA1
and NodeA2
.
Say the projection associated with hierarchy node NodeA1
has the following results:
t | Date | varA |
---|---|---|
1 | January | \(a_1\) |
2 | February | \(a_2\) |
3 | March | \(a_3\) |
4 | April | \(a_4\) |
Say the projection associated with hierarchy node NodeA2
has the following results:
t | Date | varA |
---|---|---|
1 | January | \(b_1\) |
2 | February | \(b_2\) |
3 | March | \(b_3\) |
4 | April | \(b_4\) |
Variable MpvLower
will contain the sum of the results of varA
in NodeA1
and NodeA2
:
t | Date | MpvLower |
---|---|---|
1 | December | N/A |
2 | January | \(a_1 + b_1\) |
3 | February | \(a_2 + b_2\) |
4 | March | \(a_3 + b_3\) |
Note that only the current branch (i.e. descendents of the node containing the mapProjectionValues
call) is
considered. Since both NodeA1
and NodeA2
are children of NodeA
, both are used. NodeB1
and NodeB2
are not used.
If this is not what you want, consider using mapExternalProjectionValues()
instead.
resolveMulti
¶
The resolveMulti
argument determines how the values from multiple projection nodes are combined at each time step. See
the arguments for a list of possible options. The default is "sum". You could also get the average, as
demonstrated by variable MpvLowerAverage
. Its formula is:
mapProjectionValues(
"VarA",
valnType,
hLevel-1,
valnDate,
0,
"average"
)
Variable MpvLowerAverage
will contain the average of the results of varA
in NodeA1
and NodeA2
:
t | Date | MpvLowerAverage |
---|---|---|
1 | December | N/A |
2 | January | \(\dfrac{a_1 + b_1}{2}\) |
3 | February | \(\dfrac{a_2 + b_2}{2}\) |
4 | March | \(\dfrac{a_3 + b_3}{2}\) |
Combined example¶
Many combinations of the examples above are possible. Variable MpvCombined
demonstrates one such combination. Its
formula is:
mapProjectionValues(
"varA",
"valnTypeB",
hLevel-1,
EOMONTH(valnDate, -1),
-1,
"average"
)
This will map data from variable VarA
in hierarchy nodes NodeA1
and NodeA2
(hLevel=hLevel-1
) when evaluated
using valuation type valnTypeB
, and valuation date December
(valnDate=EOMONTH(valnDate, -1)
). At each time step,
the average
is taken of the values at the previous time step (timeShift=-1
) in the precedents.
Say the precedents contain the following results:
Precedent 1 – NodeA1
with valuation type valnTypeB
and valuation date December
:
t | Date | varA |
---|---|---|
1 | December | \(c_1\) |
2 | January | \(c_2\) |
3 | February | \(c_3\) |
4 | March | \(c_4\) |
Precedent 2 – NodeA2
with valuation type valnTypeB
and valuation date December
:
t | Date | varA |
---|---|---|
1 | December | \(d_1\) |
2 | January | \(d_2\) |
3 | February | \(d_3\) |
4 | March | \(d_4\) |
Then MpvCombined
in our starting projection node will have the following results:
t | Date | MpvCombined | Explanation |
---|---|---|---|
1 | December | N/A |
Looks up November in all precedents, which is not available. |
2 | January | \(\dfrac{c_1 + d_1}{2}\) | Looks up December in all precedents. |
3 | February | \(\dfrac{c_2 + d_2}{2}\) | Looks up January in all precedents. |
4 | March | \(\dfrac{c_3 + d_3}{2}\) | Looks up March in all precedents. |