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

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 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 ...
    • 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 ...
    • 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. ...