Skip to main content

Adding extended columns to your tables

In Incentives, you can do even more with your data by adding extended columns to your tables. There are two extended column types available in the Data module:

Note

You can add extended columns only to Data and Custom tables.

Adding a calculated column to your table

You can add a calculated column to your table from the Data module. A calculated column is a column type that performs a calculation based on other columns in the table.

Note

You can add calculated columns only to Data and Custom tables.

  1. From the list of tables in the Data module landing page, find the table you'd like to add a calculated column to.

  2. Click the table, or click More actions (...) → View data.

  3. Click + Add → Calculated column.

  4. Type a name for the new column.

    Note

    Column names cannot contain spaces.

  5. Create your formula in the Formula box:

    1. From the Source drop-down menu, select the column for the calculation.

    2. Add any functions or operators.

  6. Click Add.

In the Row Viewer, calculated columns will have a green background with a calculated column icon Function icon to differentiate them from the other columns in your table.

Adding a formula to a calculated column

You can use several formula functions to set up the calculated columns that you want to add to your tables.

Table 20. Formula functions for calculated columns

Function

Description

Example

Syntax

Notes

ROWAVG

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)

ROWMAX

This operator takes the maximum value of a set of results.

If 5% commission is paid on each deal but there's 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. 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.

ROWMIN

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:

MIN(Data.Value)

MIN(Value1, Value2, Value3, etc.)

ROWSUM

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.



Editing calculated columns

You can edit the data in a calculated column from the Row Viewer.

  1. In the Row Viewer, hover over the name of the calculated column.

  2. Click More options (...) → Edit column.

  3. Edit the data in the calculated column.

  4. Click Save.

Duplicating calculated columns

From the Row Viewer, you can duplicate an existing calculated column in your table and edit the data to create a new column.

  1. In the Row Viewer, hover over the name of the calculated column.

  2. Click More options (...) → Duplicate column.

  3. Edit the calculated column as needed.

  4. Click Save.

Deleting calculated columns

You can delete calculated columns from your table from the Row Viewer.

  1. In the Row Viewer, hover over the name of the calculated column.

  2. Click More options (...) → Delete column.

  3. Click Delete.

Adding a lookup column to your table

You can add a lookup column to your table from the Data module. A lookup column uses conditions to extract data from a source table to add to the target table containing the lookup column.

Note

You can add lookup columns only to Data and Custom tables.

  1. From the list of tables in the Data module landing page, find the table you'd like to add a lookup column to.

  2. Click the table, or click More options (...) → View data.

  3. Click + Add → Lookup column.

  4. Select a table as your lookup source.

  5. Select the column you want to look up.

  6. Type a name for the new column.

  7. Click Next.

  8. Under Lookup rules, define the conditions for displaying the source values in your lookup column. You can add the following operators to your rules:

    • Equal to

      Tip

      For text columns, click the Match case checkbox to only show values with a matching case.

    • In range

      Tip

      Click the Inclusive checkbox to include the start and end values.

  9. Click Add.

In the Row Viewer, lookup columns will have a purple background with a lookup column icon Migration icon to differentiate them from the other columns in your table.

Editing lookup columns

You can edit the data in a lookup column from the Row Viewer.

  1. In the Row Viewer, hover over the name of the lookup column.

  2. Click More options (...) → Edit column.

  3. Edit the data in the lookup column.

  4. Click Next.

  5. Edit the lookup rules.

  6. Click Save.

Duplicating lookup columns

From the Row Viewer, you can duplicate an existing lookup column in your table and edit the data to create a new column.

  1. In the Row Viewer, hover over the name of the lookup column.

  2. Click More options (...) → Duplicate column.

  3. Edit the lookup column as needed.

  4. Click Add.

Deleting lookup columns

You can delete lookup columns from your table from the Row Viewer.

  1. In the Row Viewer, hover over the name of the lookup column.

  2. Click More options (...) → Delete column.

  3. Click Delete.