Functions for Segments

Functions let you apply standard mathematical operators to your segment. You can also combine different functions and database fields in an expression.

Note: Before you can add a function, you must add a filter. See Add Filters to a Segment.

After you drag a function to the expression on the Edit Expression window, you must enter the variables used in the function. You can either type the variables in the Expression field or, if the variable is an item listed in the Available Columns/Functions field, you can drag the item into the function of the expression.

Functions

Expression	Description
Aggregate Functions	Select from the following to use an aggregate function in the expression. Aggregate functions are available only when defining group filters. See Create a Group Filter.
avg(expr)	This function computes an average value for the data set `expr`.
count(expr)	This function returns the number of rows included in the data set `expr`.
first(expr, sort column1[, sort column2][,...])	This function returns the first value in the data set `expr` based on the order of the specified sort columns. For example, the expression `first(threads.note, threads.entered, threads.seq)` returns the first thread note in the data set based on time entered and thread sequence.
group_concat(expr, separator[, sort column 1, ASC\|DESC][, sort column 2, ASC\|DESC][,...])	This function lets you combine multiple values from data set `expr` into a delimited list in a single row. The list is delimited by `separator` and ordered by the specified sort columns. For example, the expression `group_concat(threads.note, ',', threads.entered, ASC)` returns a comma-delimited list of thread notes ordered by time entered ascending.
last(expr, sort column1[, sort column2][,...])	This function returns the last value in the data set `expr` based on the order of the specified sort columns. For example, the expression `last(threads.note, threads.entered, threads.seq)` returns the last thread note in the data set based on time entered and thread sequence.
max(expr)	This function returns the largest numerical value, the last string in alphabetical order, or the latest date in the data set `expr`.
min(expr)	This function returns the smallest numerical value, the first string in alphabetical order, or the earliest date in the data set `expr`.
sum(expr)	This function returns the sum of the values in the data set `expr`.
sum_distinct(expr, reference)	This function returns the sum of distinct values in an expression for a particular record (reference) rather than for all records of the same type in a table. For example, if you want to calculate the sum of all quotas for a particular sales person, you could add a function of `sum_distinct(sa_period2accts.quota, sa_period2accts.acct_id)`.
stddev(expr)	This function returns the standard deviation of `expr`. For example, the expression `stddev(answers.solved_count)` returns the standard deviation of all the values in the `solved_count` column of the Answers `(answers)` table.
String Functions	Select from the following to use a string function in the expression.
concat(str1,str2)	This function combines input character strings from multiple expressions into a single output string. Two or more expressions can be appended with this function. For example, the expression `concat('www.','global','.com')` returns the value www.global.com.
instr(str, substr)	This function returns the numeric position (in characters) of the initial occurrence of string `substr` within string `str`. For example, the expression `instr('globalcellphones.com','cell')` returns the value `7`.
length(str)	This function returns the length (in characters) of the string. For example, the expression `length('cell phones')` returns the value `10`.
lower(str)	This function returns string `str` in all lowercase characters. For example, the expression `lower('Global.COM')` returns the value `global.com`.
ltrim(str)	This function returns the string `str` without leading spaces. For example, the expression `ltrim(' Cell phone')` returns the value `Cell phone`.
lpad(str1, X, str2)	This function returns `str1` padded on the left with `str2` until `str1` is `X` characters long. For example, the expression `lpad('phone', 10, '@')` returns the value `@@@@@phone`.
rpad(str1, X, str2)	This function returns `str1` padded on the right with `str2` until `str1` is `X` characters long. For example, the expression `rpad('phone', 10, '@')` returns the value `phone@@@@@`.
rtrim(str)	This function returns the string `str` without trailing spaces. For example, the expression `rtrim('agarfield@global.com ')` returns the value `agarfield@global.com`.
substr(str, start_pos, [length])	This function returns a portion of the string `str` starting at the character defined by `start_pos` (an integer) and ending at the character defined by `length` (an integer). If `length` is not indicated, it returns the remainder of the string.
to_char(expr)	This function converts the numeric `expr` to a character string.
to_number(str)	This function converts the character string `str` to a numeric. If the `str` is not numeric, this function returns zero. If `str` is a combination of numbers and other characters and begins with a number, this function returns only the initial numeric portion. For example, `to_number('123ABC')` returns 123.
upper(str)	This function returns string `str` in all uppercase characters. For example, the expression `upper('global.COM')`returns the value `GLOBAL.COM`.
Date Functions	Select from the following to use a date function in the expression.
date_add(date, units, interval, round)	This function returns the value of `date` plus a specified amount of time where date is a datetime type column or a literal string in the format YYYY-MM-DD or YYYY-MM-DD HH:MI:SS. The amount of time to add is specified by `units` and `interval`, where `units` must be an integer specifying the number of intervals to add, and `interval` can be SECONDS, MINUTES, HOURS, DAYS, WEEKS, MONTHS, or YEARS. To round the result to the beginning of the specified interval, set the `round` argument to 1. Otherwise, set `round` to 0 and the result is not rounded. For example, the expression `date_add('2003-11-25 22:35:00', 48, HOURS, 1)` returns the value `2003-11-27 22:00:00`.
to_date(str, format)	This function converts the string `str` in the format specified by `format` to a date value. For example, the function `to_date('19981231', 'YYYYMMDD')` returns a date of 12/31/1998. Unlike date_format, the to_date function supports only the following date formats. DD MM YYYY HH24:MI:SS DD MM YYYY HH:MI:SS AM DD MON YY DD MON YYYY DD-MM-YY DD-MM-YYYY DD.MM.YY DD.MM.YYYY DD/MM/YY DD/MM/YYYY DD/MM/YYYY HH:MI:SS AM MM-DD-YY MM-DD-YYYY MM/DD/YY MM/DD/YYYY MON DD YY MON DD YYYY MON DD YYYY HH:MI AM MON DD YYYY HH:MI:SS AM YY.MM.DD YY/MM/DD YYMMDD YYYY-MM-DD YYYY-MM-DD HH24:MI:SS YYYY-MM-DDTHHH24:MI:SS YYYY.MM.DD YYYY/MM/DD YYYYMMDD
date_format(date, format)	This function converts `date` from a datetime data type to a string and reformats `date` to match the format specified by `format`, where date is a datetime type column or a literal string in the format YYYY-MM-DD or YYYY-MM-DD HH:MI:SS. For example, the expression `date_format('2003-11-25 22:25:00', 'DAY, MONTH DD, YYYY, HH12:MI PM')` returns the value `Tuesday, November 25, 2003 10:25 PM`.
date_diff(date, date)	This function returns the number of seconds occurring between two dates. For example, the expression `date_diff('2009-11-26 22:25:00', '2009-11-25 10:30:00’)` returns the value `129300`. Another example is date_diff(sysdate(),incidents.updated). This expression returns the number of seconds between the current time and the time that incidents were last updated. Note: To change the format of the output, use the time_format function. For example, `time_format(date_diff('2009-11-26 22:25:00', '2009-11-25 10:30:00’), 'HH24h MIm SSs')` returns the value `35h 55m 00s`.
date_trunc(date, units)	This function truncates `date` to the unit specified by `units`. For example, the expression `date_trunc('2003-11-25 22:25:10', HOURS)` returns the value `2003-11-25 22:00:00`.
sysdate()	This function returns the current system date in the format YYYY-MM-DD HH:MI:SS.
time_format (seconds, format)	This function converts `seconds` to the specified time format. For example, the expression `time_format(86610,'HH24 MI SS')` returns the value `24 03 30`.
date_group(expr, format)	This function groups records together according to a date range that you specify, and includes rows for date ranges that have no associated records. If you do not want to return rows for date ranges with no records, you could use date_format instead. For example, if you have incidents created in January and March, but none in February, and create a report with the expression for column A set to `date_group(incidents.created, ‘YYYY-MM’)`, and the expression for Column B set to `count(*)`, the report would output: `2006-01 10` `2006-02 0` `2006-03 15` If you changed the expression in Column A to `date_format(incidents.created, ‘YYYY-MM’)` to use the date_format function instead of date_group, the report would output similar data, but would not return a row for February. This function can be used only when defining group filters. See Create a Group Filter.
Logical Functions	Select from the following to use a logical function in the expression.
if(expression, then result, else result)	This function returns the then result if expression is true, or returns the else result if expression is false. For example, the expression `if(incident.c$field=1, 'Yes', 'No')` returns Yes for incidents where the value of c$field is 1. Otherwise, No is returned. You can use IS NULL and IS NOT NULL as part of the expression. For example, you could use the expression `if(incident.c$field IS NOT NULL, 'Yes', 'No')`.
decode(expression, test_value_1, result_1[, test_value_2, result_2][, default])	This function compares `expression` to each `test value` in order and returns `result` for the first `test value` that `expression` matches. If `expression` does not match any `test value`, `default` is returned. If `default` is undefined, a null value is returned. The decode function can perform table lookups, letting you use text strings as test values rather than requiring coded values. For example, you can use Review as a test value for the expression `faqs.status` even though the table contains code value in the `status` column.
nvl(expr1, expr2)	If the value `expr1` is null, this function returns the value `expr2`. However, if the value `expr1` is not null, then the value `expr1` is returned. The value of `expr2` must match the data type of `expr1`.
Math Functions	Select from the following to use a math function in the expression.
bitand(X, Y)	This function returns the bitwise AND of two integers, `X` and `Y`.
ceiling(X)	This function returns the smallest integer value greater than or equal to `X`.
floor(X)	This function returns the largest integer less than or equal to `X`.
power(X, Y)	This function returns the value of `X` to the power of `Y`. For example, `power(2,3)` would return `8`.
rand()	This function returns a random number between 0 and 1, containing up to nine decimal places.
round(X, D)	This function returns the value `X` rounded to the nearest number with `D` decimals. For example, `round(5.55555, 2)` returns the value `5.56`.
truncate(X, D)	This function returns the value `X` truncated to the `D` decimal places. For example, the expression truncate`(5.55555, 2)` returns the value `5.55`.
Currency Functions	Select the following to use a currency function in the expression.
to_currency(expr, str)	This function converts `expr` to a value in `str` currency. The value is returned as an amount in the staff member’s default currency. The conversion rate used for this function is the exchange rate defined in the Exchange Rates `(exchange_rates)` table. For example, for a staff member with a default currency of USD, `to_currency(opportunities.mgr_value, ‘EUR’)` returns an amount in USD that is equal to the number in the `mgr_value` converted to Euros.