Skip to main content

No Matching Results

Name Type Description Syntax Found in Excel
combine Array Combines multiple lists into one. combine(list1, list2) No
filter Array Returns items from a dataset that fit a certain criteria. filter(dataset, condition) Yes
single Array Returns a one item list to a non-list of the same item or returns an optional error message if the list is empty or contains multiple items. single(list, [detailed_error_message]) No
text_to_list Array Converts a string with delimiters to a list of items. text_to_list(source_text, delimiter_text) No
transform_list Array Returns a list of items that have had an action performed to an initial dataset.. transform_list(dataset, expression) No
unique_by Array Returns a list with duplicates removed based on the expression provided. unique_by(dataset, expression) No
amount_from Commission Amount_from is one of a few commission-specific functions in Spiff which pulls the commission amount from a specific statement, optionally filtering obligations by payout rule or user. amount_from(date, plan_name, [payout_rule_name], [user_id]) No
amounts_from Commission Amounts_from is one of a few commission-specific functions in Spiff that pulls the commission amounts from a collection of statement obligations over a period of time, optionally filtering obligations by payout rule or user. amounts_from(start_date, end_date, plan_name, [payout_rule_name], [user_id]) No
marginal_payout Commission Marginal_payout is one of a few commission-specific functions in Spiff calculating tiered commission amounts in a marginal way – similar to how income tax works. marginal_payout(input_value, range_table, [initial_value], [return_column], ) No
marginal_payout_percent Commission Marginal_payout is one of a few commission-specific functions in Spiff. It calculates tiered commission amounts in a marginal way – similar to how income tax works. marginal_payout_percent(input_percent_value, range_table, [initial_percent_value], [return_column]) No
quota Commission Returns a single quota from a specified quota table. quota(quota_type, [target_date], [user]) No
quotas Commission Returns a filtered set of all quotas for the company utilizing users, quota types, and date range as intitial criteria. quotas(user_or_users, [quota_type], [from_date], [to_date]) No
tier_payout Commission Returns the matching range in a range table that a numerical value or variable falls into, and returns the specified column expression for that row. tier_payout(input_value, table_name, [tie_break_value], [return_value_column_nam]) No
convert_currency Currency Converts one currency to another. convert_currency(amount, [source_currency_code], destination_currency_code, conversion_date) No
currency Currency Converts one currency to another. currency(number_or_string, currency_ISO_code) No
beginning_of_day Date and Time Returns a value of 12:01 AM of the date provided. beginning_of_day(date_or_time) No
beginning_of_fiscal_quarter Date and Time Returns the first day of the fiscal quarter for the provided date. beginning_of_fiscal_quarter(date_or_time, [offset]) No
beginning_of_fiscal_year Date and Time Returns the first day of the fiscal year for the provided date. beginning_of_fiscal_year(date_or_time, [offset]) No
beginning_of_month Date and Time Returns the first day of the month for the provided date. beginning_of_month(date_or_time) No
beginning_of_period Date and Time Returns the first day of the statement period for the provided date. beginning_of_period(date_or_time) No
beginning_of_quarter Date and Time Returns the first day of the quarter for the provided date. beginning_of_quarter(date_or_time) No
beginning_of_year Date and Time Returns the first day of the calendar year for the provided date. beginning_of_year(date_or_time) No
date_between? Date and Time Returns a boolean value denoting if the candidate date is within specified range. date_between?(candidate_date, candidate_date, end_date) No
day Date and Time Returns the number of the day for the provided date. day(date_or_time) Yes
days_ago Date and Time Returns the date that is a number of days prior to the provided date. days_ago(date, number) No
days_to Date and Time Returns the number of days from one provided date to another days_to(date_or_time1, date_or_time2) No
end_of_day Date and Time Returns 11:59 PM of the date provided. end_of_day(date_or_time) No
end_of_fiscal_quarter Date and Time Returns the last day of the fiscal quarter for the provided date. end_of_fiscal_quarter(date_or_time, [offset]) No
end_of_fiscal_year Date and Time Returns the last day of the fiscal year for the provided date. end_of_fiscal_year(date_or_time, [offset]) No
end_of_month Date and Time Returns the last day of the month for the provided date. end_of_month(date_or_time) No
end_of_period Date and Time Returns the last day of the statement period for the provided date. end_of_period(date_or_time) No
end_of_quarter Date and Time Returns the last day of the quarter for the provided date. end_of_quarter(date_or_time) No
end_of_year Date and Time Returns the last day of the calendar year for the provided date. end_of_year(date_or_time) No
month Date and Time Returns the number of the month for the date provided. month(date_or_time) Yes
months_ago Date and Time Returns the date that is a number of months prior to the provided date. months_ago(date, number) No
months_to Date and Time Returns the number of months from one provided date to another months_to(date_or_time1, date_or_time_2) No
networkdays Date and Time Returns the number of work days between the dates provided. networkdays(date_or_time1, date_or_time2, [holiday_date_or_time1, holiday_date_or_time2, …]) Yes
now Date and Time Returns the value of the date when calculation of the variable occurs. now Yes
periods_ago Date and Time Returns the statement period that occured a specified number of periods ago from the provided date. periods_ago(date_or_time, number) No
quarter Date and Time Returns the number of the quarter for the date provided. quarter(date_or_time) No
weekday Date and Time Returns the day of the week of the provided date. weekday(date_or_time, return_type) Yes
year Date and Time Returns the year of the provided date. year(date_or_time) Yes
error Information Displays a specified error message when run. error(value) No
PlanName Information Returns the plan name for the rep being calculated. PlanName No
RepCurrency Information Returns the currency of the rep being calculated. RepCurrency No
RepId Information Returns the Id of the rep being calculated. RepId No
RepName Information Returns the name of the rep being calculated. RepName No
all? Logical Returns TRUE if a set condition is true for all items in a dataset. all?(dataset, condition) No
and Logical Returns TRUE if all items from a dataset are TRUE. and(arg1, arg2) Yes
any? Logical Returns TRUE if a set condition is true for any items in a dataset. any?(dataset, expression) No
exists Logical Returns TRUE if a specified item exists. exists(value) No
if Logical An initial condition is calculated to be TRUE or FALSE, and the corresponding function is run. if(condition, true_value, false_value) Yes
iferror Logical Displays a specified error message if the expression is found to be an error. If not, it runs the function as-is. iferror(expression, error_expression) Yes
isnull Logical Returns TRUE if specified value is null. isnull(value) No
isnumber Logical Returns TRUE if specified value is a number. isnumber(value) Yes
istext Logical Returns TRUE if specified value is text. istext(value) Yes
let Logical Let() applies a temporary name to a specified field, and allows for functions to be written that allow usage of the temporary name in calculations – useful for comparing one field of a single list item to the same field for the entire dataset. let(alias, value, expression) Yes
not Logical Changes the boolean value to be the opposite of the provided value. not(condition) Yes
or Logical Returns TRUE if any condition provided is TRUE. or(arg1, arg2) Yes
at Lookup and Reference Returns an item from a list at a specified index point. at(list, index) No
choose Lookup and Reference Returns an item from a list at a specified index point. choose(index_number, list) Yes
range_lookup Lookup and Reference Returns a specified value from a Range Table by providing an input value and a return column. range_lookup(lookup_value, table_name, [tie_break_below], [return_value_column_name]) No
sort Lookup and Reference Returns a sorted list based on the criteria provided. sort(dataset, expression, [direction]) Yes
vlookup Lookup and Reference Returns a value from a Lookup Table based upon input provided, as well as specified return column. vlookup(lookup_value, table_name, [return_value_column_name], [exact_match], [default_value]) Yes
abs Math Returns the absolute value of a number. abs(number) Yes
ceiling Math Returns a rounded-up value of a number to a set significance. ceiling(number, [significance]) Yes
float Math Returns a floating point number from a number or a string. float(number, [significance]) No
floor Math Returns a rounded-down value of a number to a set significance. floor(number, [significance]) Yes
max Math Returns the greatest value from the values provided. max([value1, value2, …]) Yes
min Math Returns the least greatest value from the values provided. min([value1, value2, …]) Yes
mod Math Returns the remainder from a division of two values. mod(number, divisor) Yes
mround Math Rounds a number to the desired multiple. mround(number, multiple) Yes
number Math Converts a provided value into a number. number(number_string) No
power Math Raises a number to the power of another number. power(number, power)ORnumber^power Yes
round Math Rounds a provided number to the set number of significant digits. round(number, num_digits) Yes
sum Math Returns a value calculated via the addition of each item in a list. sum(dataset, [criteria], [expression]) Yes
sumif Math Returns a value calculated via the addition of each item in a list that fit a certain criteria. sumif(dataset, criteria, expression) Yes
trunc Math Truncates a value to specified significance. trunc(number, [precision]) Yes
count Statistical Determines the total number of entries in a list or dataset. count(dataset) Yes
countif Statistical Determines the total number of entries in a list or dataset that fulfill a set criteria. countif(dataset, condition) Yes
rank Statistical Returns the rank of a number in a list of numbers – its size relative to other values in the list. rank(target, [dataset], [rank_by]) Yes
rank_avg Statistical Returns the average rank of a number in a list of numbers – its size relative to other values in the list, divided by the total values in the list. rank_avg(target, [dataset], [rank_by]) No
direct_manager_of Team Returns a list of users that fall under a specified users hierarchy. direct_manager_of(user_id, [start_date], [end_date]) No
direct_member_of Team Returns a list of all teams that a specified user is a member of. direct_member_of(user_id, [start_date], [end_date]) No
in_team? Team Returns TRUE if a specified user is a member of the provided team. in_team?(team, user_id, date) No
manager_of Team Returns a list of teams that the specified user is a manger of. manager_of(user_id, [start_date], [end_date]) No
member_of Team Returns a list of teams that the specified user is a member of. member_of(user_id, [start_date], [end_date]) No
users_in_team Team Returns a list of member of a specified team. users_in_team(team, [start_date], [end_date]) No
concat Text Merges multiple values from a list into a single value. concat(list) Yes
contains Text Returns TRUE if specified value is found within the source value. contains(source_text, search_text) No
left Text Provides a subset of string by returning a specified number of characters, going from left to right. left(text_string, num_chars) Yes
len Text Returns the number of characters in a string. len(text_string) Yes
lower Text Converts a source string into all lowercase characters. lower(text_string) Yes
mid Text Provides a subset of string by returning a specified number of characters, starting from a specified point. mid(text_string, start_char, num_chars) Yes
proper Text Converts a value into proper text by first converting any uppercase letters to lowercase, then capitalizes each word proper(text, [downcase_all]) Yes
right Text Provides a subset of string by returning a specified number of characters, going from right to left right(text_string, num_chars) Yes
search Text Returns the numbered place within a source string that the provided search value can be found. search(search_text, source_text, [starting_number]) Yes
trim Text Removes whitespace from both ends of a string. trim(string) Yes
upper Text Converts a source string into all uppercase characters. upper(text_string) Yes