Creating and Editing Formulas in Lucanet xP&A

Fill Down in Lucanet xP&A changes references to the source variable so that each copied formula refers to its own destination variable. Time modifiers, such as previous month, are retained. References to other variables keep pointing to those variables; they do not shift to neighboring rows as relative cell references do in MS Excel.

The following examples show formulas copied from Revenue to Profit in Lucanet xP&A:

Skip empty cells controls which underlying cells are included in splashing, independently of the selected splashing type.

0 is a value, not an empty cell. Values calculated by formulas, including values inherited from a higher level, also count as existing values.

In hierarchies, branches whose underlying lowest-level items are all empty are excluded when Skip empty cells is activated. The selected splashing type then operates on the remaining items.

If all underlying items are empty, Lucanet xP&A cancels the distribution and displays the following message: "There are no non-empty items to distribute the value to". Deactivate Skip empty cells to distribute into those empty items.

Skip empty cells controls only the distribution to underlying items. You can still enter a value directly into an individual empty lowest-level cell.

Example: For a flat dimension with four items containing 0, 8, empty, and empty, entering 800 produces:

With Pro-Rata, if all included values are zero, the amount is split equally at each level among the included items. Empty items remain excluded when Skip empty cells is activated.

The following splashing types are available in Lucanet xP&A.

You can use the Equal split splashing type to evenly distribute a value to each level.

The total amount will be equal to the entered number. If you have multiple levels, the value will first be distributed between the higher levels and then evenly between the included child items at each subsequent level.

Example:

The following example shows the result after you enter 800 at the total level. The example applies to underlying items that already contain values, or to empty items with Skip empty cells deactivated:

You can use the Pro-Rata splashing type to distribute values across each level proportionally to their current value.

Example:

The following example shows dimension items that already have values:

After entering 800 on the total level and applying Pro-Rata splashing, the values are distributed as follows:

You can use the Repeat all leaves splashing type to repeat the entered value on each included lowest-level item. Repeat all leaves is the default setting for value distribution.

Example:

The following example shows the result after you enter 800 at the total level. The example applies to underlying items that already contain values, or to empty items with Skip empty cells deactivated:

You can use the Repeat splashing type to repeat the entered value on each included first-level item, then split that value evenly at each subsequent level.

Example:

The following example shows the result after you enter 800 at the total level. The example applies to underlying items that already contain values, or to empty items with Skip empty cells deactivated:

To distribute a value in a filtered view in Lucanet xP&A, check which dimension items the view includes. A view filter changes the available items and can change the total displayed in the view. The view filter does not remove the excluded items from the model.

Editing in a filtered view in Lucanet xP&A requires the appropriate editing or proposal permissions and an available splashing method. The following table describes how dimension filters and display settings in Lucanet xP&A affect editing:

For example, a dimension in Lucanet xP&A contains parent B 1 with children B 2 and B3, and a separate item B4. A view that includes the branch for B 1 lets you distribute 67 to B 2 and B3 using Equal split, assigning 33.5 to each. B4 retains its existing value or formula. The filtered total covers the included branch and can differ from the total across all items in the model.

If additional view filters exclude items from the selected branch, check the resulting view before editing. Selecting includes branch for does not override other view filters or permissions. For more information about configuring filters, see Using Views.

In Lucanet xP&A, a parent row can remain visible when a view filter excludes its children. The row may look like a lowest-level item, but it still represents underlying items in the model. To edit a parent total, your current view must give you the required access to all underlying items. If any of those items are excluded, the parent total is read-only.

For example, if a view uses is one of to select B 1 alone, its children B 2 and B3 are excluded and the B 1 input is read-only. If you include the full B 1 branch instead or only collapse its child rows, the B 1 input remains editable.

The same restriction applies if B 1 and B 2 are accessible but B3 is excluded. You still cannot edit the B 1 total. Lucanet xP&A does not silently distribute the value only to B 2.

In the user interface, the blocked formula input can display Cannot edit — child items hidden. The total-cell tooltip explains: You can't edit this total because this view doesn't give you access to all the items underneath it.

If you cannot edit a parent total in Lucanet xP&A, edit an available individual item or an included branch instead. If your permissions allow it, use a view that includes the required branch and its underlying items. Seeing a parent row does not grant access to its children.