DISTRIBUTE


Description

Distributes the values of the Original Node based on the value distribution from the Distribution Node - in relation to the aggregated values of the Distribution Node when rolled-up to the levels of the Original Node. Can be used to redistribute values, which are not available on a certain level. An example is the assignment of OPEX to products according to their revenue. Levels can be specified to restrict the distribution to these levels, otherwise values are distributed to all available levels.

Notes

The function groups the values according to all shared chosen levels of OriginalNode and DistributionNode. In the example given here, this is the 'Year' level, and hence the distribution is applied to each year individually. If both nodes share a hierarchy, but have different levels, the DistributionNode needs to have the finer level, and is rolled-up to match the level of OriginalNode
Signature

DISTRIBUTE(OriginalNode, DistributionNode [, Level1,...])

Parameters
  • OriginalNode: Input node, specified using the node name in single quotes (e.g. 'OPEX')
  • Level: Level by which the OriginalNode should be disaggregated, i.e. the level which should exist on the node after application of the function (e.g. "Product")
  • DistributionNode: Node, by which distribution the values of the OriginalNode should be disaggregated. Specified using the node name in single quotes (e.g. 'Revenue')

Example

OPEX

Year

OPEX

2015

2000

2016

4000

Revenue

Product Dimension Year
Product Line Product 2015 2016
M1 100 200
M2 300 600
Z3 400 800
Totals 800 1600

DISTRIBUTE('OPEX', 'Revenue')  = DISTRIBUTE('OPEX', 'Revenue', "Year", "Product") =

Product Dimension Year
Product Line Product 2015 2016
M1 2000 * 100 / 800 = 250 4000 * 200 / 1600 = 500
M2 2000 * 300 / 800 = 750 4000 * 600 / 1600 = 1500
Z3 2000 * 400 / 800 = 1000 4000 * 800 / 1600 = 2000
Totals 2000 4000

DISTRIBUTE('OPEX', 'Revenue', "Year", "Product Line") =

Product Dimension Year
Product Line 2015 2016
2000 * 400 / 800 = 1000 4000 * 800 / 1600 = 2000
2000 * 400 / 800 = 1000 4000 * 800 / 1600 = 2000
Totals 2000 4000



Was this helpful?

Yes | Somewhat | No