Managing Plan Lines

Use the Plan Lines tab to manage your plans and apply calculations.

Notes:
  • Use the search box above the grid to refine the displayed content and help you find the information you are looking for. You can search the content of any of the displayed columns.
  • While editing a field, press Escape before pressing Enter to revert it to its previous value.

Managing Rows and Columns in Plan Lines

To manage plan lines effectively, you need rows and columns in the table. The rows and columns are the basis for performing calculations and interpreting the data. You need to add new rows and columns to refine calculations and gain insights into the data and for that, the correct structure is important for maximizing data usefulness and ensuring accurate calculation, whether working with one or multiple plan lines.

Note:

You can drag and drop rows in a plan to change the row order.

To insert rows in the plan table from the Plan Lines tab:

  1. Click | Insert Row or right-click the table where the dimensions are located and click .The Insert Row window appears. By default, a new row will be added to the bottom of the plan. To insert a row on a specific line, you must right-click.
  2. Fill out the values based on the dimensions. You can type and create a new dimension value or search and choose one from the list. Ensure that each value entered is unique and not repeated. The duplicated values will not be added to the table.
    Example: You choose a row that shows both "Company" and "Account" dimensions, and there's already a row in the table with the values "Company A" and "Account A". You can add another line with "Company A" and "Account B" values. This results in a duplicate value but not a duplicate combination.
  3. Click Insert or Insert & New. The new row is added.

To insert columns in the plan table from the Plan Lines tab:

  1. Click | Insert Column or right-click the table where the measures are located and click . The Insert Column window appears. By default, a new column will be added to the bottom of the plan. To insert a column on a specific line, you must right-click.
  2. Fill out the values based on the measures. You can type and create a new measure value or search and choose one from the list. Ensure that each value entered is unique and not repeated, as duplicated values will not be added to the table.
    Example: You choose a column that shows "2019", "001", and "Count of Home Value" measures, and there's already a column in the table. You can add another column with "2020", "002" and "Total" values. This results in a duplicate value but not a duplicate combination.
  3. Click Insertor Insert & New. The new column is added.
Note:

You can drag and drop rows in a plan to change the row order.

To insert total rows in the plan table from the Plan Lines tab to get the total of all dimensions:

  1. Click | Insert Total Row or right-click the table where the dimensions are located and click . The Insert Total Row window appears. By default, a new row will be added to the bottom of the plan. To insert a row on a specific line, you must right-click.
  2. Fill out the values based on the dimensions. You can type and create a new dimension value or search and choose one from the list. Ensure that each value entered is unique and not repeated. The duplicated values will not be added to the table.
    Example: You choose a row that shows both "Company" and "Account" dimensions, and there's already a row in the table with the values "Total" and "Total". You can add another line with "Total" and "Total" values. This results in a total of duplicate value but not a duplicate combination.
  3. Click Insert. The new total row will be added to the table in the appropriate hierarchy. If a record in the measure has been selected, a new total row will be added to the bottom of the selected measure. Each row in the plan table had a formula set up.
    Example: In the first column, we have a function that displays the year 2019 or period 001, followed by the Sum of Home Value. If you check the other cells, you will notice that the period changes. When we move to the new period 2020, both the year and the period change in all columns. You can undo the changes by clicking .
  4. Click Insert & New to add a new total row to the table in the appropriate hierarchy and add another total row.

To insert total columns in the plan table from the Plan Lines tab to get the total of all measures:

  1. Click | Insert Total Column or right-click the table where the measures are located and click . The Insert Total Column window appears
  2. Fill out the values based on the measures. You can type and create a new measure value or search and choose one from the list. Ensure that each value entered is unique and not repeated, as duplicated values will not be added to the table.
    Example:
    • You choose a column that shows "2019", "001", and "Count of Home Value" measures, and there's already a column in the table. You can add another column with "Total", "Total" and "Total" values. This results in adding a new total column at the right side of the plan table with the total of values. If you check the other cells, you will notice that the values are changed. You can undo the changes by clicking .
    • You can also choose to calculate the total for the whole year 2019. You can add a column with Years as "Total", Period as "All Periods" and Measure as " Sum of Home Value". A new total column is added in the Year "2019" summing the total values.
  3. Click Insert. The new total column will be added to the table in the appropriate hierarchy. If a record in the measure has been selected, a new column will be added to the bottom of the selected measure.
  4. Click Insert & New to add a new total column to the table in the appropriate hierarchy and add another total column.

To delete a row from the plan table on the Plan Lines tab, perform one of the following:

  1. To delete one row, right-click the row and click Delete Row. The row is deleted.
  2. To delete multiple rows, select the checkbox next to the row and click . The row is deleted.

To delete a column from the plan table on the Plan Lines tab, perform one of the following:

  1. To delete one column, right-click the column and click . The column is deleted.
  2. To delete multiple columns, select one or more columns using the shift button on your keyboard, right-click the columns, and click Delete Columns.
Note: Click Undo on the Plan Lines tab toolbar to undo an action.

Adding Calculations to Plan Lines

From the Formula Builder Lightning component, you can use calculations to manipulate and extend the data displayed in your plan lines. Depending on your plan requirements, you can apply formulas to the values displayed under the Measure columns. For example, you might want to sum the value of all displayed columns in your plan items. You can use dimension fields to filter values and create the required formula.

The filter fields depend on the plan and the contained dimensions. For example, if the dimensions include the Company Name and Country fields, then those fields will be available as filters. After the filters are applied, the filtered cells are highlighted.

When using the Formula Builder component, the cell you are working on is clearly highlighted, with the surrounding cells bordered to help you identify it and stay focused. The cell you want to add the formula to is always clearly marked to help you keep track of its location.

Notes: When applying a formula in a cell, the following functionalities are disabled:
  • Deleting rows or columns. This results in the context menus for disabled cells.
  • Redoing or undoing the actions, as all the toolbar buttons are disabled.
  • Editing cells while a formula builder is active.
  • Sorting the columns.

To apply a formula or manually enter a formula in a cell, follow these steps:

  1. Click the row in the plan table where calculations are set for a cell.
  2. Enter calculation values in the Formula field. To separate multiple values, press Enter and click the Formula syntax. You can also vertically expand the Formula field.
    • After you specify the calculation value in the Formula field, the calculation is evaluated and applied for the selected cell. For more information, see Formula Syntax.
    • When you manually update any numeric cells, the calculation automatically applies to other dependent or related cells.
    • When you modify a numeric value, it triggers the formula to recalculate any affected calculations.
    • If you want to undo or redo the update, use the Undo and Redo action buttons and the changes on the dependent cells will also be undone or redone accordingly.
  3. [Optional] Select the Include Calculations checkbox to include other formulas. This will enable you to combine formulas in your current formula to create a grand total. For example, if you have a formula to calculate the total for each dimension, you can use this feature to combine these formulas. The cell you want to add the formula to is always clearly marked to help you keep track of its location. If you deselect the checkbox, you cannot include cells containing the formula in your new formula.
  4. [Optional] Select the Absolute References checkbox to copy a cell with absolute dependencies, paste it in a new location, and set the absolute character on all dimensions filtered in your formula. If you do not want all filtered dimensions to contain the absolute character, select the checkbox and apply the formula. Then, deselect the checkbox if you do not need it. For more information, see Copying and Pasting Cell in Plan.
  5. Filter the dimensions based on the available dimensions. The selected plan cells are highlighted to show inclusion in formulas.
  6. Specify the values in the available fields, based on the plan and the contained dimensions, to filter the measure rows. This determines the highlighted measure cells that indicate what is being selected for inclusion in your formula.
  7. Click Save to save the calculation for the plan line.
Notes:
  • Ensure that you specify a numeric value. A non numeric value is considered as a formula.
  • The numeric values that are added are updated at runtime. The value gets reflected in the plan cell when you stop typing in the Formula field for a certain amount of time.
  • An error message is displayed if an incorrect formula is entered in the Formula field. For more information about the error, see Plan Errors.

Manually Entering Formulas

You can manually enter a string to apply a formula in the Plan Lines tab or Measure columns.

To add a row at the bottom of the plan to show the sum of rows contained in your selected measure.

  1. Click | Add Row and give this section the name Total.
  2. Enter the string for the required formula: SUM("Year"="2019") with Year being a dimension, or SUM("Account"="Account 1"), with Account being a dimension.

To add a measure column displaying the total amount for a selected range of years. For example, let's say you want to calculate the total for the years 2019 and 2020, including all the periods.

You can add a column at the plan's right to show the sum of periods contained in your selected year. To do so:

  1. Click | Add column and give this section the name Total. A new column with the Total heading gets added.
  2. Select the cell at the bottom of the corresponding column to calculate a particular measure for a specific year in your plan. You can either enter the formula manually or click on "fx SUM()". All cells for that measure under the chosen year will be highlighted. If you haven't selected any measures from the filter, the default measure will be used for the calculation. However, if you choose a specific measure, the calculation will be performed for that measure across all periods in the selected year, and the recalculated total will be displayed.
  3. Click Save to save to the formula and display the recalculated value.
Note: If you modify any cells for that measure under the chosen year, then the total recalculated value is also modified.

To calculate the yearly total by accumulating the totals for each period. To do so:

  1. Create a new row with values as "Accumulative" and "Total". This row will be used to store the total amounts for each period as well as the overall total for the year.
  2. Select the total of the first period and click on the "fx SUM()" from the Formula field to calculate the total. The total amount for the first period is displayed.
  3. Now, move on to the second period and select the total amount for the previous period. Select the cell in the "Total" column for the current period and click on the "fx SUM()" from the Formula field again. This will calculate the total based on the total of period 1 and period 2.
  4. Repeat above steps for each period, selecting the total amount for the previous period and the corresponding cell in the "Total" column for the current period. Once you have completed all the periods, the "Total" cell in the "Accumulative" row displays the overall total for the year. You can easily calculate the yearly total by accumulating the totals for each period.

Formula Syntax

The formula symbols and syntax used in columns and rows in Plan Builder are similar to those used by Microsoft Excel.

Refer to the table below for sample basic operators and functions available for use in Plan Builder. The syntax used in each example varies according to whether it is used in a dimension or Measures column.

Many of these operators are also used in Analytics SAQL functions. For more information, see the Salesforce Help.

Operator Description
SUM() Returns the value of a numeric field.
AVERAGE() Returns the average of the values of a measure field.
COUNT() Returns the sum of all non-blank values in a row.
MAXIMUM() Returns the maximum value of a measure field.
MINIMUM() Returns the minimum value of a measure field.

Importing and Exporting Plan Lines

You can import and export plan lines using Microsoft Excel (XLSX) if you have permission.

For more information, see Importing and Exporting Plans.