Editing an agreement

Create calculations in your tables using a combination of table elements, operators, formulas, and numbers.

1. Drag and drop a table object into the canvas.

2. Select a Source table with a numeric value and select Columns that you wish to add to your plan document.

3. Click Save.

4. Click on the Add calculation button.

5. Enter a Name for your calculation, and create a Formula using the available operators, functions, and sources.

   Important

   Aggregation functions such as average, sum, minimum, and maximum cannot be used for calculated column row groups at this time.

   You can use several formula functions to set up the calculations in your Presenter Adaptive report.

   Table 75. Formula functions

   Function	Description	Example	Syntax	Notes
   ABS()	You can use this operator to take the value within the brackets and show the number as a positive, regardless of whether 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)
   AND()	This operator returns 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 are returned.	If you 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)
   AVG()	This operator takes 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 compares their average monthly sale value to the set price and pays commission based on their gross margin: (AVG Sale - Set Price)/ Set Price	AVG(Value)
   COUNT()	This operator gives a value of 1+ for each record that has a value. The value of each record is 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.
   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 receive a 5% commission. Otherwise, you 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 receive 5% commission. If your sale is greater than $50, you receive 3%; otherwise, you 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)	To include text columns in IF functions, you must disable the Enforce best practices for formulas option in Model settings → More options → Calculation.
   ISEMPTY()	This operator takes a numeric or text field and provides a true result if the field does not have a value (the equivalent of NULL in the database).	IF(ISEMPTY(Source.Salary), Source.Wage, Source.Payout)	ISEMPTY (Value)
   NOT()	This operator returns 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)
   OR()	This operator returns 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 are 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)
   POW()	This is used to create an exponent. Instead of using A^B for A to the power of B, Incentiveswill 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)
   ROUND()	This operator uses round-to-even rounding (also known as Banker's Rounding) to round the results in the brackets. You can find more information about rounding below this table.	If you want to round your results to the nearest cent, use a formula similar to this 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 operator rounds the results in the brackets down by the number of defined decimal places.	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 rounds down to the specified number of decimal places. If num_digits = 0, then the number rounds down to the nearest integer. If num_digits < 0, then the number is rounded down after the decimal point.
   SUM()	This operator takes the aggregate of whatever is within the brackets and provides 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 provide you with the proper summed amount for your calculation.
   MIN()	This operator takes the minimum value of a set of results.	If 5% commission is paid on each deal to a maximum of $100, the following formula pays 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.)
   MAX()	This operator takes the maximum value of a set of results.	If, for each deal, you receive a 5% commission but there is a minimum of $5 per deal you earn, you can use the following formula to determine whether 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.

   
   
   

   How does rounding work?

   In Incentives, the rounding function uses round-to-even rounding (also known as Banker's Rounding) to round the results in the brackets. The round-to-even method is similar to the common method of rounding, except when the digits following the rounding digit start with a five and have no non-zero digits after it. For example, with common rounding, 4.5 is rounded to 5, but this value is just as close to 4 as it is to 5. With the round-to-even method, 0.51 rounds to 1, but 0.5 rounds to 0.

   When a negative value is passed in the second parameter, the value is rounded up or down at the index to the nearest even integer.

   Table 76. Comparing the round-to-even and common methods of rounding

   Round-to-even method	Common method
   3.016 rounded to hundredths is 3.02	3.016 rounded to hundredths is 3.02
   3.013 rounded to hundredths is 3.01	3.013 rounded to hundredths is 3.01
   3.045 rounded to hundredths is 3.04	3.045 rounded to hundredths is 3.05

   
   
   

6. Select the value format, number of decimal places, display units, and decide whether you want to use the thousands separator.

7. Click Add.

8. Under the Style tab, you can change the styling and formatting of the columns.

   Important

   Aggregation functions such as average, sum, minimum, and maximum cannot be used for calculated column row groups at this time.

9. You'll notice that the added calculation is now one of the columns on the data grid. From the Data tab, you can drag and drop to rearrange the order of how the columns are displayed on the table.

   Note

   You have the option to hide, add links, edit the calculation, rename, and delete the calculation column by clicking on the more options menu (...) of the calculation.

10. Click Save.

1. Drag and drop a table object into the canvas.

2. Select a Source and the Columns you want to show.

3. Under the Default sort header, select Configure sort order.

4. Select the column you want to sort and the order in which it will be sorted.

   Note

   To sort additional columns or to identify the sort order of two or more columns that have the same values, click the + Add sort button (which can be found in the Configure sort order tab) and arrange the sort order.

5. Click Done, and Save your report.

6. In the Sales Portal, you can see that the data table has now sorted the columns in the order you indicated. The numbers beside the column names serve as an index and indicate the order of how the columns are sorted.

   Tip

   In the Sales Portal, you can add another column to sort by clicking and holding the Shift key on the header of a specific column. To go back to how the columns were originally sorted in the Admin page, click refresh.

1. From the Style tab, under Conditional format, click + Add condition.

2. Choose whether to apply conditional formatting to the table header or to the table values.

3. Select the columns where you want to apply conditional formatting.

4. Click Next.

5. Type a formula to define the condition under which you want a value to render.

   • Or use the Functions, Operators, and Source menus to define your formula.

   Tip

   If you are applying conditional formatting to a table header, you must aggregate the source in your formula.

6. Click Next.

7. Define how the cell should appear if the condition is met by changing the font style, cell colors, or how the value is displayed.

8. Click Done.

9. If you want to edit a condition, from the Style tab, under Conditional format, click the more options menu (...) and then click Edit.

1. Click the Add object button to open the menu. Drag and drop an Image object onto the canvas.

2. Drag the corner of the image on the canvas to resize it.

3. On the Style menu, select the image file you want to upload.

4. Click Edit link, and select Web address or Report under Destination type.

   1. To link a web address, select if the web address is a secure (https) or non-secure (http) URL.

      If you want to insert a variable in the URL, place your cursor in the URL where you want to insert the value and select a value from the menu. In the text box, enter the URL. Click Add.

   2. To link a report, select either Presenter Adaptive or Presenter Structured and select the report you want to add.

      If a report has more than one assigned web tab, select a web tab and add any necessary parameters. Click Add.

5. Click Done.