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 
argmax

Evaluate the specified expression and return the dimension value that is in the same row as the maximum result.  Summary Functions 
argmin

Evaluate the specified expression and return the dimension value that is in the same row as the minimum result.  Summary Functions 
average

Evaluate the specified expression on all rows, and return the average.  Summary Functions 
bit_get

Determine if the specified position in the binary form of the specified integer is set.  Math Functions 
blake2

Return a cryptographic hash value for the given string.  Miscellaneous Functions 
build_timestamp

Returns the time at which the current cBase was built. 
Miscellaneous 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 subexpression by substituting another value.  Miscellaneous Functions 
chr

Returns a singlecharacter 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 nonnull 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 
cplan_custom_property

Return the value of a custom property of a cPlan.  Miscellaneous Functions 
cplan_system_property

Return the value of a system property of a cPlan.  Miscellaneous Functions 
current_date

Return the currentdate configured in a factory script. Requires the Measure Factory license.  FactorySpecific 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 DamerauLevenshtein 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. 
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 dimextension 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 
dimsum

Calculate the specified summary over the specified dimensions using the current working set. Return the sum of the 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 
extract_timestamp

Returns the time at which the data was extracted from the source, if set at build time. 
Miscellaneous 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 
fixed100

Convert a numeric value to a fixed100 value.  Conversion Functions 
floor

Compute the largest integer not greater than a number.  Math Functions 
format_date

Format a date value.  Date and Time Functions 
format_datetime

Format a datetime 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 namedgroup 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 yaxis by using existing xvalues and yvalues.  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 latitudelongitude 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 lowercase 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 lastmonthtodate range.  TimeSeries Functions 
lytd

Determine if a date or period is within a lastyeartodate range.  TimeSeries 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.  FactorySpecific Spectre Functions 
median

Evaluate the specified expression on all rows, and return the median.  Summary Functions 
miles

Determine the distance in miles between two latitudelongitude 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 monthtodate range.  TimeSeries 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 Perlstyle regexes.  String Functions 
regexp_subst

Take a string, match a Perlstyle regex to it, and replace the matches with a specified string.  String Functions 
regexp_value

Process as `integ_regexp_value()` but with full Perlstyle 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.  TimeSeries 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 
string_agg

Aggregates all values of a column that have a matching dimension.  Summary 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 
table_custom_property

Return the value of a custom property for a cBase.  Miscellaneous Functions 
table_system_property

Return the value of a system property for a cBase.  Miscellaneous 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.  TimeSeries Functions 
ts_current_half

Determine if a date or period is in the same half as the anchor.  TimeSeries Functions 
ts_current_month

Determine if a date or period is in the same month as the anchor.  TimeSeries Functions 
ts_current_quarter

Determine if a date or period is in the same quarter as the anchor.  TimeSeries Functions 
ts_current_tertile

Determine if a date or period is in the same tertile as the anchor.  TimeSeries Functions 
ts_current_week

Determine if a period is in the same week as the anchor.  TimeSeries Functions 
ts_current_year

Determine if a date or period is in the same year as the anchor.  TimeSeries Functions 
ts_half

Determine if a date or period is within a specified half in the same year as the anchor.  TimeSeries Functions 
ts_half_to_date

Determine if a date or period is within a halftodate range.  TimeSeries Functions 
ts_month

Determine if a date or period is within a specified month in the same year as the anchor.  TimeSeries Functions 
ts_month_to_date

Determine if a date or period is within a monthtodate range.  TimeSeries Functions 
ts_offset

Shift a timeseries anchor by a specified number of intervals.  TimeSeries Functions 
ts_quarter

Determine if a date or period is within a specified quarter in the same year as the anchor.  TimeSeries Functions 
ts_quarter_to_date

Determine if a date or period is within a quartertodate range.  TimeSeries Functions 
ts_recent_day

Determine if a date is a specified number of days before the anchor.  TimeSeries Functions 
ts_recent_half

Determine if a date or period is a specified number of halves before the anchor.  TimeSeries Functions 
ts_recent_month

Determine if a date or period is a specified number of months before the anchor.  TimeSeries Functions 
ts_recent_quarter

Determine if a date or period is a specified number of quarters before the anchor.  TimeSeries Functions 
ts_recent_tertile

Determine if a date or period is a specified number of tertiles before the anchor.  TimeSeries Functions 
ts_recent_week

Determine if a date or period is a specified number of weeks before the anchor.  TimeSeries Functions 
ts_recent_year

Determine if a date or period is a specified number of years before the anchor.  TimeSeries Functions 
ts_rolling_days

Determine if a date is within a rollingdays range.  TimeSeries Functions 
ts_rolling_halves

Determine if a date or period is within a rollinghalves range.  TimeSeries Functions 
ts_rolling_months 
Determine if a date or period is within a rollingmonths range.  TimeSeries Functions 
ts_rolling_quarters 
Determine if a date or period is within a rollingquarters range.  TimeSeries Functions 
ts_rolling_tertiles 
Determine if a date or period is within a rollingtertiles range.  TimeSeries Functions 
ts_rolling_weeks

Determine if a date or period is within a rollingweeks range.  TimeSeries Functions 
ts_rolling_years

Determine if a date or period is within a rollingyears range.  TimeSeries Functions 
ts_tertile

Determine if a date or period is within a specified tertile in the same year as the anchor.  TimeSeries Functions 
ts_tertile_to_date

Determine if a date or period is within a tertiletodate range.  TimeSeries Functions 
ts_week_to_date

Determine if a date or period is within a weektodate range.  TimeSeries Functions 
ts_year_to_date

Determine if a date or period is within a yeartodate range.  TimeSeries Functions 
ucount

Evaluate the specified expression on all rows and count how many different values there are.  Summary Functions 
upper

Return the uppercase 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 
value_or

Return the value of a column at the current input record if the column exists. Otherwise, return a fallback expression.  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 
weighted_median

Calculate the weighted median of a data sample.  Summary Functions 
weighted_percentile

Calculate the weighted percentile of a data sample.  Summary 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 yeartodate range.  TimeSeries Functions 
See also: