Planning Assistant: Transfer and Reallocate

image_pdfimage_print
Transfer

This mode allows an amount to be transferred from one element to another within one dimension. The subtotals in all other dimensions will stay fixed on their current values after the transfer. The target of the transfer can be a single element or multiple elements. The transfer of the value will only happen on cells that contain a value (i.e. are not empty) in both the source area and the target area.

To perform a transfer operation, click on a data cell and open the Planning Assistant.

Transfers can also be performed manually in both Jedox Web and Jedox Excel Add-in with the following entry in a cell containing a PALO.DATA() function:

<number> to <element name>[:<element name>:>element name>…]

Example: 100000 to “Keyboard GT”:Monitors

This action transfers 100,000 from the selected source element (Stationary PCs) to “Keyboard GT”, “TFT Monitor XA”, “TFT Monitor TL”, and “TFT Monitor XP”, proportional to their existing amounts. The amount of “All Products” doesn’t change.

There is another transfer syntax you can use if you start in a target cell:

<number> from <element name>

Important notes:

  • With transfers, there are no default values preselected for the slice for reallocation. You must always select them, as in our examples.
  • If you select a parent element in the Regions dimension (e.g. “Czech”), then this can be changed and generic splashing is possible without any transfer (equal to  # 50%).
Reallocate

In this mode, an element A within a hierarchy receives the new value X; the values of sibling cells are reallocated in order to preserve the original values of all ancestors cells. The percentage option applies value Y to this operation, where Y  = old value*X%. The search algorithms equal and relative may be applied to the operation, as described under the screenshot below.

To perform a reallocate operation, click on a data cell and open the Planning Assistant.

Siblings can be changed arbitrarily. There are several solutions. The first found solution will be entered.

For example, if the value of some child element is increased using reallocate splashing by 100,000, then the same amount (100,000) has to be subtracted from the sibling elements of the changed element in some manner. In this algorithm, the server locates a sibling on its own across any of the dimensions and subtracts the full value there. The user has no control over how the values will be reallocated.

This algorithm is also used if the user inputs the reallocate value (using the ? prefix) on a cell directly.

Equal

Only siblings of one dimension can be selected. All siblings specified for the reallocation must be changed by the same absolute amount. There is only one solution.

With regards to the example described above, the user would now specify one dimension in which the values of a set of elements should be reallocated to preserve the parent values. The difference between the old value and the new value is then spread equally across the specified siblings. If the target value increases by 100,000, and three sibling elements are specified for reallocation, then a third of the difference (33,333) is subtracted from the value of each of the siblings.

Relative

Only siblings of one dimension can be selected. All siblings specified for the reallocation must be changed by same percentage. There is only one solution.

The user again specifies a dimension and sibling elements within this dimension. However, unlike the equal operation, whereby the OLAP Server subtracts an equal value from each of the siblings, the relative operation calculates the relations of the sibling values and then adjusts their values, preserving these relations.

 

image_pdfimage_print