Working with Calculated Data Items in Reports :: SAS(R) Visual Analytics 6.2: User's Guide

About Calculated Data Items

The designer enables you to calculate new data items from your existing data items by using an expression. For example, you might want to calculate a company’s profits by subtracting expenses from revenues.

In addition to performing mathematical calculations on numeric values, you can use calculated data items to create date and time values. For example, if your data contains separate categories for month, day, and year, then you can calculate a date value from those categories.

Note: All calculations are performed on unaggregated data. The calculation expression is evaluated for each row in the data source before aggregations are performed. To perform calculations on aggregated data, see Add a New Aggregated Measure to a Report.

A hierarchy can contain calculated data items.

Using the designer, you can work with calculated data items or aggregated measures.

Add a New Calculated Data Item to a Report

To add a new calculated item:

On the Data tab, click , and then select New Calculated Item. The New Calculated Item window is displayed.

New Calculated Item Window
Enter a Name.

Select a Result type from the drop-down list. Numeric is the default data type.

The following table lists the available result types:

Icon	Result Type
	Date
	Datetime
	Numeric
	Time

Calculated data items in the designer always default to the following formats, which are based on the data type:

Date: DATE9
Datetime: DATETIME10
Time: TIME8
Numeric: COMMA12.2

After you create the new calculated data item, you can change its format using the data item table on the Data tab.

SUM is the default aggregation for new numeric calculated items in the designer. You can change the aggregation for numeric calculated items using the data item table on the Data tab.

(Optional) Click to Show all drop zones. You can also choose to Show scratch area to build temporary expressions.
Build the expression for your calculated data item by dragging Data items and Operators onto the expression in the right pane. For each rectangular field in the expression, you can insert a data item, an operator, or a specific value.

When you drag items or operators onto your expression, the precise location of the cursor determines where and how the new element is added to the expression. As you drag the new element over the expression, a preview appears that displays how the expression changes if you drop the element at that location.

For example, if your current expression is ( Profit / Revenue ), and you drag and drop the x - y (subtract) operator inside the open parenthesis symbol, then the expression changes to ( [number] — ( Profit / Revenue )). If you drag and drop the operator over the division symbol, then the expression changes to ( Profit — Revenue ), and so on.

There are a large number of operator types available to perform mathematical functions, process datetime values, and evaluate logical processing such as “if” clauses. For more information, see Operators for Calculated Data Items.
Click OK. The new calculated data item appears in the Data tab. The , , or icons identify the new calculated data item on the Data tab.

Note: The icon is displayed only if you change a calculated numeric measure to a category data item.

Add a New Aggregated Measure to a Report

Aggregated measures enable you to calculate new data items by using aggregated values. For example, you might want to calculate a company's profit margin by region by taking the aggregated sum of the profit for all of the stores in a region group and dividing it by the aggregated sum of the revenue for all of the stores in that same region group. Aggregations are evaluated as part of a calculated expression.

To add an aggregated measure:

On the Data tab, click , and then select New Aggregated Measure. The New Aggregated Measure window is displayed.

New Aggregated Measure Window
Enter a Name.
(Optional) Click to Show all drop zones. You can also choose to Show scratch area to build temporary expressions.
Build the expression for your aggregated measure by dragging and dropping Data Items and Operators onto the expression in the right pane. For each field in the expression, you can insert a data item, an operator, or a specific value.

When you drag and drop data items and operators onto the expression, the precise location of the cursor determines where and how the data item or operator is added to the expression. As you drag the new element over the expression, a preview appears, which displays how the expression would change if you drop the element at the current location.

There are a large number of operator types available to perform mathematical functions and evaluate logical processing such as IF clauses.See Operators for Calculated Data Items.
For each data item in your expression, select an aggregation type. By default, Sum is used for measures and Distinct is used for categories. To select a new aggregation type, drag and drop an aggregated operator from the Operators list onto the aggregation type in the expression.See Operators for Calculated Data Items for a list of the aggregated operators that are available.
For each aggregation in your expression, select the aggregation context. A drop-down list beside each aggregation enables you to select one of the following context values:

ByGroup

calculates the aggregation for each subset of the data item that is used in a visualization. For example, in a bar chart, an aggregated measure with the ByGroup context calculates a separate aggregated value for each bar in the chart.

ForAll

calculates the aggregation for the entire data item (after filtering). For example, in a bar chart, an aggregated measure with the ForAll context uses the same aggregated value (calculated for the entire data item) for each bar in the chart.
By using the ForAll and ByGroup contexts together, you can create measures that compare the local value to the global value. For example, you might calculate the difference from mean by using an expression such as the following:
```
Avg ByGroup(X) - Avg ForAll(X)
```
Click OK. The new aggregated measure appears on the Data tab. The icon identifies the new aggregated measure.

Edit a Calculated Data Item

To edit a calculated data item:

Right-click on a calculated data item on the Data tab and select Edit Calculated Item. The Edit Calculated Item window is displayed.

Edit Calculated Item Window
Modify the Data Items and Operators for the calculated data item as needed. For information about the operators and conditions that are available, see Conditions and Operators for Filters.
Click OK.

You can duplicate, rename, hide, and delete calculated data items using the same steps as any other data item. If a calculated data item is used inside another calculated data item, then it cannot be removed.