Supported functions for rules

image_pdfimage_print

The following chart outlines the supported functions for rules. 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).

Function
Description
Argument(s)
Type(s)

ABS(number)

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

numeric argument

double

ACOS(number)

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

numeric argument

double

ADD(number1, number2)

Returns the sum of its two arguments.

numeric arguments

doubles

AND(expression1, expression2, …).

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

boolean arguments

booleans

ASIN(number)

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

numeric argument

double

ATAN(number)

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

numeric argument

double

AVERAGE(number1, number2, …)

Returns average of its arguments.

numeric arguments

doubles

CEILING(number)

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

numeric argument

double

CHAR(number)

Returns the character specified by the code number.

character code

integer

CLEAN(text)

Removes all nonprintable characters from text.

string to be cleaned

string

CODE(text)

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

string argument

string

CONCATENATE(text1, text2, …)

Joins two or more text items into one text item.

string arguments

strings

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 argument

double

COUNT(number1, number2, …)

Returns the number of values provided

numeric arguments

doubles

DATE(year, month, day)

Returns the serial number that represents a particular date.
Example: Date(2015,1,1) returns 1420070400. (60*60*24*16436 seconds since 1.1.1970).
The year argument must be four digits and greater than or equal to 1900 and smaller than or equal to 10000.
“Month” is a number representing the “month” of the year. If “month” is greater than 12, then this will be corrected: (“month” – 12) is the corrected month number and (“year” + 1) is the corrected year number. For example, DATE(2008,14,2) returns the serial number 1233532800 representing 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 this will be corrected: the excess number of days is the corrected day number and (“month” + 1) is the corrected month number. For example, DATE(2008,1,35) returns the serial number 1202083200 representing February 4, 2008.

year of date,
 month of date,
 day of date

integer,
integer,
integer

DATEFORMAT(date, format)

Converts date to string using given format, where date is a numeric date value (serial number) and format a string with the following switches: \y the last two digits of the year (97, 98, etc.) \Y the four digits of the year (1997, 1998, etc.) \m the two digits of the month (01 through 12) \M the abbreviation of the month (Jan, Feb, etc.) \d the two digits of the day (01 through 31) \D the digit of the day (1 through 31) \h the hour in military time (00 through 23) \H the standard hour (00 through 11) \i the minute (00 through 59) \s the second (00 through 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 to format,
 format of date

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 returns also correct values if date is represented as “MM-DD-YY”, “MM.DD.YY” or “MM,DD,YY” and 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). Therefore this is a correct result for the representation of the year 2070 and a wrong result for the representation of the year 1970.

date-string to convert

string

DEL(number A, number B)

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

numeric arguments

doubles

DIV(number A, number B)

Returns the result of dividing the two arguments.

numeric arguments

doubles

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 have the same type.

comparison arguments

doubles or strings

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

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

numeric arguments

doubles

EVEN(number)

Rounds a number up to the nearest even integer

numeric argument

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, 0 otherwise. EXACT is case-sensitive

comparison arguments

strings

EXP(number)

Returns e raised to the power of a given number.

numeric argument

double

FACT(number)

Returns the factorial of the argument.

numeric argument

integer

FIRST(number1, number2, …)

Returns first of its arguments.

numeric arguments

doubles

FLOOR(number)

Rounds a number down, toward zero.

numeric argument

integer

GE(operand A, operand B)

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

comparison arguments

doubles or strings

GT(operand A, operand B)

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

comparison arguments

doubles or strings

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

test,
 value-if-true,
 value-if-false

boolean,
 object,
 object

INT(number)

Rounds a number down to the nearest integer

numeric argument

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

LAST(number1, number2, …)

Returns last of its arguments.

numeric arguments

doubles

LE(operand A, operand B)

 

comparison arguments

doubles or strings

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. Num_chars specifies the number of characters you want LEFT to extract. 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.

base-string,
 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 argument

integer

LOG(number, base)

Returns the logarithm of a number to a specified base

numeric arguments

doubles

LOG10(number)

Returns the base-10 logarithm of a number.

numeric argument

integer

LOWER(text)

Converts text to lowercase.

string to be uncapitalized

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 arguments

doubles or strings

MAX(number1, number2, …)

Returns the maximum value of the given arguments.

numeric arguments

doubles

MEDIAN(number1, number2, …)

Returns the median of the given arguments.

numeric arguments

doubles

MID(text, start_num, num_chars)

Returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. 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 in text. Num_chars specifies the number of characters you want MID to return from text. If start_num is greater than the length of text, MID returns -empty text-. 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 -empty text-.If num_chars is negative, MID returns -empty text-.

base-string,
 position,
 number of chars

string,
 integer,
 integer

MIN(number1, number2, …)

Returns the minimum value of the given arguments.

numeric arguments

doubles

MOD(number, divisor)

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

numeric arguments

integers

MUL(number A, number B)

Returns the arithmetical product of its two arguments.

numeric argument

doubles

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 have the same type.

comparison arguments

doubles or strings

NOT(boolean)

Returns the logical not of given argument.

boolean argument

double

NOW()

Returns the serial number of the current date and time.

   

ODD(number)

Returns number rounded up to the nearest odd integer.

numeric argument

double

OR(expression1, expression2)

Returns the logical or of its arguments.

boolean arguments

booleans

PALO.CUBEDIMENSION(Database, Cube, num_n)

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

database,
 cube,
 dimension-index

string,
 string,
 integer

PALO.DATA(Database, Cube, Coordinate1, Coordinate2,…, CoordinateN)

Retrieves the value of the specified element from the cube.

database,
 cube,
 coordinates

strings

PALO.ECHILD(Database, Dimension, Parent Element, num_child)

Retrieves the name of the specified child-element.

database, dimension,
parent, number of child

string, string,
 string, integer

PALO.ECHILDCOUNT(Database, Dimension, Element)

Retrieves the number of children in the specified consolidated element.

database, dimension, element

strings

PALO.ECOUNT(Database, Dimension)

Retrieves the amount of dimension elements in the specified dimension.

database, dimension

strings

PALO.EFIRST(Database, Dimension)

Retrieves the first element in the specified dimension.

database, dimension

strings

PALO.EINDENT(Database, Dimension, Element)

Retrieves the indention level of the specified element.

database, dimension, element

strings

PALO.EINDEX(Database, Dimension, Element)

Retrieves the position of the specified dimension element.

database, dimension, element

strings

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

strings

PALO.ELEVEL(Database, Dimension, Element)

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

database, dimension, element

strings

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

strings

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

strings

PALO.EPREV(Database, Dimension, Element)

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

database, dimension, element

strings

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

strings

PALO.ETYPE(Database, Dimension, Element)

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

database, dimension, element

strings

PALO.EWEIGHT(Database, Dimension, Parent, Child)

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

database, dimension, parent, child

strings

PALO.MARKER(Database, Cube, Coordinate1, Coordinate2, …, CoordinateN)

Adds marker for a slice.

database, cube, coordinates

strings

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 arguments doubles

PI()

Returns the value of pi.

   

POWER(number , power)

Returns the result of a number raised to a power.

numeric arguments

doubles

PROPER(text)

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

string to be changed

string

QUOTIENT(numerator, denominator)

Returns the integer portion of a division

numeric arguments

doubles

RAND()

Returns a random number between 0 and 1

   

RANDBETWEEN(bottom, top)

Returns a random number in the range you specify.

numeric argument

doubles

REPLACE(old_text, start_num, num_chars, new_text)

Replaces part of a text string, based on the number of characters you specify, with a different text string. Old_text is 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 REPLACE to replace with new_text. New_text is the text that will replace characters in old_text.

base-string,
 start index,
 end index,
 replacement

string,
 integer,
 integer,
 string

REPT(text, num_times)

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

base-string,
 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 RIGHT to extract. Num_chars 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.

base-string,
 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.

real to be rounded,
 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. A 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.

string we search for,
 string in which you want to search for find_text

strings

SIGN(number)

Returns the sign of a number

numeric parameter

double

SIN(number)

Returns the sine. The number is given in radians.

numeric argument

double

SQRT(number)

Returns a positive square root.

numeric argument

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.

number to be converted,
 total length of resulting string,
 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 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.

base-string,
 string to be replaced,
 replacement

strings

SUM(number1, number2, …)

Returns sum of values provided through function arguments.

numeric arguments

doubles

TAN(number)

Returns the tangent. The number is given in radians.

numeric argument

double

TRIM(text)

Removes spaces from text.

string to be trimmed

string

TRUNC(number)

Truncates a number to an integer.

numeric argument

double

UPPER(text)

Converts Text to uppercase.

string to be capitalized

string

VALUE(text)

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

string to be converted

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

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

 

image_pdfimage_print
Was this post helpful?
NoYes (+4 rating, 4 votes)
Loading...