Set Up Range and Lookup Tables

Learning Objectives

After completing this unit, you’ll be able to:

• Explain range and lookup tables.
  
• Create and edit range tables.
  
• Create lookup tables.
  

What Are Range and Lookup Tables?

Salesforce Spiff is a leading sales compensation software that automates and simplifies the process of calculating and managing sales commissions. Range and lookup tables are common features of many commission plans. You use range tables for sales accelerators, and lookup tables to apply different rates based on product categories or SKUs. Accelerators typically involve paying reps' commissions at an increasing rate as the rep increases in attainment. For example, if the rep sells between $0 and $20,000, they qualify for a 4% commission rate. But if they close between $20,000 and $50,000, they qualify for a 6% commission rate. In Spiff, the easiest way to put an accelerator into your plan logic is to build a range table and then reference the table in an accelerator function.

Now let’s say you want to apply different rates based on the type of product the rep sells. For example, an electronics company gives a 10% commission on computer sales, 5% on printer sales, 12% on TV sales, and 2% on miscellaneous sales. This is best listed in a lookup table.

So range tables are used for numerical ranges, while lookup tables are used to look up string values. In this module, find out how to set up these tables and use their respective functions.

Create and Edit Range Tables

Consider this sample range table.

Here’s how you create one in Spiff. Under the Plans tab in Spiff Designer, click next to the search bar and select New Range Table.

If you create the range table here, it will live outside any specific plan. To create it for a specific plan, click next to the plan name and select New Range Table. You can always drag and drop a table to change its location!

To rename your table, hover over your new range table and click or double-click the table name. Your table has a Name and a Display Name. The Name must start with a letter and contain only letters, numbers, and underscores. This name is what’s referenced in the calculations to use the table. The Display Name is what appears in the Library panel and can contain spaces and special characters.

To enter data into the table, or edit values previously entered, click a cell in any column of your range table. Each line is a tier. Keep the lower bound on the first tier open to allow for any value less than the upper bound of the first tier. And keep the upper bound on the last tier open to allow for any values above the lower bound of that last tier. The return value is the percent you want to be returned for each tier. Just remember to press save when you are done!

Range tables are extendable, meaning that rather than creating a new table each year, you add columns for successive plan years. If you want to use the same ranges for other rules or plans, name the return column AE Rates or Mid Market Rates. When you build the calculation later, specify the column to return. You add or delete columns by clicking the dropdown on a column header. Note that you must be in edit mode to do this.

To edit the column header name, double-click the header itself. You can’t rename or delete the first three columns as they’re necessary for the functionality of this table type.

You can use previously created worksheet calculations and mathematical operations within a range table. Just make sure to spell the worksheet and formula name exactly, including capitalization. Here’s an example.

A calculation named CorpAEQuota is being multiplied by various percentages to create ranges. And an individual commission rate named CorpICR is being returned with decelerators/accelerators.

Create Lookup Tables

Lookup tables are similar to range tables except that they’re used for looking up a string value. Here’s a sample lookup table in which we’re referencing a Sales Type field that indicates what kind of sale is being applied to the deal.

To create a lookup table, navigate to the Plans tab in Spiff Designer, click next to the search bar, and select New Lookup Table. You can set up your lookup table the same way you set up the range table, and can edit its name similarly.

In a lookup table, the lookup value is a string. Wrap the value with quotation marks to ensure Spiff recognizes it and identifies the appropriate return value. The lookup value can either reference a specific field in your data, or a calculated field created from your data.

Now that you’ve learned how to create and edit range and lookup tables, review a few functions that use the tables in the next unit.