REDISTRIBUTE

Category: Dimensionality & hierarchies

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 other 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. Use when you want to redistribute values only within defined clusters instead of across the full available detail.
Syntax	`REDISTRIBUTE('RedistributionValue', 'DistributionWeight' [, "ClusterLevels"])`
Parameters	RedistributionValue: Input node with values that need to be redistributed, for example `'RedistributionValue'`. DistributionWeight: Input node whose value distribution is used to calculate the redistribution, for example `'DistributionWeight'`. ClusterLevels: Array of levels used to cluster the redistribution, for example `"Category"`. If no cluster levels are inserted, all values in `'DistributionWeight'` will be treated as belonging to a single cluster.
Limitations	An error will appear if `'RedistributionValue'` and `'DistributionWeight'` have different levels. An error will appear if `"ClusterLevels"` contains levels that are not present in `'RedistributionValue'` and therefore in `'DistributionWeight'`. An error will appear if `"ClusterLevels"` contains multiple levels from the same dimension. The result unit will be the same as the `'RedistributionValue'` input.

Examples

Redistribute Within a Category Cluster

This example shows redistribution within the same "Category" cluster. Values are only spread to other rows inside that cluster and never to the source row itself.

Input node: 'RedistributionValue'

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

Input node: 'DistributionWeight'

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

Formula: REDISTRIBUTE('RedistributionValue', 'DistributionWeight', "Category")

Category	Type	Distribution Weight	Redistribute Truck	Redistribute Boat	→ REDISTRIBUTE 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 because they are the only rows in 'RedistributionValue' that have non-zero values.

For the Truck redistribution, Boat is not included because it is within a different "Category" value. For the Boat redistribution, there are no calculations because it is the only row within its category cluster.

Without clustering, the same input redistributes across all available rows instead of staying within category boundaries.

Formula: REDISTRIBUTE('RedistributionValue', 'DistributionWeight')

Category	Type	Distribution Weight	Redistribute Truck	Redistribute Boat	→ REDISTRIBUTE 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)) = -1.67	-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, for example Car, can occur when clustering is in use and there are multiple changes to rows within the same cluster.

Redistribute with Additional Non-Cluster Levels

This example adds a "Colour" level that is not part of the clustering level. The influence of that extra level is removed automatically when building the weight distribution used for the redistribution.

Input node: 'RedistributionValue'

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

Input node: 'DistributionWeight'

Category	Type	Colour	DistributionWeight
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

Formula: REDISTRIBUTE('RedistributionValue', 'DistributionWeight', "Category")

Before calculating the redistribution, the function removes the influence of "Colour" from the weighting node and builds the following internal distribution table automatically:

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

The resulting redistributed values are then calculated as follows.

Category	Type	Colour	Distribution Weight	Redistribute Truck White	Redistribute Truck Black	Redistribute Boat White	→ REDISTRIBUTE 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)) = 1.29	-	1.29
Land	Motorbike	White	5	2 * (15 / (20 + 15)) = 0.86	-	-	0.86
Sea	Boat	White	40	-	-	-	-

Related Functions

Function	When to use instead
DISTRIBUTE	When values should be distributed across the full available detail instead of staying within defined clusters.
REBOOK	When values should be moved from one level value to another instead of being redistributed using weights.