Use the ↓ down arrow again to select an option, and then <tab> or <enter>.
Practice auto-completion by starting from a different character, such as letter G:
======= General Structure =======
Commission formulas are comprised of statements.
Statements can be variable declarations, function calls, data queries, etc.
You can separate statements explicitly using separator lines and/or empty lines.
This makes logic more readable and gives you the opportunity to add comments.
It also defines the boundary of each block in an explicit way.
This example uses separator lines (starting with '--') between statements.
Optionally, you can include comments (ex: '-- Default case --').
This example uses empty lines between statements.
You can use a combination of separator and empty lines.
However, separator and empty lines are optional.
Although less readable, the following logic will execute the same way.
If a statement is long, it will visually "fold" over multiple lines in the editor.
However, you want to manually add return carriages within statements for readability.
This example shows 2 statements with manually added return carriages.
Sales Cookie will attempt to automatically identify statement boundaries.
Sales Cookie will understand that lines 1-3 are part of the same statement.
Sales Cookie will understand that lines 4-6 are part of the same statement.
For cases where statement boundaries may be ambiguous, use separator / empty lines.
======= Variables Declaration =======
To declare or assign variables, use the "var" statement, and prefix your variable name with $.
To declare strings, use single quotes
- If your string contains single quotes, double them
To declare dates, use # at the start and end
- Always use the YYYY-MM-DD format
This formula declares variables of different types (numeric, string, date).
======= Variable Text Embedding =======
To embed variables into strings, use "{$yourVariable}".
Dates / numeric values are automatically converted to a text representation.
This avoids manually concatenating text values.
This formula embeds variables $category and $type into a message.
This is equivalent to var $msg = 'The category is ' + $category + ' and the type is ' + $type
======= Flow Control =======
Various constructs let you control the flow of execution.
Last Value = Return Value
If a formula ends with a statement which isn't a variable assignment, it becomes the return value.
This formula returns 101 because the last statement is not a variable assignment.
IIF(Condition, Value, Otherwise)
IIF() returns <Value> if <Condition> is true, and <Otherwise> if false.
This formula assigns a value of 1 to $a if the transaction's revenue is positive, and -1 otherwise.
IFEX(Condition, Value)
IFEX() returns <Value> if <Condition> is true, and exits the script immediately.
This formula returns a value of 100 if the transaction's revenue is positive (subsequent statements are not evaluated since the condition is met).
WHEN(Condition) ... END WHEN
WHEN blocks are entered when <Condition> is true.
This formula increments $a's value by 1, but only if the transaction's revenue is positive.
RETURN(Value)
RETURN() returns <Value>, and exits the script immediately.
This formula returns a value of 101, but only if the transaction's revenue is positive.
!!Throw Message
Throws an error and exits the script immediately. An error will appear in calculation alerts.
This formula throws an error (with the fee value) - processing is aborted and 1 is not returned.
!!Warn Message
Logs an error but continues running the script. An error will appear in calculation alerts.
This formula logs an error (with the fee value) - processing continues and 1 is returned.
======= Utility Functions =======
Here are some general data manipulation functions.
Logical Functions
Numerical Functions
- Abs(x) - returns x if greater than 0, and 0 otherwise
- Min(x, y) - returns x if lower than y, and y otherwise
- Max(x, y) - returns x if greater than y, and y otherwise
- Round(x, [Digits]) = returns the rounded of value x
- RoundUp(x, [Digits]) - returns the ceiling of value x
- RoundDown(x, [Digits]) - returns the floor of value x
- ConvertAmount(Value, Currency, Date) - converts a value from a specified currency, to your workspace's default currency, using an effective date
Text Functions
- LEN(Text) - gets the length of a string
- REPLACE(Text, Before, After) - replaces a string by another
- TRIM(Text) - removes leading and trailing spaces, tabs, etc.
- SUBSTRING(Text, Start, Length) - extracts a string as a specified position with a given length
- Regex(Text, Pattern) - extracts a pattern using a regular expression
- RegexReplace(Text, Pattern, Replacement) - replaces matching a pattern using a regular expression
- All text functions are case-insensitive
Date Functions
- FromUtc(Date) - returns a date in UTC using your workspace's timezone
- ToUtc(Date) - returns a date relative to your workspace's timezone
- DateAdd(Date, Unit, Count) - returns a date after adding units (Days, Months, Quarters, Years, WorkDays)
- DateDiff(Date1, Date2, Unit) - returns units between two dates (Days, Months, Quarters, Years, WorkDays)
- GetDate(Date, Unit) - returns the component of a date (Days, Months, Quarters, Years, WorkDays)
- GetDateStart(Date, Unit) - returns the start of a date (Days, Months, Quarters, Years, WorkDays)
- GetWeekDay(Date) - returns the day of the week (Sunday = 0, Monday = 1, Tuesday = 2, etc.)
- GetWeekNumber(Date) - returns the week number relative to your fiscal year (ISO week)
- UtcNow() - returns the current date
- InPeriod(Date) - checks if a date is in the calculation period
Type Conversion Functions
- ToString(Value) - converts a value to a string value with a default of '' (empty string)
- ToDecimal(Value) - converts a value to a numeric value with a default of 0
- ToDate(Value) - converts a value to a date with a default of 2000-01-01
Alias-Based Lookup Functions
GetTeams(Alias, [Date]) - returns the list of team names for a single user whose alias is Alias (separated by ";")
GetManagedTeams(Alias, [Recurse]) - returns a list of team managed by a single user whose alias is Alias (separated by ";")
GetTeamMembers(Alias, [Date]) - returns the list of user IDs who are members of teams whose aliases is Alias (ex: '9f2ff62e-1202-469b-bac4-e693f65c0733||6a485608-d3e0-41af-84bc-be39925f38cc')
GetTeamMemberCount(Alias, [Date]) - returns a count of team members for teams whose teams whose aliases is Alias
GetTeamRole(UserAlias, TeamAlias, [Date]) - returns the role (ex: 'Manager', 'Member') of a single user whose alias is UserAlias, within a team whose alias is TeamAlias (separated by ";")
GetTeamManager(TeamAlias) - returns the user ID for the manager of a team whose alias is TeamAlias
GetTags(Alias) - returns tags for the single user or team whose alias is Alias (separated by "|")
GetTargetsWithTag(Tag) - returns the list of user or team IDs who have tag Tag (ex: '9f2ff62e-1202-469b-bac4-e693f65c0733||6a485608-d3e0-41af-84bc-be39925f38cc')
Lookup(Alias) - returns email addresses of all users whose alias is Alias, or names of all teams whose alias is Alias (separated by ";")
Lookup(Alias, @@CustomVariable, [Date]) - returns a custom variable from a single user or team whose alias matches Target (an effective date can be specified). Specify [Plan] as an Alias to retrieve a custom variable from the current plan.
Lookup(Alias, KnownProperty) - gets a known property from a single user or team whose alias is Alias (separated by "|")
User properties: Id, EmailAddress, FirstName, LastName, Currency, AccessLevel, DirectManagerId, Tags, StartDate, EndDate, BaseSalary
Team properties: Id, Name, Depth, ManagerId, ParentTeamId, Tags
LookupMany(Aliases) - returns email addresses of all users whose alias is within Aliases, or names of all teams whose alias is within Aliases (separated by ";")
Other Lookup Functions
Lookup(Value, $lookupTable, [DefaultValue], [BlendRates]) - if a lookup table has been loaded into variable $lookupTable, this returns the result of performing a lookup into this table using the specified value (learn how you can define lookup tables as custom variables here)
- If you set [DefaultValue], this value is returned if the lookup table does not match the value
- If you set [BlendedRates] as true, a blended rate is calculated based on tiers and their rate
- QueryTransactionSingle(SearchField, SearchValue, ResultField) - searches for one transaction whose SearchField is set to SearchValue, and returns its ResultField (this is useful if you need to query another set of data)
- QueryTransactionSum(SearchField, SearchValue, ResultField) - searches for all transactions whose SearchField is set to SearchValue, and returns the sum of ResultField (this is useful if you need to query another set of data)
- QueryTransactionMin(SearchField, SearchValue, ResultField) - searches for all transactions whose SearchField is set to SearchValue, and returns the minimum of ResultField (this is useful if you need to query another set of data)
- QueryTransactionMax(SearchField, SearchValue, ResultField) - searches for all transactions whose SearchField is set to SearchValue, and returns the maximum of ResultField (this is useful if you need to query another set of data)
Filtering Functions
- FilterContains(Aliases, KnownProperty, TextValue) - filters aliases to those whose specified known property contains TextValue
- User properties: Id, EmailAddress, FirstName, LastName, Currency, AccessLevel, DirectManagerId, Tags, StartDate, EndDate, BaseSalary
- Team properties: Id, Name, Depth, ManagerId, ParentTeamId, Tags
- FilterContains(Aliases, @@CustomVariable, TextValue, [Date]) - filters aliases to those whose specified custom variable's value contains TextValue
Grouping Functions
- SetRewardGroup(Value) - allows grouping of transactions for advanced reward calculation scenarios. To learn more about transaction grouping, click here.
Debug Functions
- Log(x) - log a variable and its evaluated result

This formula combines utility functions and variables.
======= Custom Variables =======
You can define custom variables on plans, users, or teams.
To refer to custom variables, use expressions such as:
- [Plan].[@@CustomVariable]
- [Credited].[@@CustomVariable]
- [Beneficiary].[@@CustomVariable]
- Etc.
It is also possible to retrieve custom variables using explicit Lookup() statements (see "Alias-Based Lookup Functions" above for more details). To learn more about custom variables, click here.
======= Retrieving Commission Amounts =======
You can retrieve how much commission has been paid so far on a deal (or even who previously got paid on a deal).
GetCommission(BeneficiaryId, OnlyReleased, Estimated, [TransactionId], [PlanId], [Explanation], [ExcludeSelf])
This function returns the total amount of commissions paid (so far) on a single transaction, to a single beneficiary. You can further control whether you want to retrieved all such commissions, or filter them.
BeneficiaryId is the ID of the payee to lookup a commission for
- This parameter is required and should be non-empty
- This is typically read from [Beneficiary].[id]
OnlyReleased controls whether unreleased calculations should be considered
- This parameter is required and should be true or false
- If true, only commissions paid by fully released calculations will be considered
- If false, commissions paid by unreleased calculations will also be considered
Estimated controls whether to retrieve actual or estimated commissions
- This parameter is required and should be true or false
- If true, only estimated commissions will be considered
- If false, only actual commissions will be considered
TransactionId is the transaction ID to lookup a commission for
This parameter is either
- If not specified, the current transaction will be used
- If specified, commissions for the specified transaction ID will be considered
- This is typically read from [Transaction].[id]
- This can be a transaction system ID or a transaction Order ID
PlanId is a specific plan to consider
- This parameter is optional
- If not specified, commissions paid by all plans will be considered
- If specified and non-empty, only commissions for the specified plan ID will be considered
- If specified but empty, commissions paid by all plans will be considered
- This is typically read from [Plan].[id]
- This must be a plan system ID
Explanation is a specific explanation to consider
- This parameter is optional
- If not specified, commissions with any explanation (could be empty) will be considered
- If specified and non-empty, only commissions with the specified explanation will be considered
- If specified but empty, commissions with any explanation (could be empty) will be considered
- You can also request a partial "contains" match (ex: using either '%something%' or '*something*')
- Please note that only "contains" match are supported (ex: using 'somet%ing' is not supported)
ExcludeSelf controls whether to exclude commissions paid by the current calculation
- This parameter is optional
- If not specified, commissions paid by the current calculation will be excluded
- If specified and true, commissions paid by the current calculation will be excluded
- If specified and false, commissions paid by the current calculation will be included
GetCommissionRate(BeneficiaryId, OnlyReleased, Estimated, [TransactionId], [PlanId], [Explanation], [ExcludeSelf])
Same as the above, but returns the overall commission rate (ex: a 1% commission rate will be returned as 0.01).
GetCommissionBeneficiaries(OnlyReleased, Estimated, [TransactionId], [PlanId], [Explanation], [ExcludeSelf]
Same as the above, but returns the list of payee IDs with a commission (ex: '9f2ff62e-1202-469b-bac4-e693f65c0733||6a485608-d3e0-41af-84bc-be39925f38cc'). This is often used in dynamic crediting formulas. This formula pays the difference between a/ what has been paid so far and b/ a re-calculated owed commission amount.
======= Storing Calculated Values =======
StoreCalculation(Name, Key, Value) - stores a value within a calculation
- Name is the name of the property to store
- Key is the slot to use (ex: [Credited].[id] to store a fact for a credited user or team)
- Value is the value to store
LoadCalculation(Name, Key) - loads a value from a calculation
- Name is the name of the previously stored property to load
- Key is the slot to use (ex: [Credited].[id] to store a fact for a credited user or team)
StoreTransaction(Name, Value) - stores a value within a credited transaction
- Name is the name of the property to store
- Value is the value to store
[Transaction].[Name] - loads a stored value from a transaction
- Name is the name of the previously stored property to load