Is There A Complete Formula Technical Reference?

Is There A Complete Formula Technical Reference?


This article provides technical details about formulas. For some background information about formulas, click here. You can configure formulas to control different aspects of calculations, such as filtering, crediting, scoring, quota, custom, per-transaction rewards, global rewards, etc.

======= Using Auto-Complete =======
Start by typing a left square bracket.

Type on the ↓ down arrow key to select an option, and then <tab> or <enter>.

Type a left square bracket again to get the next auto-completion.

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
  • AND, OR, NOT
  • IN(...) - checks if an element is within a list of values
  • LIKE - use % or * as a wildcard character
    • Wildcards must be at the start or end of the pattern (ex: '%XY', 'XY%', '%XY%', but not 'X%Y')
    • Encode % and * in strings as [%] or [*]
  • >, <, <=, >=, <>, =
    • Also allowed for equal: ==, ===
    • Also allowed for different: !=, !==
  • +, -, *, /
  • % - modulus operator

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, SourceCurrency, Date) - converts a value in a source currency, to your workspace's default currency, using an effective date
  • ConvertAmount(Value, SourceCurrency, Date, DestinationCurrency) - converts a value in a source currency, to a destination 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 =======
    You can store calculated values within calculations or within credited transactions.
    • 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
    \
    This formula stores a calculated value within each credited transaction. You can access the value using [Transaction].[Commissionable].

    This formula stores a calculated value within the calculation, using the credited ID as a key. You can access the value using LoadCalculation("Goal", $creditedId).

    ======= Catalog Lookups =======
    You can perform catalog lookups.
    • GetCatalog(Product, Category) - performs an explicit catalog lookup (using the specified product and category such as catalog_revenueperunit, catalog_percentmargin, catalog_percenttax, catalog_other, catalog_other2, catalog_category, catalog_category2)
    • [Transaction].[catalog_revenueperunit] - performs an implicit catalog lookup (using the transaction's product), and returns the product's revenue per unit value from the catalog
    • [Transaction].[catalog_percentmargin] - performs an implicit catalog lookup (using the transaction's product), and returns the product's percent margin value from the catalog
    • [Transaction].[catalog] - performs an implicit catalog lookup (using the transaction's product), and returns the product's percent tax value from the catalog
    • [Transaction].[catalog_other] - performs an implicit catalog lookup (using the transaction's product), and returns the product's "other" value from the catalog
    • [Transaction].[catalog_other2] - performs an implicit catalog lookup (using the transaction's product), and returns the product's "other2" value from the catalog
    • [Transaction].[catalog_category] - performs an implicit catalog lookup (using the transaction's product), and returns the product's "category" value from the catalog
    • [Transaction].[catalog_category2] - performs an implicit catalog lookup (using the transaction's product), and returns the product's "category2" value from the catalog

    This formula performs an explicit catalog lookup.

    ======= Plan Properties ======= 
    You can refer to plan properties in some formulas. 
    • [Plan].[id] - this represents the current plan's ID
    • [Plan].[name] - this represents the current plan's name
    • [Plan].[@@CustomVariable] - this reads a custom variable set on the plan

    This formula loads a custom variable called @@Rates set on the plan, which contains a lookup table with commission rates for different products. We then perform a lookup using the transaction's product to determine the commission rate.



    Above is the custom variable @@Rates defined on the plan (as a lookup table).

    ======= Calculation Properties =======
    You can refer to calculation properties in some formulas.
    • [Calculation].[start_date] - this represents the current calculation's start date "as is"
    • [Calculation].[end_date] - this represents the current calculation's end date "as is"
    • [Calculation].[start_date_utc] - this represents the current calculation's start date in UTC
    • [Calculation].[end_date_utc] - this represents the current calculation's end date in UTC
    • [Calculation].[scan_start_date_utc] - this represents the current calculation's scan start date in UTC
    • [Calculation].[scan_end_date_utc] - this represents the current calculation's scan end date in UTC
    • [Calculation].[id] - this represents the current calculation's ID
    • [Calculation].[name] - this represents the current calculation's name

    This formula returns zero if the calculation period happens to be on the first day of a month. We load the calculation's start date (ex: 2021-07-15) and extract the day component (ex: 15).

    ======= Transaction Properties =======
    You can refer to transaction properties in some formulas.

    Most transaction properties will come from your own imported data. To see which properties are available, use auto-complete by typing [Transaction].[ as a prefix. 


    You can also review available data properties by doing the following:
    • On the left pane, click on "Transactions > All Transactions"
    • Click on "Field Layout"
    • Note display names
      • They should all end with (System) or (Imported) (learn more)


    You can reference transaction data properties using this notation: 
    • [Transaction].[Display Name]

    For example, if your transactions have a field called "City", you can use:
    • [Transaction].[City (Imported)]

    You can also reference the following system fields in some formulas:
    • [Transaction].[calculated_text] - this represents the current value to use for crediting
    • [Transaction].[calculated_revenue] - this represents the system-calculated revenue for the transaction
    • [Transaction].[calculated_profit] - this represents the system-calculated profit for the transaction
    • [Transaction].[calculated_score] - this represents the system-calculated score for the transaction
    • [Transaction].[calculated_custom] - this represents the system-calculated custom metric for the transaction
    • [Transaction].[attainment_threshold] - this represents the highest attainment threshold reached by the transaction (as specified on your plan's Incentives tab)
    • [Transaction].[attainment_actual_threshold] - same as above, but this is the calculated threshold value (this applies if you used quota-based percentages on your plan's Incentives tab and need to reference the actual threshold value - not the percentage)
    • [Transaction].[attainment_difficulty] - this represents the difficulty of the highest attainment threshold reached by the transaction
    • [Transaction].[credit_weight] - if there was a crediting split (ex: a transaction was split between 2 reps), this represents the weight applied to the transaction's attainment metric (but not other metrics)
    • [Transaction].[bucket_weight] - if there was a transaction split (ex: you are using advanced option "Split On-The-Fence Transactions Between Tiers"), this represents the split portion of the original transaction
    • [Transaction].[in_period] - 1 if the transaction date is within the calculation start & end dates
    • [Transaction].[crediting_date] - the effective crediting date in the workspace's time zone
    • [Transaction].[crediting_date_utc] - the effective crediting date in UTC
    • [Transaction].[is_locked] - 1 if the transaction has been locked
    • [Transaction].[data_source] - this represents the transaction's data source
    • [Transaction].[updated] - this represents the time the transaction was last updated (metadata) 
    • [Transaction].[X] - this reads a value previously stored using the StoreTransaction() function

    This formula reads the transaction's revenue and customer, and calculates a commission based on the customer type (with a fallback rate of 10%). 

    ======= Credited Properties =======
    You can refer to the credited entity's properties in some formulas.

    The credited entity is the entity you are measuring performance for. This may be a user (if your plan is individual-based) or it may be a team (if your plan is team-based).
    • [Credited].[id] - this represents the credited entity's ID
    • [Credited].[name] - this represents the email address of the credited user, or the name of the credited team
    • [Credited].[salary] - this represents the salary of the credited user
    • [Credited].[total_revenue] - this represents the total revenue attained by the credited entity
    • [Credited].[total_profit] - this represents the total profit attained by the credited entity
    • [Credited].[total_score] - this represents the total score attained by the credited entity
    • [Credited].[total_custom] - this represents the total custom metric attained by the credited entity
    • [Credited].[total_inband] - when used within a reward formula, this represents the total credited which "falls" within the attainment tier (based on your plan's primary metric)
    • [Credited].[attained_value] - this represents the credited entity's attained value (based on your plan's primary metric) 
    • [Credited].[attained_threshold] - this represents the credited entity's relative attainment threshold
    • [Credited].[attained_actual_threshold] - this represents the credited entity's absolute attainment threshold
    • [Credited].[attained_difficulty] - this represents the attained difficulty
    • [Credited].[@@CustomVariable] - this reads a custom variable set on the credited entity

    This formula reads custom variables @@Quota and @@Rate from the credited entity, and calculates a commission based on attainment to quota.

    ======= Beneficiary Properties =======
    You can refer to the beneficiary user's properties in some formulas.

    The beneficiary is the payee commissions are being assigned to. This is always a user.
    • [Beneficiary].[id] - this represents the beneficiary user ID
    • [Beneficiary].[name] - this represents the email address of the beneficiary user
    • [Beneficiary].[salary] - this represents the salary of the beneficiary user
    • [Beneficiary].[@@CustomVariable] - this reads a custom variable set on the beneficiary user

    This formula reads custom variables @@Quota and @@Variable from the beneficiary user, and calculates a commission based on those values.

    ======= Attainment Difficulties =======
    Difficulties are represented as integers using the following table:
    • None = -1
    • Trivial = 0
    • Easy = 1
    • Standard = 2
    • Difficult = 3
    • Outstanding = 4

    ======= Custom SQL Statements =======
    You can use SQL to process:
    • The set of credited transactions within the current calculation
      • Ex: SELECT 2.0 * SUM([Transaction].[calculated_revenue]) FROM Transactions
    • The set of rewards assigned to the beneficiary in prior calculations
      • Ex: SELECT 2.0 * SUM([Rewards].[value]) FROM Reward
    • The set of user / team custom variables
      • Ex: SELECT TOP 1 [CustomVariable].[numeric_value] FROM CustomVariables

    If your query refers to "Transactions", your SQL-like statement will be evaluated against a set of transactions credited to each target user / team for this calculation.
    If your query refers to "Rewards", your SQL-like statement will be evaluated against a set of all previous rewards assigned to the beneficiary.
    If your query refers to "CustomVariables", your SQL-like statement will be evaluated against a set of user and team custom variables.

    Getting Help
    If you are not familiar with SQL-like queries, we recommend reaching out to our support for assistance. Creating your own query is an advanced topic and you may need our assistance.

    ======= Custom SQL - Examples =======
    Assume that you uploaded transactions with the following fields:
    • Quantity (a number field)
    • City (a text field)

    Here are some examples of SQL formulas you could use to calculate a payout.

    Award $45 Per Credited transaction Whose Quantity Is Greater Than 25
    var $total = SELECT 45 * COUNT(*) FROM Transactions WHERE ([Transaction].[Quantity (Imported)] >= 25)

    Award 10% Of Revenue For Credited Transactions At The Current Attainment Level Only
    var $total = SELECT 0.10 * SUM([Transaction].[calculated_revenue]) FROM Transactions WHERE ([Transaction].[attainment_threshold] = [Credited].[attained_threshold])

    Award 8% Of Profit For All Credited Transactions Having Osaka As A City
    var $total = SELECT 0.08 * SUM([Transaction].[calculated_profit]) FROM Transactions WHERE ([Transaction].[City (Imported)] = 'Osaka')

    Award A Fixed Value Per Credited Transaction Which Depends On The City
    var $total = SELECT SUM(
                CASE
                            WHEN [Transaction].[City (Imported)] = 'Paris' THEN 10.5
                            WHEN [Transaction].[City (Imported)] = 'London' THEN 9.5
                            WHEN [Transaction].[City (Imported)] = 'Berlin' THEN 8.5
                            ELSE 0.0
                END)
    FROM Transactions WHERE [Transaction].[City (Imported)] IS NOT NULL

    Award 2x The Value Of Rewards From Other Released Calculations Within The Same Period
    var $total = SELECT 2 * SUM([Reward].[value]) FROM Rewards WHERE [Reward].[in_period] = 1 AND [Reward].[released] = 1

    The system will evaluate the formula and use the resulting value to determine the reward's value. Additional operators and cascading conditions are also available. Reach out to our support team for assistance creating a calculated reward formula.

    This formula executes a SQL query over a virtual set of credited transactions, and compares the total with a custom variable set on the beneficiary user.

    ======= Custom SQL - Selecting Transactions =======
    Let's say you have a plan with the following attainment levels:
    • Level L1 (easy) - revenue is >= 0
      • No reward
    • Level L2 (standard) - revenue is >= 5000
      • Calculated Reward
    • Level L3 (hard) - revenue is >= 9000
      • Cash reward

    Suppose that your attainment levels are cumulative. If a user or team reaches level 2 or level 3, the SQL-like query will be executed to calculate a reward for reaching level 2.

    To perform a calculation only on transactions at level 2, include:
           WHERE ([Transaction].[attainment_threshold] = [Credited].[attained_threshold])

    To perform a calculation on transactions at level 2 or higher levels, include:
           WHERE ([Transaction].[attainment_threshold]     >= [Credited].[attained_threshold])

    Without this condition, your query will be evaluated against all credited transactions.

    ======= Custom SQL - Selecting Rewards =======
    Consider this simple query which references other rewards:                   
           SELECT SUM([Reward].[value]) From Rewards 

    This query sums all rewards assigned to the current beneficiary - across all plans and all past calculations. You probably want to do something more specific when calculating a reward based on other (already assigned) rewards. For example, you may want to add a draw amount based on other rewards beneficiaries received within the same period. To do this, you would query for other rewards within the same period, calculate the sum, and issue the difference as needed. 

    Below is a reward query which assigns a reward of up to $1000, if total rewards from other calculations within the same period were under $1000. You could of course do something more complicated, for example using custom variables.
           var $total = SELECT SUM([Reward].[value]) FROM Rewards WHERE [Reward].[in_period] = 1               
           --------------------------------------------------------------------------------------- 
           IIF($total >= 2000, 0, 2000 - $total) 

    The following fields are available:
    • [Reward].[value] - the value of the reward in the workspace's currency
    • [Reward].[currency] - the workspace's currency at calculation time
    • [Reward].[beneficiary_value] - the value of the reward in the beneficiary's currency
    • [Reward].[beneficiary_currency] - the value of the reward in the beneficiary's currency
    • [Reward].[rewards_released] - 1 if the reward was released, 0 otherwise
    • [Reward].[credits_released] - 1 if the reward had its credits, 0 otherwise
    • [Reward].[recoverable] - 1 if the reward was marked as recoverable, 0 otherwise
    • [Reward].[non_deductible] - 1 if the reward was marked a non-deductible, 0 otherwise
    • [Reward].[estimated] - 1 if the reward was marked a estimated, 0 otherwise
    • [Reward].[manual] - 1 if the reward was a manual adjustment, 0 otherwise
    • [Reward].[in_period] - 1 if the reward was in the same time period as the current calculation, 0 otherwise
    • [Reward].[start_date] - the start date of the associated calculation
    • [Reward].[end_date] - the start date of the associated calculation
    • [Reward].[reward_type] - the type of reward
    • [Reward].[plan_id] - the ID of the plan for the reward
    • [Reward].[plan_name] - the name of the plan for the reward
    • [Reward].[calculation_id] - the ID of the calculation for the reward
    • [Reward].[calculation_name] - the name of the calculation for the reward

    ======= Custom SQL - Selecting Cutom Variables =======
    Typically, you do not need to use SQL to lookup custom variables, as the following are sufficient:
    1. [Credited].[@@CustomVariable]
    2. [Beneficiary].[@@CustomVariable]
    3. Lookup(Alias, @@CustomVariable, [Date]), etc.
    However, if you need to perform special operations on custom variables, this is possible. Consider this simple query, which looks up a custom variable set on a user:                   
           SELECT TOP 1 [CustomVariable].[numeric_value] FROM CustomVariables WHERE [CustomVariable].[name] = 'CustomVariable'  AND [CustomVariable].[effective_now] = 1 AND [CustomVariable].[target_id] = <UserId>

    The query above looks up the effective numeric value for a specific user for @@Customvariable. 

    The following fields are available:
    • [CustomVariable].[name] - the name of the custom variable to lookup
    • [CustomVariable].[text_value] - the text value of the custom variable to lookup (appropriate if the value is numeric)
    • [CustomVariable].[numeric_value] - the text value of the custom variable to lookup (appropriate if the value is text)
    • [CustomVariable].[date_value] - the date value of the custom variable to lookup (appropriate if the value is date)
    • [CustomVariable].[display_value] - the display value of the custom variable to lookup (works for all types)
    • [CustomVariable].[start_date] - the relative start date for the custom variable 
    • [CustomVariable].[end_date] - the relative end date for the custom variable 
    • [CustomVariable].[target_id] - the ID of the target user or team with the custom variable 
    • [CustomVariable].[effective_now] - set to 1 if the calculation's end date is within effective dates (0 otherwise) 

    ======= Advanced SQL Statements =======
    Our technical team may configure advanced SQL statements which operate directly against the database. This is done for performance reasons, or because we need to perform complex queries which span multiple entities. Those statements are always within {{...}} markers. Learn more about advanced SQL statements here.

    This formula directly accesses the Sales Cookie database and cannot be altered.

    ======= Well-Known "Magic" Variables =======
    When configuring reward formulas, you may set the following variables:
    • var $explanation - the commission's explanation will be set to this value (you can enable display of commission explanations on the Calculations tab of your plan) 
    • var $commissionRate - the commission rate will be set to this value (you can enable display of commission rates on the Calculations tab of your plan)
    • var $beneficiaryValue - the commission amount in the beneficiary's currency will be set to this value (you must still return a value in the workspace's currency)
    • var $conversionDate - the commission will be converted to the payee's currency using the specified conversion date
    • var $isEstimated - the commission estimated status will be set to this value (ex: set a value of 1 to dynamically make your reward estimated)

    This formula conditionally sets the estimated state and commission explanation text.

    You may also read built-in variable [initial_value] which a the starting value for your formula. For example, for reward formulas, the initial value is zero. 

      • Related Articles

      • What Are Commission Formulas For?

        If you are using spreadsheets to calculate commissions, you are probably: Use messy nested conditions Reference cells by name or position Have multiple sheets with v-tables, etc. If someone shifts cells, adds a column, or includes hidden rows of ...

      • Error - Your Plan Contains Advanced Formulas Which Must Be Approved By Us

        Applicability This error applies to the following situation: A plan was configured by our technical team You altered formulas within the plan Some of those formulas contain {{...}} expressions Your calculation is failing with this error Impact This ...

      • How Do Advanced SQL Statements Work?

        What Are Advanced SQL Statements? Our technical team may configure advanced SQL statements which operate directly against the database. This is done a/ for performance reasons, b/ to perform complex queries spanning multiple entities, or c/ to ...

      • Which Advanced Options Are Available?

        Sales Cookie offers many advanced capabilities via its web interface - including draws, splits, overrides, etc. However, some additional (even more) advanced options can also be set on incentive plans using a text-based representation. Advanced ...

      • How Can I Debug Formulas?

        You can use debug mode to understand how formulas are applied to a sample transaction or sample payee. You will see how each formula statement was evaluated against your sample transaction or user (payee). Debugging Formulas - Sample Transaction To ...