General Functions

Average

The Average takes a sequence of values, sums them and divides by the number of values. This function will work on all numeric types. The result will always be a double.

Comma Separated List

The Comma Separated List takes a sequence of values and returns a single string holding these values separated by commas. For example, if the selected fields from three records are Apple, Orange, Strawberry, you will get back a String "Apple, Orange, Strawberry".

Comma Separated Set

The Comma Separated Set takes a sequence of values and returns a single string holding the discrete values separated by commas. This means duplicates are removed. For example, if the selected fields from five records are Apple, Berry, Apple, Orange, Berry, you will get back a String "Apple, Berry, Orange" - each item is only listed once.

Count

The Count result is the number of values that the function has received. This is most useful in cubes where usually different numbers of records are partitioned into each cell.

First

The First function always replies with the first value that it receives.

Last

The Last function always replies with the last value that it receives.

Max

The Max result is the largest value received. This function works for all comparable types, including strings and dates as well as numbers.

Median

The median function takes a sequence of values, which should have been sorted in increasing order and returns the value in the middle. If the number of elements is even then it returns the average of the two values closest to the middle. Hence this function only works on numbers and dates. For instance, if there are numbers 1,2,2,3,3,4,5,6. There is an even number of values, so the middle or median is between the first and the second three. As they are the same the median is three, but if they were different say if the median was between 3 and 4, we would do (3+4)/2=3.5.

Min

The Min result is the smallest value received. This function works for all comparable types, including strings and dates as well as numbers.

Percent

The Percent result is the sum of all values received divided by the sum of all values available. The result will be a number between 0 and 1, which can be formatted as a percentage (for example using the Field Format in Report).

Percent100

The Percent100 result uses the same algorithm as Percent, but the value returned is scaled into the range of 0 to 100.

PercentCount

The PercentCount result is the count of all values received divided by the count of all values available. The result will be a number between 0 and 1, which can be formatted as a percentage (for example using the Field Format in Report).

PercentCount100

The PercentCount100 result uses the same algorithm as PercentCount, but the value returned is scaled into the range of 0 to 100.

Standard Deviation

The Standard Deviation is the square root of the Variance (see below).

Sum

The Sum result is the summation of all values received. The result will always be a double.

Variance

The Variance is the measure of how spread out a distribution is. It is computed as the average squared deviation of each number from its mean(average). For example, for the number 1, 2 and 3 the mean is 2 and the variance is:

[(1-2)²+(2-2)²+(3-2)²]/(3-1) = 0.5

Year To Date

The Year To Date sums value(s) of data that has the year corresponding to the current year of the system.

Month To Date

The Month To Date sums value(s) of data that has the month and year corresponding to the current month and year of the system.