Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Edit Rollup Cells with Breakback

Explains how to use breakback methods in time, level, dimension, and account rollups.

Contains preview content for the upcoming 2019.3 release.

Audience: Planners who enter data in sheets.

rollup sums or averages the values of entries according to a pre-set structure. Rollup cells represent the rollup value of time, accounts, dimensions, or levels. 

Not all rollups are editable in sheets. Look for blue cells to edit rollups. See Rollup Cells in Sheets.

When to Use

Use when you know the desired total and the details or contributing factors aren't as important.

Before You Begin

  • Know the rollup calculation type to understand how the breakback works. See the Editable Rollup Cells section.

How You Get There

Compass.png From the nav menu select Sheets, Assumptions, or Processes. Once you open a sheet, select a version and choose a level. You must choose a leaf level to edit cells.

See Open Sheets and Get to Editable Cells.

Enter Rollup Values and Choose a Breakback Method

  1. Check the rollup calculation type.
  2. Enter a value in a blue cell and press Enter
  3. Choose a method:
    • Breakback Proportionally
    • Breakback Evenly
    • Breakback Proportionally Using Prior Year
    • For time rollups, you have additional options: 4-4-5, 4-5-4, 5-4-4, or Assumption.
  4. Select Apply.
  5. Save your sheet.

No Breakback Prompt

If you enter rollup values and aren't given a breakback option, either:

  • The sheet calculates the breakback automatically according to the general setup of the model. 
  • Or, the rollup calculation type is Last. The last contributing cell fills with the same value as the rollup.

Breakback Methods

Available for Account, Time, and Dimension Rollups 

For each method, the starting values are:

Cell 1: 100

Cell 2: 200

Cell 3: 300

Enter 1000 in the rollup.

Breakback Method What it Does For Sum Rollups For Average Rollups
Breakback Proportionally

Distributes in proportion to existing values in contributing cells.

 

Cell 1: 167

Cell 2: 333

Cell 3: 500

Cell 1: 500

Cell 2: 1000

Cell 3: 1500

Breakback Proportionally Using Prior Year

Uses the prior year's value for each contributing cell and distributes proportionally. 

Similar to breakback proportionally except uses the prior year rather than the current values in the cells

Similar to breakback proportionally except uses the prior year rather than the current values in the cells
Breakback Evenly

Divides the rollup evenly or copies the average into all contributing cells.

Cell 1: 333

Cell 2: 333

Cell 3: 333

Cell 1: 1000

Cell 2: 1000

Cell 3: 1000

 

Available for Time Only

The methods are weighted according to the pattern.

For each method, the starting values are:

Cell 1: 100

Cell 2: 200

Cell 3: 300

Enter 1000 in the rollup.

Method What it does For Sum Rollups For Average Rollups 
4-4-5 Heaviest weight goes to every third cell.

Cell 1: 308

Cell 2: 308

Cell 3: 385

923, 923, 1,154

Cell 1: 308

Cell 2: 308

Cell 3: 385

4-5-4 Heaviest weight goes to the second cell and every third cell thereafter.

Cell 1: 308

Cell 2: 385

Cell 3: 308

Cell 923

Cell 2: 1154

Cell 3: 923

5-4-4 Heaviest weight goes to the first cell. And every third cell thereafter.

385, 308, 308

Cell 1: 385

Cell 2: 308

Cell 3: 308

1,154, 923, 923

Cell 1: 1154

Cell 2: 923

Cell 3: 923

Assumption Drop-down menu with breakback methods custom-created for your domain. Varies Varies

Troubleshoot Rollup Cells

If your rollups aren't editable check the sheet type and rollup calculation type. See Get to Editable Cells for general sheet troubleshooting. 

Check the Sheet Type

  • Modeled sheets: Only time rollups are editable. 
  • Standard sheets:
    • Only time and account rollups are editable.
    • If levels are down the rows instead of accounts, only time rollups are editable. Right-click on the first column and select View by Accounts, so you can edit account rollups too.
  • Cube sheets: See the Rollup Cell Guidelines for Cube Sheets section.

Check the Rollup Calculation

Right-click rollup cells and select Cell Explorer to see how the rollup calculates:

time rollup in cell explorer

The table defines each calculation and indicates if it's editable and available to each rollup.

Rollup Type Editable in Sheets Time Levels and Custom Dimension Accounts
Sum: The total of the contributing cells. ✓ (for periodic accounts)
Average: The average counting blanks as zero values.  ✓     
Non-blank average: The average of the contributing cells, not counting blanks as zero values.    
Last:  The same as the last contributing cell. ✓ (for cumulative accounts)    
Weighted average: The average weighted by another account.    
Text: Read-only text as specified by your admin.      

Rollup Cell Guidelines for Cube Sheets

To edit rollup cells in cube sheets:

  • Add only one dimension to the rows. Read guidelines (following) if you must nest dimensions in rows.
  • Keep level and custom dimensions in the rows, on the vertical access. 

Nested Dimensions in Cube Sheet Rows

In cube sheets, time, accounts, levels and all custom dimensions are dimensions. A dimension is nested in rows when you have more than one in the rows. In the following image, time is the first dimension in the rows and accounts is the second. Accounts is nesting in time: 

Accounts nested in time

If you must have more than one dimension in rows, you can generally edit the rollups that are the last nested dimension. In the preceding image, accounts is listed second, so you can edit account rollups. If you switch the order of account and time, you can edit time rollups.

Level and Custom Dimensions Rollups in Cube Sheets

To edit level and custom dimension rollups, add them as the last dimension in the rows. You can't edit these rollups if they're:

  • In the columns.
  • The sole or first dimension in the rows.

 

  • Was this article helpful?