Supported Functions for Rules

image_pdfimage_print

The following chart outlines the supported functions for rules.

For more information about the operations with 0/null and DIV, MUL, DEL, and ADD, see Storing Zero Values and Working with Business Rules.

Function
Description
Argument(s)
Data Type(s)
DDE Support

ABS(number)

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

numeric

double

2018.2

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

2018.2

AND(expression1, expression2, …)

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

Boolean

Boolean

2018.2

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

2018.2

CEILING(number)

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

numeric

double

2018.2

CHAR(number)

Returns a character specified by a code number.

numeric

integer

2018.3 (CPU only)

CLEAN(text)

Removes all non-printable characters from the text.

string

string

2018.3 (CPU only)

CODE(text)

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

string

string

2018.3 (CPU only)

CONCATENATE(text1, text2, …)

Joins two or more text items into one text item.

string

string

2018.3 (CPU only)

CONTINUE()

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

 n/a n/a 2018.2

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:

\y Last two digits of the year (97, 98, etc)
\Y Four digits of the year (1997, 1998, etc)
\m Two digits of the month (01 – 12)
\M Abbreviation for the month (Jan, Feb, etc)
\d Two digits for the day (01 – 31)
\D Digit for the day (1 – 31)
\h Hour in military time (00 – 23)
\H Hour in standard time (01 – 12)
\i Minutes (00 – 59)
\s Seconds (00 – 59)
\p AM or PM

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

2018.2

DIV(number A, number B)

Returns the result of dividing the two arguments.

numeric

double

2018.2

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

2018.2

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

2018.2

EVEN(number)

Rounds a number up to the nearest even integer.

numeric

double

2018.2

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

 
EXIST(x)

 

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

2018.4

EXP(number)

Returns e raised to the power of a given number. As of version 2018.3: returns null for null input. As a workaround to obtain the former result, you can change exp(['empty cell']) to exp(['empty cell']+0).

numeric

double

2018.3 (CPU only)

FACT(number)

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

numeric

integer

2018.2

FIRST(number1, number2, …)

Returns first of its arguments.

numeric

double

 

FLOOR(number)

Rounds a number down toward zero.

numeric

integer

2018.2

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

2018.2

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

2018.2

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.

As of version 2018.3, 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)

Boolean
object
object (optional)
2018.2

INT(number)

Rounds a number down to the nearest integer.

numeric

integer

2018.2

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

2018.2

ISNULL(value)

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

 numeric, string

Function is and will NOT be DDE supported

LAST(number1, number2, …)

Returns last of its arguments.

numeric

double

 

LE(operand A, operand B)

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

comparison

double or string

2018.2

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

2018.3 (CPU only)

LEN(text)

Returns the number of characters in a text string.

string to be counted

string

2018.3 (CPU only)

LN(number)

Returns the natural logarithm of a number.

numeric

integer

2018.2

LOG(number, base)

Returns the logarithm of a number to a specified base.

numeric

double

2018.2

LOG10(number)

Returns the base-10 logarithm of a number.

numeric

integer

2018.2

LOWER(text)

Converts text to lowercase.

string

string

2018.3 (CPU only)

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

2018.2

MAX(number1, number2, …)

Returns the maximum value of the given arguments.

numeric

double

2018.2

MEDIAN(number1, number2, …)

Returns the median of the given arguments.

numeric

double

2018.2

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

2018.3 (CPU only)

MIN(number1, number2, …)

Returns the minimum value of the given arguments.

numeric

double

2018.2

MOD(number, divisor)

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

numeric

integer

2018.3 (CPU)
2018.2 (GPU)

MUL(number A, number B)

Returns the product of multiplying the two arguments.

numeric

double

2018.2

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

2018.2

NOT(boolean)

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

Boolean

double

2018.2

NOW()

Returns the serial number of the current date and time. See note on server rules.

n/a n/a  

ODD(number)

Returns number rounded up to the nearest odd integer.

numeric

double

2018.2

OR(expression1, expression2)

Returns the logical OR of its arguments.

Boolean

Boolean

2018.2

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.

database, cube, coordinates

string

2018.2

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

 

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.

database, cube, coordinates

string

2018.2

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.

numeric double  

PI()

Returns the value of pi.

     

POWER(number, power)

Returns the result of a number raised to a power.

numeric

double

2018.3 (CPU only)

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

2018.3 (CPU only)

QUOTIENT(numerator, denominator)

Returns the integer portion of a division equation.

numeric

double

2018.3 (CPU only)

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

2018.3 (CPU only)

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

2018.3 (CPU)
2018.2 (GPU)

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

2018.2

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

2018.2

SIN(number)

Returns the sine. The number is given in radians.

numeric

double

 

SQRT(number)

Returns a positive square root.

numeric

double

2018.2

STET()

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

    2018.2

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

2018.3 (CPU only)

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

2018.2

TAN(number)

Returns the tangent in radians.

numeric

double

 

TRIM(text)

Removes spaces from text.

string

string

2018.3 (CPU only)

TRUNC(number)

Truncates a number to an integer.

numeric

double

2018.2

UPPER(text)

Converts text to uppercase.

string

string

2018.3 (CPU only)

VALUE(text)

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

string

string

2018.3 (CPU only)

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

2018.3 (CPU only)

WEEKDAY(serial_number)

Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.

numeric argument representing a date

double

2018.3 (CPU only)

  

Server rules have a time system with 1.1.1970 as start time and seconds as units, e.g.: 1.1.1970 00:00:00 = 0, 2.1.1970 00:00:00 = 86400 (= 24h * 60min * 60sec).

image_pdfimage_print