Plan Design > Formulas
The Formulas area contains user-defined expressions that you can use when creating rules for incentive compensations. Formulas can be either numeric (returns a number), relational (results in true or false), or date (returns a date).
Relational formulas are used in rule conditions, and numeric formulas are used in rate tables, rule conditions, and for rules results. You can also create date formulas that allow you to control the holding of credits, commissions, and bonuses to a specific date, as derived by the formula.
note: Xactly Incent performs integer arithmetic for the division operation in cases when the numerator and denominator are both integers (Incent performs decimal arithmetic if either the numerator and denominator is a decimal).
For example, 100 / 3 yields 33 (since both values are integers). Similarly, 100.0 / 3 or 100 / 3.0 yields 33.33 since one of the values is a decimal.
Common Formulas
For Direct Credit rules, the following formulas are typically created and used:
- OrderItem.SplitAmount, which makes the Credit equal to the Order Amount times the split amount for the person.
For Indirect Credit rules:
- Credit.Amount, which is equal to the amount given in the Direct Credit.
- CreditOrderItem.TotalAmount, which is equal to the total amount of the Order
Item.
Screen
Formula Screen
All Fields
The following table describes the information you can specify when managing formulas:
| Field | Description |
|---|---|
| Name | The name of the formula. |
| Formula Type | The type of formula, from among the following: • Numeric • Relational • Date • Illustrator |
| Description | A description of the formula. |
| Tags | The tags associated with the formula (as defined in the Setup > Tags area). |
| Formula Expression | The formula expression, created using data objects and system functions. |
| Data Objects | Elements that can be used in the creation of formulas and return data that is stored in Xactly Incent |
| System Functions | Functions, such as DurationInDays and TotalCreditValue, that you can use in conjunction with data objects to define formula expressions |
Data Objects
Data objects are elements that can be used in the creation of formulas and returns data that is stored in Xactly Incent. Credit is used in commission and bonus rules to get information on the credit a user has.
Credit
If you select Credit from the Data Objects drop-down list, Credit information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Drop-Down List | Amount Credit Type |
CreditOrderItem and OrderItem
CreditOrderItem and OrderItem are used in rules to get information on the order item that is created for a user.
If you select CreditOrderItem or OrderItem from the Data Objects drop-down list, CreditOrderItem or OrderItem information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Selection List | Amount Customer Discount Geography IncentiveDate OrderDate OrderType Product Quantity SplitAmount TotalAmount |
Illustrator
(Incent Only) If you select Illustrator from the Data Objects drop-down list, Illustrator information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Drop-Down List | Input1 Input2 Input3 |
Person
If you select Person from the Data Objects drop-down list, Person information appears in the Formula Expression area, and the following choices become available, along with any custom fields you have defined:
| Field | Description |
|---|---|
| Drop-Down List | PersonName PersonSalary PersonalTarget PersonHireDate PersonTerminationDate |
Position
If you select Position from the Data Objects drop-down list, Position information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Drop-Down List | PositionName PositionTitle PositionIncentiveStartDate PositionIncentiveEndDate |
RelOrderItem
If you select RelOrderItem from the Data Objects drop-down list, RelOrderItem information appears in the Formula Expression area, and the following choices become available, along with any custom fields you have defined:
| Field | Description |
|---|---|
| Selection List | Amount Customer Discount Geography IncentiveDate OrderDate OrderType Product Quantity SplitAmount TotalAmount |
System Functions
System functions are elements that can be used in the creation of formulas and allow users to access results information and leverage other special functions for making calculations.
Absolute
If you select Absolute from the System Functions drop-down list, Absolute information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Drop-Down List | Credit.Amount CreditOrderItem.Amount CreditOrderItem.TotalAmount CreditOrderItem.SplitAmount CreditOrderItem.Quantity OrderItem.Amount OrderItem.TotalAmount OrderItem.SplitAmount |
note: OrderItem.Amount is available only for backward compatibility. Xactly strongly recommends that you instead use either OrderItem.SplitAmount or OrderItem.TotalAmount, based on your specific business case.
Contains
Tests whether a person or order item field contains the specified string. If you select Contains from the System Functions drop-down list and add it to the Formula Expression area, the following choices become available:
| Field | Description |
|---|---|
| Selection List | The person or order item field to use in the system function. |
| Text Field | A regular expression to check against the selected person or order item field. A regular expression is a special sequence of characters that define a search pattern. For example, case insensitive regular expressions for the word "Sales" include the following: • (?i)sales • sales/i • [sS][aA][lL][eE][sS] For more information about constructing regular expressions, refer to a suitable online or offline resource. Specific examples for the Contains function against "Sales Rep" include: • "Sales" (without the quotes) returns true • "Admin" (without the quotes) returns false |
DateOffset
If you select DateOffset from the System Functions drop-down list, DateOffset information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Drop-Down List One | OrderItem.IncentiveDate OrderItem.OrderDate CreditOrderItem.IncentiveDate CreditOrderItem.OrderDate Position.PositionIncentiveEndDate Position.PositionIncentiveStartDate Person.PersonTerminationDate Person.PersonHireDate RelOrderItem.IncentiveDate |
| Field Two | Offset in days, prefixed with a minus (-) for negative offsets; for example, -2. |
| Field Three | e Offset in months, prefixed with a minus (-) for negative offsets; for example, -3. |
| Field Four | Offset in years, prefixed with a minus (-) for negative offsets; for example, -1. |
DurationInDays and Proration
If you select DurationInDays or Proration from the System Functions drop-down list, three drop-down boxes appear in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Drop-Down List One (Start Date) | OrderItem.IncentiveDate OrderItem.OrderDate Position.IncentiveStartDate Person.HireDate Period.StartDate |
| Drop-Down List Two (End Date) | OrderItem.IncentiveDate OrderItem.OrderDate Position.IncentiveEndDate Person.TerminationDate Period.EndDate |
| Drop-Down List Three (Period) | MONTHLY QUARTERLY YEARLY |
DurationInPeriods
If you select DurationInPeriods from the System Functions drop-down list, two drop-down boxes appear in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Drop-Down List One | OrderItem.IncentiveDate OrderItem.OrderDate Person.PersonHireDate Person.PersonTerminationDate Position.PositionIncentiveStartDate Position.PositionIncentiveEndDate |
| Drop-Down List Two | OrderItem.IncentiveDate OrderItem.OrderDate Person.PersonHireDate Person.PersonTerminationDate Position.PositionIncentiveStartDate Position.PositionIncentiveEndDate |
EndsWith
Tests whether a person or order item field ends with the specified string. If you select EndsWith from the System Functions drop-down list and add it to the Formula Expression area, the following choices become available:
| Field | Description |
|---|---|
| Selection List | The person or order item field to use in the system function. |
| Text Field | A regular expression to check against the selected person or order item field. A regular expression is a special sequence of characters that define a search pattern. For example, case insensitive regular expressions for the word "Sales" include the following: • (?i)sales • sales/i • [sS][aA][lL][eE][sS] For more information about constructing regular expressions, refer to a suitable online or offline resource. Specific examples for the EndsWith function against "Sales Rep" include: • "[rR]ep" (without the quotes) returns true • "rep" (without the quotes) returns false |
GetExchangeRate
If you select GetExchangeRate from the System Functions drop-down list, GetExchangeRate information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Drop-Down List One | This is a list of currencies (UnitTypes) and the following: OrderItem.TotalAmountUnitType Credit.AmountUnitType Person.PersonalCurrency Person.SalaryCurrency Person.PaymentCurrency BusinessGroup.GroupCurrency Product.PriceUnitType Product.CostUnitType |
| Drop-Down List Two | This is a list of currencies (UnitTypes) and the following: OrderItem.TotalAmountUnitType Person.PersonalCurrency Person.SalaryCurrency Person.PaymentCurrency BusinessGroup.GroupCurrency Product.PriceUnitType Product.CostUnitType |
| Drop-Down List Three | OrderItem.IncentiveDate OrderItem.OrderDate RelOrderItem.IncentiveDate RelOrderItem.OrderDate Person.HireDate Person.TerminationDate Position.IncentiveStartDate Position.IncentiveEndDate |
GetMaxRate
If you select GetMaxRate from the System Functions drop-down list, GetMaxRate information appears in the Formula Expression area, and two GetRate selections appear.
| Field | Description |
|---|---|
| Drop-Down List One | This is a list of all the Rate Tables that have been created in Plan Design > Rate Tables. |
| Drop-Down List Two | Credit.Amount CreditOrderItem.Amount CreditOrderItem.TotalAmount CreditOrderItem.SplitAmount CreditOrderItem.Quantity OrderItem.Amount OrderItem.TotalAmount OrderItem.SplitAmount OrderItem.Quantity |
| Drop-Down List Three | This is a list of all the Rate Tables that have been created in Plan Design > Rate Tables. |
| Drop-Down List Four | Credit.Amount CreditOrderItem.Amount CreditOrderItem.TotalAmount CreditOrderItem.SplitAmount CreditOrderItem.Quantity OrderItem.Amount OrderItem.TotalAmount OrderItem.SplitAmount OrderItem.Quantity |
note: OrderItem.Amount is available only for backward compatibility. Xactly strongly recommends that you instead use either OrderItem.SplitAmount or OrderItem.TotalAmount, based on your specific business case.
GetMinRate
If you select GetMinRate from the System Functions drop-down list, GetMinRate information appears in the Formula Expression area, and two GetRate selections appear.
| Field | Description |
|---|---|
| Drop-Down List One | This is a list of all the Rate Tables that have been created in Plan Design > Rate Tables. |
| Drop-Down List Two | Credit.Amount CreditOrderItem.Amount CreditOrderItem.TotalAmount CreditOrderItem.SplitAmount CreditOrderItem.Quantity OrderItem.Amount OrderItem.TotalAmount OrderItem.SplitAmount OrderItem.Quantity |
| Drop-Down List Three | This is a list of all the Rate Tables that have been created in Plan Design > Rate Tables. |
| Drop-Down List Four | Credit.Amount CreditOrderItem.Amount CreditOrderItem.TotalAmount CreditOrderItem.SplitAmount CreditOrderItem.Quantity OrderItem.Amount OrderItem.TotalAmount OrderItem.SplitAmount OrderItem.Quantity |
note: OrderItem.Amount is available only for backward compatibility. Xactly strongly recommends that you instead use either OrderItem.SplitAmount or OrderItem.TotalAmount, based on your specific business case.
GetPayCurve
The GetPayCurve system function allows you to get the attainment percentage for a personal target or quota for a specific pay curve, tier, and person (based on assignment), and then use it in calculations.
If you select GetPayCurve from the System Functions drop-down list, GetPayCurve information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Drop-Down List | The personal target or quota attainment percentage, defined as part of the pay curve. |
| Selection List | The pay curve, as defined in the Plan Design > Pay Curves area. |
| Numeric Field | The pay curve tier. For example, enter 1 for Tier 1, 2 for Tier 2, and so on. |
GetQRPersonalTargetWeight
Sales representatives might be assigned more than one quota and measured against those quotas based on their performance throughout the measurement period. In this case, each quota has an earning potential that is part of the sales representative’s target incentives.
The GetQRPersonalTargetWeight system function allows you to get the personal target weighting from a quota relationship for a specific quota, and then use it to determine the total potential that can be earned against a particular quota.
If you select GetQRPersonalTargetWeight from the System Functions drop-down list, GetQRPersonalTargetWeight information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Selection List One | The quota relationship, as defined in the Plan Design > Quota Relationships area. |
| Selection List Two | The specific quota, defined as part of the quota relationship. |
GetQRQuotaWeight
Sales representatives might be assigned more than one quota and measured against those quotas based on their performance throughout the measurement period. In this case, each quota represents a percentage of the total quota (even if the quota is not included in total quota).
The GetQRQuotaWeight system function allows you to get a quota weighting from a quota relationship for a specific quota. The quota weighting is then normally used to create a formula for a pay curve.
If you select GetQRQuotaWeight from the System Functions drop-down list, GetQRQuotaWeight information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Selection List One | The quota relationship, as defined in the Plan Design > Quota Relationships area. |
| Selection List Two | The specific quota, defined as part of the quota relationship. |
| Drop-Down List One | The quota period, from among the following: - YEARLY - QUARTERLY - MONTHLY - QTD - YTD |
GetQRTotalQuota
Sales representatives might be assigned more than one quota and measured against those quotas based on their performance throughout the measurement period. The GetQRTotalQuota system function allows you to calculate the sum of the quotas that are used for performance evaluation.
If you select GetQRTotalQuota from the System Functions drop-down list, GetQRTotalQuota information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Selection List | The quota relationship, as defined in the Plan Design > Quota Relationships area. |
| Drop-Down List | The quota period, from among the following: - YEARLY - QUARTERLY - MONTHLY - QTD - YTD |
| Numeric Field | An optional period offset. For example: 0 uses the selected quota period on the quota relationship, and references the current quota period. This means that if YEARLY is selected, Incent chooses the current year value. -1 uses the selected quota period on the quota relationship, and references the previous quota period. This means that if YEARLY is selected, Incent chooses the annual value for the previous year. 1 uses the selected quota period on the quota relationship, and references one quota period forward. This means that if YEARLY is selected, Incent chooses the annual value for the next year. |
GetQuotaCustomFieldValue
The GetQuotaCustomFieldValue system function allows you to add quota-based custom fields to formulas, allowing you to use these fields to complete incentive calculations.
If you select GetQuotaCustomFieldValue from the System Functions drop-down list, GetQuotaCustomFieldValue information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Selection List | The quota, as defined in the Plan Design > Quotas area. |
| Selection List | The quota custom field, as defined in the Setup > Custom Fields area. |
GetRate
If you select GetRate from the System Functions drop-down list, GetRate information appears in the Formula Expression area, and two drop-down boxes appear that gather information from the following area:
| Field | Description |
|---|---|
| Drop-Down List One | This is a list of all the Rate Tables that have been created in Plan Design > Rate Tables. |
| Drop-Down List Two | Credit.Amount CreditOrderItem.Amount CreditOrderItem.Discount CreditOrderItem.Quantity CreditOrderItem.SplitAmount CreditOrderItem.TotalAmount OrderItem.Amount OrderItem.Discount OrderItem.Quantity OrderItem.SplitAmount OrderItem.TotalAmount RelOrderItem.Quantity |
note: Numeric custom fields (defined using Setup > Custom Fields) are automatically included in the rate tables you can choose for GetRate.
note: OrderItem.Amount is available only for backward compatibility. Xactly strongly recommends that you instead use either OrderItem.SplitAmount or OrderItem.TotalAmount, based on your specific business case.
Commission rules using the GetRate system function and referencing an order item custom field evaluate absolute values when looking up the rate. This means that negative inputs are not evaluated at 0%.
For example, consider looking up a value of -$100 using the following rate table:
In this case, the GetRate function uses the absolute value of $100 to evaluate against the rate table, returning a value of 7% to the calculation.
IsNotNull
Tests whether a person or order item field is not null. Null is defined as a value that is either blank or a single space. Note that a zero (0) value is not null.
If you select IsNotNull from the System Functions drop-down list and add it to the Formula Expression area, the following choice becomes available:
| Field | Description |
|---|---|
| Selection List | The person or order item field to use in the system function. |
IsNull
Tests whether a person or order item field is null. Null is defined as a value that is either blank or a single space. Note that a zero (0) value is not null.
If you select IsNull from the System Functions drop-down list and add it to the Formula Expression area, the following choice becomes available:
| Field | Description |
|---|---|
| Selection List | The person or order item field to use in the system function. |
LookupTable
If you select LookupTable from the System Functions drop-down list, LookupTable information appears in the Formula Expression area, and a selection list and text field appear that allow you to specify the following information:
| Field | Description |
|---|---|
| Selection List One | The lookup table, as defined in Plan Design > Lookup Tables. |
| Selection List Two | The default return value if no match is found. |
Matches
Tests whether a person or order item field matches the specified string. If you select Matches from the System Functions drop-down list and add it to the Formula Expression area, the following choices become available:
| Field | Description |
|---|---|
| Selection List | The person or order item field to use in the system function. |
| Text Field | A regular expression to check against the selected person or order item field. A regular expression is a special sequence of characters that define a search pattern. For example, case insensitive regular expressions for the word "Sales" include the following: • (?i)sales • sales/i • [sS][aA][lL][eE][sS] For more information about constructing regular expressions, refer to a suitable online or offline resource. Specific examples for the Matches function against "Sales Rep" include: • "sales rep/i" (without the quotes) returns true • "sales rep" (without the quotes) returns false |
Max
The Max system function determines the maximum between two values.
If you select Max from the System Functions drop-down list, Max information appears in the Formula Expression area, and two drop-down boxes appear that gather information from the following areas:
| Field | Description |
|---|---|
| Drop-Down List One | This is a list of formulas defined in Plan Design > Formulas |
| Drop-Down List Two | This is a list of formulas defined in Plan Design > Formulas |
Min
The Min system function determines the minimum between two values.
If you select Min from the System Functions drop-down list, Min information appears in the Formula Expression area, and two drop-down boxes appear that gather information from the following areas:
| Field | Description |
|---|---|
| Drop-Down List One | This is a list of formulas defined in Plan Design > Formulas |
| Drop-Down List Two | This is a list of formulas defined in Plan Design > Formulas |
QuotaAttainment
The QuotaAttainment system function calculates the aggregated credit divided by the quota value.
note: The QuotaAttainment system function returns zero (0) when the quota value is zero.
If you select QuotaAttainment from the System Functions drop-down list, the following fields appear in the Formula Expression area:
| Field | Description |
|---|---|
| Selection List One | The product, as defined in the Company Data > Product area (Name field). |
| Selection List Two | The customer, as defined in the Company Data > Customer area (Name field). |
| Selection List Three | The geography, as defined in the Company Data > Geography area (Name field). |
| Drop-Down List One | The credit period, from among the following: • MONTHLY • QUARTERLY • YEARLY • QTD • YTD |
| Text Field One | The credit period offset. This is a numeric value and can be either negative or 0. |
| Drop-Down List Two | One or more credit types, as defined in the Setup > Type Codes > Credit Type area. |
| Drop-Down List Three | The unit type, as defined in the Setup > Type Codes > Unit Type area. |
| Selection List Four | The list of quotas |
| Drop-Down List Four | The quota period, from among the following: • MONTHLY • QUARTERLY • YEARLY • QTD • YTD |
| Text Field Two | The quota period offset. This is a numeric value and can be either negative or 0. |
| Drop-Down List Five | Whether to include total held and released credits when calculating the Quota Attainment, from among the following: • Yes—Include held and released credits • No (default)—Include only released credits (do not include held credits |
QuotaValue
If you select QuotaValue from the System Functions drop-down list, QuotaValue appears in the Formula Expression area and two drop-down boxes appear that pull from the following area:
| Field | Description |
|---|---|
| Drop-Down List One | This is a list of all the Quotas that have been created in Plan Design > Quotas |
| Drop-Down List Two | The period, from among the following: • MONTHLY • QUARTERLY • YEARLY • QTD • YTD |
| Drop-Down List Three | Period Offset (This is a numeric value and can be either negative or 0). See “Period Offset Examples” on page 325 |
Round
The Round system function allows you to specify the precision of formula calculation results, along with the rounding method to apply. For example, a company might choose to round dollar values to two decimal places ($12.45) and use three decimal places for most rates (4.453%). Similarly, very small payments on large quotas might require even greater decimal precision.
If you select Round from the System Functions drop-down list, Round information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Selection List | The formula to which to apply the rounding function. |
| Numeric Field | The number of positions to the right or left of the decimal point in the original value. Positive values set the number of decimals to the left of the point and shall represent the number of positions used in calculation. Negative values set the number of decimals to the right of the point and shall represent the number of positions used in calculation. Setting the field to 0 rounds values to whole numbers. |
| Drop-Down List | The rounding method, from among the following: - HALF_EVEN - If the fraction of the original value is 0.5, then the rounded value is the even integer nearest to the original value. For example, 23.5 becomes 24, as does 24.5. Similarly, −23.5 becomes −24, as does −24.5. This approach minimizes the expected error when summing over rounded figures. - UP - The value is rounded up. - DOWN - The value is rounded down. |
StartsWith
Tests whether a person or order item field starts with the specified string. If you select Contains from the System Functions drop-down list and add it to the Formula Expression area, the following choices become available:
| Field | Description |
|---|---|
| Selection List | The person or order item field to use in the system function. |
| Text Field | A regular expression to check against the selected person or order item field. A regular expression is a special sequence of characters that define a search pattern. For example, case insensitive regular expressions for the word "Sales" include the following: • (?i)sales • sales/i • [sS][aA][lL][eE][sS] For more information about constructing regular expressions, refer to a suitable online or offline resource. Specific examples for the StartsWith function against "Sales Rep" include: • "Sales" (without the quotes) returns true • "the Sales" (without the quotes) returns false |
TotalCommissionValue
If you select TotalCommissionValue from the System Functions drop-down list, five drop-down boxes appear in the Formula Expression area that gather information from the following areas:
| Field | Description |
|---|---|
| Selection List One | The product, as defined in the Company Data > Product area (Name field). |
| Selection List Two | The customer, as defined in the Company Data > Customer area (Name field). |
| Selection List Three | The geography, as defined in the Company Data > Geography area (Name field). |
| Drop-Down List One | The period type, from among the following: • MONTHLY • QUARTERLY • YEARLY • QTD • YTD |
| Drop-Down List Two | The unit type, as defined in the Setup > Type Codes > Unit Type area. |
| Drop-Down List Three | The period offset. This is a numeric value and can be either negative or 0. |
| Drop-Down List Four | One or more credit types, as defined in the Setup > Type Codes > Credit Type area. |
note: When using the TotalCommissionValue system function, commissions from previous periods are filtered by the specified credit types. Commissions in the current period, on the other hand, are calculated based on the running commission (dependent on the order of the commissions processed so far in the batch) without applying the credit type filter.
As a general rule, the TotalCommissionValue function with zero offset is not intended for use in commission rules and can produce inconsistent results.
If you need to use the TotalCommissionValue function with a zero offset, you should process each commission rule of this type in a separate batch to ensure the consistency of the results.
TotalCreditValue
If you select TotalCreditValue from the System Functions drop-down list, TotalCreditValue information appears in the Formula Expression area, and six drop-down boxes appear that gather information from the following area:
| Field | Description |
|---|---|
| Selection List One | Company Data > Product |
| Selection List Two | Company Data > Customer |
| Selection List Three | Company Data > Geography |
| Drop-Down List One | The period, from among the following: • MONTHLY • QUARTERLY • YEARLY • QTD • YTD |
| Selection List Four | Displays a pop-up showing the list of Available Credit Types (as defined in the Setup > Type Codes > Credit Type area) and the list of Selected Credit Types. You can move entries between the lists using the arrow buttons (Ctrl-click to select multiple entries). Note that when you choose multiple credit types, Incent totals the credit amounts of the selected credit types for the specified period (MONTHLY, QUARTERLY, or YEARLY), taking into account any filters (such as product, customer, geography, or currency) defined for the formula. Incent also takes period offsets into account when multiple credit types are chosen. The field displays “ |
| Drop-Down List Two | Setup > Type Codes > Unit Type |
| Field | Period Offset (This is a numeric value and can be either negative or 0). |
TotalOrderItemAmountToDate and TotalOrderItemQuantityToDate
If you select TotalOrderItemAmountToDate or TotalOrderItemQuantityToDate from the System Functions drop-down list, TotalOrderItemAmountToDate or TotalOrderItemQuantityToDate information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Drop-Down List | YEARLY QUARTERLY MONTHLY |
note: The TotalOrderItemAmountToDate and TotalOrderItemQuantityToDate functions are scheduled to be discontinued. Xactly recommends that you do not use these functions in formulas.
note: Held and released credits are included in the calculation of the TotalCreditValue() function. However, only released credits are included in the calculation of the TotalCreditAttainment() function. You can specify whether to include both held and released credits, or just released credits, in the QuotaAttainment() function.
WeightedAvgDays
One method to calculate proration involves deriving prorated amounts based on the number of days in relation to the total number of days. For example, you could use this method with new hire salaries where the prorated payment amount is based on the number of days since the hire date.
The WeightedAvgDays system function calculates the weighted average amount of a number based on either the number of days in the period or in the year. If you select WeightedAvgDays from the System Functions drop-down list, WeightedAvgDays information appears in the Formula Expression area, and the following choices become available:
| Field | Description |
|---|---|
| Drop-Down List One | The value that is to be weighted over time, including the following: - Person.PersonalTarget - Person.PersonSalary - Person record custom fields |
| Drop-Down List Two | The periods to include (which forms the denominator in the calculation): - Incentive Year - Incentive Month |
Relational Expressions
Relational expressions have operators that appear in them that are called relational operators. This table covers most of the possibilities for boolean results.
Relational Operators
| Field | Description |
|---|---|
| Operator | Meaning |
| AND | Logical AND operation |
| OR | Logical OR operation |
| !ERROR! unexpected operator '=' | Is equal to |
| != | is not equal to |
| < | Is less than |
| > | Is greater than |
| <= | Is less than or equal to |
| >= | Is greater than or equal to |
How to…
This section describes how to manage formulas.
Search Formulas
1. Click the Plan Design tab, then click Formulas in the secondary menu. The Formulas screen appears.
2. (Optional) In the Name* field, type part of the name of the formula.
3. (Optional) In the FormulaType* field, choose an option using the drop-down list.
4. (Optional) In the Description field, type part of the formula description.
5. (Optional) In the Tags field, click the Select button. The Select General Tags dialog appears.
Select the tags to use in the search and click the Apply Selected Tags to Formula button.
6. Click the Search button. The matching formulas appear in the results area.
Create a Formula
1. Click the Plan Design tab, then click Formulas in the secondary menu. The Formulas screen appears.
2. Click the New button.
3. Enter a formula name in the Name* field. (Formula names cannot contain blank spaces.)
note: You can enter integer values between -2147483648 and 2147483647 in formula fields. To enter larger values, add a decimal point to the value (for example, 2247483647.0).
4. Select a FormulaType* (Numeric or Relational) from the drop-down list.
• Numeric—When the formula needs to result in a number.
• Relational—When the formula needs to result in true or false. Relational Formulas can only be used in the conditions step of Rules. Refer to “Step 3: Define result(s) created when the condition is true” on page 385.
• Date—When the formula needs to result in a date.
• Illustrator—(Incent Only) When the formula needs to handle Plan Illustrator inputs
note: If you select the Relational type, the following additional expressions appear: AND, OR, ==, =, <, >, <=, and >=. Each expression is explained in detail in Relational Expressions. For this example, the Numeric type was selected.
5. (Optional) Enter a description of the formula you are creating in the Description
field.
6. (Optional) In the Tags field, add or remove the tags associated with the formula.
Do the following:
• To add or remove tags, click the Select button. The Select General Tags dialog appears.
Select the tags you want to associate with the formula and click the Apply
Selected Tags to Formula button. You can also find specific tags (using the Find Tags search box), or create and upload new tags, as required.
note: You can associate up to 50 tags with a formula. For more information about managing tags, see “Tags” on page 101.
• To remove all tags associated with the formula, click the Remove All button. Exercise caution when removing all associated tags because this action does not ask for confirmation.
7. Select one of the following options from the Data Objects area:
• Credit
• CreditOrderItem
• Illustrator (Incent Only)
• OrderItem
• Person
• Position
• RelOrderItem
Data Objects are elements that can be used in the creation of formulas and return data that is stored in Xactly Incent. This example uses Person as the Data Object.
note: There is no pagination on the pop-up windows that appear when you select a formula data object.
8. Click the 
box in the Formula Expression* section, or double-click the Data Object. Person appears in the box with a drop-down list. Select one of the following options using the pop-up list:
• PersonTerminationDate
• PersonHireDate
• PersonalTarget
• PersonSalary
• Department
Custom fields are also displayed as part of the pop-up list. This example uses PersonalTarget.
9. Select an expression in the InputBox area.
InputBox List
This example uses the + symbol.
You can also choose to nest formulas by selecting Formula and then choosing the formula to nest as part of your expression. Nesting formulas enables you to create reusable calculation components (a formula) that may represent a common calculation. This allows you to modify common calculations in one place.
Note that you can only embed formulas up to five levels.
10. Click the box in the Formula Expression* section. The + symbol appears in the Formula Expression* section.
11. Select one of the following options from the System Functions area:
• Absolute—This option specifies an absolute number based on the selection you choose. The value for each of these options is a positive or negative numeric value that is taken from a defined amount, quantity, or discount associated with a credit, order, or related order.
• DateOffset—Enter a date plus or minus the number of days, months, and year that will return a new date.
• DurationInDays—This option specifies the duration in days between two dates, which you select from the drop-down lists in the two fields that appear when you choose DurationInDays. From each of these fields, select a start and end date that defines a number of days. Some of these options, such as Position.IncentiveStartDate and Position.IncentiveEndDate, define the start and end of a period. You can use other options, such as OrderItem.IncentiveDate and OrderItem.OrderDate (which are usually the same date), as a start or end date in combination with one of the other options.
• DurationInPeriods—Enter two dates that define a period of time. This option returns the number of monthly periods between the two dates, inclusive, and includes any partial periods.
• GetExchangeRate—Enter the source and target currency. This option returns the exchange rate value (numeric), and is typically used in Credit rules to convert one currency to another.
• GetMaxRate—Select two rate tables to evaluate based on an input. This option returns the higher of the two rates.
• GetMinRate—Select two rate tables to evaluate based on an input. This option returns the lower of the two rates.
• GetPayCurve—Select either personal target or quota attainment percentage, the pay curve, and the pay curve tier.
• GetQRPersonalTargetWeight—Select the quota relationship along with the specific quota (defined as part of the quota relationship).
• GetQRQuotaWeight—Select the quota relationship, the specific quota (defined as part of the quota relationship), and the quota period.
• GetQRTotalQuota—Select the quota relationship, the quota period, and an optional period offset.
• GetQuotaCustomFieldValue—Select the quota and the quota custom field.
• GetRate—Select a rate table, the input amount, and numeric order custom fields. This option returns the rate for the amount by looking it up in the rate table.
• LookupTable—Select the lookup table, along with a default return value (if no match is found).
• Max—Select two formulas. This option returns the maximum between the two formulas.
• Min—Select two formulas. This option returns the maximum between the two formulas.
• Proration—Enter two dates that define the start and end of the period. Enter a third definition to define the period type (e.g., monthly). This option returns a percentage, for example, if the first day of the period is the 15th of the month, and the last is the 30th, the percent returned will be .5 (or 50%).
• QuotaAttainment—Select values that represent the attainment measure criteria and the quota information. This option returns a number that represents the Quota Credit divided by the Quota.
• QuotaValue—Select the quota and the period of the quota. The offset enables you to walk past periods by entering a negative offset. An offset of zero is the current period. This system function defines the source currency, the target currency, and the date. For more information, see the example starting at Step 11.
• Round—Select the formula to which to apply the rounding function, the precision, and the rounding method.
• TotalCommissionValue—This option returns a person’s total commissions, based on the filters defined.
• TotalCreditValue—This option returns a person’s total credits, based on the filters defined.
• TotalOrderItemAmountToDate—This option returns, based on a specified period, the sum of the amount of orders that apply for a person.
• TotalOrderItemQuantityToDate—This option returns, based on a specified period, the quantity of orders that apply for a person.
• WeightedAvgDays—Select the value to be weighted over time, along with the periods to include.
note: The TotalOrderItemAmountToDate and TotalOrderItemQuantityToDate functions are scheduled to be discontinued. Xactly recommends that you do not use these functions in formulas.
QuotaValue is the only system function available when defining Illustrator formulas. System Functions are elements that can be used in the creation of formulas and allow users to access results information and leverage other special functions for making calculations.
For this example QuotaValue was selected.
note: The following calculation functions now include Estimator Orders when Estimating incentives: Quota Attainment, Total Credit, and Total Commission. This enables sales representations to estimate commission calculations more accurately.
12. Click the box in the Formula Expression* section. QuotaValue appears in the box with three lists. The last list is pre-populated with 0 (the period offset, indicating the current period).
13. Select a quota from the first list. A pop-up screen appears. For this example, AM 2006 Printer Quota was selected.
Quota Pop-up Screen
14. Select Yearly from the second drop-down list, from among the following options:
• Yearly
• Quarterly
• Monthly
All the choices appear in the Formula Expression area, shown in the figure below.
Formula Expression Area
note: If you need to change your choices, click the Clear button to reset the Formula Expression. If you need to remove a single element from the expression, select the element and use the Delete key on your keyboard.
A list of Data Objects, System Functions, and Relational Operators, which are used to create Formula Expressions, can be found at the end of this section under “Data Objects” on page 306, “System Functions” on page 307 and “Relational Expressions” on page 318
15. Click the Save button. The newly created Numeric Formula now appears, with parenthesis around it, in the Results Pane area.
Newly Created Formula
Relational Formula Example
note: Only use when the formula needs to result in true or false.
Relational Formula Example
Date Formula Example
note: Only use when the formula needs to result in a date, and will be used to hold calculated results.
Date Formula Example
Period Offset Examples
Assuming calendar year and date is 2/1/2006.
• If TotalCreditValue (Product, Customer, Geography, Monthly, Booking, USD, 0)
• An offset of “0” is equivalent to taking the current month.
• If TotalCreditValue (Product, Customer, Geography, Monthly, Booking, USD, -2)
• An offset of “-2” subtracts two from the current month (February) to provide the Total Credits for December 2005.
• If TotalCreditValue (Product, Customer, Geography, Quarterly, Booking, USD, -3)
• An offset of “-3” takes Total Credits for Q2 2005.
• 0: Q1 2006
• -1: Q4 2005
• -2: Q3 2005
• -3: Q2 2005
Illustrator Formula Example
Edit a Formula
note: The Formula Name cannot be changed once it is saved.
1. Select a Formula to be modified from the Results Pane area.
2. Select and edit the fields that require changes.
3. Click the Save button. The Formula has now been modified.
Special Actions for Formulas
This section describes special actions you can perform on formulas.
Insert an Element into a Formula String
1. Click a Data Object or System Function element that you want to insert into the formula.
2. Mouse over the formula string element in the Formula Expression box to enter the element before or after the formula string in the box.
3. Click the left mouse button. The new element appears in the Formula Expression box.
note: You can also enter an element into the formula by selecting a Data Object or System Function from the drop-down list, and then double-clicking the Data Object or System Function box. This will add the element to the end of the formula only.
Delete an Element in a Formula String
1. Mouse over the formula string element in the Formula Expression box .
2. Click the Delete button. The element is deleted.
Delete a Formula
1. Click a Formula. It should become highlighted.
2. Click the Delete button. A message appears asking “Do you really want to delete the selected item?”.
3. Click the OK button to confirm the deletion, or click the Cancel button to cancel the action.
note: If the formula is used in a rule or rate table, the formula cannot be deleted. Unlink the formula first from the rule or rate table, and then delete the formula.
Determine Where a Formula is Used
1. Highlight a formula in the Results Pane area.
2. Click the Where Used button. A pop-up window appears that shows the related objects to which the formula belongs and the rate tables associated with it.
3. From the Where Used window, you can download the rate tables and rules associated with the formula by clicking the Download button, or close the window by clicking the Close button.
Create a New Formula by Copying an Existing Formula
1. Select a formula from the Results Pane. The selected formula is highlighted.
2. Click the Copy button.
3. Type a new name in the Name* field.
4. Modify the formula, as required. For more information about modifying formulas, see “Edit a Formula” on page 326.
5. Click the Save button. The newly created formula appears in the Results Pane.