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 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, or to a specified unit. 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_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
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: