Help Topics

Calculation Function Selector pane


A function can take the following types of arguments:

If a function can have more than one argument, and you want to add an argument, click the Add argument button.

Missing values are ignored. For example, if the expression is Average(East, West, South) and South is missing, the calculation finds the average of East and West.

Functions

The following table shows the functions that you can add to your expression.

Function

Description

Abs

Returns the absolute value of a number. Abs accepts only one argument.

Average

Returns the average of all the values in the arguments. The average is the sum of the values divided by the count. If the count is zero, Average returns a missing value. Average accepts any number of arguments. For example:

Average(Product 1, Product 2) returns the average of Product 1 and Product 2.
Average(Gen(2)) returns the average of all members with a generation level of 2.

Count

Returns the count of all the values in the arguments. If there are no values to count, zero is returned. Count accepts any number of arguments.

Count(Product 1, Product 2, Gen(2)) returns 2 + the number of members with a generation level of 2

If

Returns a value based on whether a given condition is true. If accepts three arguments, where:

  • The first argument is the condition to test. The condition has a left part and a right part, separated by one of the following operators: <=, >=, =, <, >, or != (<> can also be used for not equal tests).
  • The second argument is the value to return if the condition is true.
  • The third argument is the value to return if the condition is false.

Max

Returns the maximum value from all the values in the arguments. Max accepts any number of arguments.

Median

Returns the median value from all the values in the arguments. The median value is the value at which half the numbers are above the value and half the numbers are below the value. Median accepts any number of arguments.

Min

Returns the minimum value from all the values in the arguments. Min accepts any number of arguments.

Power

Returns the value of a first value raised to the power of a second value. Power accepts two arguments, where:

  • The first argument is the base.
  • The second argument is the exponent.

Power returns the base raised to the power of the exponent.

Product

Returns the multiplication product of all the values in the arguments. Product accepts any number of arguments.

Round

Returns the integer part of the number rounded to the nearest whole number. Round accepts only one argument.

Sqrt

Returns the square root of a number. Sqrt accepts only one argument.

Stdev

Returns the standard deviation of all the values in the arguments. The standard deviation is a measure of how widely values are dispersed from the average value. Stdev accepts any number of arguments.

Sum

Returns the sum of all the values in the arguments. If there are no values to add, Sum returns zero. Sum accepts any number of arguments.

Sum(Product 1, Product 2) returns the sum of Product 1 and Product 2.
Sum(Gen(2)) returns the sum of all members with a generation level of 2.

Var

Returns the variance of all the values in the arguments. The variance is the average squared deviation of each number in the set from the average. Var accepts any number of arguments.

Percentoftotal

Note: Percentoftotal applies only to calculations on multidimensional databases.

Returns a specified member as a percentage of some total. The total is the value of the cell at the intersection of the specified member and another member on the other axis. For example, a grid shows sales, where Time is on the rows and Products is on the columns. You can find the percentage of total for Product 1, where the total is the sales for Product 1 for All Time. The calculated member returns the sales for Product 1 for each time period divided by the sales for Product 1 for All Time.

Percentoftotal has the following arguments:

Member
Is the member in the dimension (that is, column or row) that you want to calculate the percentage of total for.
Dimension
Is the dimension on the opposite axis that contains the member that is used to determine the total. For example, if the grid shows Time and Locations on the rows, and Products on the columns, and Member is a member of Products, then Dimension can be either Time or Locations.
Opposite axis member
Is the member of Dimension that, with Member, determines the total to calculate the percentage with. The total is the value of the cell at the intersection of Member and Opposite axis member.
Generation
Is the generation that the percentages are calculated for. A generation of 0 means that all members show the percentages.
Grouping
When the axis has more than one dimension, specifies if each grouping of the dimensions has a separate percentage. If Grouping is set to ungrouped, the percentages are calculated across groups.

For example, the grid below shows the calculated member Percentages whose expression is a Percentoftotal function. The Percentoftotal function has the following values for its arguments:

  • Member = Product
  • Dimension = Locations
  • Opposite axis member = All Locations
  • Generation = 1
  • Grouping = Grouped

The calculated member percentages is calculated as the sales for the product at each location as a percentage of the sales for the product at all locations.

Locations

Time

Product

Percentages

East

2000

5

0.25

2001

20

0.40

2002

20

0.50

West

2000

15

0.75

2001

30

0.60

2002

20

0.50

All Locations

2000

20

1

2001

50

1

2002

40

1


Rank

Returns the ranking of each item in a column or row.

Rank has the following arguments:

Member
Is the member in the dimension (that is, column or row) that you want to rank.
Dimension
Is the dimension on the opposite axis whose members are used to generate the rank. For example, the grid shows sales, where Time and Locations are on the rows, and Products is on the columns, and Member is a member of Products. Dimension can be either Time or Locations. If Dimension is Time, then Rank ranks sales of Products over Time.
Generation
Is the generation of the members of the dimension to rank. A generation of 0 means that all members are ranked.
Order
Is either ascending or descending. In descending order, the largest number will be ranked 1. In ascending order, the smallest number will be ranked 1.
Grouping
When the axis has more than one dimension, specifies if each grouping of the dimensions is ranked separately. If Grouping is set to ungrouped, the ranks are calculated across groups.

Note: The Dimension, Generation, and Grouping arguments apply only to calculations on multidimensional databases.

For example, the grid below shows the calculated member Rank whose expression is a Rank function. The Rank function has the following values for its arguments:

  • Member = Product
  • Dimension = Time
  • Generation = 1
  • Order = Descending
  • Grouping = Grouped

Locations

Time

Product

Rank

East

2000

102

1

2001

76

3

2002

88

2

West

2000

49

3

2001

52

1

2002

51

2


Runningtotal

Returns the accumulative sum of values from the specified dimension for the specified member.

Runningtotal has the following arguments:

Member
Is the member in the dimension (that is, row or column) that you want to calculate the running totals for.
Dimension
Is the dimension on the opposite axis whose members are used to calculate the running totals. For example, the grid shows sales, where Time and Locations on the rows, and Products on the columns, and Member is a member of Products. Dimension can be either Time or Locations. If Dimension is Time, then Runningtotal returns the running total of sales for Products over Time.
Generation
Is the generation of the members of the dimension to total. A generation of 0 means that all members are totalled.
Grouping
When the axis has more than one dimension, specifies if each grouping of the dimensions is totalled separately. If Grouping is set to ungrouped, the totals are calculated across groups.

Note: The Dimension, Generation, and Grouping arguments apply only to calculations on multidimensional databases.

For example, the grid below shows the calculated member RunningTotal whose expression is a Runningtotal function. The Runningtotal function has the following values for its arguments:

  • Member = Product
  • Dimension = Time
  • Generation = 1
  • Grouping = Grouped

Locations

Time

Product

RunningTotal

East

2000

102

102

2001

76

178

2002

88

266

West

2000

49

49

2001

52

101

2002

51

152


 

Related topics: