`mapProjectionValues`¶

This function is used to map data from one or more projections in …

another Valuation Type
a higher Hierarchy Level
all nodes of a lower Hierarchy Level

… 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 time t in the projection node containing this formula will get results from time t+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)
- NodeB (hLevel=2)
  - NodeB1 (hLevel=1)
  - NodeB2 (hLevel=1)

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.

mapProjectionValues¶