Analytica’s language XL supports a multitude of functions in order to define the values of calculated properties. The following lists all functions, its parameters and explains its semantics.
Legend
- A single character or a character combined with a number, e.g. p1, represents a single parameter
- A single character c in [c] represents a set of parameters of any size
- Parameters can be individual properties (scalar or collections), or constants
- Constants are quote-delimited string constants, numbers, true, false, null, NaN and undefined
- Expressions are single or nested function invocations
Functions
Boolean Functions |
||||
| Function | Description | Parameters | Returns | Examples |
| and([b]) | Performs a logical AND operation | [b]: one or more boolean properties, constants or boolean expressions | Returns the logical AND of the parameters in [b]. | and (true, true, false) returns false |
| eq(p1, p2) | Tests for equality (equal) | p1: first parameter to compare; p2: second parameter to compare | Returns true if p1 and p2 are equal. False if p1 and p2 are not equal. | eq (“analytica”, “analytica”) returns true |
| neq(p1, p2) | Tests for inequality (not equal) | p1: first parameter to compare; p2: second parameter to compare | Returns false if p1 and p2 and equal. True if p1 and p2 are not equal. | neq (“a”, false) returns true |
| not(b) | Performs a logical NOT operation | b: boolean property, constant or boolean expression | Returns the logical NOT of parameter b | not (eq(“a”, “b”)) returns true |
| or([b]) | Performs a logical OR operation | [b]: one or more boolean properties, constants or boolean expressions | Returns the logical OR of the parameters in [b]. | or (null, false) returns false |
| xor([b]) | Performs a logical XOR operation | [b]: one or more boolean properties, constants or boolean expressions | Returns the logical XOR of the parameters in [b] | xor (null, false, NaN) returns false |
Date Functions |
||||
| Function | Description | Parameters | Returns | Examples |
| datetimefrom_id(_id) | Extracts date/time from MongoDB’s _id | Property _id from a MongoDB document | Returns date/time equivalent of date and time data inside_id | |
| datetimefromstring(s) | Creates a date/time | s: string that contains a date and optionally a time | Returns a date/time representing s | datetimefromstring (“2012-10-11 18:59″) returns 10/11/2012 6:59:00 PM; this can be formatted in Excel with Date formats |
| datevalue(s) | Calculates the Excel representation of a date | s: string that contains a date and optionally a time | Returns the Excel representation of s | datevalue (“2012-10-11 18:59″) returns 41193.7909722222; this can be formatted in Excel with Date formats |
| day(d) | Returns the day of a date | d: date/time | Returns the day of d | day (datetimefromstring(“2012-10-11 18:59″)) returns 11 |
| hour(d) | Returns the hour of a date | d: date/time | Returns the floor of the minutes and seconds to the hour of d | hour (datetimefromstring(ʺ2012-10-11 18:59ʺ)) returns 18 |
| month(d) | Returns the month of a date | d: date/time | Returns the month of d | month (datetimefromstring(“2012-10-11 18:59″)) returns 10 |
| stripseconds(d) | Returns date/time with seconds set to zer | d: date/time | Returns d with the seconds set to zero | |
| weekday(d) | Returns the day of the week of a date | d: date/time | Returns the day of week of d | weekday (datetimefromstring(“2012-10-11 18:59″)) returns 4 |
| weeknum(d) | Returns the number of the week of a date | d: date/time | Returns the number of the week of d | weeknum (datetimefromstring(“2012-10-11 18:59″)) returns 41 |
| year(d) | Returns the year from a date | d: date/time | Returns the year of d | year (datetimefromstring(“2012-10-11 18:59″)) returns 2012 |
| yearfrac(d1, d2) | Calculates the fraction of the year between two dates | d1: start date/time, d2: end date/time | Returns a number representing the fraction of a year (360 days) of the number of days between the start and end date | yearfrac (datetimefromstring (“2012-05-11 18:59″) , datetimefromstring (“2012-10-11 18:59″)) returns 0.416666666666667 |
Math Functions |
||||
| Function | Description | Parameters | Returns | Examples |
| abs(n) | Calculates the absolute value | n: a number | Returns the absolute value of n | abs (-5) returns 5 |
| acos(n) | Calculates the arc cosine | n: a number | Returns the angle whose cosine is n | acos (1) returns 0 |
| asin(n) | Calculates the arc sine | n: a number | Returns the angle whose sine is n | |
| atan(n) | Calculates the arc tangent | n: a number | Returns the angle whose tangent is n | |
| average([n]) | Calculates the arithmetic mean | [n]: zero or more numbers | Returns the arithmetic mean of the numbers in [n] | average (-1, 0, 1) returns 0 |
| ceiling(n) | Calculates the ceiling | n: a number | Returns the smallest integral value that is greater than or equal to n | |
| cos(n) | Calculates the cosine | n: a number | Returns the cosine of n | |
| cosh(n) | Calculates the hyperbolic cosine | n: a number | Returns the hyperbolic cosine of n | |
| div(n1, n2) | Division | n1: dividend; n2: divisor | Returns the value of n1 divided by n2 | div (0, 5) returns 0; div (5,0) returns a/e (for Analytica Error) |
| exp(n) | Calculates the exponential | n: a number | Returns e to the power of n | |
| floor(n) | Calculates the floor | n: a number | Returns the largest integer less than or equal to n | |
| ge(n1, n2) | Tests for greater or equal | n1: first parameter to compare; n2: second parameter to compare | Returns true if n1 is greater than or equal to n2 | |
| gt(n1, n2) | Tests for greater than | n1: first parameter to compare; n2: second parameter to compare | Returns true if n1 is greater than n2 | |
| le(n1, n2) | Tests for less or equal | n1: first parameter to compare; n2: second parameter to compare | Returns true if n1 is less than or equal to n2 | |
| ln(n) | Calculates the natural logarithm | n: a number | Returns the natural logarithm of n | |
| log(n) | Calculates the logarithm | n: a number | Returns the logarithm of n | |
| lt(n1, n2) | Tests for less than | n1: first parameter to compare; n2: second parameter to compare | Returns true if n1 is less than n2 | |
| max([n]) | Calculates the maximum | [n]: one or more parameters | Returns the maximum of the parameters in [n] | |
| median([n]) | Calculates the median | [n]: a set of parameters | Returns the median value of the parameters in [n] | |
| min([n]) | Calculates the minimum | [n]: one or more parameters | Returns the minimum of the parameters in [n] | |
| mult([n]) | Calculates the product | [n]: one or more parameters | Returns the product of the parameters in [n] | |
| percent(n1, n2) | Calculates the percent | n1: number that is being divided; n2: number dividing the dividend times 100 | Returns n1/n2*100 | |
| power(n1, n2) | Calculates the power | n1: the base; n2: the power | Returns n1^n2 | |
| product([n]) | Calculates the product | [n]: one or more parameters | Returns the product of the parameters in [n] | |
| sign(n) | Sign of a number | n: a number | Returns -1 if number is negative, 0 if number is 0 and 1 if number is positive | |
| sin(n) | Calculates the sine | n: a number | Returns the sine of n | |
| sinh(n) | Calculates the hyperbolic sine | n: a number | Returns the hyperbolic sine of n | |
| sqrt(n) | Calculates the square root | n: a number | Returns the square root of n | |
| stdev([n]) | Calculates the standard deviation | [n]: one or more parameters | Returns the standard deviation of the parameters in [n] | |
| stdevp([n]) | Calculates the standard deviation | [n]: one or more parameters | Returns the standard deviation of the parameters in [n] | |
| sub([n]) | Subtraction | [n]: one or more parameters | Returns the subtraction of all parameters in [n] (except the first) from the first parameter in [n] | |
| sum([n]) | Sum | [n]: one or more parameters | Returns the sum of all parameters in [n] | |
| tan(n) | Calculates the tangent | n: a number | Returns the tangent of n | |
| tanh(n) | Calculates the hyperbolic tangent | n: a number | Returns the hyperbolic tangent of n | |
Query Functions |
||||
| Function | Description | Parameters | Returns | Examples |
| by([p]) | Used in conjunction with group() or order(). Specifies the properties based on which the grouping / ordering takes place | [p]: one or more parameters that determine grouping / ordering | n/a; to be used only in conjunction with order(), orderdesc(), orderasc() or group() | |
| choose(i, [v]) | Extracts value at index from a list of parameters | i: index; v: list of parameters (not a collection!) | Value in [v] at position i | choose (3, 1, 2, 3, 4) returns 3 |
| compute(e) | Returns value of expression | e: expression in Analytica language | Returns the value of the expression | |
| count(p) | Count | p: path to property | Returns the number of occurrences in p | |
| countdistinct(p) | Count | p: path to scalar property | Returns the number of distinct occurrences in p | |
| exists(v,p) | Checks existence of value in parameter | v: value to check for; p path to property being checked | Returns true if p contains v | exists (6,7) returns false |
| firstvalue(p, t) | Retrieves first value | p: name of a property; t:collection or document from which value for property name is extracted (first document in case of collection) | Returns value for property with name p | firstvalue (“First Name”, GD.People) returns “Octavius” |
| group(p) | Groups documents by specific values | p: path to a collection; its documents will be grouped according to by() | Returns collection with the documents grouped by the properties in by() | group (GD.People.by (First Name)) returns a collection with documents, each of which represents a group |
| ifnull(p, v) | Checks if property is null and returns defined value in this case | p: path to property to check for null; v: value returned if p is null | Returns v if p is null | ifnull (null, “a”) return “a” |
| index(i,c) | Returns the element of a collection at a specific index | i: index into collections (index starting at 1)c: collection from which the document at index is returned | Returns the value at index i in collection c | |
| order(p) | Orders a collection by specific values | p:path to collection; the documents will be ordered according to by() | Returns the ordered collection | order(GD.People.by (First Name)) returns a collection ordered by First Name |
| orderasc(p) | Orders a collection in ascending order | p:path to collection; the documents will be ordered according to by() | Returns the ordered collection | orderasc (GD.People.by (First Name)) returns a collection ordered by First Name |
| orderdesc(p) | Orders a collection in descending order | p:path to collection; the documents will be ordered according to by() | Returns the ordered collection | orderdesc (GD.People.by (First Name)) returns a collection ordered by First Name |
| project(p, c) | Projects a property from each document of a collection | p: name of a property that is to be projected; c: collection | Returns a collection where each document contains the projected property | project (“First Name”, GD.People) returns a collection of documents containing the First Name of each document |
| propertyvalue(p, d) | Extracts a property value for a given property name from a document | p: property named: document | Returns the value of propert p from d | propertyvalue (“First Name”, index (1, GD.People)) returns “Octavius” |
| row() | Calculates the index of a document within a collection | none | Returns the index for the position of a document in a collections; the row() for the first document is 1 | |
| select(p) | Selection of document with predicates | p: path to collection; the documents will be selected based on the criteria in where() | Returns collection with documents from p that satisfy predicate in where() | select (GD.People.where (First Name = “Octavius”)) |
| values([p]) | Helper function to construct a collection | [p]: one or more parameter | one or more paths to property or constant values that are returned as one collection; if there is only one parameter, a document is returned | |
| xlgroup() | Same as group(); to be used in Excel to avoid naming conflict | |||
| xlif(e1, e2, e3) | If-then-else | e1: boolean expression, e2: expression evaluated when e1 results in true, e3: expression evaluated when e1 results in false | returns the evaluation of e2 when e1 is true, otherwise the evaluation of e3 | xlif(false, “a”, “b”) returns “b” |
| xlselect() | Same as select(); to be used in Excel to avoid naming conflict | |||
| xlwhere() | Same as where(); to be used in Excel to avoid naming conflict | |||
String Functions |
||||
| Function | Description | Parameters | Returns | Examples |
| concatblank([s]) | Concatenates strings using blank space as a separators | [s]: one or more strings | Returns a string whose value is the concatentation of the strings specified by [s] separated by a blank space | concatblank (“a”, “b”) returns “a b” |
| concatcomma([s]) | Concatenates strings using commas as a separator | [s]: one or more strings | Returns a string whose value is the concatentation of the strings specified by [s] separated by commas | concatcomma (“a”, “b”) returns “a,b” |
| concatenate([s]) | Concatenates strings with no separator | [s]: one or more strings | Returns a string whose value is the concatentation of the strings specified by [s] | concatenate (“a “, “b”) returns “ab” |
| contains(s1,s2) | Checks whether a string is contained in another string | s1: container string to be searched; s2: string to be searched for in the container string | True if s2 is contained in s1. False otherwise | contains (“ab”, “a”) returns true |
| left(s, n) | Returns a number of characters from the beginning of a string | s: string; n: number | Returns the first n characters from s starting from the first character | left (“abc”, 2) returns “ab” |
| len(s) | Calculates the length of a string | s: string | Returns the length (number of characters) in s | len (“abc”) returns 3 |
| lower(s) | Converts all characters in string to lower case | s: string | Returns s with all characters converted to their lower case equivalent | lower (“AbC”) returns “abc” |
| md5(s) | Returns the MD5 hash of a string | s: string | The MD5 hash of s | md5 (“AbC”) returns 25aa3ee1c93cad3f274567281066dc18 |
| mid(s, p, l) | Returns a substring starting at a specific position | s: string, p: position, l: length | Creates and returns a string of length l starting at position p in string s | mid (“AbC”, 1, 1) returns “b” |
| right(s, n) | Returns a number of characters from the end of the string towards its beginning | s: string, n: number | Returns the last n characters from s | right (“AbC”, 2) returns “bC” |
| trim(s) | Trims leading and trailing whitespace from a string | s: string | The string with leading or trailing whitespace removed | trim (” A b C “) returns “A b C” |
| upper(s) | Converts all characters in string to upper case | s: string | Returns s with all charecters converted to their upper case equivalent | upper (“A b C”) returns “A B C” |
| upperfirst(s) | First letter will be upper case | s: string | Returns s with its first character converted to upper case | upperfirst (“a b C”) returns “A b C” |
System Functions |
||||
| Function | Description | Parameters | Returns | Examples |
| getDatabases() | Returns collection of connected databases | none | Returns a collection of documents that contain the name of a connected database each | getDatabases() returns [{"databaseName": "GD"}, {"databaseName": "twitter"}] when connected to “GD” and “twitter” |
URI Functions |
||||
| Function | Description | Parameters | Returns | Examples |
| urigethostname(uri) | Returns the hostname from a URI | uri | Returns the host name of the uri | urigethostname (“http://www.analytica.com:8080″) returns www.analytica.com |
| urigetmarker(uri) | Returns the marker from a URI | uri | Returns the marker of a uri | urigetmarker (“http://www.analytica.com:8080/#howToGetStarted”) returns “howToGetStarted” |
| urigetportname(uri) | Returns the port number from a URI | uri | Returns the port number of the uri | urigetportname (“http://www.analytica.com:8080?a=b&c=d”) returns 8080 |
| urigetqueryparameter(uri) | Returns the query parameter of a URI | uri | Returns the query parameter of the uri | urigetqueryparameter (“http://www.analytica.com:8080?a=b&c=d”) returns a=b&c=d |
| urigetqueryparametername(uri, n) | Returns the name of a query parameter | uri: urin: name | Returns the name n in uri, otherwise the empty string | urigetqueryparametername (“http://www.analytica.com:8080?a=b&c=d”, “a”) returns “a” |
| urigetqueryparametervalue(uri) | Returns the value of a query parameter | uri: urin: name | Returns the value of parameter n | urigetqueryparametervalue (“http://www.analytica.com:8080?a=b&c=d”, “a”) returns “b” |
| urigetresourcename(uri) | Returns the resource name of a uri | uri: uri | Returns the resource name (last segment) of a uri | urigetresourcename (“http://www.analytica.com:8080/test/today/initial/?a=b&c=d”) returns “initial/” |
| urigetsegment(uri, n) | Returns a segment of a URI | uri: urin: segment number | Returns segment n from the uri | urigetsegment (“http://www.analytica.com:8080/test/?a=b&c=d”, 1) returns “test/” |
Value Functions |
||||
| Function | Description | Parameters | Returns | Examples |
| constant(v) | Returns the value as constant | v: value | Returns v as constant: a string or number constant, or null, true, false, NaN, undefined, or #ERROR | constant (true) returns true |
System Functions (cannot be used in XL expressions) |
||||
| Function | Description | Parameters | Returns | Examples |
| describe(p) | Shows the types for a property | p:path to property | Returns all types for the property | describe (GD.People.First Name) returns [{"GD.People.First Name": "NString","kind": "NScalarS"}] indicating that First Name is a scalar type: a string |
| executexl(e) | Executes an analytica language expression | e: an expression | Returns the value of the expression | executexl (sum(1,2)) returns 3 |
| fastaccess() | Function that can only be used by a REST client | <database>/<collection>/<index or predicate> | Returns the document that complies to a predicate or an index | <uri>/fastaccess/GD/People/First Name = “Octavius”; or: <uri>/fastaccess/GD/People/1 |
| forget(cp) | Deletes a calculated property | cp: calculated property path | Deletes the calculated property cp | |
| forgetvc(vc) | Deletes a virtual collection | vc: name of a virtual collection | Deletes the virtual collection vc | |
| get(p) | Gets a property or collection | p: path to property or collection | Returns the property p of every document in a collection | |
| help() | Returns list of all functions | none | Returns the list of all functions | |
| list() | Returns the list of all calculated properties | none | Returns the list of all calculated property definitions | |
| listfunctions() | Returns a list of functions (this list) | none | Returns the list of all functions | |
| listvc() | Returns a list of virtual collections | none | Returns the list of all virtual collection definitions | |
| runreport(r) | Executes a report | r: report name | Executes a report | |
| saveas(sdb, sc, tdb, tc) | Saves a collection to a database | sdb: source database, sc: source collection, tdb: target database, tc: target collection | Saves collection sc of sdb to collection tc in tdb | |
| set(cp, e) | Creates a calculated property | cp: calculated property; e: XL expression | Creates new calculated property at path cp and sets it to value of expression e | |
| setvc(vcn, db, c, q) | Creates a virtual collection | vcn: virtual collection name, db: database, c: collection, q:query | Creates a new virtual collection vcn in database db with documents from c that satisfy the native database query q | |
| update(cp, e) | Updates a calculated property | cp: calculated property; e: XL expression | Updates the existing calculated property at path cp and sets it to value of expression e | |
| updatevc(vcn, db, c, q) | Updates a a virtual collection | vcn: virtual collection name, db: database, c: collection, q:query | Updates the existing virtual collection vcn in database db with documents from c that satisfy the native database query q |