Item-based Calculations

You can create a calculation if you need a comparison or ratio that does not exist in the data source, such as actual revenue as a percentage of planned revenue, or the average revenue per employee.

You can perform the following arithmetic calculations in an analysis.

You can perform the following percentage calculations in an analysis.

You can perform the following analytic calculations in an analysis.

You can perform the following ranking calculations in an analysis. By default, the highest value is ranked 1.

Create an Item-Based Calculation

You can create a calculation if you need a comparison or ratio that does not exist in the data source, such as actual revenue as a percentage of planned revenue, or the average revenue per employee. The calculations available depend on how many columns or rows you select .

Calculation results are not stored in the underlying data source. Instead, Analysis Studio reruns the calculation each time you view the analysis. The results are always based on the most current data in the data source.

An item-based calculation that you create has no inherent aggregation rule. Because "sum" is the most common aggregation rule, Analysis Studio provides the "sum" aggregation rule to calculations so that the appropriate solve order occurs.

If you have several calculations in a crosstab, you should be aware of the default solve order .

Note: Cells show "--" (two dashes) rather than a value in calculations that use nonadditive measures such as count or average. Nonadditive measures are distinguished by a nonadditive measure icon  in the source tree.

Steps

1. Select the rows or columns that you want to use in the calculation.

2. Click the calculate  button on the toolbar.

3. Choose whether to use a predefined calculation or create a member-based calculation:

   • To use a predefined calculation, click one of the predefined calculations.

   • To create an item-based calculation, click Custom.

     The Calculate dialog box appears.

4. In the Operation box, click the operation you want to perform.

   Depending on the selected data, you may first need to choose an operation type from the Operation type box.

5. Choose which data to use for the calculation:

   • To use existing items, click the items you want in the Available items box.

   • To use other data, specify a number in the Number box.

6. Click OK.

The calculated results appear in a new row or column after the last item used as an operand in the calculation.

Tips

• To make the name of the calculated row or column more meaningful, or to avoid a default heading name which is too long, type a new name in the Name box.

• To edit a calculation, right-click the calculated row or column and click Edit this Calculation.

Calculation Solve Order

When calculations in the rows and columns of a report intersect, Analysis Studio performs the calculations in the following order:

• addition or subtraction

• multiplication or division

• aggregation (rollup)

• remaining arithmetic functions: absolute, round, round down, average, minimum, maximum, medium, count

• percentage, % difference (growth) or % of total

• rank, quartile, quantile, or percentile

If both calculations have the same precedence, for example, if they are both business functions, then the row calculation takes precedence.

If you have the necessary permissions, you can open your analysis in Report Studio and override the order of precedence by changing the solve order property. For more information, see the Report Studio User Guide.

Rank Values

Ranking items identifies their relative position to help you compare data.

For example, you can rank a product line, such as golf equipment, to identify how well sales for golf equipment performed relative to other years.

You can also rank to identify how well sales for golf equipment performed relative to other products in the same axis.

Ranking operations include rank, percentile, quartile, and quantile .

If you use the default settings, you can rank values quickly by right-clicking a single row or column and clicking Rank.

Analysis Studio uses Olympic ranking for data sources.

The exception is that SAP/BW data sources use serial ranking.

Olympic ranking handles ties by assigning an equal rank to items with equal results. Serial ranking handles ties by assigning a sequential number to items in the order they are retrieved in the data source.

Steps

1. Select a row or column to rank.

2. Click the calculate button  on the toolbar.

3. Click Custom.

   The Calculate dialog box appears.

4. In the Operation type box, click Ranking.

5. Choose the desired options under Operation, Available items, and Based on measure.

6. Under Rank, choose whether to rank within the selected item only, or to rank with other values on the same axis.

7. Click OK.

You can now sort the rank results to show them in numerical order.

Use Custom Ranking

By default, ranking is based on the innermost nested set and calculates the items that meet the filter criteria in the selected row or column. To calculate rank using different segments of the data or the outermost set, use custom ranking. For an example of custom ranking, see Example - Use Custom Rank.

Steps

1. Select a row or column to rank.

2. Click the calculate button  on the toolbar.

3. Click Custom.

   The Calculate dialog box appears.

4. In the Operation type box, click Ranking.

5. Under Rank, click Custom.

6. Choose the options that you want:

   • Specify whether to rank the highest value or the lowest value as number 1.

   • Change the scope of the rank, such as by ranking only within visible items.

   • Rank by the innermost or outermost nested set in a crosstab.

7. Click OK.

You can now sort the rank results to show them in numerical order.