Omniscope provides a comprehensive library of functions that can be applied in various areas of the platform, including:

1. Data Preparation Workflow: Within the Field Organiser block.
2. Report Area: Specifically in the Data Model, Formula Measures, and Content View.

This documentation aims to assist users in searching and understanding the available functions. You can filter and search for specific functions and explore their usage areas on the online live Report https://omniscope.me/Demos/Formulas+and+variables/Omniscope+functions+picker.ior/ 

Below is a list of available functions in Omniscope: 

Omniscope Evo Formula Functions

Workflow: Field Organiser block

ABS

Maths/Stats

Returns the absolute value of a number, a number without its sign.

Syntax: ABS(number)

ACOS

Maths/Stats

Returns the arccosine of a number, in radians in the range 0 to Pi. The arccosine is the angle whose cosine is Number.

Syntax: ACOS(number)

ACOSH

Maths/Stats

Returns the inverse hyperbolic cosine of a number.

Syntax: ACOSH(number)

ALLRECORDCOUNT

Maths/Stats

Evaluates to the total number of records (rows) in all data, before any filtering or in-view aggregation has been applied. Not supported for dynamic evaluation in aggregated views.

Syntax: ALLRECORDCOUNT()

AND

Logical

Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE.

Syntax: AND(logical1, logical2, ...)

ASIN

Maths/Stats

Returns the arcsine of a number in radians, in the range -Pi/2 to Pi/2.

Syntax: ASIN(number)

ASINH

Maths/Stats

Returns the inverse hyperbolic sine of a number.

Syntax: ASINH(number)

ATAN

Maths/Stats

Returns the arctangent of a number in radians, in the range -Pi/2 to Pi/2.

Syntax: ATAN(number)

ATANH

Maths/Stats

Returns the inverse hyperbolic tangent of a number.

Syntax: ATANH(number)

AVG

Maths/Stats

Returns the average (arithmetic mean) of its arguments.

Syntax: AVG(value1, value1, ...)

BUCKET

Maths/Stats

Retrieves a range for a date or a number as a text value in the format "A to B".
For numbers, use BUCKET(value, width, format); for dates use BUCKET(value, width, unit, format). In either case, "format" is optional.
Date units can be one of: "year", "month", "week", "day", "hour", "minute", "second", "millisecond".

Numeric examples:
BUCKET(5, 10) = '0 to 10'
BUCKET(0.08, 0.1) = '0 to 0.1'
BUCKET(0.1, 0.1) = '0.1 to 0.2'
BUCKET(0, 0.1) = '0 to 0.1'
BUCKET(0.11, 0.1) = '0.1 to 0.2'
BUCKET(1002, 10) = '1000 to 1010'
BUCKET(10000, 11) = '9999 to 10010'

Date examples where field1 is 10 Feb 2007 12:43:
BUCKET([field1], 2, "hours") = '10 Feb 2007 12:00 to 10 Feb 2007 14:00'
BUCKET([field1], 2, "hours", "HH:mm") = '12:00 to 14:00'
BUCKET([field1], 2, "months", "dd MMM yyyy") = '01 Jan 2007 to 01 Mar 2007'
BUCKET([field1], 2, "years", "dd MMM yyyy") = '01 Jan 2006 to 01 Mar 2008'
BUCKET([field1], 12, "seconds", "HH:mm ss") = '12:43 00 to 12:43 12'

Syntax: BUCKET(value, width, number_format_or_date_unit, date_format)

CATCH

Data

Suppresses all evaluation errors in the sub-formula. If an error occurs, results in null.

Syntax: CATCH(sub_formula)

CELL

Data

Retrieves a cell value for a field using an absolute row number. The field must be a field name; see also FIELDNAME and FIELDCOUNT functions.

Syntax: CELL(field, record)

CHOOSE

Data

Chooses a value from a list of values, based on an index number (beginning at 1).

Syntax: CHOOSE(index_num, value1, value2, ...)

COMBIN

Maths/Stats

Returns the number of combinations for a given number of items.

Syntax: COMBIN(number, number_chosen)

CONTAINS

Text

Returns true if [sub_text] occurs anywhere within [text] (case insensitive).

Syntax: CONTAINS(text, sub_text)

CONVERT_LATLON_TO_OSGBX

GIS

Converts coordinates from the latitude value in WGS 84 (GPS lat/lon) to British National Grid (OSGB 36) X.

Syntax: CONVERT_LATLON_TO_OSGBX(lat, lon)

CONVERT_LATLON_TO_OSGBY

GIS

Converts coordinates from the latitude value in WGS 84 (GPS lat/lon) to British National Grid (OSGB 36) Y.

Syntax: CONVERT_LATLON_TO_OSGBY(lat, lon)

CONVERT_OSGB_TO_LAT

GIS

Converts coordinates from British National Grid (OSGB 36) to the latitude value in WGS 84 (GPS lat/lon).

Syntax: CONVERT_OSGB_TO_LAT(osgbX, osgbY)

CONVERT_OSGB_TO_LON

GIS

Converts coordinates from British National Grid (OSGB 36) to the longitude value in WGS 84 (GPS lat/lon).

Syntax: CONVERT_OSGB_TO_LON(osgbX, osgbY)

COS

Maths/Stats

Returns the cosine of an angle.

Syntax: COS(number)

COSH

Maths/Stats

Returns the hyperbolic cosine of a number.

Syntax: COSH(number)

CURRENTROW

Data

Evaluates to the row number of the record being evaluated. Numbering starts at 1.

Syntax: CURRENTROW()

DATE

Date

Creates a date using numerical arguments.
If omitted or null, the first value for each arguments is used.
So, if year is null, year 1 is used.
If month is null or omitted, January is used.
Etc.
BC dates are not supported.
Illegal dates such as day 29 Feb on a non-leap year will be rolled over.
Time zone is a text value in the format "GMT-8:00" or "PST"; if omitted, the local time zone is used.

Syntax: DATE(year, month, day, hour, minute, second, millisecond, timezone)

DATEADD

Date

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".

Syntax: DATEADD(date, number, unit)

DATEDIFF

Date

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 by specifying "true" for "approximate". Approximate date difference divides elapsed time between the two dates by the typical unit length (e.g. 30 days for days-in-month).

Syntax: DATEDIFF(date1, date2, unit, approximate)

DATETOTEXT

Date

Converts a Date into text, optionally using a custom date format and time zone.

Syntax: DATETOTEXT(date_value, custom_format, time_zone)

DATEUNIT

Date

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)".

Syntax: DATEUNIT(date, unit)

DATEVALUE

Date

Converts a text string that represents a date to a date. For more options, see TEXTTODATE.

Syntax: DATEVALUE(text)

DECLARE

Data

Declares one or more named values, each with a value expression, for repeated use in a sub-formula.
The names must not clash with existing field or variable names, and must not be quoted.
For more information, see http://archives.visokio.com/kb/kb.visokio.com/functions-guide/declare.html.

Syntax: DECLARE(name1, expression1, name2, expression2, name3, expression3..., sub_formula)

DEPENDENCIES

Data

Provides a hint to Omniscope that the nested formula has references to other fields. Use this when you are building up field references in ways that Omniscope cannot automatically determine, such as via the SCRIPT function. Omniscope needs to know about all field references in a formula to ensure correct evaluation.
Example:
DEPENDENCIES(SCRIPT(... dataArray("MyField") ... refVal("MyOtherField") ...), [MyField], [MyOtherField])
This tells Omniscope that the script has references to MyField and MyOtherField.

Syntax: DEPENDENCIES(nested_formula, ref1, ref2, ref3...)

E

Maths/Stats

Euler's number, e, also called the base of natural logarithms.

Syntax: E()

ENDSWITH

Text

Returns true if [sub_text] occurs in the end of [text] (case insensitive).

Syntax: ENDSWITH(text, sub_text)

EQUIV

Logical

Returns true if [value1] is equivalent to [value2] (automatically converting between text, numbers and dates to attempt to find a match).

Syntax: EQUIV(value1, value2)

EXP

Maths/Stats

Returns e raised to the power of a given number.

Syntax: EXP(number)

FACT

Maths/Stats

Returns the factorial of a number, equal to 123*...*Number.

Syntax: FACT(number)

FIELDCOUNT

Data

Returns the number of fields present.

Syntax: FIELDCOUNT()

FIELDFORMAT

Data

Converts any value into the text equivalent, using a field format.
Warning: boolean values (true/false) will be translated using the current Language setting.
Usage examples:
FIELDFORMAT(424, [Field 1]) = "424.00"

Syntax: FIELDFORMAT(value, field)

FIELDNAME

Data

Looks up a field name by number, e.g. the 5th field.

Syntax: FIELDNAME(field_number)

FIELDNUMBER

Data

Looks up a field number by name (reverse of FIELDNAME).

Syntax: FIELDNUMBER(field_name)

FINDBETWEEN

Text

Returns the first shortest matching text surrounded by [before] and [after], or null if not found.
For example, FINDBETWEEN("apple apple orange plum pear apple banana pear", "apple", "pear") would return " orange plum "

Syntax: FINDBETWEEN(all, before, after)

FINDLASTBETWEEN

Text

Returns the last shortest matching text surrounded by [before] and [after], or null if not found.
For example, FINDLASTBETWEEN("apple apple orange plum pear apple banana pear", "apple", "pear") would return " banana "

Syntax: FINDLASTBETWEEN(all, before, after)

FORMATDURATION

Date

Formats a number as a duration in the format 0:00:00, rounded to the nearest second.
The number given is in seconds by default, unless a unit argument is specified. Supported time unit values: "days", "hours", "minutes", "seconds", "milliseconds".
Usage examples:
FORMATDURATION(3723) = "1:02:03"
FORMATDURATION(4.5, "hours") = "4:30:00"
FORMATDURATION(1.5, "days") = "36:00:00"

Syntax: FORMATDURATION(number, unit)

HTTPREDIRECT

Web

Retrieves the HTTP redirect target for a URL, if the server provides one.

Syntax: HTTPREDIRECT(url)

IF

Logical

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+"
)

Syntax: IF(logical_test, value_if_true, value_if_false) or IF(test1, value1, test2, value2, test3, value3..., else_value)

INTCEIL

Maths/Stats

Rounds a number up to the nearest integer.

Syntax: INTCEIL(number)

INTERSECTION

Data

Returns the data subset which is the intersection of records for all data subset arguments. If a record is present in ALL supplied subsets, it will be present in the result.
WARNING: this is an experimental function that has not been optimised for performance. Use alternative solutions such as extra formulas and the SUBSET function if performance is unacceptable.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: INTERSECTION(subset1, subset2, ...)

INTFLOOR

Maths/Stats

Rounds a number down to the nearest integer.

Syntax: INTFLOOR(number)

INTROUND

Maths/Stats

Rounds a number to the nearest integer.

Syntax: INTROUND(number)

INVERSE

Logical

Inverts the set of included records of a data subset. All records included in the subset supplied will be excluded in the result, and vice versa.
WARNING: this is an experimental function that has not been optimised for performance. Use alternative solutions such as extra formulas and the SUBSET function if performance is unacceptable.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: INVERSE(subset)

ISO_COUNTRY_NAME

GIS

Converts an ISO country code into readable form.
The function uses two arguments:

code. The ISO language code. This can be either a 2-letter code language code or a 3-letter code.

display_language. Optional argument. The language of the function text result. This can be either a 2-letter code language code or a 3-letter code.

Syntax: ISO_COUNTRY_NAME(code, display_language)

ISO_LANGUAGE_NAME

GIS

Converts an ISO language code into readable form.
The function uses three arguments:

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 argument. 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.

display_language. Optional argument. The language of the function text result. This can be either a 2-letter code language code or a 3-letter code.

Syntax: ISO_LANGUAGE_NAME(code, show_country, display_language)

LASTDAYOFMONTH

Date

Returns last day of month for a given date. Date value should include month and year value.

Syntax: LASTDAYOFMONTH(date_value)

LAT_LON_DISTANCE

GIS

Returns the surface distance (in KM) from one latitude/longitude point to another.

Syntax: LAT_LON_DISTANCE(lat1, lon1, lat2, lon2)

LEFT

Text

Returns the specified number of characters from the start of a text string.

Syntax: LEFT(text, num_chars)

LEN

Text

Returns the number of characters in a text string.

Syntax: LEN(text)

LEVENSHTEIN

Text

Evaluates the Levenshtein distance between two text values (case insensitive).

Syntax: LEVENSHTEIN(value1, value2)

LG

Maths/Stats

Returns the base-2 logarithm of a number.

Syntax: LG(number)

LN

Maths/Stats

Returns the natural logarithm of a number.

Syntax: LN(number)

LOG

Maths/Stats

Returns the logarithm of a number to the base you specify.

Syntax: LOG(number, base)

LOG10

Maths/Stats

Returns the base-10 logarithm of a number.

Syntax: LOG10(number)

LOOKUPCSVCELL

Data

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.<br>The fifth argument 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".

Syntax: LOOKUPCSVCELL(csv_data, row, column, has_headers, locale)

LOOKUPJSONVALUE

Data

Reads JSON data, extracting a single value by its JSONPath location. See http://goessner.net/articles/JsonPath/ or test your paths at http://jsonpath.herokuapp.com

Syntax: LOOKUPJSONVALUE(json_data, json_path)

LOWER

Text

Converts all letters in a text string to lowercase.

Syntax: LOWER(text)

MAX

Maths/Stats

Returns the largest value in a set of values.

Syntax: MAX(value1, value1, ...)

MID

Text

Returns the specified number of characters from the middle of a text string, given a starting position and length.

Syntax: MID(text, start_num, num_chars)

MIN

Maths/Stats

Returns the smallest number in a set of values.

Syntax: MIN(value1, value1, ...)

MOD

Maths/Stats

Returns the remainder after a number is divided by a divisor.

Syntax: MOD(number, divisor)

NETWORKDAYS

Date

Returns the net number of working days (excluding weekends) between two dates, inclusive.

Syntax: NETWORKDAYS(start_date, end_date)

NORMDIST

Maths/Stats

Gives the probability that a number falls at or below a given value of a normal distribution.
X - is the value for which you want the distribution,
mean (by default 0) - is the arithmetic mean of the distribution,
sdev (by default 1) - is the standard deviation of the distribution, the value should be positive (>0)
cumulative (by default TRUE) - is a logical value that determines the form of the function. If cumulative is TRUE, NORMDIST returns the cumulative distribution function; if FALSE, it returns the probability mass function.
See also NORMSDIST: NORMDIST(x,mu,sigma,TRUE) = NORMSDIST((x - mu)/sigma).

Syntax: NORMDIST(x, mean, sdev, cumulative)

NORMINV

Maths/Stats

It is the inverse of the NORMDIST function. It calculates the x variable given a probability.
probability - is a probability corresponding to the normal distribution, the value should be in the range (0, 1), >0 and <1,
mean (by default 0) - is the arithmetic mean of the distribution,
sdev (by default 1) - is the standard deviation of the distribution, the value should be positive (>0).

Syntax: NORMINV(probability, mean, sdev)

NORMSDIST

Maths/Stats

Gives the probability that a number falls at or below a given value of a standard normal distribution.
X - is the value for which you want the distribution,
cumulative (by default TRUE) - is a logical value that determines the form of the function. If cumulative is TRUE, NORMSDIST returns the cumulative distribution function; if FALSE, it returns the probability mass function.
See also NORMDIST: NORMDIST(x,mu,sigma,TRUE) = NORMSDIST((x - mu)/sigma).

Syntax: NORMSDIST(x, cumulative)

NORMSINV

Maths/Stats

It is the inverse of the NORMSDIST function. It calculates the x variable given a probability.
probability - is a probability corresponding to the normal distribution, the value should be in the range (0, 1), >0 and <1.

Syntax: NORMSINV(probability)

NOT

Logical

Changes FALSE to TRUE, or TRUE to FALSE.

Syntax: NOT(logical)

NOW

Date

Returns the current date/time.

Syntax: NOW()

NUMVALUE

Text

Converts a text string that represents a number to a number. Accepts a locale as an optional second argument in the format 'en_GB'.

Syntax: NUMVALUE(text, locale)

NUM_OF_TOKENS

Text

Returns the number of tokens (split by a single character such as a comma) in a text value.

Syntax: NUM_OF_TOKENS(text, token_separator)

OR

Logical

Checks whether any arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE.

Syntax: OR(logical1, logical2, ...)

PI

Maths/Stats

Returns the value of Pi, 3.141592653589793, accurate to over 15 digits.

Syntax: PI()

POWER

Maths/Stats

Returns the result of a number raised to a power.

Syntax: POWER(number, power)

PROPER

Text

Converts a text string to proper case; the first letter in each word in uppercase, and all other letters to lowercase.

Syntax: PROPER(text)

RAND

Maths/Stats

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.

Syntax: RAND() or RAND(seed)

RANK

Maths/Stats

Returns the rank of a value within the field specified.

This is the value position within the array of ordered field values.
If isAscending parameter is not specified, FALSE or takes string "descending", the rank is against a descending order (higher values give better rank, with the highest number giving rank 1).
If isAscending parameter is TRUE or takes string "ascending", the rank is against an ascending order.
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.

Examples:
RANK(10.1, [field1])
RANK(10.1, [field1], 1)
RANK(10.1, [field1], true)
RANK(10.1, [field1], "ascending")

Valid argument combinations without data subset:
RANK(field) (shorthand for RANK(field, field))
RANK(value, field)
RANK(value, field, isAscending)
RANK(value, field, isAscending, includeNulls)

Valid argument combinations with data subset:
RANK(value, field, dataSubset)
RANK(value, field, isAscending, dataSubset)
RANK(value, field, isAscending, includeNulls, dataSubset)

For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: RANK(value, field, isAscending, includeNulls, dataSubset)

READRES

Web

Reads the text contents from a file or URL. If the maximum cache age in seconds is not specified, it will be assumed to be 1 minute. Downloaded data will be cached for this period. Use -1 to disable caching.

Syntax: READRES(file_path_or_url, max_cache_age_seconds)

RECORDCOUNT

Data

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.

Syntax: RECORDCOUNT(subset)

REFVAL

Data

Looks up the value of a field or variable by its name. The name can be dynamically determined, such as by concatenating text and/or using variables.

Syntax: REFVAL(field_name)

REPLACE

Text

Replaces part of a text string with a different text string.

Syntax: REPLACE(old_text, start_num, num_chars, new_text)

REPLACEREGEX

Data

Replaces text matching a regular expression. In the replace text, use "$1" to refer to group 1 in the regular expression, and "$$" to mean a single dollar character. If use_empty_if_no_match (optional, default false) is true, and the expression does not match, results in null. For more information on regular expressions, see www.regular-expressions.info

Syntax: REPLACEREGEX(text, regular_expression, replace_text, use_empty_if_no_match)

REPT

Text

Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.

Syntax: REPT(text, number_times)

RIGHT

Text

Returns the specified number of characters from the end of a text string.

Syntax: RIGHT(text, num_chars)

ROUND

Maths/Stats

Rounds a number to a specified number of digits.
For example:
ROUND(123.7825, 2) = 123.78
ROUND(123.456, 1) = 123.5
ROUND(123.456, -2) = 100);

Syntax: ROUND(number, num_digits)

ROWINDEXSUBSET

Data

Results in a subset matching the row index(es) specified. Row indexes start from 1.
Custom operator should be one of: "=", "<>", ">", ">=", "<", "<="
If not specified, "=" (equals) is used.

Syntax: ROWINDEXSUBSET(rowIndex, operator)

RUNNINGTOTAL

Maths/Stats

Evaluates to the running total in field for 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.
If sortOrder is true, values will sum in ascending order. If false, in descending order. If null or missing, in record order.

Syntax: RUNNINGTOTAL(field, subset, sortOrder)

SAFEDIV

Maths/Stats

Divides a number by another number, safely handling the zero divisor case.

The 3rd argument if_zero is optional; if not specified, null is assumed.

Examples:

• SAFEDIV(2, 3) is equivalent to 2/3
• SAFEDIV(2, null) results in null (as does 2/null)
• SAFEDIV(2, 0) results in null
• SAFEDIV(2, 0, -1) results in -1

Syntax: SAFEDIV(numerator, divisor, if_zero)

SCRIPT

Script

Executes a javascript expression. Requires Java 6, included with the full Omniscope installer. Supports core Javascript 1.5, but not browser-related objects.
The script will be terminated after 10 seconds unless "timeout" is specified (in milliseconds).
Note: you can use back-quotes () instead of normal quotes to quote your script, such as:1 + 2. Unlike with normal quotes, back-quoted text can contain any other character including backslash. For literal back-quotes, use two, e.g. SCRIPT(var x = "Her name was Jane";`)
You must ensure you declare any dependencies (referenced fields inside your script) using the DEPENDENCIES function.

Syntax: SCRIPT(script, var1Name, var1Value, var2Name, var2Value, var3Name, var3Value..., timeout)

SEARCH

Text

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). The first character is numbered 1. Returns null if not found.

Syntax: SEARCH(find_text, within_text, start_num)

SEARCHREGEX

Text

Returns the number of the character at which a regular expression is first found, reading from left to right (not case-sensitive). The first character is numbered 1. Returns null if not found. For more information on regular expressions, see www.regular-expressions.info

Syntax: SEARCHREGEX(reg_expr, within_text, start_num)

SIGN

Maths/Stats

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.

Syntax: SIGN(number)

SIN

Maths/Stats

Returns the sine of an angle.

Syntax: SIN(number)

SINH

Maths/Stats

Returns the hyperbolic sine of a number.

Syntax: SINH(number)

SOUNDEX

Text

Converts a text value containing a single word into its Soundex phonetic code.

Syntax: SOUNDEX(word)

SQRT

Maths/Stats

Returns the square root of a number.

Syntax: SQRT(number)

STARTSWITH

Text

Returns true if [sub_text] occurs in the beginning of [text] (case insensitive).

Syntax: STARTSWITH(text, sub_text)

SUBSET

Data

Evaluates to a subset of the data. Normally used only as an argument to another function.
Either:
(a) all records (if no arguments are specified);
(b) a current subset of records (if field is specified);
(c) a subset of records (if field and field_value are specified);
(d) a subset of records, using a custom operator such as = (if all arguments are specified).
Custom operator should be one of: "=", "<>", ">", ">=", "<", "<=", "contains" (2.8+), "does-not-contain" (2.8+), "starts-with" (2.8+), "ends-with" (2.8+)
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET(field, field_value, field_operator)

SUBSET2

Data

Evaluates to a subset of the data. Normally used only as an argument to another function.
Either:
(a) a current subset of records (if field_N is specified);
(b) a subset of records (if field_N and field_N_value are specified);
(c) a subset of records, using a custom operator such as = (if all arguments are specified).
Custom operator should be one of: "=", "<>", ">", ">=", "<", "<=", "contains" (2.8+), "does-not-contain" (2.8+), "starts-with" (2.8+), "ends-with" (2.8+)
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.
Deprecated - please use the SUBSET and INTERSECTION functions instead.

Syntax: SUBSET2(field_1, field_2, field_1_value, field_2_value, field_1_operator, field_2_operator)

SUBSET3

Data

Evaluates to a subset of the data. Normally used only as an argument to another function.
Either:
(a) a current subset of records (if field_N is specified);
(b) a subset of records (if field_N and field_N_value are specified);
(c) a subset of records, using a custom operator such as = (if all arguments are specified).
Custom operator should be one of: "=", "<>", ">", ">=", "<", "<=", "contains" (2.8+), "does-not-contain" (2.8+), "starts-with" (2.8+), "ends-with" (2.8+)
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.
Deprecated - please use the SUBSET and INTERSECTION functions instead.

Syntax: SUBSET3(field_1, field_2, field_3, field_1_value, field_2_value, field_3_value, field_1_operator, field_2_operator, field_3_operator)

SUBSET4

Data

Evaluates to a subset of the data. Normally used only as an argument to another function.
Either:
(a) a current subset of records (if field_N is specified);
(b) a subset of records (if field_N and field_N_value are specified);
(c) a subset of records, using a custom operator such as = (if all arguments are specified).
Custom operator should be one of: "=", "<>", ">", ">=", "<", "<=", "contains" (2.8+), "does-not-contain" (2.8+), "starts-with" (2.8+), "ends-with" (2.8+)
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.
Deprecated - please use the SUBSET and INTERSECTION functions instead.

Syntax: SUBSET4(field_1, field_2, field_3, field_4, field_1_value, field_2_value, field_3_value, field_4_value, field_1_operator, field_2_operator, field_3_operator, field_4_operator)

SUBSET5

Data

Evaluates to a subset of the data. Normally used only as an argument to another function.
Either:
(a) a current subset of records (if field_N is specified);
(b) a subset of records (if field_N and field_N_value are specified);
(c) a subset of records, using a custom operator such as = (if all arguments are specified).
Custom operator should be one of: "=", "<>", ">", ">=", "<", "<=", "contains" (2.8+), "does-not-contain" (2.8+), "starts-with" (2.8+), "ends-with" (2.8+)
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.
Deprecated - please use the SUBSET and INTERSECTION functions instead.

Syntax: 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, field_1_operator, field_2_operator, field_3_operator, field_4_operator, field_5_operator)

SUBSET_EMPTYCOUNT

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_EMPTYCOUNT(stat_field, subset)

SUBSET_FIRST

Data

Calculates the statistical function [The first value (or null) in the original data order] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_FIRST(stat_field, subset)

SUBSET_FIRSTNONNULL

Data

Calculates the statistical function [The first non-null value in the original data order] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_FIRSTNONNULL(stat_field, subset)

SUBSET_LAST

Data

Calculates the statistical function [The last value (or null) in the original data order] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_LAST(stat_field, subset)

SUBSET_LASTNONNULL

Data

Calculates the statistical function [The last non-null value in the original data order] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_LASTNONNULL(stat_field, subset)

SUBSET_LOWERQUARTILE

Data

Calculates the statistical function [Lower quartile] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_LOWERQUARTILE(stat_field, subset)

SUBSET_MAX

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MAX(stat_field, subset)

SUBSET_MEAN

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MEAN(stat_field, subset)

SUBSET_MEDIAN

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MEDIAN(stat_field, subset)

SUBSET_MIN

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MIN(stat_field, subset)

SUBSET_MODE

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MODE(stat_field, subset)

SUBSET_NONEMPTYCOUNT

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_NONEMPTYCOUNT(stat_field, subset)

SUBSET_PRODUCT

Data

Calculates the statistical function [Product 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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_PRODUCT(stat_field, subset)

SUBSET_RANGE

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_RANGE(stat_field, subset)

SUBSET_SINGLETON

Data

Calculates the statistical function [The single value, if there is one, otherwise null. If there are nulls present, always null.] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_SINGLETON(stat_field, subset)

SUBSET_STDDEV

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_STDDEV(stat_field, subset)

SUBSET_SUM

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_SUM(stat_field, subset)

SUBSET_UNIQUECOUNT

Data

Calculates the statistical function [Number of unique non-empty values. Nulls are ignored.] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_UNIQUECOUNT(stat_field, subset)

SUBSET_UNIQUESLIST

Data

Calculates the statistical function [A comma-separated list of all unique values, not including null.] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_UNIQUESLIST(stat_field, subset)

SUBSET_UPPERQUARTILE

Data

Calculates the statistical function [Upper quartile] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_UPPERQUARTILE(stat_field, subset)

SUM

Maths/Stats

Adds all the arguments.

Syntax: SUM(number1, number2, ...)

TAN

Maths/Stats

Returns the tangent of an angle.

Syntax: TAN(number)

TANH

Maths/Stats

Returns the hyperbolic tangent of a number.

Syntax: TANH(number)

TEXTTODATE

Date

Converts text into a date.

If custom_format is specified, this is a custom date format pattern such as "dd/MM/yyyy HH:mm:ss". If not specified, the default format for the current data locale will be used.

If lenient is omitted or is true, this controls whether non-existent dates are permitted, such as 29 February on a non-leap-year (corrected to 1 March).

If time zone value is specified, this controls how Omniscope interprets time values. If not specified, the text will be assumed to be a date/time from the same time zone as the system.

Syntax: TEXTTODATE(text_value, custom_format, lenient, time_zone)

TEXTVALUE

Text

Converts any value into the text equivalent, optionally using a format pattern.
The format pattern (quoted text) defines how numbers or dates are formatted.
Alternatively, simply supply a number to specify the number of decimal places.
Warning: boolean values (true/false) will be translated using the current Language setting.
Usage examples:
TEXTVALUE(424) = "424"
TEXTVALUE(123456.789, "$###,###.###") = "$123,456.789"
TEXTVALUE(123456.789, 4) = "123,456.7890"

Syntax: TEXTVALUE(value, formatPatternOrDecimalPlaces)

TEXT_LINE_COUNT

Text

Returns the number of lines (split by line-break) in a text value.

Syntax: TEXT_LINE_COUNT(text)

TODAY

Date

Returns the current date.

Syntax: TODAY()

TRENDVALUE

Maths/Stats

Evaluates the linear trend for X and Y fields, then evaluates the Y value for X value specified. If 'xValue' is omitted, uses the current record's X value.
Used to determine the Y coordinate in a best-fit line showing the trend of two fields.
If 'subset' is omitted, the full dataset is used.

Syntax: TRENDVALUE(xField, yField, subset, xValue)

TRIM

Text

Removes all spaces from a text string except for single spaces between words.

Syntax: TRIM(text)

TYPEOF

Data

Returns the type of the argument.

Syntax: TYPEOF(arg)

TZCORRECT

Date

Corrects dates in the wrong timezone.
Converts from one timezone to another, preserving the same logical local time in each timezone.
Observes the current and historical rules for time zone offset and daylight saving according to the Olson time zone database.
For example, if your data was recorded as 9am in time zone X, but it should have been recorded as 9am in time zone Y, you would use: TZCORRECT(date, X, Y)
If either time zone is missing, your file's time zone will be used, as configured in the Regional Settings dialog, which defaults to your local system time zone.

Syntax: TZCORRECT(date, input_timezone, output_timezone)

TZDEFAULT

Date

Returns the Olson ID for the default time zone for this file, as configured in the Regional Settings dialog, which defaults to your local system time zone.

Syntax: TZDEFAULT()

TZOFFSET

Date

Returns the UTC offset, in milliseconds, for the time zone specified.
If time_zone is not supplied, your file's time zone will be used, as configured in the Regional Settings dialog, which defaults to your local system time zone.
If date is specified, returns the offset for that date/time, observing the current and historical behaviour and daylight saving rules. If not specified, returns the current offset from UTC for standard time in that time zone (i.e. without daylight saving).

Syntax: TZOFFSET(time_zone, date)

UNION

Data

Returns the data subset which is the union of records for all data subset arguments. If a record is present in ANY supplied subset, it will be present in the result.
WARNING: this is an experimental function that has not been optimised for performance. Use alternative solutions such as extra formulas and the SUBSET function if performance is unacceptable.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: UNION(subset1, subset2, ...)

UNIX_MILLISECONDS_TO_DATE

Date

Converts a number of milliseconds since unix epoch (00:00 1 January 1970 UTC) to a date.

Syntax: UNIX_MILLISECONDS_TO_DATE(unixtime)

UNIX_SECONDS_TO_DATE

Date

Converts a number of seconds since unix epoch (00:00 1 January 1970 UTC) to a date.

Syntax: UNIX_SECONDS_TO_DATE(unixtime)

UPPER

Text

Converts all letters in a text string to uppercase.

Syntax: UPPER(text)

VALUE

Data

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.

Syntax: VALUE(text)

XPATH

Script

Executes an XPath expression against an XML document. To obtain XML data, see the READRES function. For more information on XPath, see www.w3schools.com/xpath/

Syntax: XPATH(xml_data, xpath_expression)

Report: Data Model (row-based)

AND

Logical

Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE.

Syntax: AND(logical1, logical2, ...)

CURRENTROW

Data

Evaluates to the row number of the record being evaluated. Numbering starts at 1.

Syntax: CURRENTROW()

IF

Logical

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+"
)

Syntax: IF(logical_test, value_if_true, value_if_false) or IF(test1, value1, test2, value2, test3, value3..., else_value)

INTERSECTION

Data

Returns the data subset which is the intersection of records for all data subset arguments. If a record is present in ALL supplied subsets, it will be present in the result.
WARNING: this is an experimental function that has not been optimised for performance. Use alternative solutions such as extra formulas and the SUBSET function if performance is unacceptable.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: INTERSECTION(subset1, subset2, ...)

INTROUND

Maths/Stats

Rounds a number to the nearest integer.

Syntax: INTROUND(number)

NOT

Logical

Changes FALSE to TRUE, or TRUE to FALSE.

Syntax: NOT(logical)

OR

Logical

Checks whether any arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE.

Syntax: OR(logical1, logical2, ...)

ROUND

Maths/Stats

Rounds a number to a specified number of digits.
For example:
ROUND(123.7825, 2) = 123.78
ROUND(123.456, 1) = 123.5
ROUND(123.456, -2) = 100);

Syntax: ROUND(number, num_digits)

SAFEDIV

Maths/Stats

Divides a number by another number, safely handling the zero divisor case.

The 3rd argument if_zero is optional; if not specified, null is assumed.

Examples:

• SAFEDIV(2, 3) is equivalent to 2/3
• SAFEDIV(2, null) results in null (as does 2/null)
• SAFEDIV(2, 0) results in null
• SAFEDIV(2, 0, -1) results in -1

Syntax: SAFEDIV(numerator, divisor, if_zero)

SUBSET

Data

Evaluates to a subset of the data. Normally used only as an argument to another function.
Either:
(a) all records (if no arguments are specified);
(b) a current subset of records (if field is specified);
(c) a subset of records (if field and field_value are specified);
(d) a subset of records, using a custom operator such as = (if all arguments are specified).
Custom operator should be one of: "=", "<>", ">", ">=", "<", "<=", "contains" (2.8+)
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET(field, field_value, field_operator)

SUBSET_LOWERQUARTILE

Data

Calculates the statistical function [Lower quartile] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_LOWERQUARTILE(stat_field)

SUBSET_MAX

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MAX(stat_field)

SUBSET_MEAN

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MEAN(stat_field)

SUBSET_MEDIAN

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MEDIAN(stat_field)

SUBSET_MIN

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MIN(stat_field)

SUBSET_SINGLETON

Data

Calculates the statistical function [The single value, if there is one, otherwise null. If there are nulls present, always null.] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_SINGLETON(stat_field)

SUBSET_SUM

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_SUM(stat_field)

SUBSET_UNIQUECOUNT

Data

Calculates the statistical function [Number of unique non-empty values. Nulls are ignored.] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_UNIQUECOUNT(stat_field)

SUBSET_UPPERQUARTILE

Data

Calculates the statistical function [Upper quartile] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_UPPERQUARTILE(stat_field)

TEXTVALUE

Text

Converts any value into the text equivalent, optionally using a format pattern.
The format pattern (quoted text) defines how numbers or dates are formatted.
Alternatively, simply supply a number to specify the number of decimal places.
Warning: boolean values (true/false) will be translated using the current Language setting.
Usage examples:
TEXTVALUE(424) = "424"
TEXTVALUE(123456.789, "$###,###.###") = "$123,456.789"
TEXTVALUE(123456.789, 4) = "123,456.7890"

Syntax: TEXTVALUE(value, formatPatternOrDecimalPlaces)

UNION

Data

Returns the data subset which is the union of records for all data subset arguments. If a record is present in ANY supplied subset, it will be present in the result.
WARNING: this is an experimental function that has not been optimised for performance. Use alternative solutions such as extra formulas and the SUBSET function if performance is unacceptable.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: UNION(subset1, subset2, ...)

Report: Formula measures

AND

Logical

Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE.

Syntax: AND(logical1, logical2, ...)

CURRENTROW

Data

Evaluates to the row number of the record being evaluated. Numbering starts at 1.

Syntax: CURRENTROW()

IF

Logical

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+"
)

Syntax: IF(logical_test, value_if_true, value_if_false) or IF(test1, value1, test2, value2, test3, value3..., else_value)

INTERSECTION

Data

Returns the data subset which is the intersection of records for all data subset arguments. If a record is present in ALL supplied subsets, it will be present in the result.
WARNING: this is an experimental function that has not been optimised for performance. Use alternative solutions such as extra formulas and the SUBSET function if performance is unacceptable.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: INTERSECTION(subset1, subset2, ...)

INTROUND

Maths/Stats

Rounds a number to the nearest integer.

Syntax: INTROUND(number)

NOT

Logical

Changes FALSE to TRUE, or TRUE to FALSE.

Syntax: NOT(logical)

OR

Logical

Checks whether any arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE.

Syntax: OR(logical1, logical2, ...)

ROUND

Maths/Stats

Rounds a number to a specified number of digits.
For example:
ROUND(123.7825, 2) = 123.78
ROUND(123.456, 1) = 123.5
ROUND(123.456, -2) = 100);

Syntax: ROUND(number, num_digits)

SAFEDIV

Maths/Stats

Divides a number by another number, safely handling the zero divisor case.

The 3rd argument if_zero is optional; if not specified, null is assumed.

Examples:

• SAFEDIV(2, 3) is equivalent to 2/3
• SAFEDIV(2, null) results in null (as does 2/null)
• SAFEDIV(2, 0) results in null
• SAFEDIV(2, 0, -1) results in -1

Syntax: SAFEDIV(numerator, divisor, if_zero)

SUBSET

Data

Evaluates to a subset of the data. Normally used only as an argument to another function.
Either:
(a) all records (if no arguments are specified);
(b) a subset of records (if field and field_value are specified);
(c) a subset of records, using a custom operator such as = (if all arguments are specified).
Custom operator should be one of: "=", "<>", ">", ">=", "<", "<=", "contains" (2.8+)
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET(field, field_value, field_operator)

SUBSET_LOWERQUARTILE

Data

Calculates the statistical function [Lower quartile] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_LOWERQUARTILE(stat_field)

SUBSET_MAX

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MAX(stat_field)

SUBSET_MEAN

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MEAN(stat_field)

SUBSET_MEDIAN

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MEDIAN(stat_field)

SUBSET_MIN

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MIN(stat_field)

SUBSET_SINGLETON

Data

Calculates the statistical function [The single value, if there is one, otherwise null. If there are nulls present, always null.] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_SINGLETON(stat_field)

SUBSET_SUM

Data

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 is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_SUM(stat_field)

SUBSET_UNIQUECOUNT

Data

Calculates the statistical function [Number of unique non-empty values. Nulls are ignored.] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_UNIQUECOUNT(stat_field)

SUBSET_UPPERQUARTILE

Data

Calculates the statistical function [Upper quartile] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_UPPERQUARTILE(stat_field)

TEXTVALUE

Text

Converts any value into the text equivalent, optionally using a format pattern.
The format pattern (quoted text) defines how numbers or dates are formatted.
Alternatively, simply supply a number to specify the number of decimal places.
Warning: boolean values (true/false) will be translated using the current Language setting.
Usage examples:
TEXTVALUE(424) = "424"
TEXTVALUE(123456.789, "$###,###.###") = "$123,456.789"
TEXTVALUE(123456.789, 4) = "123,456.7890"

Syntax: TEXTVALUE(value, formatPatternOrDecimalPlaces)

UNION

Data

Returns the data subset which is the union of records for all data subset arguments. If a record is present in ANY supplied subset, it will be present in the result.
WARNING: this is an experimental function that has not been optimised for performance. Use alternative solutions such as extra formulas and the SUBSET function if performance is unacceptable.
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: UNION(subset1, subset2, ...)

Report: Content view

ABS

Maths/Stats

Returns the absolute value of a number, a number without its sign.

Syntax: ABS(number)

ACOS

Maths/Stats

Returns the arccosine of a number, in radians in the range 0 to Pi. The arccosine is the angle whose cosine is Number.

Syntax: ACOS(number)

ACOSH

Maths/Stats

Returns the inverse hyperbolic cosine of a number.

Syntax: ACOSH(number)

AND

Logical

Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE.

Syntax: AND(logical1, logical2, ...)

ASIN

Maths/Stats

Returns the arcsine of a number in radians, in the range -Pi/2 to Pi/2.

Syntax: ASIN(number)

ASINH

Maths/Stats

Returns the inverse hyperbolic sine of a number.

Syntax: ASINH(number)

ATAN

Maths/Stats

Returns the arctangent of a number in radians, in the range -Pi/2 to Pi/2.

Syntax: ATAN(number)

ATANH

Maths/Stats

Returns the inverse hyperbolic tangent of a number.

Syntax: ATANH(number)

AVG

Maths/Stats

Returns the average (arithmetic mean) of its arguments.

Syntax: AVG(value1, value1, ...)

BUCKET

Maths/Stats

Retrieves a range for a date or a number as a text value in the format "A to B".
For numbers, use BUCKET(value, width, format); for dates use BUCKET(value, width, unit, format). In either case, "format" is optional.
Date units can be one of: "year", "month", "week", "day", "hour", "minute", "second", "millisecond".

Numeric examples:
BUCKET(5, 10) = '0 to 10'
BUCKET(0.08, 0.1) = '0 to 0.1'
BUCKET(0.1, 0.1) = '0.1 to 0.2'
BUCKET(0, 0.1) = '0 to 0.1'
BUCKET(0.11, 0.1) = '0.1 to 0.2'
BUCKET(1002, 10) = '1000 to 1010'
BUCKET(10000, 11) = '9999 to 10010'

Date examples where field1 is 10 Feb 2007 12:43:
BUCKET([field1], 2, "hours") = '10 Feb 2007 12:00 to 10 Feb 2007 14:00'
BUCKET([field1], 2, "hours", "HH:mm") = '12:00 to 14:00'
BUCKET([field1], 2, "months", "dd MMM yyyy") = '01 Jan 2007 to 01 Mar 2007'
BUCKET([field1], 2, "years", "dd MMM yyyy") = '01 Jan 2006 to 01 Mar 2008'
BUCKET([field1], 12, "seconds", "HH:mm ss") = '12:43 00 to 12:43 12'

Syntax: BUCKET(value, width, number_format_or_date_unit, date_format)

CATCH

Data

Suppresses all evaluation errors in the sub-formula. If an error occurs, results in null.

Syntax: CATCH(sub_formula)

CHOOSE

Data

Chooses a value from a list of values, based on an index number (beginning at 1).

Syntax: CHOOSE(index_num, value1, value2, ...)

COMBIN

Maths/Stats

Returns the number of combinations for a given number of items.

Syntax: COMBIN(number, number_chosen)

CONTAINS

Text

Returns true if [sub_text] occurs anywhere within [text] (case insensitive).

Syntax: CONTAINS(text, sub_text)

CONVERT_LATLON_TO_OSGBX

GIS

Converts coordinates from the latitude value in WGS 84 (GPS lat/lon) to British National Grid (OSGB 36) X.

Syntax: CONVERT_LATLON_TO_OSGBX(lat, lon)

CONVERT_LATLON_TO_OSGBY

GIS

Converts coordinates from the latitude value in WGS 84 (GPS lat/lon) to British National Grid (OSGB 36) Y.

Syntax: CONVERT_LATLON_TO_OSGBY(lat, lon)

CONVERT_OSGB_TO_LAT

GIS

Converts coordinates from British National Grid (OSGB 36) to the latitude value in WGS 84 (GPS lat/lon).

Syntax: CONVERT_OSGB_TO_LAT(osgbX, osgbY)

CONVERT_OSGB_TO_LON

GIS

Converts coordinates from British National Grid (OSGB 36) to the longitude value in WGS 84 (GPS lat/lon).

Syntax: CONVERT_OSGB_TO_LON(osgbX, osgbY)

COS

Maths/Stats

Returns the cosine of an angle.

Syntax: COS(number)

COSH

Maths/Stats

Returns the hyperbolic cosine of a number.

Syntax: COSH(number)

DATE

Date

Creates a date using numerical arguments.
If omitted or null, the first value for each arguments is used.
So, if year is null, year 1 is used.
If month is null or omitted, January is used.
Etc.
BC dates are not supported.
Illegal dates such as day 29 Feb on a non-leap year will be rolled over.
Time zone is a text value in the format "GMT-8:00" or "PST"; if omitted, the local time zone is used.

Syntax: DATE(year, month, day, hour, minute, second, millisecond, timezone)

DATEADD

Date

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".

Syntax: DATEADD(date, number, unit)

DATEDIFF

Date

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 by specifying "true" for "approximate". Approximate date difference divides elapsed time between the two dates by the typical unit length (e.g. 30 days for days-in-month).

Syntax: DATEDIFF(date1, date2, unit, approximate)

DATETOTEXT

Date

Converts a Date into text, optionally using a custom date format and time zone.

Syntax: DATETOTEXT(date_value, custom_format, time_zone)

DATEUNIT

Date

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)".

Syntax: DATEUNIT(date, unit)

DATEVALUE

Date

Converts a text string that represents a date to a date. For more options, see TEXTTODATE.

Syntax: DATEVALUE(text)

DECLARE

Data

Declares one or more named values, each with a value expression, for repeated use in a sub-formula.
The names must not clash with existing field or variable names, and must not be quoted.
For more information, see http://archives.visokio.com/kb/kb.visokio.com/functions-guide/declare.html.

Syntax: DECLARE(name1, expression1, name2, expression2, name3, expression3..., sub_formula)

DEPENDENCIES

Data

Provides a hint to Omniscope that the nested formula has references to other fields. Use this when you are building up field references in ways that Omniscope cannot automatically determine, such as via the SCRIPT function. Omniscope needs to know about all field references in a formula to ensure correct evaluation.
Example:
DEPENDENCIES(SCRIPT(... dataArray("MyField") ... refVal("MyOtherField") ...), [MyField], [MyOtherField])
This tells Omniscope that the script has references to MyField and MyOtherField.

Syntax: DEPENDENCIES(nested_formula, ref1, ref2, ref3...)

E

Maths/Stats

Euler's number, e, also called the base of natural logarithms.

Syntax: E()

ENDSWITH

Text

Returns true if [sub_text] occurs in the end of [text] (case insensitive).

Syntax: ENDSWITH(text, sub_text)

EQUIV

Logical

Returns true if [value1] is equivalent to [value2] (automatically converting between text, numbers and dates to attempt to find a match).

Syntax: EQUIV(value1, value2)

EXP

Maths/Stats

Returns e raised to the power of a given number.

Syntax: EXP(number)

FACT

Maths/Stats

Returns the factorial of a number, equal to 123*...*Number.

Syntax: FACT(number)

FIELDCOUNT

Data

Returns the number of fields present.

Syntax: FIELDCOUNT()

FIELDFORMAT

Data

Converts any value into the text equivalent, using a field format.
Warning: boolean values (true/false) will be translated using the current Language setting.
Usage examples:
FIELDFORMAT(424, [Field 1]) = "424.00"

Syntax: FIELDFORMAT(value, field)

FIELDNAME

Data

Looks up a field name by number, e.g. the 5th field.

Syntax: FIELDNAME(field_number)

FIELDNUMBER

Data

Looks up a field number by name (reverse of FIELDNAME).

Syntax: FIELDNUMBER(field_name)

FINDBETWEEN

Text

Returns the first shortest matching text surrounded by [before] and [after], or null if not found.
For example, FINDBETWEEN("apple apple orange plum pear apple banana pear", "apple", "pear") would return " orange plum "

Syntax: FINDBETWEEN(all, before, after)

FINDLASTBETWEEN

Text

Returns the last shortest matching text surrounded by [before] and [after], or null if not found.
For example, FINDLASTBETWEEN("apple apple orange plum pear apple banana pear", "apple", "pear") would return " banana "

Syntax: FINDLASTBETWEEN(all, before, after)

FORMATDURATION

Date

Formats a number as a duration in the format 0:00:00, rounded to the nearest second.
The number given is in seconds by default, unless a unit argument is specified. Supported time unit values: "days", "hours", "minutes", "seconds", "milliseconds".
Usage examples:
FORMATDURATION(3723) = "1:02:03"
FORMATDURATION(4.5, "hours") = "4:30:00"
FORMATDURATION(1.5, "days") = "36:00:00"

Syntax: FORMATDURATION(number, unit)

HTTPREDIRECT

Web

Retrieves the HTTP redirect target for a URL, if the server provides one.

Syntax: HTTPREDIRECT(url)

IF

Logical

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+"
)

Syntax: IF(logical_test, value_if_true, value_if_false) or IF(test1, value1, test2, value2, test3, value3..., else_value)

INTCEIL

Maths/Stats

Rounds a number up to the nearest integer.

Syntax: INTCEIL(number)

INTERSECTION

Data

Returns the data subset which is the intersection of records for all data subset arguments. If a record is present in ALL supplied subsets, it will be present in the result.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: INTERSECTION(subset1, subset2, ...)

INTFLOOR

Maths/Stats

Rounds a number down to the nearest integer.

Syntax: INTFLOOR(number)

INTROUND

Maths/Stats

Rounds a number to the nearest integer.

Syntax: INTROUND(number)

INVERSE

Logical

Inverts the set of included records of a data subset. All records included in the subset supplied will be excluded in the result, and vice versa.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: INVERSE(subset)

ISO_COUNTRY_NAME

GIS

Converts an ISO country code into readable form.
The function uses two arguments:

code. The ISO language code. This can be either a 2-letter code language code or a 3-letter code.

display_language. Optional argument. The language of the function text result. This can be either a 2-letter code language code or a 3-letter code.

Syntax: ISO_COUNTRY_NAME(code, display_language)

ISO_LANGUAGE_NAME

GIS

Converts an ISO language code into readable form.
The function uses three arguments:

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 argument. 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.

display_language. Optional argument. The language of the function text result. This can be either a 2-letter code language code or a 3-letter code.

Syntax: ISO_LANGUAGE_NAME(code, show_country, display_language)

LASTDAYOFMONTH

Date

Returns last day of month for a given date. Date value should include month and year value.

Syntax: LASTDAYOFMONTH(date_value)

LAT_LON_DISTANCE

GIS

Returns the surface distance (in KM) from one latitude/longitude point to another.

Syntax: LAT_LON_DISTANCE(lat1, lon1, lat2, lon2)

LEFT

Text

Returns the specified number of characters from the start of a text string.

Syntax: LEFT(text, num_chars)

LEN

Text

Returns the number of characters in a text string.

Syntax: LEN(text)

LEVENSHTEIN

Text

Evaluates the Levenshtein distance between two text values (case insensitive).

Syntax: LEVENSHTEIN(value1, value2)

LG

Maths/Stats

Returns the base-2 logarithm of a number.

Syntax: LG(number)

LN

Maths/Stats

Returns the natural logarithm of a number.

Syntax: LN(number)

LOG

Maths/Stats

Returns the logarithm of a number to the base you specify.

Syntax: LOG(number, base)

LOG10

Maths/Stats

Returns the base-10 logarithm of a number.

Syntax: LOG10(number)

LOOKUPCSVCELL

Data

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.<br>The fifth argument 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".

Syntax: LOOKUPCSVCELL(csv_data, row, column, has_headers, locale)

LOOKUPJSONVALUE

Data

Reads JSON data, extracting a single value by its JSONPath location. See http://goessner.net/articles/JsonPath/ or test your paths at http://jsonpath.herokuapp.com

Syntax: LOOKUPJSONVALUE(json_data, json_path)

LOWER

Text

Converts all letters in a text string to lowercase.

Syntax: LOWER(text)

MAX

Maths/Stats

Returns the largest value in a set of values.

Syntax: MAX(value1, value1, ...)

MID

Text

Returns the specified number of characters from the middle of a text string, given a starting position and length.

Syntax: MID(text, start_num, num_chars)

MIN

Maths/Stats

Returns the smallest number in a set of values.

Syntax: MIN(value1, value1, ...)

MOD

Maths/Stats

Returns the remainder after a number is divided by a divisor.

Syntax: MOD(number, divisor)

NETWORKDAYS

Date

Returns the net number of working days (excluding weekends) between two dates, inclusive.

Syntax: NETWORKDAYS(start_date, end_date)

NORMDIST

Maths/Stats

Gives the probability that a number falls at or below a given value of a normal distribution.
X - is the value for which you want the distribution,
mean (by default 0) - is the arithmetic mean of the distribution,
sdev (by default 1) - is the standard deviation of the distribution, the value should be positive (>0)
cumulative (by default TRUE) - is a logical value that determines the form of the function. If cumulative is TRUE, NORMDIST returns the cumulative distribution function; if FALSE, it returns the probability mass function.
See also NORMSDIST: NORMDIST(x,mu,sigma,TRUE) = NORMSDIST((x - mu)/sigma).

Syntax: NORMDIST(x, mean, sdev, cumulative)

NORMINV

Maths/Stats

It is the inverse of the NORMDIST function. It calculates the x variable given a probability.
probability - is a probability corresponding to the normal distribution, the value should be in the range (0, 1), >0 and <1,
mean (by default 0) - is the arithmetic mean of the distribution,
sdev (by default 1) - is the standard deviation of the distribution, the value should be positive (>0).

Syntax: NORMINV(probability, mean, sdev)

NORMSDIST

Maths/Stats

Gives the probability that a number falls at or below a given value of a standard normal distribution.
X - is the value for which you want the distribution,
cumulative (by default TRUE) - is a logical value that determines the form of the function. If cumulative is TRUE, NORMSDIST returns the cumulative distribution function; if FALSE, it returns the probability mass function.
See also NORMDIST: NORMDIST(x,mu,sigma,TRUE) = NORMSDIST((x - mu)/sigma).

Syntax: NORMSDIST(x, cumulative)

NORMSINV

Maths/Stats

It is the inverse of the NORMSDIST function. It calculates the x variable given a probability.
probability - is a probability corresponding to the normal distribution, the value should be in the range (0, 1), >0 and <1.

Syntax: NORMSINV(probability)

NOT

Logical

Changes FALSE to TRUE, or TRUE to FALSE.

Syntax: NOT(logical)

NOW

Date

Returns the current date/time.

Syntax: NOW()

NUMVALUE

Text

Converts a text string that represents a number to a number. Accepts a locale as an optional second argument in the format 'en_GB'.

Syntax: NUMVALUE(text, locale)

NUM_OF_TOKENS

Text

Returns the number of tokens (split by a single character such as a comma) in a text value.

Syntax: NUM_OF_TOKENS(text, token_separator)

OR

Logical

Checks whether any arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE.

Syntax: OR(logical1, logical2, ...)

PI

Maths/Stats

Returns the value of Pi, 3.141592653589793, accurate to over 15 digits.

Syntax: PI()

POWER

Maths/Stats

Returns the result of a number raised to a power.

Syntax: POWER(number, power)

PROPER

Text

Converts a text string to proper case; the first letter in each word in uppercase, and all other letters to lowercase.

Syntax: PROPER(text)

RAND

Maths/Stats

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.

Syntax: RAND() or RAND(seed)

READRES

Web

Reads the text contents from a file or URL. If the maximum cache age in seconds is not specified, it will be assumed to be 1 minute. Downloaded data will be cached for this period. Use -1 to disable caching.

Syntax: READRES(file_path_or_url, max_cache_age_seconds)

RECORDCOUNT

Data

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.

Syntax: RECORDCOUNT(subset)

REPLACE

Text

Replaces part of a text string with a different text string.

Syntax: REPLACE(old_text, start_num, num_chars, new_text)

REPLACEREGEX

Data

Replaces text matching a regular expression. In the replace text, use "$1" to refer to group 1 in the regular expression, and "$$" to mean a single dollar character. If use_empty_if_no_match (optional, default false) is true, and the expression does not match, results in null. For more information on regular expressions, see www.regular-expressions.info

Syntax: REPLACEREGEX(text, regular_expression, replace_text, use_empty_if_no_match)

REPT

Text

Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.

Syntax: REPT(text, number_times)

RIGHT

Text

Returns the specified number of characters from the end of a text string.

Syntax: RIGHT(text, num_chars)

ROUND

Maths/Stats

Rounds a number to a specified number of digits.
For example:
ROUND(123.7825, 2) = 123.78
ROUND(123.456, 1) = 123.5
ROUND(123.456, -2) = 100);

Syntax: ROUND(number, num_digits)

SAFEDIV

Maths/Stats

Divides a number by another number, safely handling the zero divisor case.

The 3rd argument if_zero is optional; if not specified, null is assumed.

Examples:

• SAFEDIV(2, 3) is equivalent to 2/3
• SAFEDIV(2, null) results in null (as does 2/null)
• SAFEDIV(2, 0) results in null
• SAFEDIV(2, 0, -1) results in -1

Syntax: SAFEDIV(numerator, divisor, if_zero)

SEARCH

Text

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). The first character is numbered 1. Returns null if not found.

Syntax: SEARCH(find_text, within_text, start_num)

SEARCHREGEX

Text

Returns the number of the character at which a regular expression is first found, reading from left to right (not case-sensitive). The first character is numbered 1. Returns null if not found. For more information on regular expressions, see www.regular-expressions.info

Syntax: SEARCHREGEX(reg_expr, within_text, start_num)

SIGN

Maths/Stats

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.

Syntax: SIGN(number)

SIN

Maths/Stats

Returns the sine of an angle.

Syntax: SIN(number)

SINH

Maths/Stats

Returns the hyperbolic sine of a number.

Syntax: SINH(number)

SOUNDEX

Text

Converts a text value containing a single word into its Soundex phonetic code.

Syntax: SOUNDEX(word)

SQRT

Maths/Stats

Returns the square root of a number.

Syntax: SQRT(number)

STARTSWITH

Text

Returns true if [sub_text] occurs in the beginning of [text] (case insensitive).

Syntax: STARTSWITH(text, sub_text)

SUBSET

Data

Evaluates to a subset of the data. Normally used only as an argument to another function.
Either:
(a) all records (if no arguments are specified);
(b) a subset of records (if field and field_value are specified);
(c) a subset of records, using a custom operator such as = (if all arguments are specified).
Custom operator should be one of: "=", "<>", ">", ">=", "<", "<=", "contains" (2.8+)
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET(field, field_value, field_operator)

SUBSET_EMPTYCOUNT

Data

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 is specified).
Use the SUBSET function to define a data subset.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_EMPTYCOUNT(stat_field, subset)

SUBSET_LOWERQUARTILE

Data

Calculates the statistical function [Lower quartile] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_LOWERQUARTILE(stat_field, subset)

SUBSET_MAX

Data

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 is specified).
Use the SUBSET function to define a data subset.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MAX(stat_field, subset)

SUBSET_MEAN

Data

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 is specified).
Use the SUBSET function to define a data subset.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MEAN(stat_field, subset)

SUBSET_MEDIAN

Data

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 is specified).
Use the SUBSET function to define a data subset.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MEDIAN(stat_field, subset)

SUBSET_MIN

Data

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 is specified).
Use the SUBSET function to define a data subset.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MIN(stat_field, subset)

SUBSET_MODE

Data

Calculates the statistical function [Most common value (null 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 is specified).
Use the SUBSET function to define a data subset.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_MODE(stat_field, subset)

SUBSET_NONEMPTYCOUNT

Data

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 is specified).
Use the SUBSET function to define a data subset.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_NONEMPTYCOUNT(stat_field, subset)

SUBSET_SINGLETON

Data

Calculates the statistical function [The single value, if there is one, otherwise null. If there are nulls present, always null.] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_SINGLETON(stat_field, subset)

SUBSET_STDDEV

Data

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 is specified).
Use the SUBSET function to define a data subset.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_STDDEV(stat_field, subset)

SUBSET_SUM

Data

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 is specified).
Use the SUBSET function to define a data subset.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_SUM(stat_field, subset)

SUBSET_UNIQUECOUNT

Data

Calculates the statistical function [Number of unique non-empty values. Nulls are ignored.] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_UNIQUECOUNT(stat_field, subset)

SUBSET_UNIQUESLIST

Data

Calculates the statistical function [A comma-separated list of all unique values, not including null.] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_UNIQUESLIST(stat_field, subset)

SUBSET_UPPERQUARTILE

Data

Calculates the statistical function [Upper quartile] of a field.
Either:
(a) for all records (if only field_name is specified); or
(b) for a subset of data (if subset is specified).
Use the SUBSET function to define a data subset.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: SUBSET_UPPERQUARTILE(stat_field, subset)

SUM

Maths/Stats

Adds all the arguments.

Syntax: SUM(number1, number2, ...)

TAN

Maths/Stats

Returns the tangent of an angle.

Syntax: TAN(number)

TANH

Maths/Stats

Returns the hyperbolic tangent of a number.

Syntax: TANH(number)

TEXTTODATE

Date

Converts text into a date.

If custom_format is specified, this is a custom date format pattern such as "dd/MM/yyyy HH:mm:ss". If not specified, the default format for the current data locale will be used.

If lenient is omitted or is true, this controls whether non-existent dates are permitted, such as 29 February on a non-leap-year (corrected to 1 March).

If time zone value is specified, this controls how Omniscope interprets time values. If not specified, the text will be assumed to be a date/time from the same time zone as the system.

Syntax: TEXTTODATE(text_value, custom_format, lenient, time_zone)

TEXTVALUE

Text

Converts any value into the text equivalent, optionally using a format pattern.
The format pattern (quoted text) defines how numbers or dates are formatted.
Alternatively, simply supply a number to specify the number of decimal places.
Warning: boolean values (true/false) will be translated using the current Language setting.
Usage examples:
TEXTVALUE(424) = "424"
TEXTVALUE(123456.789, "$###,###.###") = "$123,456.789"
TEXTVALUE(123456.789, 4) = "123,456.7890"

Syntax: TEXTVALUE(value, formatPatternOrDecimalPlaces)

TEXT_LINE_COUNT

Text

Returns the number of lines (split by line-break) in a text value.

Syntax: TEXT_LINE_COUNT(text)

TODAY

Date

Returns the current date.

Syntax: TODAY()

TRIM

Text

Removes all spaces from a text string except for single spaces between words.

Syntax: TRIM(text)

TYPEOF

Data

Returns the type of the argument.

Syntax: TYPEOF(arg)

TZCORRECT

Date

Corrects dates in the wrong timezone.
Converts from one timezone to another, preserving the same logical local time in each timezone.
Observes the current and historical rules for time zone offset and daylight saving according to the Olson time zone database.
For example, if your data was recorded as 9am in time zone X, but it should have been recorded as 9am in time zone Y, you would use: TZCORRECT(date, X, Y)
If either time zone is missing, your file's time zone will be used, as configured in the Regional Settings dialog, which defaults to your local system time zone.

Syntax: TZCORRECT(date, input_timezone, output_timezone)

TZDEFAULT

Date

Returns the Olson ID for the default time zone for this file, as configured in the Regional Settings dialog, which defaults to your local system time zone.

Syntax: TZDEFAULT()

TZOFFSET

Date

Returns the UTC offset, in milliseconds, for the time zone specified.
If time_zone is not supplied, your file's time zone will be used, as configured in the Regional Settings dialog, which defaults to your local system time zone.
If date is specified, returns the offset for that date/time, observing the current and historical behaviour and daylight saving rules. If not specified, returns the current offset from UTC for standard time in that time zone (i.e. without daylight saving).

Syntax: TZOFFSET(time_zone, date)

UNION

Data

Returns the data subset which is the union of records for all data subset arguments. If a record is present in ANY supplied subset, it will be present in the result.null
For more information, please visit http://archives.visokio.com/kb/kb.visokio.com/kb/subset-functions.html.

Syntax: UNION(subset1, subset2, ...)

UNIX_MILLISECONDS_TO_DATE

Date

Converts a number of milliseconds since unix epoch (00:00 1 January 1970 UTC) to a date.

Syntax: UNIX_MILLISECONDS_TO_DATE(unixtime)

UNIX_SECONDS_TO_DATE

Date

Converts a number of seconds since unix epoch (00:00 1 January 1970 UTC) to a date.

Syntax: UNIX_SECONDS_TO_DATE(unixtime)

UPPER

Text

Converts all letters in a text string to uppercase.

Syntax: UPPER(text)

VALUE

Data

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.

Syntax: VALUE(text)

XPATH

Script

Executes an XPath expression against an XML document. To obtain XML data, see the READRES function. For more information on XPath, see www.w3schools.com/xpath/

Syntax: XPATH(xml_data, xpath_expression)