How Can I Specify A Dynamic Transaction Filtering Formula (Advanced)

Sometimes, you want to filter transactions based on dynamic rules.

Do You Need A Filtering Formula?

In most cases, it's sufficient to rely on saved queries to filter transactions. For example, you could define a transaction filter such as this:

And save it as a query called "My Filter", which can then be applied to your plan (on the "Limits" tab):

However, there are situations where you may need to filter transactions in a more flexible way. For example:

• Transactions should be filtered out when one field is equal to another
• Transactions should be filtered out based on a product catalog lookup
• Transactions should be filtered out if commissions have already been paid before
• Etc.

For those cases, you can use a dynamic transaction filtering formula.

Creating a Dynamic Filtering Formula

First, review this article about formulas to understand how they work, and which constructs are available.

Next, create a dynamic transaction filtering formula:

• Edit your plan
• Click on the "Limits" tab (if needed)
• Scroll down to advanced options
• Enter a dynamic transaction filtering formula (examples below)

Your formula's output should be 0 (or less) if the transaction should be filtered out, and strictly more than 0 if the transaction should be processed. For simplicity, you can return a value of 0 to exclude and 1 to include it.

Dynamic Filtering Formula Examples
Assume that you uploaded transactions with the following fields:

• City (a text field)
• WonDate (a date field)

Here are some examples of formulas you could use to configure dynamic transaction filtering.

Exclude Transactions Whose City Starts With 'S' (ex: Seattle)

var $city = [Transaction].[City (Imported)]

--------------------------------------------------------------------

IIF($city LIKE 'S*', 0, 1)

Exclude Transactions Whose WonDate Is Within 1 Month Of The Calculation's Start Date

var $startDate = [Calculation].[start_date_utc]

--------------------------------------------------------------------

var $wonDate = [Transaction].[WonDate (Imported)]

--------------------------------------------------------------------

var $months = DateDiff($startDate, $wonDate, Months)

--------------------------------------------------------------------
IIF($months <= 1, 0, 1)

Exclude Transactions For Which A Commission Has Already Been Paid

var $rep = [Transaction].[Rep (Imported)]

--------------------------------------------------------------------

var $userId = Lookup($rep, 'Id')

--------------------------------------------------------------------

IFEX(LEN($userId) <= 0, 1)

--------------------------------------------------------------------

var $paid = GetCommission($userId, 0, 0)

--------------------------------------------------------------------

IIF($paid <> 0, 0, 1)

• Related Articles

• How Can I Specify A Dynamic Crediting Formula (Advanced)

  Sometimes, you want to credit users or teams based on dynamic crediting rules. Do You Need A Crediting Formula? In most cases, it's sufficient to rely on aliases and system mapped transaction fields to credit users or teams. The following crediting ...

• How Can I Specify a Dynamic Quota Formula (Advanced)?

  Sometimes, you need quotas to be calculated dynamically using a quota formula. Do You Need A Quota Formula? In most cases, it's sufficient to use available quota options (such as "As Percentages of a Per-Target Dynamic Quota" or "As Percentages of a ...

• How Can I Specify a Custom Formula (Advanced)?

  Sometimes, you want to customize attainment and require a formula to do so. Do You Need A Custom Formula? In most cases, calculating a transaction's value based on its revenue or profit is sufficient. When using revenue or profit as an attainment ...

• How Can I Specify a Scoring Formula (Advanced)?

  Sometimes, you want each transaction's value to be calculated using dynamic rules. Do You Need A Scoring Formula? In most cases, calculating a transaction's value based on its revenue or profit is sufficient. When using revenue or profit as an ...

• Recipe - How To Store Per-Transaction Values

  This article describes a recipe to store per-transaction values. For example, you could calculate a "Commissionable" amount once and store it. You can then re-use this value within different reward formulas, or perhaps simply show it to payees. ...