Adding calculated rows and columns

To compare data, you can create a calculated symbol. You can also use a calculated symbol to compare values from any row symbol or column symbol.

You can create a calculated row or column that compares two other rows or columns according to percentage, ratio, or variance. For example, the following illustration shows a calculated column that displays the variance between the first two time period symbols.

A calculated symbol is a symbol that exists in the template or view only. It does not exist in the Longview database, and its data is not submitted to the database.

Because multiple calculated symbols from various dimensions may intersect, a decision must be made to determine which of the calculations should apply at those intersections. You can determine the order of calculations of all calculated symbols in a template, or DataView, and can therefore control which formula applies at each calculation intersection.

You can perform calculations based on a complete slice of data, or you can limit calculations to specific ranges of cells within that slice of data. For example, you may need to create reports containing “per unit” calculations that vary between revenue and cost accounts.

This section contains information on these main topics:

• "Creating a ratio row or column "
• "Creating a variance row or column "
• "Creating a percentage row or column "
• "Creating a formula row or column "
• "Defining calculation order for calculated rows and columns"
• "Editing a calculated row or column "
• "Deleting a calculated row or column"

Creating a ratio row or column

A ratio row or column is a type of calculated symbol that shows the ratio between two other rows or columns. When you place the cursor over this row or column, the calculation appears in a ScreenTip.

You may be able to create a calculated row or column that compares two other rows or columns according to the ratio between them.

To create a calculated ratio row or column, follow these steps.

1. Click on a symbol next to which you want to create a calculated symbol.
2. Right-click the symbol name. A pop-up menu appears.
3. Choose Insert Calculated Symbol. The Create Calculated Symbol dialog appears. The appearance of this dialog varies according to the orientation of the symbol you selected in step 1, and the type of calculation you select next in this procedure.
   
4. Complete these fields:

   Field	Description
   Name	Type a name for the calculated row or column.
   Description	Type a description for the calculated row or column.
   Type	Select Ratio. The available fields in the dialog change according to your selection.
   Numerator	Select the symbol for the numerator in the ratio.
   Denominator	Select the symbol for the denominator in the ratio.
   Insert Point	Select a location for the comparison row or column. You can place it either before or after the symbol you selected in step 1.
   Decimal	Select the number of decimal places to include in the calculated data. To retain the global decimal setting for the calculated symbol, select Default.

5. You may be able to create a condition for data in the calculated row or column, so that values meeting a certain condition are highlighted, filtered, or suppressed altogether.
   • To create a calculated row or column without a condition, proceed to step 6.
   • To create a condition, click Conditional Display, and complete the dialog, as described in “Working with conditions”.
6. Click OK. The view appears, showing the calculated row or column.
   

   If you hold the cursor over a cell in the calculated row or column, a ScreenTip appears, showing the formula.

Creating a variance row or column

A calculated variance row or column is a type of calculated symbol that shows the difference between two other rows or columns as either a currency value, a percentage, or both.

When you place the cursor over this row or column, the calculation appears in a ScreenTip. You may be able to create a calculated variance row or column.

To create a calculated variance row or column, follow these steps.

1. Click on a symbol next to which you want to create a calculated symbol.
2. Right-click the symbol name. A pop-up menu appears.
3. Choose Insert Calculated Symbol. The Create Calculated Symbol dialog appears.
4. For Type, select Variance. The appearance of the dialog changes.
   
5. Complete these fields:

   Field	Description
   Name	Type a name for the variance row or column.
   Description	Type a description for the variance row or column.
   Type	You have already selected Variance.
   Base	Select the base symbol against which you want to compare another symbol, to derive the variance between the two.
   Comparison	Select the symbol you want to compare with the base symbol.
   Use	Select one of the following: Value As Calculated: To show the value as it is calculated whether positive or negative. Absolute Value: To show the value as a positive number only.
   Display	Select one of the following: In Currency: To show the variance as a currency value. As a Percentage: To show the variance as a percentage. Both: To show the variance as both a currency value and a percentage. If you select this option, two calculated rows or columns appear.
   Insert Point	Select the location of the comparison row or column. You can place it either before or after the symbol you selected in step 1.
   Decimal	Select the number of decimal places to include in the calculated data. To retain the global decimal setting for the calculated symbol, select Default.

6. You can create a condition for data in the calculated row or column, so that values meeting a certain condition are highlighted, filtered, or suppressed altogether.
   • To create a calculated row or column without a condition, proceed to step 7.
   • To create a condition, click Conditional Display, and complete the dialog, as described in “Working with conditions”.
7. Click OK. The view appears, showing the calculated row or column.
   

   If you hold the cursor over a cell in the calculated row or column, a ScreenTip appears, showing the formula.

Creating a percentage row or column

A calculated percentage row or column is a type of calculated symbol that shows the value of each symbol in a selected row or column as a percentage of one of the symbols in that same row or column.

When you place the cursor over this row or column, the calculation appears in a ScreenTip.

You may be able to create a calculated percentage row or column.

To create a calculated percentage row or column, follow these steps.

1. Click on a symbol next to which you want to create a calculated symbol.
2. Right-click the symbol name. A pop-up menu appears.
3. Choose Insert Calculated Symbol. The Create Calculated Symbol dialog appears.
4. For Type, select Percentage of. The appearance of the dialog changes.
   
5. Complete these fields:

   Field	Description
   Name	Type a name for the percentage row or column.
   Description	Type a description for the percentage row or column.
   Type	You have already selected Percentage Of.
   Symbol To Calculate	Select the symbol, the values of which you want to view as a percentage of the values of another symbol.
   Base	Select the base symbol which is given the value of 100%, against which the other symbols in the row or column are compared.
   Display	Select whether you want the data shown as a percentage or as a decimal.
   Insert Point	Select the location of the comparison row or column. You can place it either before or after the symbol you selected in step 1.
   Decimal	Select the number of decimal places to include in the calculated data. To retain the global decimal setting for the calculated symbol, select Default.

6. You can create a condition for data in the calculated row or column, so that values meeting a certain condition are highlighted, filtered, or suppressed altogether.
   • To create a calculated row or column without a condition, proceed to step 7.
   • To create a condition, click Conditional Display, and complete the dialog, as described in “Working with conditions”.
7. Click OK. The view appears, showing the calculated row or column.
   

   If you hold the cursor over a cell in the calculated row or column, a ScreenTip appears, showing the formula.

Creating a formula row or column

A calculated formula row or column is a type of calculated symbol that shows data that has been calculated according to a particular algebraic formula. For example, you might want to create a formula that performs a Quick Ratio calculation:

(Total cash + Other current assets + Accounts receivable) / Total current liabilities

When you place the cursor over this row or column, the calculation appears in a ScreenTip.

You may be able to create a calculated formula row or column.

To create a calculated formula row or column, follow these steps.

1. Click on a symbol next to which you want to create a calculated symbol.
2. Right-click the symbol name. A pop-up menu appears.
3. Choose Insert Calculated Symbol. The Create Calculated Symbol dialog appears.
4. For Type, select Formula. The appearance of the dialog changes.
   
5. Complete these fields:

   Field	Description
   Name	Type a name for the formula row or column.
   Description	Type a description for the formula row or column.
   Type	You have already selected Formula.
   Insert Point	Select the location of the calculated row or column. You can place it either before or after the symbol you selected in step 1.
   Decimal	Select the number of decimal places to include in the calculated data. To retain the global decimal setting for the calculated symbol, select Default.

6. For Formula, type the formula you want to apply to the calculated row or column. If you are not sure how to create a formula, click Edit. The Build Formula dialog appears.
   
7. To create a formula, do any of the following:
   • In Formula, type the formula using your keyboard.
   • To use the value of a symbol for the formula, navigate through the hierarchy in Available Symbols, and double-click the symbol to select it.
   • To use the value of a symbol for the formula, type the full or partial symbol name in the Search field. Click Find Next and double-click the symbol to select it.
   • To use the value of a floating time period (for formulas in the TIMEPER dimension only), navigate through the time periods hierarchy in the Available Symbols list, and double-click a floating time period (indicated by an asterisk) to select it. You may also type the attribute name of the floating time period, enclosed in square brackets, in Formula (for example, [SGPCurrentYear]). For more information on floating time periods, see “Using floating time period symbols”.
   • To insert mathematical operators ( + ,  - , and so on), type them, or click the buttons under Formula.
   • Use any combination of the above.
8. When you are finished creating the formula, click OK. The Create Calculated Symbol dialog appears, showing the formula in the Formula field.
   
9. You can create a condition for data in the calculated row or column, so that values meeting a certain condition are highlighted, filtered, or suppressed altogether.
   • To create a calculated row or column without a condition, proceed to step 10.
   • To create a condition, click Conditional Display, and complete the dialog, as described in “Working with conditions”.
10. Click OK. The view appears, showing the calculated row or column.
    

Defining calculation order for calculated rows and columns

When you have more than one calculated symbol in a template, or DataView, you can determine the order in which these symbols are calculated. This is useful when two or more calculated symbols intersect, or when one calculation depends on the results of another.

For example, the following illustration contains two calculated symbols—a variance symbol in the down direction, and a percentage symbol in the across direction.

The variance column shows the variance between two time periods for all displayed accounts. The percentage row shows Gross Profit as a percentage of Total revenue over the course of the year.

In this case, where the two calculated symbols intersect, you would typically want the percentage symbol to be calculated and displayed. This is precisely what happens as a default, because the percentage symbol is in the down dimension and the inner down dimension always “wins” as a default.

However, if the dimension orientation gets reversed, the desired calculated symbol is no longer displayed. You can make sure that where the calculations intersect, the percentage symbol always gets calculated last (and is thus the calculation displayed) by defining the calculation order.

Depending on your role, you may be able to define the order in which calculated symbols are calculated.

To specify the order in which calculated symbols are calculated, follow these steps.

1. In the view window, choose Tools > Calculation Order. The Calculation Order dialog appears, listing all calculated symbols in the view.
   

   The calculations are listed in the order in which they are calculated, from the top down. This means that where calculated symbols intersect, the calculation lower in the list is the one that appears, because it is calculated last.

2. Select the calculation that you want to relocate.
3. Use the arrow buttons to move the calculation either up or down in the list.

Editing a calculated row or column

Once you have created a calculated symbol, you may be able to make changes to it, as new requirements arise.

To edit a calculated row or column, follow these steps.

1. In the view window, click the calculated symbol name to highlight it.
2. Right-click the symbol name. A pop-up menu appears.
3. Choose Edit Calculated Symbols. The Edit Calculated Symbol dialog appears.
   
4. Make changes to the calculated row or column, in the same way you created it. For more information, see
   • “Creating a ratio row or column”
   • “Creating a variance row or column”
   • “Creating a percentage row or column” or
   • “Creating a formula row or column”.

Deleting a calculated row or column

If you do not need a calculated symbol, you may be able to delete it.

To delete a calculated row or column, follow these steps.

Caution: Warnings provide cautionary information on the possible effect of certain actions, including the unintentional deletion of data. Be sure to read and understand all warnings before performing a related procedure. If you follow this procedure, you cannot reverse your decision. Use with caution.

1. Click the calculated symbol name to highlight it.
2. Right-click the symbol name. A pop-up menu appears.
3. Choose Remove Calculated Symbols. The calculated row or column is deleted.