Editing Formulas or Calculated Measures

Topics:

• Editing the Formula for a Column

• Editing Calculated Measures

Editing the Formula for a Column

 Video

You can edit the formulas for attribute columns and measure columns when you specify the criteria for an analysis. This editing affects the column only in the context of the analysis and does not modify the formula of the original column in the subject area.

A column formula specifies what the column values represent. In its most basic form, such as "Revenue Metrics"."Revenue", a column takes the data from the data source as is. You can edit the formula to add functions, conditional expressions, and so on. This editing enables you to present analysis results in a variety of ways. For example, you can edit the formula of a Revenue column to display values after a 10% increase in revenue. You can do this by setting the formula to (Revenue*1.1).

To edit the formula for a column:

1. Open the analysis for editing.

2. In the Selected Columns pane, click Options beside the column name.

   
   Description of the illustration analysis17.gif
   
   

3. Select Edit Formula.

4. On the Column Formula tab of the Edit Column Formula dialog, enter a formula in the Column Formula pane.

   
   Description of the illustration analysis29.gif
   
   

   By default, the name of the column on the Selected Columns pane displays in the Column Formula pane.

   • You can use the operator and character buttons on the bottom of the Column Formula pane to help build the formula.

   • You can use the f(...) button to display the Insert Function dialog that enables you to include a function in the column formula. For example, you can build a formula based on a SQL function, such as RANK("Sales Measures"."Dollars"). For information about SQL functions, see Expression Editor Reference.

   • You can use the Filter... button to display the Insert Filter dialog that enables you to include a filter expression in the column formula. Start the filter expression with at least one measure column. Include a Boolean expression that contains no measure columns or nested queries.

     For example, you can build a formula that uses the SQL FILTER function to filter the data, such as FILTER("Sales Measures"."Dollars" USING ("Markets"."Region" = 'EASTERN REGION').

   • You can use the Variable button to include a variable in the formula. For information, see Advanced Techniques: Referencing Stored Values in Variables.

5. (Optional) In the Subject Area pane, double-click a column to replace the column name in the Column Formula pane. This action creates a new formula.

6. (Optional) Click the Bins tab to perform "binning," which enables you to combine values for the column into sets. For example, suppose that the Region column has a value EASTERN. You can specify "My Home Region" instead as the text that displays for that value in a view. Binning differs from groups in that groups enable the view to display each member in the group.

   Click Add Bin to display the dialog for creating a filter expression to display as a CASE statement in the column's formula. You can combine multiple values or ranges of values from a given column into bins. When you add and name the bins, all instances of the various values that comprise the bin in the result set values are replaced by the bin name. Aggregations are performed accordingly as well. For example, you can specify that all values over $10M are displayed as "My10M."]]

   Note:

   The Bins tab is not affected by the button bar in the Formula area in the Formula tab. However, if you create a CASE statement using the Bins tab, the button bar is hidden when you click the Column Formula tab. You must clear all bins to display the button bar.

7. Click OK. On the Analysis editor: Results tab, the column displays its values with the formula applied.

   
   Description of the illustration analysis30.gif
   
   

Editing Calculated Measures

You can use calculated measures that are derived from other measures and created by using formulas. For example, you might need to find the value after discounts are taken off the revenue. You create a calculated measure that subtracts the Discounted Amount from the Revenue value.

To create a calculated measure for an analysis:

1. Open the analysis for editing.

2. On the Analysis editor: Results tab, click New Calculated Measure on the toolbar to display the dialog.

   
   Description of the illustration filtering32.gif
   
   

3. Edit the formula for the calculated measure as described in Editing the Formula for a Column.

4. Click OK.