REDISTRIBUTE

Basic Overview

Description	Calculates a weighted redistribution of the RedistributionValue following the specified DistributionWeight. The redistribution stays within the boundaries of clusters, which can be specified by the provided ClusterLevels. Each value is redistributed exclusively on the elements within the same cluster, though not including the distributed element itself. For example, assume you have a dimension with the levels “Product Category” and “Product”. The function allows you to distribute a value for a single product across all others products within the same “Product Category”. The value will not be distributed onto any product in a different “Product Category”, and it will not be distributed on the product itself.
Signature	REDISTRIBUTE(RedistributionValue, DistributionWeight [, ClusterLevels])
Parameters	RedistributionValue: An input node with values that need to be distributed DistributionWeight: An input node with the distribution of the RedistributionValue on which the redistribution will be calculated on. ClusterLevels: An array of levels to cluster the distribution when calculating the redistribution. If no cluster levels are inserted, all values in ‘DistributionWeight’ will be treated as belonging to a single cluster.
Limitations	If any of the following points occurs, an error will appear: If ‘RedistributionValue’ and ‘DistributionWeight’ have different levels If ‘ClusterLevels’ contains levels that are not present in ‘RedistributionValue’ (and therefore in ‘DistributionWeight’) If ‘ClusterLevels’ contains multiple levels form the same dimension The result unit will be the same as the RedistributionValue input.

Example

An example is shown below, where “Category” and “Type” are from the same dimension:

‘RedistributionValue’ node: the input node with values that are to be redistributed

Category	Type	Value
Land	Car	-
Land	Truck	2
Land	Motorbike	-
Sea	Boat	-5

‘DistributionWeight' node: a node with the distribution weight values of the redistribution values

Category	Type	DistributionWeight
Land	Car	10
Land	Truck	20
Land	Motorbike	30
Sea	Boat	40

If the function was executed clustering on the “Category” level, e.g. REDISTRIBUTE(‘RedistributionValue’, ‘DistributionWeight', [“Category”]) then the redistribution of the RedistributionValue would be as follows:

Category	Type	DistributionWeight	Redistribute Truck	Redistribute Boat	Result
Land	Car	10	2 * (10 / (10 + 30)) = 0.5	-	0.5
Land	Truck	20	-	-	-
Land	Motorbike	30	2 * (30 / (10 + 30)) = 1.5	-	1.5
Sea	Boat	40	-	-	-

Redistribution calculations only need to be performed for “Truck” and “Boat” as they are the only rows in the RedistributionValue table that have non-zero values.
For the “Truck“ redistribution the “Boat” is not included as it is within a different “Category” level value.
For the “Boat” redistribution as it is the only row that is within the category cluster there are no calculations that need to be done.

If the same RedistributionValue was used but the function was called with no clustering of levels, e.g. REDISTRIBUTE( ‘RedistributionValue’, ‘DistributionWeight') then the redistribution of RedistributionValue would be as follows

Category	Type	DistributionWeight	Redistribute Truck	Redistribute Boat	Result
Land	Car	10	2 * (10 / (10 + 30 + 40)) = 0.25	-5 * (10 / (10 + 20 + 30)) = -0.83	0.25 + (-0.83)= -0.58
Land	Truck	20	-	-5 * (20 / (10 + 20 + 30)) = -0.83	-1.67
Land	Motorbike	30	2 * (30 / (10 + 30 + 40)) = 0.75	-5 * (30/ (10 + 20 + 30)) = -2.5	0.75 + (-2.5) = -1.75
Sea	Boat	40	2 * (40 / (10 + 30 + 40)) = 1	-	1

Note: cases where there are multiple values for a single line (e.g., for “Car”) can occur when clustering is in use and if there are multiple changes to rows within the same cluster.

Example 2

In a case where there are levels included in the RedistributionValue frame that are not within the same dimension of the clustering level(s), it is required to remove the influence of these levels when calculating the “value” in calculations, which gives the distribution of the rows within the clustered levels. Though these levels are still used to match rows between the RedistributionValue and DistributionWeight tables.

An example of this can be shown by expanding the RedistributionValue from above by adding a “Colour” level:

‘RedistributionValue’ node: the input node with values that are to be redistributed

Category	Type	Colour	Value
Land	Car	White	-
Land	Car	Black	-
Land	Truck	White	2
Land	Truck	Black	3
Land	Motorbike	Black	-
Land	Motorbike	White	-
Sea	Boat	White	-5

‘DistributionWeight’ node: a node with the distribution weight values of the redistribution values.

Category	Type	Colour	Distribution
Land	Car	White	10
Land	Car	Black	10
Land	Truck	White	10
Land	Truck	Black	20
Land	Motorbike	Black	10
Land	Motorbike	White	5
Sea	Boat	White	40

If the function was executed with clustering on the “Category” level again, e.g. REDISTRIBUTE(‘RedistributionValue’, ‘DistributionWeight', [“Category”]), then it first requires a distribution table to be calculated that removes the influence of “Colour” by dropping the level from the DistributionWeight table. This happens automatically.

Category	Type	DistributionWeight
Land	Car	20
Land	Truck	30
Land	Motorbike	15
Sea	Boat	40

The new redistribution result values are calculated as follows:

Category	Type	Colour	DistributionWeight	Redistribute Truck White	Redistribute Truck Black	Redistribute Boat White	Result
Land	Car	White	10	2 * (20 / (20 + 15)) = 1.14	-	-	1.14
Land	Car	Black	10	-	3 * (20/ (20 + 15)) = 1.71	-	1.71
Land	Truck	White	10	-	-	-	-
Land	Truck	Black	20	-	-	-	-
Land	Motorbike	Black	10	-	3 * (15/ (20 + 15)) = 129	-	1.29
Land	Motorbike	White	5	2 * (15/ (20 + 15)) = 0.86	-	-	0.86
Sea	Boat	White	40	-	-	-	-5