Editing Formulas or Calculated Measures

You can fine-tune the columns in an analysis by editing the formulas of columns or editing calculated measures.


Editing the Formula for a Column

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 doesn’t 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 writing a formula that multiplies the Revenue column by 1.1.

  1. Open the analysis for editing.
  2. In the Selected Columns pane, click Options beside the column name, and then select Edit Formula.
  3. On the Column Formula tab of the Edit Column Formula dialog, enter a formula in the Column Formula pane.

    By default, the name of the column on the Selected Columns pane is displayed 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"). 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. See Advanced Techniques: Referencing Stored Values in Variables.

    • You can reference a column name in the formula using the form Folder-Name.Column.Name. If either the folder name or the column name includes non-alphanumeric characters (such as spaces or underscores), then enclose each name in double quotes. You can enclose the names in double quotes even if they have all alphanumeric characters.

    • You use single quotes to include literals or constants that have a data type of string. For example, you can include constants such as ‘John Doe’ or ‘Best Selling Product’ in a formula.

  4. Optionally, in the Subject Areas pane, double-click a column to replace the column name in the Column Formula pane. This action creates a new formula.
  5. Optionally, 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."]]


    The Bins tab isn’t 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.

  6. Click OK. On the Results tab, the column displays its values with the formula applied.

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.

  1. Open the analysis for editing.
  2. On the Results tab, click New Calculated Measure on the toolbar to display the dialog.
  3. (Administrators only). To customize folder and column headings with HTML markup, including JavaScript, select Contains HTML Markup, and then enter the HTML markup you want to apply. For examples, see Advanced Techniques: Formatting with HTML Tags.
  4. Edit the formula for the calculated measure. See Editing the Formula for a Column.
  5. Click OK.