Function | Usage | Description |
| ABS |
ABS(number) |
Returns the absolute value of a number, a number without its sign. |
| ACOS |
ACOS(number) |
Returns the arccosine of a number, in radians in the range 0 to Pi. The arccosine is the angle whose cosine is Number. |
| ACOSH |
ACOSH(number) |
Returns the inverse hyberbolic cosine of a number. |
| AND |
AND(logical1, logical2, ...) |
Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE. |
| ASIN |
ASIN(number) |
Returns the arcsine of a number in radians, in the range -Pi/2 to Pi/2. |
| ASINH |
ASINH(number) |
Returns the inverse hyperbolic sine of a number. |
| ATAN |
ATAN(number) |
Returns the arctangent of a number in radians, in the range -Pi/2 to Pi/2. |
| ATANH |
ATANH(number) |
Returns the inverse hyperbolic tangent of a number. |
| AVG |
AVG(number1, number2, ...) |
Returns the average (arithmetic mean) of its arguments. |
| CHOOSE |
CHOOSE(index_num, value1, value2, ...) |
Chooses a value from a list of values, based on an index number (beginning at 1). |
| COMBIN |
COMBIN(number, number_chosen) |
Returns the number of combinations for a given number of items. |
CONVERT_OSGB_TO_LAT
(Omniscope 2.5+) |
CONVERT_OSGB_TO_LAT(osgbX, osgbY) |
Converts coordinates from British National Grid (OSGB 36) to the latitude value in WGS 84 (GPS lat/lon). |
CONVERT_OSGB_TO_LON
(Omniscope 2.5+) |
CONVERT_OSGB_TO_LON(osgbX, osgbY) |
Converts coordinates from British National Grid (OSGB 36) to the longitude value in WGS 84 (GPS lat/lon). |
| COS |
COS(number) |
Returns the cosine of an angle. |
| COSH |
COSH(number) |
Returns the hyperbolic cosine of a number. |
| CURRENTROW |
CURRENTROW() |
Evaluates to the row number of the record being evaluated. Numbering starts at 1. |
DATEADD
(Omniscope 2.5+) |
DATEADD(date, number, unit) |
Adds a specified number of a specified unit to a date.
To subtract, use a negative number.
Unit is optional; if not specified, "day" is assumed, otherwise it should be specified as:
"year", "month", "week", "day", "hour", "minute", "second", "millisecond". |
| DATEDIFF |
DATEDIFF(date1, date2, unit, approximate) |
Establishes the difference between two dates, as a decimal number of a specified unit (optional, default: "day").
If date2 is before date1, the result will be negative.
E.g. the decimal number of weeks between two dates.
Units should be specified as:
"year", "month", "week", "day", "hour", "minute", "second", "millisecond".
You can optionally choose approximate date difference for faster value calculation. Approximate date difference divides elapsed time between the two dates by the typical unit length (e.g. 30 days for days-in-month). |
| DATETOTEXT |
DATETOTEXT(date_value, custom_format) |
Converts a Date into text, optionally using a custom date format.
For more information please click here. |
DATEUNIT
(Omniscope 2.5+) |
DATEUNIT(date, unit) |
Retrieves a specified unit from a date as a numeric value.
For example, the date "12 Feb 2006" has 12 as the "day" unit.
Unit is optional; if not specified, "day" is assumed, otherwise it should be specified as:
"year", "month", "week", "day", "hour", "minute", "second", "millisecond".
NB. "days" is interpreted as "day of month", "weeks" as "week of year", and "hours" as "hour of day (24)". |
| DATEVALUE |
DATEVALUE(text) |
Converts a text string that represents a date to a date. |
| E |
E() |
Euler's number, e, also called the base of natural logarithms. |
| EXEC |
EXEC(command) |
Executes an operating system command, resulting in a text value containing the command's output |
| EXP |
EXP(number) |
Returns e raised to the power of a given number. |
| FACT |
FACT(number) |
Returns the factorial of a number, equal to 1*2*3*...*Number. |
| IF |
IF(logical_test, value_if_true, value_if_false) or IF(test1, value1, test2, value2, test3, value3..., else_value) |
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
If the longer form is used, returns value1 if test1 is true, otherwise returns value2 if test2 is true, otherwise returns value3 if test3 is true..., otherwise returns else_value.
For example:
IF(
[Coupon]<6, "less than 6",
[Coupon]<7, "6 to 7",
[Coupon]<8, "7 to 8",
"8+"
) |
| INTCEIL |
INTCEIL(number) |
Rounds a number up to the nearest integer. |
| INTFLOOR |
INTFLOOR(number) |
Rounds a number down to the nearest integer. |
| INTROUND |
INTROUND(number) |
Rounds a number to the nearest integer. |
ISO_COUNTRY_NAME
(Omniscope 2.5+) |
ISO_COUNTRY_NAME(code) |
Converts an ISO country code into readable form. |
ISO_LANGUAGE_NAME
(Omniscope 2.5+) |
ISO_LANGUAGE_NAME(code, show_country) |
Converts an ISO language code into readable form.
The function uses two parameters:
code. The ISO language code. This can be either a 2-letter code language code, 2-letter language/country code or a 3-letter code.
show_country. Optional parameter. If this is set to true and a 2-letter language/country code is provided the country will be shown in brackets after the language. The default value is true. |
| LEFT |
LEFT(text, num_chars) |
Returns the specified number of characters from the start of a text string. |
| LEN |
LEN(text) |
Returns the number of characters in a text string. |
| LG |
LG(number) |
Returns the base-2 logarithm of a number. |
| LN |
LN(number) |
Returns the natural logarithm of a number. |
| LOG |
LOG(number, base) |
Returns the logarithm of a number to the base you specify. |
| LOOKUPCSVCELL |
LOOKUPCSVCELL(csv_data, row, column, has_headers, locale) |
Parses and reads CSV data, which may or may not contain a header row. Returns the cell specified (NB. row/column numbers start at zero). This will not perform well for large numbers of calculations or large CSV data.
The fifth arguement is optional and you can use it to override the default locale. For an example if the default is italian and you are loading a CSV file with english decimals, add "en". |
| LOWER |
LOWER(text) |
Converts all letters in a text string to lowercase. |
| MAX |
MAX(number1, number2, ...) |
Returns the largest value in a set of values. |
| MID |
MID(text, start_num, num_chars) |
Returns the specified number of characters from the middle of a text string, given a starting position and length. |
| MIN |
MIN(number1, number2, ...) |
Returns the smallest number in a set of values. |
| MOD |
MOD(number,divisor) |
Returns the remainder after a number is divided by a divisor. |
| NOT |
NOT(logical) |
Changes FALSE to TRUE, or TRUE to FALSE. |
| NOW |
NOW |
Returns the current date/time. |
| NUM_OF_TOKENS |
NUM_OF_TOKENS(text, token_separator) |
Returns the number of tokens (split by a single character such as a comma) in a text value. |
| NUMVALUE |
NUMVALUE(text) |
Converts a text string that represents a number to a number. |
| OR |
OR(logical1, logical2, ...) |
Checks whether any arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE. |
| PI |
PI() |
Returns the value of Pi, 3.141592653589793, accurate to over 15 digits. |
| POWER |
POWER(number, power) |
Returns the result of a number raised to a power. |
| PROPER |
PROPER(text) |
Converts a text string to proper case; the first letter in each word in uppercase, and all other letters to lowercase. |
| RAND |
RAND() or RAND(seed) |
Returns a random number greater than or equal to 0 and less than 1, evenly distributed. The result changes on every recalculation, unless a seed value (any number) is specified, in which case the number generated is always the same for a given seed. |
RANK
(Use of dataSubset parameter requires Omniscope 2.5+) |
RANK(value, field, ascending, includeNulls, dataSubset) |
Returns the rank of a value within the field specified.
This is the value position within the ordered field values.
If ascending is not specified, or false, the rank is against a descending order (higher values give better rank, with the highest number giving rank 1).
If the value is NULL, or not present in the list, NULL is returned, unless includeNulls is specified as true, in which case empty cells in the list will be considered.
If dataSubset is specified, the RANK is calculated for a subset of the data which can be specified using the SUBSET function.
Valid parameter combinations without data subset:
RANK(field) (shorthand for RANK(field, field))
RANK(value, field)
RANK(value, field, ascending)
RANK(value, field, ascending, includeNulls)
Valid parameter combinations with data subset:
RANK(value, field, dataSubset)
RANK(value, field, ascending, dataSubset)
RANK(value, field, ascending, includeNulls, dataSubset) |
| READRES |
READRES(file_path_or_url) |
Reads the text contents from a file or URL. |
RECORDCOUNT
(Omniscope 2.5+) |
RECORDCOUNT(subset) |
Evaluates to the number of records (rows) in the all data (if subset is not specified), or a subset (if subset is specified). The subset should be a data subset as evaluated using the SUBSET function. |
| REPLACE |
REPLACE(old_text, start_num, num_chars, new_text) |
Replaces part of a text string with a different text string. |
| REPT |
REPT(text, number_times) |
Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string. |
| RIGHT |
RIGHT(text, num_chars) |
Returns the specified number of characters from the end of a text string. |
| SEARCH |
SEARCH(find_text, within_text, start_num) |
Returns the number of the character at which a specific character or text string is first found, reading from left to right (not case-sensitive). |
| SEARCHREGEX |
SEARCHREGEX(reg_expr, within_text, start_num) |
Returns the number of the character at which a regular expression is first found, reading from left to right (not case-sensitive). |
| SIGN |
SIGN(number) |
Returns the sign of a number: 1 if the number is positive, zero if the number is zero, or -1 if the number is negative. |
| SIN |
SIN(number) |
Returns the sine of an angle. |
| SINH |
SINH(number) |
Returns the hyperbolic sine of a number. |
| SQRT |
SQRT(number) |
Returns the square root of a number. |
SUBSET
(Omniscope 2.5+) |
SUBSET(field, field_value) |
Evaluates to a subset of the data. Normally used only as a parameter to another function.
Either:
(a) all records (if no parameters are specified); or
(b) a current subset of records (if field is specified); or
(c) another subset of records (if field and field_value are specified).
For more information, please visit SUBSET functions. |
SUBSET2
(Omniscope 2.5+) |
SUBSET2(field_1, field_2, field_1_value, field_2_value) |
Evaluates to a subset of the data. Normally used only as a parameter to another function.
Either:
(a) a current subset of records (if field_N is specified); or
(b) another subset of records (if field_N and field_N_value are specified).
For more information, please visit SUBSET functions. |
SUBSET3
(Omniscope 2.5+) |
SUBSET3(field_1, field_2, field_3, field_1_value, field_2_value, field_3_value) |
Evaluates to a subset of the data. Normally used only as a parameter to another function.
Either:
(a) a current subset of records (if field_N is specified); or
(b) another subset of records (if field_N and field_N_value are specified).
For more information, please visit SUBSET functions. |
SUBSET4
(Omniscope 2.5+) |
SUBSET4(field_1, field_2, field_3, field_4, field_1_value, field_2_value, field_3_value, field_4_value) |
Evaluates to a subset of the data. Normally used only as a parameter to another function.
Either:
(a) a current subset of records (if field_N is specified); or
(b) another subset of records (if field_N and field_N_value are specified).
For more information, please visit SUBSET functions. |
SUBSET5
(Omniscope 2.5+) |
SUBSET5(field_1, field_2, field_3, field_4, field_5, field_1_value, field_2_value, field_3_value, field_4_value, field_5_value) |
Evaluates to a subset of the data. Normally used only as a parameter to another function.
Either:
(a) a current subset of records (if field_N is specified); or
(b) another subset of records (if field_N and field_N_value are specified).
For more information, please visit SUBSET functions. |
SUBSET_EMPTYCOUNT
(Omniscope 2.5+) |
SUBSET_EMPTYCOUNT(stat_field, subset_dataset) |
Calculates the statistical function [Number of empty values] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset_dataset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit SUBSET functions. |
SUBSET_MAX
(Omniscope 2.5+) |
SUBSET_MAX(stat_field, subset_dataset) |
Calculates the statistical function [Maximum value (highest or last)] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset_dataset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit SUBSET functions. |
SUBSET_MEAN
(Omniscope 2.5+) |
SUBSET_MEAN(stat_field, subset_dataset) |
Calculates the statistical function [Mean (average) of values] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset_dataset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit SUBSET functions. |
SUBSET_MEDIAN
(Omniscope 2.5+) |
SUBSET_MEDIAN(stat_field, subset_dataset) |
Calculates the statistical function [Median (middle) of values] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset_dataset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit SUBSET functions. |
SUBSET_MIN
(Omniscope 2.5+) |
SUBSET_MIN(stat_field, subset_dataset) |
Calculates the statistical function [Minimum value (lowest or first)] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset_dataset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit SUBSET functions. |
SUBSET_MODE
(Omniscope 2.5+) |
SUBSET_MODE(stat_field, subset_dataset) |
Calculates the statistical function [Most common value (first if multimodal)] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset_dataset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit SUBSET functions. |
SUBSET_NONEMPTYCOUNT
(Omniscope 2.5+) |
SUBSET_NONEMPTYCOUNT(stat_field, subset_dataset) |
Calculates the statistical function [Number of non-empty values] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset_dataset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit SUBSET functions. |
SUBSET_RANGE
(Omniscope 2.5+) |
SUBSET_RANGE(stat_field, subset_dataset) |
Calculates the statistical function [Range of values (maximum minus minimum)] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset_dataset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit SUBSET functions. |
SUBSET_SINGLETON
(Omniscope 2.5+) |
SUBSET_SINGLETON(stat_field, subset_dataset) |
Calculates the statistical function [The single value, if there is one] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset_dataset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit SUBSET functions. |
SUBSET_STDDEV
(Omniscope 2.5+) |
SUBSET_STDDEV(stat_field, subset_dataset) |
Calculates the statistical function [Standard deviation of values from the mean] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset_dataset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit SUBSET functions. |
SUBSET_SUM
(Omniscope 2.5+) |
SUBSET_SUM(stat_field, subset_dataset) |
Calculates the statistical function [Sum (total) of values] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset_dataset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit SUBSET functions. |
SUBSET_UNIQUECOUNT
(Omniscope 2.5+) |
SUBSET_UNIQUECOUNT(stat_field, subset_dataset) |
Calculates the statistical function [Number of unique non-empty values] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset_dataset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit SUBSET functions. |
SUBSET_UNIQUESLIST
(Omniscope 2.5+) |
SUBSET_UNIQUESLIST(stat_field, subset_dataset) |
Calculates the statistical function [A comma-separated list of all unique values] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset_dataset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit SUBSET functions. |
| SUM |
SUM(number1, number2, ...) |
Adds all the arguments. |
| TAN |
TAN(number) |
Returns the tangent of an angle. |
| TANH |
TANH(number) |
Returns the hyperbolic tangent of a number. |
| TEXT_LINE_COUNT |
TEXT_LINE_COUNT(text) |
Returns the number of lines (split by line-break) in a text value. |
| TEXTTODATE |
TEXTTODATE(text_value, custom_format, lenient) |
Converts a Date into text, optionally using a custom date format (e.g. "dd/MM/yyyy HH:mm:ss", defaults to the current regional format), optionally specifying whether parsing is lenient (true/false, default true). |
| TEXTVALUE |
TEXTVALUE(text, pattern) |
Converts any value into the text equivalent, optionally using a format pattern.
The format pattern (which must be quoted text) defines how numbers or dates are formatted.
Usage examples:
TEXTVALUE(424) = "424"
TEXTVALUE(123456.789 , "$###,###.###") = "$123,456.789"
TEXTVALUE(DATEVALUE("02/06/2009") , "yyyy.MMMM.dd") = "2009.June.02"
For more information on date and time formats please click here.
For more information on number formats please click here.
|
TODAY
(Omniscope 2.5+) |
TODAY |
Returns the current date. |
| TRIM |
TRIM(text) |
Removes all spaces from a text string except for single spaces between words. |
| UPPER |
UPPER(text) |
Converts all letters in a text string to uppercase. |
| VALUE |
VALUE(text) |
Converts a text string that represents a number to a number, or date text into a date, where possible, otherwise resulting in a text value. |
