Syntax Definitions

The following table summarizes the functions available within Audiences. Detailed breakdowns with examples are available in each function's dedicated article.

Note: dates in Audiences need to be formatted as either YYYY-MM-DD or MM/DD/YYYY. Entering a date as 01-02-2023 would be read as Jan-2 not 1-Feb.

Function	Category	Summary
ABS	Maths Function	Returns the Absolute Value of a number.
AFTER	Cross-table	Checks the source query against the target query and returns True if any of the records in the source query came after those in the target query.
AGE	Date Function	Returns the age in years from the value supplied to the current system date.
AGEAT	Date Function	Returns the age in years from the value supplied in the first argument to the value supplied in the second argument.
ANNIVERSARY_DATE	Date Function	Returns the date of an anniversary.
ANNIVERSARY_DAYSTO	Date Function	Returns the number of days to an anniversary.
AREA_LABEL	Geographic	This function will extract the area label from a postcode in standard UK format.
AVERAGE	Cross-table	Takes all the child records that match the optional filter query and sums the values for each parent record. Then divides the sum by the number of child records for the parent that match the criteria.
AVGDIFF	Cross-table	Returns a number that represents the average difference between each of a child records of the parent.
BEFORE	Cross-table	Takes the child records for each parent and sorts them by the field specified in the fieldName parameter.
CATEGORISE	Text Function	Turns a delimited list of data values into a categorised data banding.
CEILING	Maths Function	Returns the value from a numeric field rounded up to the next whole number.
CHR	Query Domain	Converts the value supplied into the equivalent ASCII character for that number.
CODE	Text Function	Returns the ASCII character code for the first character of the string supplied.
CONCAT	Text Function	Joins several text values together into a single text value.
CONCATENATE	Text Function	Joins several text values together into a single text value.
CONVERT	Data Conversion	Converts one datatype to another.
COUNTDISTINCT	Cross-table	Returns the number of distinct values in a field on the child table for each parent record.
CTCOUNT	Cross-table	Returns the number of child records for each parent. The optional filter query restricts the child records to be included in the calculation.
CTSUM	Cross-table	Returns a number that represents the sum of a parents child records.
CURRENT_TIMESTAMP	Constants	Returns the current date and time.
DATEADD	Date Function	Adds a time interval to the input date.
DATEPART	Date Function	Returns just the date part from a value that includes the date and time.
DAY	Date Function	Returns the day component of the date supplied. Any leading zeros are removed.
DAYOFMONTH	Date Function	Returns the day in a month for the date supplied, from 1 to 31.
DAYOFWEEK	Date Function	Returns the day of week component of the date supplied. Any leading zeros are removed.
DAYOFYEAR	Date Function	Returns the day of the year for the date supplied. Any leading zeros are removed.
DAYSTO	Date Function	Returns the number of whole days between two given dates.
DECODE	Conditions	Decodes values within a discrete field to an alternative set of values.
EXP	Maths Function	Returns e raised to the power of the value in a numeric field.
FIRST	Cross-table	For each parent record, returns the value from the first child record. The child records are looked at in natural unsorted order.
FirstDistinct	Query Domain	When used within a query the function produces a recordset that contains only one record for each unique value of the argument.
FLOOR	Maths Function	Returns the value from a numeric field rounded down to the next whole number.
HOUR	Date Function	This function returns just the hour from a date time field.
IF	Conditions	Checks whether the condition is true or false and returns the value specified in the true argument if the condition is met or in the false argument if it is not.
INDEX	Cross-table	Returns a number for each child record that indicates its index.
INDEXOF	Text Function	Returns the position of the first character that matches the specified string.
ISNULL	Conditions	Checks the value for a specified field and if the value is NULL it will replace the NULL value with the value supplied in the field name or constant value argument.
LARGEST	Maths Function	Returns the largest value from the supplied values.
LAST	Cross-table	Returns the value from the last child record for each parent record.
LEFT	Text Function	Returns the number of characters specified from the left-hand end of a string.
LEN	Text Function	Returns the number of characters in the specified string.
LN	Maths Function	Returns the Natural Logarithm (base 2.71828) for the values in a numeric field.
LOG (Log10)	Maths Function	Returns the Common Logarithm (base 10) for the values in a numeric field.
LOWER	Text Function	Converts all the characters of the specified string into lower case.
MAXDIFF	Cross-table	For each parent, returns a number that represents the maximum difference between each of the child records.
MAXIMUM	Cross-table	Returns the maximum value of a field on the child table for each parent record.
MEDIAN	Cross-table	Returns the median value of the child records for each parent record.
MINDIFF	Cross-table	For each parent, returns a number that represents the minimum difference between each of the child records.
MINIMUM	Cross-table	Returns the minimum value of the field on the child table for each parent record.
MINUTE	Date Function	This function returns just the minute from a date time field.
MODE	Cross-table	Return the most frequent value of a field.
MONTH	Date Function	Returns just the month element of the date supplied.
MONTHSTO	Date Function	Returns the number of whole months between two given dates.
N_PER_TABLE	Cross-table	Allows N records to be selected from a table across a 1 to many join based on the value of a field at the many end.
NETWORKDAYS	Date Function	Returns the number of working days between two dates.
NOT	Query Domain	This function inverts the recordset that is created by the expression argument.
ONE_IN_N	Query Domain	When used within a query the function produces a recordset that contains a sample of records.
ONE_PER_TABLE	Query Domain	When used within a query the function produces a recordset that contains one record for each parent record defined by the relationship between the tables.
POWER	Maths Function	Raises the value in a numeric field to a specified power.
PROPER	Text Function	Uppercases the first letter of each separate word in the specified string.
QUARTER	Date Function	Returns the quarter value (1-4) from a date time field.
RAND	Maths Function	Returns a random number between 1 and the value specified.
REPLACE	Data Conversion	Returns a new string in which all occurrences of the match string are replaced by the replace string.
RIGHT	Text Function	Returns the number of characters specified from the right hand end of a string.
ROUND	Maths Function	Rounds the values in a numeric field.
SAMPLE_N	Query Domain	When used within a query the function produces a recordset of only a number of the records from the input recordset.
SAMPLE_P	Query Domain	When used within a query the function produces a recordset of only a percentage of the records from the input recordset.
SDEV	Cross-table	Returns a number that represents the standard deviation of a parents child records.
SECOND	Date Function	This function returns just the second from a date time field.
SECTORLABEL	Geographic	This function will extract the sector label from a postcode in standard UK format.
SIGN	Maths Function	Returns -1 for negative values in a field, 0 for zero values and 1 for positive values.
SIZE_SELECTION	Query Domain	When used within a query the function produces a recordset of the specified number of records.
SMALLEST	Maths Function	Returns the smallest value from the supplied values.
SOUNDEX	Text Function	Soundex is a phonetic algorithm for indexing names by sound, as pronounced in English.
SQRT	Maths Function	Returns the square root of a value in a numeric field.
SQUARE	Maths Function	Returns the square of a value in a numeric field.
STAT_5TH_PERCENTILE	Field Statistics	This function returns the 5th percentile value for every record in a table.
STAT_95TH_PERCENTILE	Field Statistics	This function returns the 95th percentile value for every record in a table.
STAT_IQ_RANGE	Field Statistics	Returns the same value for every record in a table and allows the resultant summary statistic for a field to be compared against each individual value for the field.
STAT_MAXIMUM	Field Statistics	This function returns the maximum value for every record in a table.
STAT_MEAN	Field Statistics	This function returns the mean value for every record in a table.
STAT_MEDIAN	Field Statistics	This function returns the median value for every record in a table.
STAT_MINIMUM	Field Statistics	This function returns the minimum value for every record in a table.
STAT_MODE	Field Statistics	This function returns the mode value for every record in a table.
STAT_RECORD_COUNT	Field Statistics	This function returns the record count value for every record in a table.
STAT_SDEV	Field Statistics	This function returns the standard deviation value for every record in a table.
STAT_SUM	Field Statistics	This function returns the sum value for every record in a table.
STRIP	Text Function	Removes all spaces from a string.
SUBSTRING	Text Function	Returns a number of characters from the middle of a string.
TABLE	Query Domain	The function increases the flexibility of the query language by mimicking the results of rolling a selection.
TABLERANK	Field Statistics	The Tablerank function returns the rank of each record within a table, based on the specified numeric field. This will allow you to sort and filter your data before ranking it, to calculate and flag the top or bottom records for that value.
TagSelection	Campaign Function	Function to allow querying of Tags by name or id.
TIMEPART	Date Function	Returns the time part from a datetime value.
TIMESTAMPADD	Date Function	Returns a new date value based on adding an interval to the date supplied.
TIMESTAMPDIFF	Date Function	Calculates the interval between two given dates. The interval timeframe can be changed depending on the fields need.
TOP_N	Query Domain	When used within a query the function produces a recordset that contains the top or bottom n records based on their values for a given numeric field.
UNICHR	Text Function	The function returns the Unicode character for the numeric value supplied.
UNICODE	Text Function	The function returns the Unicode character code for the character value supplied.
UPPER	Text Function	Uppercases all the characters in the string supplied.
WEEK	Date Function	Returns the week number of the year (1-52) in which a date falls as a number, allowing transactions to be grouped and reported on by fiscal week number.
WEEKDAY	Date Function	Returns the day of the week for the date supplied.
YEAR	Date Function	Returns the year portion of the date supplied.