Skip to main content
Skip table of contents

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

-

-

-

-

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.