Bookshelf Home | Contents | Index | Search | PDF

Siebel Analytics User Guide > Working with Siebel Analytics Views > Working with Siebel Analytics Pivot Table Views >

Using Calculations in Siebel Analytics Pivot Tables

---

You can use calculations in a pivot table to obtain different views of the data. The calculations allow you to override the default aggregation rule specified in the Siebel Analytics repository, and for an existing report, the aggregation rule chosen by the author.

Table 18 describes the calculations that you can use in pivot tables.

Table 18.  Siebel Analytics Pivot Table Calculations

Calculation	Description
Sum	Calculates the sum obtained by adding up all values in the result set. Use this on items that have numeric values.
Min	Calculates the minimum value (lowest numeric value) of the rows in the result set. Use this on items that have numeric values.
Average	Calculates the average (mean) value of an item in the result set. Use this on items that have numeric values.
First	In the result set, selects the first occurrence of the item.
Last	In the result set, selects the last occurrence of the item.
Count	Calculates the number of rows in the result set that have a nonnull value for the item. The item is typically a column name, in which case the number of rows with nonnull values for that column are returned.
Count Distinct	Adds distinct processing to the Count function. This means that each distinct occurrence of the item is counted only once.
Formula	Opens a toolbar that lets you select mathematical operators to include in the calculation.

Internally, Siebel Analytics Web processes pivot table calculations as SQL SELECT statements, and performs the indicated functions on the result set. For more information about SQL functions, see Siebel Analytics Server Administration Guide.

Building Calculations in Siebel Analytics Pivot Tables

You can build calculations for items in the Pages, Sections, Rows, and Columns areas.

To build a calculation for an item

In the Pages, Sections, Rows, or Columns area, click the More Options icon for the measure on which you want a calculation performed.
Select the option New Calculated Item.

This opens the Calculated Item window.

Assign a name for the calculation in the Name field.
To build a calculation other than a formula, choose from the following options:
To build one calculation, select the function to work with from the Function drop-down list, and click on one or more items in the Values list to add them to the Function field.
To build multiple calculations for multiple items, type the functions and click on the item names to add them to the Function field. See Examples of Calculations in Siebel Analytics Pivot Tables for examples of the kinds of calculations that you can build.
To build a formula, choose the Formula function.

NOTE:  A formula creates a dynamic custom grouping within the pivot table. All measures referenced in a formula must be from the same logical column and must be present in the results. Formulas can be inserted into, or combined with, other calculations.

The mathematical operators become visible below the Functions field. The operators are shown in the following table.

Operator	Description
+	Plus sign, for an addition operation in the formula.
-	Minus sign, for a subtraction operation in the formula.
*	Multiply sign, for a multiplication operation in the formula.
/	Divide By sign, for a division operation in the formula.
$	Dollar sign, for acting upon the row position of an item in a formula.
(	Open parenthesis, to signify the beginning of a group operation in the formula.
)	Close parenthesis, to signify the ending of a group operation in a formula.

In the Function field, build the formula by typing or clicking measure names, and clicking operators to insert them into the formula. See Examples of Calculations in Siebel Analytics Pivot Tables for examples of what you can do.
Use parentheses, where appropriate.
When the calculation is complete, click Finished.

If any errors are detected, a message will appear. Correct the error and click Finished again.

Examples of Calculations in Siebel Analytics Pivot Tables

The following examples and explanations assume that you have a basic understanding of SQL and its syntax. The examples are hypothetical. Not all possible calculations are shown.

Example 1. This obtains the value of the current measure, such as dollar sales, for each of the products SoftDrinkA, SoftDrinkB, and SoftDrinkC, and adds the values together.

sum('SoftDrinkA','SoftDrinkB','SoftDrinkC')

This is equivalent to selecting Sum from the Function drop-down list, and then typing or clicking `SoftDrinkA',`SoftDrinkB',`SoftDrinkC' to add them to the Function field.

Example 2. This obtains the minimum current measure, such as dollars in sales, for SoftDrinkA or SoftDrinkB, whichever is lower.

min('SoftDrinkA','SoftDrinkB')

In Example 1 and Example 2, each functional calculation is performed for each item in the outer layer, such as the Product layer. For example, if Year and Product are laid out on an axis, and one of the above calculations is built on the Product layer, the results will be computed per year.

Example 3. This obtains the values for each item in the outer layer, such as Year and Product, and adds them together.

sum(*)

Example 4. This obtains the current measure, such as dollar sales, of the item from the first, second, and third rows, and sums them.

sum($1,$2,$3)

Instead of specifying a named item, such as SoftDrinkA, you can specify $n or $-n, where n is an integer that indicates the item's row position. If you specify $n, the measure is taken from the nth row. If you specify $-n, the measure is taken from the nth to the last row.

For example, for dollar sales, $1 obtains the measure from the first row in the data set, and $-1 obtains the measure from the last row in the data set.

Example 5. This adds sales of SoftDrinkA, SoftDrinkB, and SoftDrinkC.

'SoftDrinkA' + 'SoftDrinkB' + 'SoftDrinkC'

This is equivalent to the following calculation:

sum('SoftDrinkA','SoftDrinkB','SoftDrinkC')

Example 6. This adds sales of SoftDrinkA with sales of diet SoftDrinkA, then adds sales of SoftDrinkB with sales of diet SoftDrinkB, and then returns the maximum of these two amounts.

max('SoftDrinkA' + 'diet SoftDrinkA', 'SoftDrinkB' + 'diet SoftDrinkB')

Bookshelf Home | Contents | Index | Search | PDF