Planning Assistant

image_pdfimage_print

The Planning Assistant provides many options for entering data when consolidated elements are involved. It is especially useful for creating planning scenarios and entering data distribution along predefined ratios. It also enables controlled write-back, for when you do not want data to land in your cube immediately, and other steps, such as using named ranges. 

To open the Planning Assistant in a Jedox spreadsheet: right-click on a cube cell in a Jedox view and choose Planning Assistant from the context menu, as shown below.

To open the Planning Assistant in Jedox Excel Add-in: select a cube cell in a Jedox view and then click on the Planning Assistant button in the Jedox Ribbon, as shown in the screenshot below.

The Planning Assistant offers several ways to manipulate your data. These options are described below the screenshot.

Planning Type Description
Splashing Distributes a user-defined value in a consolidated cell across all the base cells in the aggregation.
Copy Copies the values from one cube slice, including their distribution on the base level, to another cube slice. 
Like Applies a user-defined value to a cube slice using the distribution values from a different cube slice on the base level.
Linear regression A prediction as linear regression is only possible if multiple elements (calculation base) of exactly one dimension are specified. Also, the final value of the multiple elements (calculation base) must not be zero.
Transfer Allows transfer of an amount from one element to another within one dimension. The subtotals in all other dimensions will stay fixed on their current values after the transfer.
Reallocate A hierarchy receives the new value X (with the option percentage of new value Y = old value*X%) and computes the values of sibling cells to preserve original values of all ancestor cells.
Hold Holds a value to prevent any further changes to it and its base values, through either direct changes or splashing the value. 

Optimizing time dimensions

Due to the way time dimensions are built, using any prediction function can return incorrect or inconsistent results. This is because the function reads the dimension by element ID. Therefore, it is important to have well-defined time dimensions for correct calculations.

You must optimize the time dimension by making sure that the distance between element IDs remains constant. This means that similar elements must have consecutive numbers as IDs. For example, to predict months:

ID name
1 2018-01
2 2018-02
3 2018-03

.
.
.

.
.
.

12 2018-12
13 2019-01
14 2019-02

To predict quarters:

ID name
180 Qtr 1 (2018)
181 Qtr 2 (2018)
182 Qtr 3 (2018)
183 Qtr 4 (2018)
184 Qtr 1 (2019)

.
.
.

.
.
.

204 Qtr 4 (2025)

To predict years:

ID name
2600 2018
2601 2019
2602 2020

.
.
.

.
.
.

2608 2025

If you want to use the predict function for the first time on this dimension, or if you want to add elements, the Month dimension has to be rebuilt.

Important: changing element IDs can cause data loss. The best way to change the IDs is by following these steps:

  1. Export all base values (without rule calculation) of cubes containing the Month dimension.
  2. Rebuild the Month dimension (so the element IDs are in the right increasing order again).
  3. Import all values.
image_pdfimage_print