Formula operators
Several formula operators are available to aid in setting up the calculation you want to create in IBM® Cognos® Incentive Compensation Management.
| Operator | Description | Example | Syntax | Notes |
|---|---|---|---|---|
| SUM() | This will take the aggregate of whatever is within the brackets and give you a total. | To calculate 5% of the value of each sale as commission: Sum(SourceTable.ValueColumn) *0.05 |
Sum(Value) | The use of SUM is recommended for most calculations as it will not only provide you with the proper summed amount for your calculation, but will also enable clear reporting in Payee Ledger. If you are showing negative values in Payee Ledger, make sure your formula is using the SUM operator. |
| COUNT() | This will give a value of 1+ for each record that has a value. This means that each record will be one more than the previous amount. | To calculate the total number of deals in a period: COUNT(SourceTable.ValueColumn) |
Count(Value) | If the data is partitioned by PayeeID and Month, the count will reset back to zero after each month and start again. |
| MAX() | This will take the maximum value of a set of results. | If, for each deal, you will receive a 5% commission but there is a minimum of $5 per deal you will earn, you can use the following formula to determine if the 5% commission is higher than the $5 minimum: MAX(Data.Value*0.05, $5) MAX can also be used on a single value to be used as an aggregate function (like SUM, AVG, COUNT). For example, MAX(Data.Value) |
MAX(Value1, Value2, Value3, etc.) | If you have an if statement similar to the following example: IF(A>B,A,B), you may want to use the MAX operator. |
| MIN() | This will take the minimum value of a set of results. | If 5% commission is paid on each deal to a maximum of $100, the following formula will pay either 5% commission or $100, whichever is less: MIN(Data.Value*0.05 , 100) MIN can also be used on a single value to be used as an aggregate function (like SUM, AVG, COUNT): MIN(Data.Value) |
MIN(Value1, Value2, Value3, etc.) | |
| AVG() | This will take the average of the value within the brackets. | If a product has a set price, but sales reps can sell above or below this price, the following formula will compare their average monthly sale value to the set price and pay commission based on their gross margin: (AVG Sale - Set Price)/ Set Price |
AVG(Value) | |
| IF() | You can use this operator to perform a logical test and provide a true and false result. | If the sale amount is greater than $100, you will receive a 5% commission, otherwise you will receive a 2% commission. IF(Data.Value > 100, Data.Value * 0.05, Data.Value * 0.02) Nested IF() Example If your sale amount is greater than $100, you will receive 5% commission. If your sale is greater than $50 you will receive 3%, otherwise you will receive 2%. IF(Data.Value > 100, Data.Value * 0.05, IF(Data.Value > 50, Data.Value * 0.03, Data.Value * 0.02))
|
IF(Logical Test, True Result, False Result) | |
| ISEMPTY() | This will take a Date or Numeric field and provide a true result if the field does not have a value (i.e. the equivalent of NULL in the database). | If(IsEmpty(Payee.DateOfHire), DefaultDate, Payee.DateOfHire) | ISEMPTY (Value) | |
| AND() | This will return results if all conditions are true. If you use the AND operator to return results for Logical Test 1 AND Logical Test 2, results that include both Value 1 and Value 2 will be returned. | If you only want to show results for transactions that are bigger than $1,000 and less than $10,000, use a formula similar to the following example: AND(SourceTable.ValueColumn > 1000, SourceTable.ValueColumn < 10000) |
AND(Logical Test 1, Logical Test 2) | |
| OR() | This will return results if one or more conditions are true. If you use the OR operator to return results for Logical Test 1 OR Logical Test 2, any records with either value will be returned. | If you want to show a result of 1 for values that fall between 10,000 and 20,000 and a result of zero for all other values, use a formula similar to the following example: IF(OR(SourceTable.ValueColumn < 10,000, SourceTable.ValueColumn > 20,000),0,1) |
OR(Logical Test 1, Logical Test 2) | |
| NOT() | This will return results when a condition is not met. You can use this function to exclude records from your results. | If you want to apply a 5% commission to all transactions, except when the transaction value is less than $1,000, use a formula similar to the following example: SUM((SourceTable.ValueColumn) *0.05, NOT(SourceTable.ValueColumn < 1000)) |
NOT(Logical Test) | |
| ABS() | You can use this operator to take the value within the brackets and show the number as a positive, regardless of whether or not the value within the brackets is positive or negative. | If you want to show the difference between this month's sales and last month's sales as a percentage, but you do not want the percent change to be a negative value, use a formula similar to the following example: ABS(Current - Prior)/Prior |
ABS(Value) | |
| ROUND() | This will use Banker's Rounding to round the results in the brackets. For example. 0.5 will round to 0, but 0.51 will round to 1. | If you want to round your results to the nearest cent, use a formula similar to the following example: ROUND(Result, 2) |
ROUND (Value, Decimal Places) | You must specify the value you want to round, then the amount of decimal places you want to round to. Type 0 for a whole number, 1 for one decimal place, 2 for two decimal places, etc. |
| ROUND DOWN() | This will round any number down towards zero by the number of decimal places you define in the formula. | If you want to round your results down to two decimal places, use a formula similar to the following example: ROUNDDOWN(Result, 2) |
ROUND DOWN (Source Table. ValueColumn, num_digits) | num_digits in the number of digits to which you want to round the number. If num_digits > 0, then the number is rounded down to the specified number of decimal places. If num_digits = 0, then the number is rounded down to the nearest integer. If num_digits < 0, then the number is rounded down after the decimal point. |
| MULT() | This is used to multiply numerous numbers together. | If you wanted to multiply all the values in your data table together, use a formula similar to the following example: MULT(Data.Value) |
MULT(Value) | |
| PREV () | The Prev() operator references to the previous value of the calculation you are modifying along it's accumulating partition. This is important any time you have a situation where you need to have explicit access to the previous iteration of the calculation you are modifying in order to come up with the required current period results. | If a minimum of $500 is to be paid each month to each rep but if commissions fall short, they have to be clawed back in subsequent months, use a formula similar to the following example: MAX(0,PREV() + Monthly Minimum.Value - Monthly Sales.Value) |
PREV() | PREV does not have any values inserted in the brackets. This function will only work properly if your data only has one row per partition in the calculation table. |
| POW() | This is used to create an exponent. Instead of using A^B for A to the power of B, Cognos Incentive Compensation Management will use the formula POW(A,B). | If you want to calculate the value of a deal which is sold today but paid upon completion in 5 years, use a formula similar to the following example: Present Value = Future Value / (1+Interest Rate)^Term If you will be paid 20,000 in 5 years where the interest rate is 5% the formula would look like this: 20,000 / POW((1+.05),5) |
POW(Value, Exponent) |