This section expands on the information contained in our applications regarding functions used in formula fields to calculate values based on values in other columns. Most of the functions/syntax are the same as those used in spreadsheets like Excel, but there are also powerful Visokio-specific functions that perform operations on entire data sets, both aggregated and disaggregated.
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. |
| 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. |
| COMBIN | COMBIN(number, number_chosen) | Returns the number of combinations for a given number of items. |
| CONVERT_OSGB_TO_LAT | 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 | 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. |
| E | E | Euler's number, e, also called the base of natural logarithms. |
| 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. |
| 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. |
| LG | LG(number) | Returns the base-2 logarithm of a number. |
| LN | LN(number) | Returns the natural logarithm of a number. |
| LOG10 | LOG10(number) | Returns the base-10 logarithm of a number. |
| LOG | LOG(number, base) | Returns the logarithm of a number to the base you specify. |
| MOD | MOD(number, divisor) | Returns the remainder after a number is divided by a divisor. |
| 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. |
| 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. |
| 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. |
| SUM | SUM(number1,number2,...) | Adds a list of arguments (e.g. field values) |
| TAN | TAN(number) | Returns the tangent of an angle. |
| TANH | TANH(number) | Returns the hyperbolic tangent of a number. |
| AND | AND(logical1, logical2, ...) | Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE. |
| OR | OR(logical1, logical2, ...) | Checks whether any arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE. |
| CURRENTROW | CURRENTROW() | Evaluates to the row number of the record being evaluated. Numbering starts at 1. |
| CHOOSE | CHOOSE(index_num, value1, value2, ...) | Chooses a value from a list of values, based on an index number (beginning at 1). |
| DATEDIFF | DATEDIFF(date1, date2, unit) | Establishes the difference between two dates, as a decimal number of a specified unit (optional, default: "days").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: "years", "months","weeks", "days", "hours", "minutes", "seconds", "milliseconds" |
| DATETOTEXT | DATETOTEXT(date_value, custom_format) | Converts a Date into text, optionally using a custom date format. |
| DATEVALUE | DATEVALUE(text) | Converts a text string that represents a date to a date. |
| EXEC | EXEC(command) | Executes an operating system command, resulting in a text value containing the command's output |
| IF | IF(logical_test, value_if_true, value_if_false) | Checks whether a condition is met, and returns one value if TRUE,and another value if FALSE |
| LEFT | LEFT(text, num_chars) | Returns the specified number of characters from the start of a text string |
| RIGHT | RIGHT(text, num_chars) | Returns the specified number of characters from the end of a text string |
| LEN | LEN(text) | Returns the number of characters in a text string. |
| 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" |
| MID | MID(text, start_num, um_chars) | Returns the specified number of characters from the middle of a text string, given a starting position and length |
| 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. |
| RANK | RANK(value, fieldName, ascending, includeNulls) | Returns the rank of a value within the field specified (use a double-quoted field name). 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 higher rank). 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. |
| READRES | READRES(file_path_or_url) | Reads the text contents from a file or URL. |
| 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. |
| 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). |
| TRIM | TRIM(text) | Removes all spaces from a text string except for single spaces between words |
| PROPER | PROPER(text) | LOWER LOWER(text) Converts all letters in a text string to lowercase. |
| 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 pattern string. The pattern string refers to the format you want your end results to be in. Click here for information on date and time formats. For numbers please click here. |
| 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. |
This section documents the Visokio-specific DATASET functions currently available in Visokio applications:
DATASET_EMPTYCOUNT
DATASET_EMPTYCOUNT(field_name, category_field, category_field_value)
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 current subset of records (if category_field is specified); or
(c) for another subset of records (if category_field and category_field_value are specified):
Do not select field_name from drop down, instead type field_name and put it in quotes
DATASET_NONEMPTYCOUNT
DATASET_NONEMPTYCOUNT(field_name, category_field, category_field_value)
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 current subset of records (if category_field is specified); or
(c) for another subset of records (if category_field and category_field_value are specified):
Do not select field_name from drop down, instead type field_name and put it in quotes
DATASET_UNIQUECOUNT
DATASET_UNIQUECOUNT(field_name, category_field, category_field_value)
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 current subset of records (if category_field is specified); or
(c) for another subset of records (if category_field and category_field_value are specified):
Do not select field_name from drop down, instead type field_name and put it in quotes
DATASET_MEAN
DATASET_MEAN(field_name, category_field, category_field_value)
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 current subset of records (if category_field is specified); or
(c) for another subset of records (if category_field and category_field_value are specified):
Do not select field_name from drop down, instead type field_name and put it in quotes
DATASET_MEDIAN
DATASET_MEDIAN(field_name, category_field, category_field_value)
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 current subset of records (if category_field is specified); or
(c) for another subset of records (if category_field and category_field_value are specified):
Do not select field_name from drop down, instead type field_name and put it in quotes
DATASET_MIN
DATASET_MIN(field_name, category_field, category_field_value)
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 current subset of records (if category_field is specified); or
(c) for another subset of records (if category_field and category_field_value are specified):
Do not select field_name from drop down, instead type field_name and put it in quotes
DATASET_MAX
DATASET_MAX(field_name, category_field, category_field_value)
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 current subset of records (if category_field is specified); or
(c) for another subset of records (if category_field and category_field_value are specified):
Do not select field_name from drop down, instead type field_name and put it in quotes
DATASET_RANGE
DATASET_RANGE(field_name, category_field, category_field_value)
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 current subset of records (if category_field is specified); or
(c) for another subset of records (if category_field and category_field_value are specified):
Do not select field_name from drop down, instead type field_name and put it in quotes
DATASET_SUM
DATASET_SUM(field_name, category_field, category_field_value)
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 current subset of records (if category_field is specified); or
(c) for another subset of records (if category_field and category_field_value are specified):
Do not select field_name from drop down, instead type field_name and put it in quotes
DATASET_STDDEV
DATASET_STDDEV(field_name, category_field, category_field_value)
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 current subset of records (if category_field is specified); or
(c) for another subset of records (if category_field and category_field_value are specified):
Do not select field_name from drop down, instead type field_name and put it in quotes
----------------------------------
We have also added DATASET_SUM2, DATASET_SUM3, DATASET_SUM4 and DATASET_SUM5, so (unrelated to aggregation in the table), for each record, you can now calculate the sum (or mean, etc.) of a field for all records in the same group of records specified by up to 5 field intersections.
So, as per DATASET_SUM (or DATASET_MEAN, etc), where you could find the SUM (or MEAN, etc) of a field for all records with the same [Month] value, now you can find the SUM (or MEAN, etc) for all records with the same [Month][Client] values using DATASET_SUM2, or with the same [Month][Client][Currency Pair] values using DATASET_SUM3.
Below are some examples of formulae/useful syntax useful for defining commonly-used calculated Formula fields:
Note: You can type function names or pick from the list; text strings in quotes should be entered literally; field names in [ ] should be input using the [Insert field ] tool in the Add/Edit Formula wizard.
To create a new column where values in a source column are categorised according to user-defined upper and lower limits:
=IF([SourceColumn]<50 "0-50",IF[SourceColumn]<500,"50-500",">500"))
In this example, values in [SourceColumn] are evaluated and placed in 'bucket' categories 0-50, 50-500, or >500
Dates of payments and other events sometimes need to be categorised by fiscal rather than calendar year. One way to do this is to use the following formula, which can be extended to any number of years:
=IF([Date]=null,null,IF(DATEDIFF(DATEVALUE("1 Oct 2001"),[Date],"day")<366,"FY2002",IF(DATEDIFF(DATEVALUE("1 Oct 2002"),[Date],"day")<366,"FY2003","Out of range")))
In this example, the company's fiscal year runs from October, and the formula column Fiscal Year will be populated with null, FY2002, FY2003, or "out of range" depending on the value in the [Date] field.
Dates can be converted to week numbers like this:
=DATETOTEXT([DateField],"w")
This formula evaluates [DateField] and returns the corresponding week number.
Using a Variable to define the threshold, you can flag Values which are outside a user-selectable upper-lower tolerance range:
=IF(OR([Value]<(-1*{Threshold variable}),[Value]>{Threshold variable}),"Flagged","Not Flagged")
Before writing the formula, you must define the Threshold Variable, its min, max and default values
Counting the number of times a particular value appears in a column:
=DATASET_NONEMPTYCOUNT("Column Name","Column Name", [Insert field-Column Name])
The first free text input "Column Name" specifies the column, the second the non-empty and the last is an [Insert Field] of the field 'Column Name' to pick up the values. This admittedly obscure syntax will return for each row the total number of times the value in the row appears in that same column in the entire data set.
Back to Functions Guide
Below are some new functions which have already been proposed...
1. Working Days between Dates -function calculates days between given dates excluding list of holidays and weekends.
2. Capture Text Match(es) -when an external list of terms/text items is submitted for matching to an internal target text column, the function records the term or terms (tokenized) from the input list that were matched within the text of the target column. (submitted by Amr Hassan- Segmetrix)
3. Date/Time Bucketing- set of functions (e.g. @Year, @Month, @Day, @Hour, @Minutes, @Seconds) that examine a target Date/Time field and return a Category field value for the Year, Month, etc. using specified values/format options for the Categories.
4. MillisecondsToDate converts date values in millieseconds to a date value. First argument is the date value, second argument is the custom format for displaying date information.
-----------------------------------
To propose a new function, or refine an existing or proposed function, please contact us