Editing the Formula of a Column

You can edit the formulas for columns when you specify the criteria for an analysis.
You can also customize table and column headings and specify the aggregation rule
for column totals. (This functionality is not available for hierarchical columns.)
note: 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.

How to …

Manually Edit the Formula of a Column

1. Click the button for the column, and choose Edit Formula from the drop-down
menu. The Edit Column Formula dialog appears.

Column Formula Tab

2. In the Column Formula area, type the formula.

note: When calculating the sum of numeric values, choose Sum using the Aggregation
Rule drop-down list (instead of Default).

3. Click the OK button.

 

Add Operators and Characters to the Column Formula Area

1. Click the button for the column, and choose Edit Formula from the drop-down
menu. The Edit Column Formula dialog appears.

2. Click the corresponding buttons (in the button bar) to insert the following operators/characters
into the Column Formula area:

• Plus – (the ‘+’ button inserts the ‘+’ character)
• Minus – (the ‘-‘ button inserts the ‘-‘ character)
• Multiply – (the ‘x’ button inserts the ‘*’ character)
• Divide – (the ‘/’ button inserts the ‘/’ character)
• Percentage – (the ‘%’ button inserts the ‘/100’ characters)
• Open parenthesis – (the ‘(‘ button inserts the ‘(‘ character)
• Close parenthesis – (the ‘)’ button inserts the ‘)’ character)
• Concatenate – (the ‘||’ button inserts the ‘||’ character))

 

Add Functions to the Column Formula Area

1. Click the  button for the column, and choose Edit Formula from the drop-down
menu. The Edit Column Formula dialog appears.

2. Click the Function button. The Insert Function dialog appears.

3. Expand a function group to show a list of functions within that group.

4. Select a function name to show the syntax of the selected function and its
description in the lower part of the dialog.

5. Click the OK button to insert the selected function expression into the Column Formula
area.
If text is selected in the Column Formula area when you click the Function button,
the selected text is incorporated into the inserted function formula.
If no text is selected in the column formula when you click the Function button, the
function expression is inserted and the first argument (denoted by tokens expr or
expr1) is automatically selected.

 

Add Column Expressions to the Column Formula Area

1. Click the  button for the column, and choose Edit Formula from the drop-down
menu. The Edit Column Formula dialog appears.

2. Click the Column button and choose a column name using the drop-down menu
of the available column names that are already selected into the criteria of the
current report.
When you choose a column name from the menu, the column’s expression is
inserted into the Column Formula area.

 

Add Variables to the Column Formula Area

1. Click the  button for the column, and choose Edit Formula from the drop-down
menu. The Edit Column Formula dialog appears.

2. Click the Variable button, and choose Presentation using the drop-down menu.
The Insert Variable dialog appears.

note: Analytics does not currently support repository or session variables—do not
select either when choosing a variable from the menu.

3. In the Variable Expression field, type the name of the variable.

4. (Optional) In the Default Value field, type a default value for the variable, if applicable.

5. Click the OK button.
The appropriate formula expression for the variable is created and inserted into
the Column Formula area.
Presentation variables are referred to using their name, and optionally by a
default value.
The appropriate formula expression for the variable is created and inserted into
the Column Formula area. The example below shows the formula for a Presentation
variable named presentationvar with a default value of 10.
“Sales Measures”.Dollars + @{presentationvar}{10}

note: When referencing a column that uses presentation variables, Analytics only
uses the default value of the presentation variable. If you do not specify a default
value for the presentation variable, you will need to manually input the presentation
variable (in the form of @{presentationvar}) in the formula.

 

Add Filters to the Column Formula Area

1. Click the button for the column, and choose Edit Formula from the drop-down
menu. The Edit Column Formula dialog appears.

2. Click the Filter button. The Insert Filter dialog appears containing a complex filter
editor.

Insert Filter Dialog

The filter is initially blank, but you populate the filter by clicking a column in the
selection pane to add a column filter and create a filter expression.

3. In the Subject Areas pane, double-click a column name. The New Filter dialog
appears.

4. Specify the filter, and click the OK button.
The resulting filter expression is added to the complex filter definition in the Insert Filter
dialog.
The filter expression is in the following form:
FILTER( USING )
where:

• + is an expression that contains at least one measure. For example,
the expression “sales + 1” is allowed if “sales” is a measure. The expression
“productid” is not allowed if “productid” is a scalar attribute.
• + is a boolean expression (evaluates to TRUE or FALSE) and
does not contain any measures. Also, this expression may not contain any
nested queries.

If you select text in the Column Formula area before clicking the Filter button, it
is incorporated in the inserted filter expression, replacing the expression token.
For example, if you have selected “Sales Measures”.Dollars before clicking the
Filter button. A complex filter expression might replace the filter_expressions
token as follows:
FILTER(“Sales Measures”.Dollars USING ((Periods.”Year” = ‘1999’) AND ((Markets.District
= ‘CINCINNATI DISTRICT”) OR (Markets.District = ‘DENVER DISTRICT’))))

note: You need to use key columns instead of the corresponding display columns
when creating filters for fields that have translated values in the Translation Workbench.
For more information, see “Understanding Custom Translations and Double
Columns” and “TWB Key Values”

5. Add other column names to define more expressions to build a complex filter
statement involving multiple expressions.
Use the AND/OR link to specify the relationship between multiple expressions.

6. Click the OK button.
The filter expression appears the Column Formula area.

7. Click the OK button.

 

Add Case Statements to the Column Formula Area

1. Click the  button for the column, and choose Edit Formula from the drop-down
menu. The Edit Column Formula dialog appears.

2. Click the Bins tab.

note: The Bins tab is not affected by the button bar. However, if you create a CASE
statement using the Bins tab, the button bar will be hidden when you click the Column
Formula tab. The button bar is only displayed if you clear all bins.

3. Click the Add Bin button. The New Filter dialog appears.

4. Create a filter expression to display as a CASE statement in the Column Formula
area.

5. Click the OK button. The filter expression appears in the Bins tab.

6. Click the Column Formula tab to view filter expressions as CASE statements.

7. Click the OK button.