Calculation Breakback

4 Minutes to read

Print
Dark
Light
PDF

Article summary

Did you find this summary helpful?

Thank you for your feedback

Calculation Breakback Basics

Breakback requires several pieces of information:

Model name
Parent-level data intersection from which to allocate data to children
Spread method
Type of change to make: amount or percentage increase/decrease
Value of the change to make
Data intersection to use as a reference for the Breakback (optional)
Hold members (optional, child members that will be skipped in the Breakback)

You can provide the required information using a Breakback button on the Calculation ribbon.

This button functions similarly to the Breakback button in Views and Reports. The main difference is that you must also specify the parent-level data intersection; in views and reports, this is the data cell that your cursor is on before you select Breakback.

Note:
Breakback honors data locking. If a member or member combination is locked from input, then Breakback will treat those members as if they are on Hold.

Calculation Breakback, Value Input Example

This example uses similar choices as are demonstrated in the help topic for Breakback from a view or report. See Using Breakback based on Current Data with % Change (Increase or Decrease). We want to increase the Expense (account 6000) amounts by 3%. Here is the view before the calculation is run, with the Breakback Cell highlighted.

How to Add Breakback to a Calculation in SpotlightXL?

In SpotlightXL, select Model > Calculation.
Select a model from the Model drop-down.
Give the Calculation a name and click Save.
Under Type, select Breakback.
Under Name, type None.
Click Save. Notice that the Variables section is now populated with several variables.
Select the cell with all the Variables and licck the Breakback button on the ribbon.
Select the model from the Model drop-down. By default, the model specified in the calculation is selected.
In the Breakback Cell area, specify the parent-level data intersection. This is the highest level parent that you will base the allocation from. The allocation will go to the leaf-level children.
In the Spread area, select Current. For information on spread types, see Breakback Types.
In the Value area, select % Increase.
Select Input because we will manually input the % increase that we want by typing 3 in the % box.
Skip the Hold area and click Update. Now you are returned to the calculation where you can add more commands.
In the second row of the calculation, select Aggregation from the Type column.
Under Name, type None and click Save.

Note:

If you want additional Breakbacks that are similar to the one you have already added, you can copy the Breakback row to a new row in Excel. Then when you put your cursor on the row under the Variables column and select Breakback from the ribbon, it is pre-populated with the last Breakback, and you can make changes.

Your calculation is ready to run.

After running the calculation and refreshing the View, here is the resulting data.

Calculation Breakback, Value Reference Example

You can set up Breakback to refer to a data intersection in your model as the basis for the allocation. For example, if you do not know the exact value of the % Increase or the Value Input, or if you want the % Increase or Value to be variable, you can specify a data cell from the model to reference for the value.

Using a Cell in the Model as the Value for the Allocation

Use a report to set up a form for a user to enter the amount to use or the amount to increase or decrease.
Use the cell intersection coordinates from the report as the reference for the Breakback in the calculation.
Use one Breakback row in the calculation for each amount entered by the user.
Add the name of the calculation to the report Calculation on Save so that each time the user saves, the Breakbacks are calculated.

Example

The following example uses a report Sales Increase % to let users input values for regional sales increases. The values inputted by the user are used in the Breakback calculation to increase Sales values in the model. The report automatically runs the calculation when the user saves their input values.

Note:

If the user enters 0 for a % increase or decrease or for a value change, Breakback skips the operation and no data is updated.

Use a report to set up a form for a user to enter regional sales increases.
Note the data intersection from the report using Design Manager.
In the calculation, add a Breakback row, and use the cell intersection coordinates from the report as the reference for the % Increase.
Use one Breakback row in the calculation for each amount entered by the user.
Note the name of the calculation.
Add the name of the calculation to the report Calculation on Save so that each time the user saves, the Breakbacks are calculated.

Note:
After entering values in the report, users must select Save Data to ensure that their inputs are used by the Calculation and Breakback.

Using Substitution Variables in Breakback Operations from a Calculation

You can use substitution variables in place of member names in Breakback operations within a calculation.

This feature is supported both in SpotlightXL and Spotlight.

When defining Breakback operations in a calculation, you can use the member select icon to select a Substitution Variable instead of a fixed member name in the Breakback cell.