Create Basic Data Filters

Learning Objectives

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

• Create a ByRep data filter.
  
• Verify data filter results.
  
• Create InPeriod and ClosedWon data filters.
  
• Combine data filters.
  

Create a ByRep Data Filter

One of the simplest and most essential ways to filter your data is by the rep who owned the opportunity, or closed the deal. You accomplish this by creating a ByRep data filter. Let’s create the filter.

Navigate to Spiff Designer and to the Data Filters tab in the left panel. If you want to filter records that were synced in from an Opportunities object, click New Filter after clicking the three dots next to that object name. Name your filter ByRep in the right panel.

Underneath the filter name there are options to work in the Guided Editor or the Syntax Editor.

These instructions use the Guided Editor. Keep the Simple option selected under Guided Editor.

There are three picklists: OwnerId, is equal to, and rep.Id.

OwnerId is the field commonly used in Salesforce on the Opportunities object to identify the rep that closed the deal. The rep.Id syntax is how you return the rep's Salesforce Spiff User ID. Select OwnerId is equal to rep.Id to show records where the OwnerId field matches the User Id of the selected rep. In this way, you match deals to the correct rep.

You can use a different field than OwnerId in your source data to attribute deals to reps. In that case, reference that field name to create your ByRep filter.

Verify Your Data Filter Results

You can use the context selector above your filtered data to validate the results of the ByRep data filter.

You can select various statement periods, plans, and users in the dropdowns, assuming plans have already been created and users assigned. Select a rep in the context selector to show their assigned deals. Switching reps changes which deals are displayed.

You might wonder why some deals remain with a CloseDate in a particular period when the context is set to a different statement period. The ByRep filter only identifies each record such that the OwnerId on the record is the same as the rep's user Id. A rep’s OwnerId is shown regardless of the close date. You must use other data filters to sort deals closed within certain dates. Then you can change the period of the context to different months and have that reflected in the records that are displayed.

Filter Data by Date

Let’s build a data filter using date conditions. Create a new data filter, name it InPeriod, and proceed with Guided Mode.

In the first picklist, choose a date field from your opportunities object, such as CloseDate. The next two picklists define how CloseDate is filtered. Greater than would be after a given point in time, while less than would be prior. SelectiswithinCurrent Period for a data filter where all close dates are within the current period.

Toggling the data filters menu to Syntax Editor displays the following logic behind the InPeriod data filter.

date_between?(CloseDate, statement_period.start_date, end_of_period(statement_period.start_date))

Date_between? is a Salesforce Spiff function, CloseDate is a data field, and the rest is syntax to reference Spiff's dynamic statement period dates. You'll know that the syntax is correct and recognized by Spiff when the text is highlighted in color.

Statement_period.start_date and statement_period.end_date return the start and end dates of the statement period that is being calculated. Using this syntax allows each calculation or data filter to dynamically change with each statement that you calculate. If you used a specific, hard-coded date for the start of the period, that data filter would only work for that date until you update that value.

End_of_period returns the last day of the statement period for the provided date (statement period start date).

Use this table as a reference in the Syntax Editor when using mathematical symbols to define your date ranges.

Create a ClosedWon Data Filter

To create a ClosedWon data filter, reference the fields that you use to designate that a deal is closed and that a deal is won. In Salesforce, the Opportunities object has two commonly used fields called IsClosed and IsWon. These are Boolean fields containing true and false values. You can add multiple fields to a single data filter by adding new conditions.

Create a new filter called ClosedWon. In the Guided Editor, select IsClosedis equal totrue across the picklists. Click + New Condition. And select IsWonis equal totrue.

Now you have a filter where opportunities displayed must be both Closed and Won.

Combine Data Filters

You can combine any prebuilt data filters together using AND/OR conjunctions. Now that you have defined ByRep, InPeriod, and ClosedWon data filters, let’s combine them into one data filter called ClosedInPeriod.

Create a new data filter and name it ClosedInPeriod. Instead of referencing a field in the first menu, simply reference the previously built ByRep filter. Click + New Condition and leave the default And conjunction selected. In the next menu, select the ClosedWon filter. Do this one more time to add the InPeriod filter. The result is a data filter that returns records by a specific rep where the deal was closed and won within a selected statement period.

It’s best practice to create data filters for a single field, then combine these single-field data filters to create more complex data filters. Why? You can modify them and troubleshoot any potential problems with ease. It’s also easier to copy and edit such data filters, without having to recreate simple data filters like InPeriod multiple times.

You can apply the principles learned in this unit to other data filters that you need to create. The possibilities are endless!

Now that you’ve built a few helpful data filters, learn about additional capabilities in the next unit.