Alphabetical List of Supported Datameer Functions

Owned by Charles Bishop (Deactivated)
Last updated: Feb 04, 2019
11 min read
  • ABS Returns the absolute value of a number.
  • ACOS Returns the arccosine of a number.
  • ACOSH Returns the inverse hyperbolic cosine of a number.
  • ADD Adds a value or values into a list.
  • ADDTODATE Adds (or subtracts) time to a date using an integer and one date constant.
  • AFTER Tests if this date is after the specified date.
  • AND Returns TRUE if all of its arguments are TRUE.
  • ASDATE Converts a character sequence to a date using predefined (dd-MM-yyyy hh:mm:ss, dd.MM.yyyy hh:mm:ss, dd-MM-yyyy, dd.MM.yyyy) or an optional date parse pattern.
  • ASIN Returns the arc sine of a number.
  • ASINH Returns the inverse hyperbolic sine of a number.
  • ATAN Returns the arc tangent of a number.
  • ATAN2 Returns the arctangent from x- and y-coordinates.
  • ATANH Returns the inverse hyperbolic tangent of a number.
  • AVERAGE Returns the average of its arguments.
  • BEFORE Tests if this date is before the specified date.
  • BIGDECIMAL Converts the values of a column with a number data type  to the big decimal data type.
  • BIGINTEGER Converts the values of a column with a number data type to the big integer data type.
  • BITAND Bitwise AND operation on given arguments.
  • BITOR Bitwise OR operation on given arguments.
  • CASE Exchanges values for others based on either specific cases or boolean conditions.
  • CEILING Rounds a number to the nearest integer or to the nearest multiple of significance.
  • CEILINGDATE Rounds a date argument up to the beginning of the next date interval.
  • CHAR Returns the character specified by the codepoint number.
  • CHISQDIST Returns a chi square distribution at a given value x for a certain number of degrees of freedom.
  • COALESCE Returns the first non-null argument.
  • CODE Returns a numeric code for the first character in a text string.
  • COMBIN Returns the number of combinations for a given number of objects.
  • COMBIND Returns the double representation of combinations for a given number of objects.
  • COMPARE Compares two values. Returns a negative integer, zero, or a positive integer as this object is less than, equal to, or greater than the specified object.
  • CONCAT Concatenates several text items into one text item.
  • CONTAINS_IC Returns whether text contains the second argument. (Not case sensitive)
  • CONTAINS Returns whether text contains the second argument.
  • CONTENTS_BY_TAG_NAME Returns all contents of elements with the specified tag name in a list.
  • CONVERT Converts a number from one measurement to another measurement.
  • COPY Copies values from a sheet column to a new sheet.
  • CORRECT_SPELLING Corrects spelling of tokenized text. 
  • COS Returns the cosine of a number.
  • COSH Returns the hyperbolic cosine of a number.
  • COT Returns the cotangent of a number.
  • COUNTMATCHES Evaluates the regular expression against the column and returns the number of matches. The function is case sensitive. 
  • COUNTTEXT Evaluates the substring against the column and returns the number of matches. The function is case sensitive.
  • CRON_MATCHER Checks if the given date matches the given cron expression.
  • DAY Returns only the day of the month of a date. The day is given as an integer, ranging from 1 to 31.
  • DAYOFYEAR Returns the day of the year for the supplied date in a range from 1 to 366.
  • DEC_BASE64 Decodes a base64 encoded string.
  • DEC2HEX Converts a decimal number to hexadecimal.
  • DEGREES Converts radians to degrees.
  • DENULLIFY Converts null values to a non null default value.
  • DIFF Returns the difference of the numbers. The first argument value can't be null.
  • DIV Returns the quotient of the numbers.
  • E Returns the value of e.
  • ELEMENTS_BY_SELECTOR_QUERY Returns all elements that match the selector query. 
  • ELEMENTS_BY_TAG_NAME Returns all elements with a specified tag name in a list.
  • ENC_BASE64 Encodes string as base64.
  • ENDOFMONTH Returns the date of the last day of month (ending in the evening at 11:59:59.999 PM / 23:59:59.999) for the given date.
  • ENDSWITH Tests if this string ends with the specified suffix.
  • EQUALS Checks to see if two values are identical.
  • EVEN Rounds a number up to the nearest even integer.
  • ExcelConverter Converts integer dates from Excel to a date.
  • EXP Returns e raised to the power of a given number.
  • EXPAND_DATE_RANGE Expands a date range into multiple rows, one for each interval, one day by default.
  • EXPONDIST Returns he exponential distribution for a certain value with given lambda.
  • FACT Returns the factorial of a number.
  • FACTD Returns the double factorial of a number.
  • FDIST Returns the F probability distribution for a certain value with given degrees of freedom for the numerator and degrees of freedom for the denominator.
  • FLOAT Converts a text to a float value.
  • FLOOR Rounds a number down, toward 0 (zero).
  • FLOORDATE Rounds a date argument down to the beginning of the next date interval.
  • FORMATDATE Converts date to string by applying a format pattern.
  • GAMMADIST Returns the gamma distribution for a certain value with given parameters alpha and beta.
  • GCD Returns the greatest common divisor.
  • GE Greater equal.
  • GEOHASH_DEC_LAT Decodes the given geohash value into a latitude with a precision of 6 after the decimal place.
  • GEOHASH_DEC_LONG Decodes the given geohash value into a longitude with a precision of 6 after the decimal place.
  • GEOHASH_ENC Encodes the given latitude and longitude into a geohash value with precision of 12 digits.
  • GROUPACCUMULATE Returns the sum of all previous records in a group.
  • GROUPAND Returns the Boolean value after checking if all of the group's values are TRUE.
  • GROUPANOVA Compares the means of three or more samples to see if they are significantly different at a certain significance level.
  • GROUPANY Returns an arbitrarily selected value contained in the group.
  • GROUPAVERAGE Returns the average of its arguments.
  • GROUPBOTTOMN Selects the bottom N values from a group. If this function is applied on a date column, bottom N means the N least recent dates.
  • GROUPBY Groups records.
  • GROUPBYBIN Groups values into bins.
  • GROUPBYCUSTOMBIN Groups selected values into bins created at a custom sizes.
  • GROUPBYGAP Groups records by maximum gap between two values in a timestamp or numerical value.
  • GROUPCOMBIN Generates combinations of values in a group.
  • GROUPCONCAT Creates a list of all non-null values seen in a group.
  • GROUPCONCATDISTINCT Concatenates all non-null values seen in a group.
  • GROUPCOUNT Counts the records in one group.
  • GROUPCOUNTDISTINCT Counts the distinct values in one group.
  • GROUPFIRST Returns a value in a group as specified by the ordered column's first value.
  • GROUPJSONOBJECTMERGE Merges all elements in a grouped series of JSON maps.
  • GROUPMAP Creates a JSON map based on two columns of all records within a group, keys and values.
  • GROUPMAX Maximum of its arguments.
  • GROUPMEDIAN Returns the median of all values in a group.
  • GROUPMIN Minimum of its arguments.
  • GROUPOR Returns the Boolean value after checking if any of the group's values are TRUE.
  • GROUPPERCENTILE Return the number which is nth percentile in this distribution.
  • GROUPPREDICTIVEWINDOWS This function assigns IDs to events. It does that based on an event that defines the center of a time-based window (center-event) and all events in that window get assigned the ID of the center-event.
  • GROUPROWNUMBER Returns the row number within a sorted group.
  • GROUPSELECT Create a column with selected values using a key constant from a specified grouped column in the workbook.
  • GROUPSESSIONS Assigns IDs to events based on session start and session end. All events with the session ID of the start event between start and end event get assigned the ID of the start event.
  • GROUPSTDEVP Estimates standard deviation on the entire population.
  • GROUPSTDEVS Estimates standard deviation on a sample of the entire population.
  • GROUPSUM Adds its arguments.
  • GROUPTOPN Selects the top N values from a group.
  • GROUPTTEST The Student's t-test compares the means of two samples (or treatments), even if they have a different numbers of replicates. It is used to determine if sets of data are significantly different from each other.
  • GROUPUNIQUES Outputs unique column values for a group.
  • GROUP_DIFF Computes the difference to the previous value in a group.
  • GROUP_JACCARD_DIST Computes the jaccard distance of all values in a group.
  • GROUP_PAIR Generates pairs of values in a group.
  • GROUP_PATH Create paths from a field (e.g., for click stream analysis).
  • GROUP_PATH_CHANGES Create paths from a field, but only if the value changes (e.g., for click stream analysis).
  • GROUP_PREVIOUS Returns the value of the previous record in a group.
  • GROUP_SORT_ASC Groups records in sorted ascending order.
  • GROUP_SORT_DESC Groups records in sorted descending order.
  • GT Greater than.
  • HEX2DEC Converts a hexadecimal number given as a string to decimal number.
  • HOUR Returns only the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).
  • IF Specifies a logical test to perform.
  • INDEX Finds the first occurrence of the given regular expression.
  • INDEXOF Returns (as an integer) the index of the first occurrence of an element in a list or -1 if the element isn't found.
  • INT Converts a string to an integer.
  • INTERSECTION Returns the intersection of all list arguments. The resulting list contains only the elements found in all the list arguments and doesn't contain any duplicate elements.
  • IP_TO_NUMBER Converts IP4 address to decimal presentation.
  • ISBLANK Returns TRUE if the value is blank.
  • ISEMPTY Checks to see if a list is empty and returns a Boolean value.
  • ISEVEN Returns TRUE if the number is even.
  • ISNULL Returns TRUE if the value is null.
  • ISODD Returns TRUE if the number is odd.
  • JSONTOLIST Converts the selected JSON data type column into a list data type column.
  • JSON_ELEMENT Returns the given element of a json array.
  • JSON_ELEMENTS Returns all elements of a json array.
  • JSON_KEYS Returns all keys from a json string.
  • JSON_MAP Creates a JSON map from two JSON arrays.
  • JSON_VALUE Returns a value for a given key from a json object.
  • LCM Returns the least common multiple.
  • LE Less equal.
  • LEFT Returns the leftmost characters from a text value.
  • LEN Returns the number of characters in a text string.
  • LN Returns the natural logarithm of a number.
  • LOG Returns the logarithm of a number to a specified base.
  • LOGNORMDIST Returns the log-normal distribution for a certain value with given mean and standard deviation .
  • LOWER Converts text to lowercase.
  • LOG10 Returns the base-10 logarithm of a number.
  • LSHIFT Shifts bits left by the given number of bits.
  • LT Less than.
  • LTRIM Removes leading whitespace or other characters from text. 
  • MATCHES Return true if a string matches a regular expression.
  • MAX Returns maximum of arguments.
  • MILLISECOND Truncates a date to only display the milliseconds associate with the date.
  • MIN Returns minimum of arguments.
  • MIDNIGHT Returns the given date with a time of midnight (12:00:00 am).
  • MOD Returns the modula of the arguments.
  • MONTH Returns only the month of a date represented by a serial number.
  • MROUND Returns a number rounded to the desired multiple
  • MSTODATE Converts an integer representing a timestamp in milliseconds from January 1, 1970, 00:00:00 GMT to its corresponding date value.
  • NGRAM Returns subsequences of N characters of a given text.
  • NGRAM_OLD Returns subsequences of N words of a given text.
  • NORMDIST Returns the normal distribution for a certain value with given mean and standard deviation.
  • NOT Reverses the logic of its argument.
  • NOT_EQUALS Checks to see if two values aren't identical.
  • NOW Returns the current date and time.
  • NUMBER_TO_IP Converts number to internet standard dot notation.
  • ODD Rounds a number up to the nearest odd integer.
  • OR Returns TRUE if any argument is TRUE.
  • PI Returns the value of pi.
  • PLUS Adds its arguments (or concats string values).
  • POWER Returns the result of a number raised to a power.
  • PRODUCT Multiplies the arguments.
  • PROPER Capitalizes the first letter in each word of a text value.
  • PROPERTY_VALUE_BY_TAG_NAME Returns the value of the specified property by tag name in a list.
  • QUARTER Returns the quarter of the year for the supplied date in a range from 1 to 4.
  • QUOTIENT Returns the integer portion of a division.
  • RADIANS Converts degrees to radians.
  • RAND Returns a random number between 0 and 1.
  • RANDBETWEEN Returns a random number between the numbers that you specify.
  • RANDGAUSSIAN Generates normal distributed random values with mean of 0 and standard deviation of 1.
  • RANGE Returns in the form of a list, integers ranging in numerical order from the first value (FROM) and ending with last value (TO).
  • REGEX Replaces characters within a text string which matches a regular expression with a different text string.
  • REGEXTRACT Extracts tokens that match a regular expression.
  • REMOVE Removes a value from a list.
  • REMOVEALL Removes all elements from one list that exist in a specified list or lists.
  • REMOVE_ELEMENTS_BY_TAG_NAME Removes elements by tag name from the document object model.
  • REMOVE_HTML_TAGS Removes all HTML tags.
  • REPLACE Replaces characters within a text string with a different text string.
  • REPLACEALL Replaces each substring of this string that matches the given regular expression with the given replacement.
  • REPT Repeats text a given number of times.
  • RIGHT Returns the rightmost characters from a text value.
  • ROUND Rounds a number to a specified number of digits.
  • ROUNDDOWN Rounds a number down, toward zero.
  • ROUNDTIME Rounds a date field down to the nearest (s)econd, (m)inute, (h)our, or (d)ay set by a time constant.
  • ROUNDUP Rounds a number up, away from zero.
  • RSHIFT Shifts bits right by the given number of bits.
  • RTRIM Removes trailing whitespace or other characters from text.
  • SECOND Truncates a date to only display the seconds associate with the date.
  • SELECT Create columns with selected values using a key constant from a specified column in the workbook. 
  • SHIFTTIMEZONE Shift the date from one timezone to another.
  • SIGN Returns the sign of a number.
  • SIN Returns the sine of a number.
  • SINH Returns the hyperbolic sine of a number.
  • SPLIT Splits text specified by a string separator(s). An additional index value (with the index of 0 returning the first argument) can be assigned to return a specific split argument. 
  • SQRT Returns the square root of a number.
  • SQRTPI Returns the square root of (number * pi).
  • STARTOFMONTH Returns the date of the first day of month (starting the morning at 00:00:00 / 12:00:00 AM) for the given date.
  • STARTOFWEEK Returns the date of the first day of week (Sunday night/Monday morning 00:00:00) for the given date.
  • STARTSWITH Tests if this string starts with the specified prefix.
  • STDEVP Estimates standard deviation on the entire population.
  • STDEVS Estimates standard deviation on a sample of the entire population.
  • SUBSTITUTEALL Substitutes new text for all occurencies old text in a text string.
  • SUBSTITUTEFIRST Substitutes new text for first occurency old text in a text string.
  • SUBSTR Returns a specific number of characters from a text string starting at the position that you specify.
  • SUBTRACT Substracts all values from the first argument.
  • SUM Adds its arguments.
  • T Converts its arguments to text.
  • TAN Returns the tangent of a number.
  • TANH Returns the hyperbolic tangent of a number.
  • TDIST Returns student t-distribution at a given value x for a certain number of degrees of freedom.
  • TEXT2HEX Converts text into a hex string.
  • TIMESTAMP Difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC.
  • TIMESTAMPDIFF Returns the number of whole date intervals between two date arguments.
  • TODAY Returns the current day (time at midnight). 
  • TOKENIZE Tokenizes text.
  • TOKENIZELIST Tokenizes text returning a list of values or a specified element as a string.
  • TRIM Removes spaces from text.
  • TRUNC Truncates a number to an integer or truncates its decimal places.
  • UPPER Converts text to uppercase.
  • URL_AUTHORITY Gets the authority part of the URL provided as string.
  • URL_DECODE Decodes an encoded string.
  • URL_DEFAULT_PORT Gets the default port number of the protocol associated with this URL.
  • URL_ENCODE Encodes a string.
  • URL_FILE Gets the file name of the URL provided as string.
  • URL_HOST Gets the host name of the URL provided as string.
  • URL_PARAM Gets a value addressed by a param of the URL provided as string.
  • URL_PARAMS Gets the values addressed by a param of the URL provided as list.
  • URL_PATH Gets the path part of the URL provided as string.
  • URL_PLD Extract the PLD (paid-level domain, as per the IRLbot paper) from a url.
  • URL_PORT Gets the port number of the URL provided as long.
  • URL_PROTOCOL Gets the protocol name of the URL provided as string.
  • URL_QUERY Gets the query part of the URL provided as string.
  • URL_REF Gets the anchor ('reference') of the URL provided as string.
  • URL_TLD Gets the TLD of an URL.
  • URL_USERINFO Gets the userinfo part of the URL provided as string.
  • URSHIFT The unsigned right shift operator shifts a zero into the leftmost position.
  • WEEKDAY Returns an integer between (1-7) corresponding to the day of the week of the date.
  • WEEKOFYEAR Returns the week of the year for the supplied date in a range from 1 to 53.
  • WEIBULLDIST Returns the weibull distribution for a certain value with given alpha and beta.
  • XMLVALUE Returns a value selected by the given XPath expression out of an XML document.
  • XMLVALUES Returns the values selected by the given XPath expression out of an XML document.
  • YEAR Returns the year corresponding to a date. The year is returned as an integer, ranging from 1900-9999.