Spiff Function List
Spiff supports the most commonly used formulas used in many modern spreadsheet solutions. Spiff also provides access to a wide array of commission-specific functions, allowing for easier logic creation through streamlined formulas.
Below is a list of all currently available functions found within Spiff, categorized by type. Brackets – [ ] – indicate optional parameters within function syntax, allowing for added functionality when used. Examples of function usage and supplementary information can be found within the Commission Designer.
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 |