Supported Functions for Rules

ABS(number)

Returns the absolute value of a number. The absolute value of a number is the number without its sign.

numeric

double

ACOS(number)

Returns the inverse cosine of a number. The returned angle is given in radians.

numeric

double

ADD(number1, number2)

Returns the sum of its two arguments.

numeric

double

AND(expression1, expression2, ...)

Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE.

Boolean

Boolean

ASIN(number)

Returns the inverse sine. The returned angle is given in radians.

numeric

double

ATAN(number)

Returns the inverse tangent. The returned angle is given in radians.

numeric

double

AVERAGE(number1, number2, …)

Returns the average of its arguments.

numeric

double

CEILING(number)

Rounds a number up to the nearest integer or the nearest multiple of significance.

numeric

double

CHAR(number)

Returns a character specified by a code number.

numeric

integer

CLEAN(text)

Removes all non-printable characters from the text.

string

string

CODE(text)

Returns a numeric code for the first character in a text string.

string

string

CONCATENATE(text1, text2, ...)

Joins two or more text items into one text item.

string

string

CONTINUE()

If a rule evaluates to CONTINUE then that rule is skipped and search for valid rule is continued.

COS(number)

Returns the cosine. The number is given in radians.

numeric

double

COUNT(number1, number2, …)

Returns the number of values provided.

numeric

double

DATE(year, month, day)

Returns a serial number that represents a particular date based on the server rules time system. The year argument must be four digits that are greater than or equal to 1900 and smaller than or equal to 10,000.

Example: Date(2015,1,1) returns 1420070400. (60*60*24*16436 seconds since 1.1.1970).

"Month" is a number representing the month of the year. If Month is greater than 12, then the date will be calculated as follows: the month will be determined as Month – 12, and the year will be determined as Year + 1. For example, DATE(2008,14,2) returns the serial number 1233532800, which represents February 2, 2009.

"Day" is a number representing the day of the month. If Day is greater than the number of days in the month specified, then the date will be calculated as follows: the day will be determined as the excess number of days, and the month will be determined as Month + 1. For example, DATE(2008,1,35) returns the serial number 1202083200, which represents February 4, 2008.

all parameters numeric

integer

DATEFORMAT(date, format)

Converts a serial number date to string format, where "date" is a numeric date value (serial number) and "format" is a string with the following switches:

Example: =DATEFORMAT(1294158800, "\Y \M \d \H \i \s \p") returns "2011 Jan 04 04 33 20 PM" (Universal Time Coordinated).

date: numeric date value
format: string

double
string

DATEVALUE(date_text)

Returns the serial number of the date represented as MM-DD-YYYY, MM.DD.YYYY, or MM,DD,YYYY. The function also returns correct values if the date is represented as MM-DD-YY, MM.DD.YY, or MM,DD,YY, as long as YY represents a year later than 1999.

Examples:

=DATEVALUE("01-02-2015") returns serial number 1420156800, which represents 02-Jan-2015 (=60*60*24*16437 seconds since 1.1.1970).

=DATEVALUE("01-02-70") returns serial number 3155846400, which represents 02-Jan-2070 (=60*60*24*36526 seconds since 1.1.1970). Note that this would be an incorrect result if the intended year was 1970.

date string to convert

string

DEL(number A, number B)

Returns the result of subtracting the second argument from the first.

numeric

double

DIV(number A, number B)

Returns the result of dividing the two arguments.

numeric

double

EQ(operand A, operand B)

Checks if the two arguments, which can be of type string or double, are exactly equal. Returns true or false accordingly. Both arguments must be of the same type.

comparison

double or string

ES(k-th percentile, number1, [number2, …])

Returns the average of the values exceeding k-th percentile of values in a list, where k-th percentile is in the range 0..1 (inclusive).

numeric

doubles

EVEN(number)

Rounds a number up to the nearest even integer.

numeric

double

EXACT(text1, text2)

Checks to see if two text values are identical. Compares two text strings and returns 1 if they are exactly the same; otherwise returns 0. This function is case-sensitive.

comparison

string

Determines whether a cube cell holds a value or not. It returns null if x is empty, or 1 if x is numeric, string, or an error. Parameter can be numeric (including zero) or string (including empty).

Note: this rule replaces ISNULL, which will be deprecated in the future.

value can be any type

numeric, string

EXP(number)

Returns e raised to the power of a given number. Returns null for null input.

numeric

double

FACT(number)

Returns the factorial of the argument. Can be slow for very large numbers.

numeric

integer

FIRST(number1, number2, …)

Returns first of its arguments.

numeric

double

FLOOR(number)

Rounds a number down toward zero.

numeric

integer

GE(operand A, operand B)

Checks if the first argument is greater than or equal to the second argument. Both arguments must be the same type, which can be double or string.

comparison

double or string

GT(operand A, operand B)

Checks if the first argument is greater than the second argument. Both arguments must be the same type, which can be double or string.

comparison

double or string

IF(test, value-if-true, value-if-false) *

Checks if the first parameter is TRUE. In this case, the second parameter is returned. Otherwise, the third parameter is returned.

The third (false) argument can be omitted for OLAP rules; in this case, the default value for the third argument internally will be null. This option is also available for the graphical rule editor. To remove the third argument from the IF expression, click the cogwheel menu in the interface and select Delete.

test, value-if-true, value-if-false (optional)

Evaluates multiple expressions and returns a value that corresponds to the first TRUE result. In contrast to IFS in Excel , a default result can automatically correspond to the last given argument, instead of a pseudo TRUE condition in the end, as is done in Excel.

Note: <default> (=default result) can only be the last argument, but is optional.

test, value-if-true, value-if-false (optional)

INT(number)

Rounds a number down to the nearest integer.

numeric

integer

ISERROR(test)

Returns TRUE if test expression can't be evaluated or evaluates to non-string or non-numeric value.

test expression

double or string

Function returns:

• 1 if value is null
• null if value is error, numeric (including 0), or string (including empty string)

Note: this function has been effectively replaced by EXIST(x) and will be deprecated in the future.

Value can be any type

LAST(number1, number2, …)

Returns last of its arguments.

numeric

double

LE(operand A, operand B)

comparison

double or string

LEFT(text, num_chars)

Returns the leftmost characters from a text value. "text" is the text string that contains the characters you want to extract, and "num_chars" specifies the number of characters to be extracted. The value of num_chars must be greater than or equal to zero. If num_chars is greater than the length of text, LEFT returns all of the text. If num_chars is omitted, it is assumed to be 1.

text: base string
num_chars: number of chars

string
integer

LEN(text)

Returns the number of characters in a text string.

string to be counted

string

LN(number)

Returns the natural logarithm of a number.

numeric

integer

LOG(number, base)

Returns the logarithm of a number to a specified base.

numeric

double

LOG10(number)

Returns the base-10 logarithm of a number.

numeric

integer

LOWER(text)

Converts text to lowercase.

string

string

LT(operand A, operand B)

Checks if the first argument is less than the second argument. Both arguments must have the same type, which can be double or string.

comparison

double or string

MAX(number1, number2, …)

Returns the maximum value of the given arguments.

numeric

double

MEDIAN(number1, number2, …)

Returns the median of the given arguments.

numeric

double

MID(text, start_num, num_chars)

Returns a specific number of characters from a text string, starting at the specified position and number of characters. "text" is the text string containing the characters you want to extract. "start_num" is the position of the first character you want to extract from the text. "num_chars" specifies the number of characters you want to return from text.

Output:

• If start_num is greater than the length of text, MID returns null
• If start_num is less than the length of text, but start_num plus num_chars exceeds the length of text, MID returns the characters up to the end of text
• If start_num is less than 1, MID returns null
• If num_chars is negative, MID returns null

text: base string
start_num: position
num_chars: number of characters

string
integer
integer

MIN(number1, number2, …)

Returns the minimum value of the given arguments.

numeric

double

MOD(number, divisor)

Returns the remainder after the number is divided by divisor. The result has the same sign as the divisor.

numeric

integer

MUL(number A, number B)

Returns the product of multiplying the two arguments.

numeric

double

NE(operand A, operand B)

Checks if the two arguments, which can be of type string or double, are exactly equal. Returns true if there is no equality and false otherwise. Both arguments must be the same type.

comparison

double or string

NOT(boolean)

Returns the logical NOT of given argument. Can be slow when negating a large, sparse data set.

Boolean

double

NOW()

Returns the time when an OLAP job began processing. See note on the time system for server rules.

Warning: using NOW() could slow down subsequent report calculations, because rule results that depend on the NOW() function result and other volatile data sources are not stored in cube caches.

ODD(number)

Returns number rounded up to the nearest odd integer.

numeric

double

OR(expression1, expression2)

Returns the logical OR of its arguments.

Boolean

Boolean

PALO.CUBEDIMENSION(database, cube, num_n)

Returns the name of the nth dimension in a specified hypercube.

database, cube, number of dimension

string, string, integer

PALO.DATA(database, cube, coordinate1, coordinate2,..., coordinateN)

Retrieves the value of the specified element from the cube.

An empty string ("") can be entered for the <database> and <cube> arguments.

database, cube, coordinates

string

PALO.ECHILD(database, dimension, parent element, num_child)

Retrieves the name of the specified child element.

database, dimension, parent element, number of child element to retrieve

string, string, string, integer

PALO.ECHILDCOUNT(database, dimension, element)

Retrieves the number of children in the specified consolidated element.

database, dimension, element

string

PALO.ECOUNT(database, dimension)

Retrieves the amount of dimension elements in the specified dimension.

database, dimension

string

PALO.EFIRST(database, dimension)

Retrieves the first element in the specified dimension.

database, dimension

string

PALO.EINDENT(database, dimension, element)

Retrieves the indention level of the specified element.

database, dimension, element

string

PALO.EINDEX(database, dimension, element)

Retrieves the position of the specified dimension element.

database, dimension, element

string

Checks if a consolidated element contains a specified element in all of its descendants; results in 0 or 1.

Similar to PALO.EISCHILD but also checks indirect relations between elements, e.g. Year to January, when Q1 is child of Year and Q1 is parent of January.

PALO.EISANC("", "month", "Year", "January") returns 1.

Function returns #ERROR when nonexistent database, dimension, or element is specified as a parameter.

database, dimension, parent, element

string

PALO.EISCHILD(database, dimension, parent element, element)

Checks if a consolidated element contains the specified element, results in 0 or 1.

database, dimension, parent, element

string

PALO.ELEVEL(database, dimension, element)

Returns the level in the dimension hierarchy of a specified element.

database, dimension, element

string

PALO.ENAME(database, dimension, position)

Retrieves the name of the element at the specified position (First Position is 1).

database, dimension, position

string, string, integer

PALO.ENEXT(database, dimension, element)

Retrieves the name of the succeeding element of a dimension element.

database, dimension, element

string

PALO.EOFFSET(database, dimension,element, index)

Retrieves the name of the dimension element distant by offset from specified element.

database, dimension,
element,
index of offset

string, string, string, integer

PALO.EPARENT(database, dimension, element, num_n)

Retrieves the name of the n-th parent of the specified element.

database, dimension, element,
index of parent

string, string, string, integer

PALO.EPARENTCOUNT(database, dimension, element)

Retrieves the number of consolidated elements which contain the specified element.

database, dimension, element

string

PALO.EPREV(database, dimension, element)

Retrieves the name of the preceding element of a dimension element.

database, dimension, element

string

PALO.ESIBLING(database, dimension, element, index)

Retrieves the name of the specified sibling.

database, dimension, element, index of sibling

string, string, string, integer

PALO.ETOPLEVEL(database, dimension)

Returns the level number of the highest element in the consolidation hierarchy of a dimension.

database, dimension

string

PALO.ETYPE(database, dimension, element)

Retrieves the type of the specified element.(numeric, string, or consolidated).

database, dimension, element

string

PALO.EWEIGHT(database, dimension, parent, child)

Returns the consolidation weight of a specified component of an element.

database, dimension, parent, child

string

PALO.MARKER(database, cube, coordinate1, coordinate2, ..., coordinateN)

Adds marker for a slice.

An empty string ("") can be entered for the <database> and <cube> arguments.

database, cube, coordinates

string

PERCENTILE(k-th percentile, number1, [number2, …])

Returns the k-th percentile of the values in a list, where k-th percentile is in the range 0..1, inclusive.

PI()

Returns the value of pi.

POWER(number, power)

Returns the result of a number raised to a power.

As of version 2018.3, this function returns null for null input. As a workaround to obtain the result from previous versions, you can change POWER(['number'], ['power']) to POWER(['number'], ['power'] + 0)

numeric

double

PROPER(text)

Capitalizes the first letter in a text string, as well as any other letters in the text that follow any character other than a letter. Converts all other letters to lowercase letters.

string

string

QUOTIENT(numerator, denominator)

Returns the integer portion of a division equation.

numeric

double

RAND()

Returns a random number between 0 and 1.

RANDBETWEEN(bottom, top)

Returns a random number in the range you specify.

numeric

double

REPLACE(old_text, start_num, num_chars, new_text)

Replaces part of a text string with a different text string, based on the specified number of characters. "old_text" is the text in which you want to replace some characters. "start_num" is the position of the character in old_text that you want to replace with new_text. "num_chars" is the number of characters in old_text that you want to replace with new_text. "new_text" is the text that will replace characters in old_text.

old_text: base string
start_num: start index
num_chars: end index
new_text: replacement

string, integer, integer, string

REPT(text, num_times)

Repeats text a given number of times. "text" is the text you want to repeat. "num_times" is a positive number specifying the number of times to repeat text. If num_times is 0 (zero), REPT returns null. If number_times is not an integer, it is truncated.

text: base-string
num_times: number of repetitions

string, integer

RIGHT(text, num_chars)

Returns the rightmost characters from a text value. "text" is the text string that contains the characters you want to extract. "num_chars" specifies the number of characters you want to extract and must be greater than or equal to zero. If num_chars is greater than the length of text, RIGHT returns all of the text. If num_chars is omitted, it is assumed to be 1.

text: base-string
num_chars: number of chars

string, integer

ROUND(num_base, num_digits)

Rounds a number to a specified number of digits. If num_digits is 0, then number is rounded to the nearest integer. If num_digits is less than 0, then number is rounded to the left of the decimal point.

num_base: number to be rounded
num_digits: number of digits

double,
integer

SEARCH(find_text, within_text)

Finds one text value within another (not case-sensitive). "find_text" is the text you want to find. You can use the wildcard characters question mark (?) and asterisk (*) in find_text: question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. Within_text is the text in which you want to search for find_text. If find_text is not found, then 0 is returned. If found, then position is returned.

find_text: string to search for
within_text: string in which you want to search for find_text

string

SIGN(number)

Returns the sign of a number.

numeric parameter

double

SIN(number)

Returns the sine. The number is given in radians.

numeric

double

SQRT(number)

Returns a positive square root.

numeric

double

STET()

If a rule evaluates to STET, then the resulting cell behaves like no rule is existent for this cell.

STR(number, width, precision)

Converts number to string. As of 2018.3: 2nd and 3rd parameters are optional. Default is 0.

number: number to be converted
width: total length of resulting string
precision: number of decimal places

double, integer, integer

SUBSTITUTE(text_base, old_text, new_text)

Substitutes new text for old text in a text string. "text_base" is the text (or the reference to a cell containing text) for which you want to substitute characters. "old_text" is the text you want to replace. "new_text" is the text you want to replace old_text with.

text_base: base-string
old_text: string to be replaced
new_text: replacement

string

SUM(number1, number2, …)

Returns sum of values provided through function arguments.

numeric

double

TAN(number)

Returns the tangent in radians.

numeric

double

TRIM(text)

Removes spaces from text.

string

string

TRUNC(number)

Truncates a number to an integer.

numeric

double

UPPER(text)

Converts text to uppercase.

string

string

VALUE(text)

Converts a string that represents a number to a number. Returns 0 if the conversion fails.

string

string

VALUEDATE(number)

Returns a date_text for a given serial number in the format MM-DD-YY. Target area must be of type string.

numeric argument representing a date

double