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 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 |
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 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. |
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 |
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 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 |
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. | 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 |
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 year-to-date range. | Time-Series Functions |
See also: