To calculate
a new data item:
-
If you
are viewing OLAP data, then select
Data
Calculate New Measure from
the main menu bar.
If you
are viewing relational data, then select
DataCalculate New Column from the main menu bar.
-
Specify
a
Name for the new data item.
-
Create
an
Expression to define the new data item.
For OLAP data, you can either create a custom expression or use a
function.
-
Create a custom expression:
To add a data item
to your expression, select it in the
Available data items pane and click
. To add mathematical symbols to your expression,
use the following buttons:
inserts a plus sign
(add) into the expression.
inserts a minus sign
(subtract) into the expression.
inserts an asterisk
(multiply) into the expression.
inserts a slash (divide)
into the expression.
inserts parentheses
(group) into the expression. If you select part of the expression
before clicking this button, the parentheses are inserted before and
after the selection.
You can also edit your
expression manually. This enables you to enter fixed numeric values.
For example, to average two data items, add them to the expression,
insert a plus sign between them, and add
/2 to the expression.
Note: The name that
identifies a data item in an expression might differ from the name
that appears in the
Available Items pane.
To ensure that you use the correct name in your expression, add the
data item by clicking
.
-
Use a function (OLAP data only):
Note: Functions are
available only when a data item in your query contains date or time
values.
To add a function to
your expression:
-
Select
a function from the
Functions drop-down list:
Functions for Calculating Time Data
|
|
|
Difference (consecutive
periods)
|
calculates the
difference between the previous period and the current period.
|
Difference (parallel
periods)
|
calculates the
difference between the current period and the previous parallel period.
For example, you might calculate the difference between the third
quarter of the current year and the third quarter of the previous
year.
|
Percent Change
(consecutive periods)
|
calculates the
difference between the previous period and the current period, and
expresses the difference as a percentage of the previous period's
value.
|
Percent Change
(parallel periods)
|
calculates the
difference between the current period and the previous parallel period,
and expresses the difference as a percentage of the previous period's
value. For example, you might calculate the percentage change between
the third quarter of the current year and the third quarter of the
previous year.
|
Rolling Total
(period to date)
|
calculates the
sum of the values for the current period and all of the preceding
periods within the same hierarchy level. For example, you might calculate
the year-to-date total for each quarter.
|
Rolling Total
(from selected member)
|
calculates the
sum of the values for the current period, the selected period, and
all of the periods between the selected period and the current period.
|
-
If you
selected the
Rolling Total (from selected member) function, select the starting period from the
Starting
Period pane.
-
Select
a data item from the
Available Data Items pane, and then click
.
-
(Optional)
Select a data format for the new data item.
-
-
In the
Format dialog box, select the type of format from
Format
type drop-down list.
-
From the
selection box, select the format that you want to use. A description
of the selected format is displayed to the right of the selection
box.
-
Specify
the width of the format in the
Valid width range field. The width that you specify includes every character in the
value. For example,
$1,978.53 has a
width of 9.
-
Specify
the number of decimal places for the format in the
Valid
decimal range field. For example,
3.141593 has a decimal range of 6.
Note: The number
of decimal places is limited by the width that you specify in the
Valid width range field and the number of characters
to the left of the decimal. For example, if you specify a width of
8 and a decimal range of 6, the value
346.734947 is represented as
346.7349.
-
Click
OK to apply your format and return to the Calculate
New Measure dialog box. The format appears in the
Format field.
You can
clear the format by clicking
Clear Format.
-
When you
have finished creating your expression, click
Add to create the new data item. The new data item appears in the
New items pane.
-
Click
OK to add the new data item to your query.
Note: By default,
the format for the new data item is the same as the first data item
in the calculation expression. However, if the expression uses division,
the default format is BEST12.2. If you use a function that uses percentages,
the default format is PERCENT. You can specify a different format
by clicking
Select Format.
