With goalseek splashing, an element A within a hierarchy receives the new value X (with option percentage, the new value Y = old value*X%), and goalseek computes the values of siblings cells in order to preserve original values of all ancestors cells.
For goalseek, four search algorithms are available: complete, equal, relative, and transfer.
To perform goalseek splashing, open the Splashing Wizard and select goalseek. The following dialog appears:
The goalseek algorithms are described below:
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 via goalseek splashing by 100,000, then—since goalseek is used, and the value of parent elements has to be preserved—the same amount (100,000) has to be subtracted from the singling 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 goalseek value (using the ? prefix) on a cell directly.
Only siblings of one dimension can be selected. All siblings specified for the reallocation must be changed by the same absolute amount. There exists only one solution.
With regards to the example described above, the user would now specify one dimension, and in that dimension, a set of elements, where values should be re-allocated 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 were specified for re-allocation, then a third of the differencev(33,333) is subtracted from the value of each of the siblings.
Only siblings of one dimension can be selected. All siblings specified for the reallocation must be changed by same percentage. There exists only one solution.
The user again specifies a dimension, and sibling elements within this dimension. However, the OLAP Server now will not subtract an equal value from each of the siblings. Instead, it calculates the sibling values’ relations, and then adjusts their values preserving these relations.
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 hold a value (i.e. are not empty) in both the source area and the target area.
This mode is only available with Splashing Wizard of Jedox Excel Add-in; it is not yet implemented in the Jedox Web Splashing Wizard. However, the transfer can 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>
The highlighted value 600,000 should change to 800,000. Distribution area: Slovakia, Poland, Austria.
- With goalseek splashing, there are no default values preselected for the slice for reallocation. You must always select them, as in our examples.
- If you select in the Regions dimension a parent element (e.g. “Czech”), then this can be changed and generic splashing is possible without any goalseek (equal to # 50%).