Alphabetized Functions for Spectre

The Spectre language has over 200 different functions. The following table provides a list of all functions. Click the function name for more information, including syntax and examples of usage.

Function	Description	Function Type
abs	Compute the absolute value of a number.	Math Functions
age	Return the age of a person with a specified birth date on a specified day.	Date and Time Functions
all	Return true if the condition is true on all rows.	Predicate Functions
and	Provide shorthand for a sequence of binary `and` operations.	Predicate Functions
any	Evaluate the specified expression on a randomly selected row and return that value as the result.	Summary Functions
arccos	Compute the arc cosine (in radians) of a number.	Math Functions
arcsin	Compute the arc sine (in radians) of a number.	Math Functions
arctan	Compute the arc tangent (in radians) of a number.	Math Functions
average	Evaluate the specified expression on all rows, and return the average.	Summary Functions
calc	Define a named expression, or substitute with the specified named expression, defined earlier.	Pseudo Functions
calc_custom_property	Return the value of a custom property of a calculation.	Miscellaneous Functions
calc_system_property	Return the value of a system property of a calculation.	Miscellaneous Functions
calc_label	Return the label of a calculation.	Miscellaneous Functions
capitalize	Uppercase the first character of each word in the specified string and lowercase the other characters.	String Functions
case	Evaluate one of several statements, depending on a sequence of conditions.	Logic Functions
catch	Handle an error in a sub-expression by substituting another value.	Miscellaneous Functions
chr	Returns a single-character string with the character represented by the number value in the current character set (ASCII or EBCDIC).	String Functions
ceil	Compute the smallest integer not less than a number.	Math Functions
coalesce	Return the first non-null value in a list of values, or null if all values are null.	Miscellaneous Functions
column_custom_property	Return the value of a custom property of a column.	Miscellaneous Functions
column_system_property	Return the value of a system property of a column.	Miscellaneous Functions
concat	Concatenate a sequence of strings.	String Functions
const	Evaluate the specified expression on a randomly selected row and return that value as the result.	Summary Functions
cos	Compute the cosine of an angle (in radians).	Math Functions
count	Return the number of rows.	Summary Functions
current_date	Return the current-date configured in a factory script. Requires the Measure Factory license.	Factory-Specific Spectre Functions
current_file	Return the project location of the current file.	Miscellaneous Functions
current_user	Return the username for the current user.	Miscellaneous Functions
damerau	Compute the Damerau-Levenshtein distance between two strings.	String Functions
date	Parse a string as a date, return a date for year, month, and day integers, or return a date for a datetime type element.	Conversion Functions
date_from_date_number	Return the date which is a number of days after 1899/12/30.	Date and Time Functions
date_number	Return the number of days between 1899/12/30 and a date.	Date and Time Functions
datetime	Return a value of type datetime from a string or from a date and seconds. NOTE: Added in Spectre 7.0(35), but not all clients recognize the datetime data type.	Conversion Functions
day	Return the day of the month for a date.	Date and Time Functions
day_name	Return the name of the day of the week containing a date.	Date and Time Functions
dimaverage	Calculate the specified summary over the specified dimensions using the current working set, then average the values.	Dimension Functions
dimcount	Count the number of values for the specified dimension.	Dimension Functions
dimgeomean	Calculate the specified summary over the specified dimensions using the current working set. Return the geometric mean over those summary values.	Dimension Functions
dimintercept	Calculate the dim-extension of intercept().	Dimension Functions
dimmax	Calculate the specified summary over the specified dimensions using the current working set. Return the maximum of those values.	Dimension Functions
dimmedian	Calculate the specified summary over the specified dimensions using the current working set. Return the median of the values.	Dimension Functions
dimmin	Calculate the specified summary over the specified dimensions using the current working set. Return the minimum of the values.	Dimension Functions
dimmode	Calculate the specified summary over the specified dimensions using the current working set. Return the value that occurs most often.	Dimension Functions
dimpercentile	Calculate the specified summary over the specified dimensions using the current working set. Return the specified percentile of the values.	Dimension Functions
dimslope	Calculate the specified summary over the specified dimensions using the current working set. Return the least square linear regression slope for the summary values.	Dimension Functions
dimstddev	Calculate the specified summary over the specified dimensions using the current working set. Return the Sample Standard Deviation for the summary values.	Dimension Functions
dimstddevp	Calculate the specified summary over the specified dimensions using the current working set. Return the Population Standard Deviation for the summary values.	Dimension Functions
dmetaphone	Perform a double metaphone phonetic string comparison.	String Functions
double	Convert a numeric value to a double.	Conversion Functions
elapsed_time	Return a string for the elapsed time between two dates and times, or between two datetime values.	Date and Time Functions
elapsed_time_in_seconds	Return a number for the elapsed time in seconds between two dates and times specified as integers, or between two datetime values.	Date and Time Functions
error	Emit an error.	Miscellaneous Functions
eval	Parse the specified string as an expression, and evaluate it.	Pseudo Functions
exp	Compute the value of e (Euler's number) raised to the power of a number.	Math Functions
extract	Return an integral element of a date or period.	Date and Time Functions
file_contents	Return the contents of a file as a string.	Miscellaneous Functions
filter	Filter the records that a summary expression is run over.	Miscellaneous Functions
first	Evaluate the specified expression on the first record in the working set.	Summary Functions
floor	Compute the largest integer not greater than a number.	Math Functions
format_date	Format a date value.	Date and Time Functions
format_number	Format a number.	Conversion Functions
format_period	Format a period value.	Date and Time Functions
format_time	Format a time value.	Date and Time Functions
format_timevalue	Return a string representation of the integer time.	Conversion Functions
geomean	Evaluate the specified expression on all rows, and return the geometric mean.	Summary Functions
group	Simplify to the named-group predicate for the specified dimension and group name	Predicate Functions
group_match	Determine if the current user is in a specified group.	Miscellaneous Functions
harmean	Evaluate the specified expression on all rows, and return the harmonic mean.	Summary Functions
hours	Return the number of hours in a time string.	Date and Time Functions
hyperloglog	Return an estimate of the number of unique combinations of [v1], [v2], and other values.	Summary Functions
if	Conditionally evaluate another expression.	Logic Functions
index	Find a string within another string, and return its position.	String Functions
info	Evaluate whether the specified expression has the same result on every row, and return that; otherwise, return an error.	Summary Functions
input	Select a named input for the sub expression to run on.	Miscellaneous Functions
insert_decimal	Insert a decimal into a number.	Math Functions
integ_regexp	Perform a `regexp()` as it exists in Integrator	String Functions
integ_regexp_value	Perform a `regexp_value()` as it exists in Integrator	String Functions
intercept	Calculate the point at which a line will intersect the y-axis by using existing x-values and y-values.	Math Functions
integer	Convert a numeric value to an integer.	Conversion Functions
is_error	Return whether or not the specified value is an error value.	Predicate Functions
is_in	Return whether or not the specified value is in the subsequent list of values.	Predicate Functions
is_in_list	Provide a shorthand for `is_in()` on a delimited list of strings.	Predicate Functions
is_null	Return whether or not the specified value is a null value.	Predicate Functions
is_unknown	Return whether or not the specified value is an unknown value.	Predicate Functions
isnull	Return whether or not the specified value is a null value.	Predicate Functions
kahan_sum	Perform as the `sum()` function, but with countermeasures against floating point imprecision.	Summary Functions
kilometers	Determine the distance in kilometers between two latitude-longitude pairs.	Miscellaneous Functions
kurtosis	Evaluate the specified expression on all rows, and return the sample excess kurtosis.	Summary Functions
last	Evaluate the specified expression on the last record in the working set.	Summary Functions
length	Return the length of a string.	String Functions
levenshtein	Compute the Levenshtein distance between two strings.	String Functions
ln	Compute the natural (base e) logarithm of a number.	Math Functions
log10	Compute the common (base 10) logarithm of a number.	Math Functions
lower	Return the lower-case variant of the specified string.	String Functions
lpad	Pad the beginning of the specified string with the specified characters until the result is the specified length.	String Functions
ltrim	Return a string after removing specified leading characters from the string.	String Functions
lymtd	Determine if a date or period is within a last-month-to-date range.	Time-Series Functions
lytd	Determine if a date or period is within a last-year-to-date range.	Time-Series Functions
map_filename	Return the specified string with characters that are not valid in file names replaced by underscores.	String Functions
match	Return a number indicating which of several values is equal to a target value.	Logic Functions
max	Return the largest value of a list of numbers.	Math Functions
max	Evaluate the specified expression on all rows, and return the maximum value.	Summary Functions
measure	Return the summary value for the named measure as part of a measure calculation in a factory script. Requires the Measure Factory license.	Factory-Specific Spectre Functions
median	Evaluate the specified expression on all rows, and return the median.	Summary Functions
miles	Determine the distance in miles between two latitude-longitude pairs.	Miscellaneous Functions
min	Return the smallest value of a list of numbers.	Math Functions
min	Evaluate the specified expression on all rows, and return the minimum value.	Summary Functions
minutes	Return minutes from a standard time string.	Date and Time Functions
mod	Compute the remainder of dividing one number by another.	Math Functions
mode	Returns the value that occurs most often.	Summary Functions
month	Return the month number for a date or period.	Date and Time Functions
month_name	Return the name of the month containing a date.	Date and Time Functions
mtd	Determine if a date or period is within a month-to-date range.	Time-Series Functions
mul	Evaluate the specified expression on every row, and multiply the results.	Summary Functions
not	Apply the unary operator to negate a Boolean value.	Predicate Functions
notnull	Determine if a value is not null.	Miscellaneous Functions
now	Return a datetime value representing the start date and time of the script that is running.	Date and Time Functions
offset	Shift a date or period by a specified number of intervals.	Date and Time Functions
on_previous_row	Run the specified expression on the values from the previous row.	Miscellaneous Functions
or	Shorthand for a sequence of binary `or` operations.	Predicate Functions
param	Return the value of a parameter.	Pseudo Functions
param_contains	Determine if a parameter contains a specified value.	Pseudo Functions
param_match	Determine if a parameter matches the value of a column.	Pseudo Functions
parent	Run a summary expression over all records in the window.	Miscellaneous Functions
parse_double	Parse a string as a double. (Previously named parse_float.)	Conversion Functions
parse_fixed100	Parse a string as a fixed100.	Conversion Functions
parse_float	Parse a string as a double.	Conversion Functions
parse_integer	Parse a string as an integer.	Conversion Functions
pct_var	Compute the difference between one number and another as a percentage.	Math Functions
percentile	Return the specified percentile over the specified column. The column must be numeric.	Summary Functions
period	Parse a string as a period.	Conversion Functions
period	Return the period containing a date.	Date and Time Functions
period_calendar	Generate a period with the same calendar as another period.	Conversion Functions
period_end	Return the last day in a period. Return the last day in an interval containing a period.	Date and Time Functions
period_start	Return the first day in a period. Return the first day in an interval containing a period or date.	Date and Time Functions
period_type	Return the string name of period type for the named period.	Conversion Functions
pow	Compute one number raised to the power of another.	Math Functions
previous	Return the expression using the detail records for the prior row.	Window Functions
quarter	Return the quarter number for a date or period.	Date and Time Functions
random	Compute a random real number between 0 and 1.	Math Functions
rank	Rank the result of the specified expression within the breaks for the specified dimensions.	Dimension Functions
rank_in	Rank the result of the specified expression using the specified sort and break.	Dimension Functions
regexp	Process as `integ_regexp()` but with full Perl-style regexes.	String Functions
regexp_subst	Take a string, match a Perl-style regex to it, and replace the matches with a specified string.	String Functions
regexp_value	Process as `integ_regexp_value()` but with full Perl-style regexes.	String Functions
replace	Replace every occurrence of a string within another string.	String Functions
reverse	Reverse the position of the characters in a string.	String Functions
rindex	Find the position of the last occurrence of the specified substring in the specified string.	String Functions
rolling	Determine if a date or period is within a rolling month range.	Time-Series Functions
round	Compute a number rounded to the nearest integer, or to a specified unit.	Math Functions
row_count	Return the number of rows in the current window.	Window Functions
row_number	Return the row number in the current window of the cell being filled.	Window Functions
rpad	Pad the end of the specified string with the specified characters until the result is the specified length.	String Functions
rsubstr	Reverse substring.	String Functions
rtrim	Return a string after removing specified trailing characters from the string.	String Functions
same_period	Return a period value for a date using the source calendar and period type.	Conversion Functions
scan	Parse the string into separate segments based on the specified delimiters.	String Functions
seconds	Return the seconds from a standard time string.	Date and Time Functions
sign	Compute the sign (-1, 0, or 1) of a number.	Math Functions
sin	Compute the sine of an angle (in radians).	Math Functions
skewness	Evaluate the specified expression on all rows and return the sample skewness.	Summary Functions
slope	Compute the slope of the linear regression line through the specified data points.	Summary Functions
some	Return true if the condition is true on at least one row.	Predicate Functions
soundex	Perform phonetic string mapping.	String Functions
sqrt	Compute the square root of a number.	Math Functions
squash	Eliminate records by summarizing values.	Summary Functions
squash_right	Eliminate records by summarizing values from columns on the right.	Summary Functions
startswith	Determine if one string starts with another.	String Functions
stddev	Evaluate the specified expression on all rows, and return the Sample Standard Deviation.	Summary Functions
stddevp	Evaluate the specified expression on all rows, and return the Population Standard Deviation.	Summary Functions
string	Convert a value to a string.	Conversion Functions
substr	Return a string that is a portion of another string.	String Functions
sum	Evaluate the specified expression on every row, and add the results.	Summary Functions
switch	Evaluate one of several statements, depending on the value of an expression.	Logic Functions
tan	Compute the tangent of an angle (in radians).	Math Functions
time_string	Return the time portion of the datetime element.	Conversion Functions
time_value	Convert a time string into the number of seconds since midnight.	Date and Time Functions
timevalue	Convert a time string into the number of seconds since midnight.	Date and Time Functions
today	Return the date when the script started to execute.	Date and Time Functions
token_count	Return a count of the tokens in the string using the specified delimiter.	String Functions
top	Run a summary expression over all records in the cPlan.	Miscellaneous Functions
translate	Substitute the indicated characters with the specified characters.	String Functions
trunc	Compute the nearest integer not greater in magnitude than a number.	Math Functions
ts_current_day	Determine if a date is the same as the anchor.	Time-Series Functions
ts_current_half	Determine if a date or period is in the same half as the anchor.	Time-Series Functions
ts_current_month	Determine if a date or period is in the same month as the anchor.	Time-Series Functions
ts_current_quarter	Determine if a date or period is in the same quarter as the anchor.	Time-Series Functions
ts_current_tertile	Determine if a date or period is in the same tertile as the anchor.	Time-Series Functions
ts_current_week	Determine if a period is in the same week as the anchor.	Time-Series Functions
ts_current_year	Determine if a date or period is in the same year as the anchor.	Time-Series Functions
ts_half	Determine if a date or period is within a specified half in the same year as the anchor.	Time-Series Functions
ts_half_to_date	Determine if a date or period is within a half-to-date range.	Time-Series Functions
ts_month	Determine if a date or period is within a specified month in the same year as the anchor.	Time-Series Functions
ts_month_to_date	Determine if a date or period is within a month-to-date range.	Time-Series Functions
ts_offset	Shift a time-series anchor by a specified number of intervals.	Time-Series Functions
ts_quarter	Determine if a date or period is within a specified quarter in the same year as the anchor.	Time-Series Functions
ts_quarter_to_date	Determine if a date or period is within a quarter-to-date range.	Time-Series Functions
ts_recent_day	Determine if a date is a specified number of days before the anchor.	Time-Series Functions
ts_recent_half	Determine if a date or period is a specified number of halves before the anchor.	Time-Series Functions
ts_recent_month	Determine if a date or period is a specified number of months before the anchor.	Time-Series Functions
ts_recent_quarter	Determine if a date or period is a specified number of quarters before the anchor.	Time-Series Functions
ts_recent_tertile	Determine if a date or period is a specified number of tertiles before the anchor.	Time-Series Functions
ts_recent_week	Determine if a date or period is a specified number of weeks before the anchor.	Time-Series Functions
ts_recent_year	Determine if a date or period is a specified number of years before the anchor.	Time-Series Functions
ts_rolling_days	Determine if a date is within a rolling-days range.	Time-Series Functions
ts_rolling_halves	Determine if a date or period is within a rolling-halves range.	Time-Series Functions
ts_rolling_months	Determine if a date or period is within a rolling-months range.	Time-Series Functions
ts_rolling_quarters	Determine if a date or period is within a rolling-quarters range.	Time-Series Functions
ts_rolling_tertiles	Determine if a date or period is within a rolling-tertiles range.	Time-Series Functions
ts_rolling_weeks	Determine if a date or period is within a rolling-weeks range.	Time-Series Functions
ts_rolling_years	Determine if a date or period is within a rolling-years range.	Time-Series Functions
ts_tertile	Determine if a date or period is within a specified tertile in the same year as the anchor.	Time-Series Functions
ts_tertile_to_date	Determine if a date or period is within a tertile-to-date range.	Time-Series Functions
ts_week_to_date	Determine if a date or period is within a week-to-date range.	Time-Series Functions
ts_year_to_date	Determine if a date or period is within a year-to-date range.	Time-Series Functions
ucount	Evaluate the specified expression on all rows and count how many different values there are.	Summary Functions
upper	Return the upper-case variant of the specified string.	String Functions
user_property_contains	Determine if a user property contains a specified value.	Window Functions
value	Return the value of a column at the current input record.	Miscellaneous Functions
weekday	Return the day of the week for the specified date.	Date and Time Functions
week_date	Returns the ISO 8601 week date representation for the specified date.	Date and Time Functions
year	Return the year containing a date or period.	Date and Time Functions
year_day	Return the day of the year for a date.	Date and Time Functions
ytd	Determine if a date or period is within a year-to-date range.	Time-Series Functions