Creating calculated expressions in documents

A calculated expression is a metric that is calculated dynamically, when the document is executed, directly from metrics on a document dataset. A calculated expression is created using at least one of the metrics in the document. Calculated expressions allow you to combine metrics from different datasets in the document using:

• Constants

• Simple arithmetic operators (+, -, *, /)

• Certain advanced functions, including many financial, statistical, and mathematical functions

  To use an advanced function, you must type the function syntax in the calculated expression. For an example of an advanced function used in a calculated expression, see the Designing Documents chapter of the Document Creation Help. For a complete list of the supported functions and their syntax, see the Advanced Functions for Calculated Expressions appendix in the Document Creation Help.

• The logical operator IF

• Parentheses to define the order of arithmetic operations

Calculated expressions are created directly in text fields on the document, which makes them quicker to create than derived metrics. However, unlike derived metrics, calculated expressions cannot be reused within the document because they are not added to the document as dataset objects. They appear as text fields only.

Example of creating a calculated expression

Your grouping and sorting dataset contains the Revenue by Region metric and a second dataset contains the Cost by Region metric. You need the profit, which is calculated from the revenue less the cost. You can create a calculated expression with a definition of Revenue - Cost.

To do this, create a calculated expression. Drag the Revenue metric from the Datasets pane to the Layout area, which creates a new text field. Right-click the new text field and select Edit Text. Type - Cost inside the braces, then press Enter. The final text field looks like {Revenue – Cost} in Design View.

Example of an advanced function in a calculated expression

An example of an advanced function is Banding, which maps metric values that fall within a certain range to a particular band value. The function returns a number indicating the band. The syntax of this function is Banding(ValueList, StartAt, StopAt, Size).

For example, create bands on the Revenue metric in the document described in the previous example. Bands are created in $250,000 increments, starting at $1,000,000 and ending at $4,000,000. Band 1 is $1,000,000 to $1,250,000; Band 2 is $1,250,001 to $1,500,000, which includes the Regions South and Northwest; and so on.

To create these bands in a calculated expression, create a text field and type the following in it:

{Banding(Revenue, 1000000, 4000000, 250000)}

Remember to use braces { } if you type the expression.

The resulting document is shown below:

Because no Revenue value falls between $1,000,000 and $1,250,000, there is no Band 1. Because two regions have revenue between $1,250,001 and $1,500,000, there are two lines for Band 2.

To create a calculated expression

1. Open the document in the Document Editor. (How?)

2. Expand the section where you want to place the calculated expression by clicking the plus sign next to the section name.

3. To enter the expression, do one of the following:

   • Drag and drop data fields from the Datasets pane.

   • Add a text field and type the expression in it.

   • Combine the above methods.

Join the data fields with an arithmetic operator (+, -, *, /) or use an advanced function. You can use constants and also parentheses within the calculation. Parentheses indicate the order of arithmetic operations.

For more information on using these methods, see Adding data fields to documents.

Related Topics

• Creating derived metrics in documents

• Creating summary metrics in documents

• Adding text and data to documents