Managing Plan Lines
Use the Plan Lines tab to manage your plans and apply calculations.
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.
To insert rows in the plan table from the Plan Lines tab:
- 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.
- 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.
- Click Insert or Insert & New. The new row is added.
To insert columns in the plan table from the Plan Lines tab:
- 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.
- 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.
- Click Insertor Insert & New. The new column is added.
To insert total rows in the plan table from the Plan Lines tab to get the total of all dimensions:
- 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.
- 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.
- 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.
- 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:
- Click | Insert Total Column or right-click the table where the measures are located and click . The Insert Total Column window appears
- 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.
- 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.
- 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:
- To delete one row, right-click the row and click Delete Row. The row is deleted.
- 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:
- To delete one column, right-click the column and click . The column is deleted.
- To delete multiple columns, select one or more columns using the shift button on your keyboard, right-click the columns, and click Delete Columns.
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.
To apply a formula or manually enter a formula in a cell, follow these steps:
- Click the row in the plan table where calculations are set for a cell.
- 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.
- [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.
- [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.
- Filter the dimensions based on the available dimensions. The selected plan cells are highlighted to show inclusion in formulas.
- 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.
- Click Save to save the calculation for the plan line.
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.
- Click | Add Row and give this section the name Total.
- 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:
- Click | Add column and give this section the name Total. A new column with the Total heading gets added.
- 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.
- Click Save to save to the formula and display the recalculated value.
To calculate the yearly total by accumulating the totals for each period. To do so:
- 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.
- 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.
- 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.
- 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.