Overview

Using Filters is a core skill in Pipeliner that allows you to manipulate sets of data. Filters can be used to select the data you want to:

Report on in Standard, Pivot, Advanced Reports, Forecasts, Targets AND Dashboards
View Insights based on (for example, Life Cycle based on a specific Lead Source)
Base Target settings on (for example, Target revenue for this year based on Opportunity Types of “New Business” rather than “Renewals”)
See custom Navigator dashboard metrics on (for example, for one particular sales unit or territory)
Export (if you have rights)
Change Ownership (using the Ownership button, for example when a user leaves and you need to assign their records to someone else)
Bulk Update (change a value on multiple records in one go)

This article is aimed at Power Users and Pipeliner Admins with an in depth knowledge of working with Filters in Pipeliner and covers using Complex Expressions with different combinations of AND & OR criteria and parentheses. Please read Filters in Detail before moving on to using Complex Expressions.

Filters are constructed by:

selecting one or more fields, the values of which identify the records you wish to include in your filtered dataset
using comparison operators such as “IS”, “IS NOT”, “MORE THAN OR EQUAL TO”, “BETWEEN” or a dynamic range (for dates) to compare the data in the chosen fields to the values which identify the data you are interested in (the full list of comparison operators is in this article)
joining multiple fields with their comparison operators in your expression using the logical operators AND & OR.

The end result of a filter is evaluated using comparison operators applied to fields and the logical operators in the order that they appear in the filter left to right. Parentheses are used to build sub clauses within your expression that are evaluated first and the results then added to the next logical operator.

Creating a Complex Filter

From the main menu area - Opportunities, Accounts etc - navigate to the Filters tab in the "Power Panel" and, if necessary, switch to “Custom” and then add the fields you want to use in your filter ⤵

Logical Operator Expressions

Click on the dropdown arrow in the top right corner of the Filters area to access the options for using logical operators ⤵

The three options available are:

Apply to all (AND)

All of the values in all of the fields you have selected must meet the comparison operator criteria. The more fields you have selected, the fewer records will show in your filtered dataset.

The example below is filtering Opportunities by Sales Step, Opportunity Value and Time to Purchasing Decision.

Of the 35 Opportunities that are in the pipeline, only 4 of them meet all three criteria specified i.e. that for each Opportunity, Sales Step must be one of Scope or Proposal as well as the Opportunity Value being more than or equal to $10000 and the value selected in Time to Purchasing Decision must be “<3 Months” ⤵

If I was to write out this filter, it would look like this:

(Sales Step = “Scope” OR “Proposal”) AND Opportunity Value >= “10000” AND Time to Purchasing Decision = “< 3 Months”

Apply to any (OR)

Any of the values in any of the fields you have selected must meet the comparison operator criteria. The more fields you have selected, the more records will show in your filtered dataset.

Using the same example, filtering Opportunities by Sales Step, Opportunity Value and Time to Purchasing Decision, 29 meet one (or more) of the criteria i.e. that for each Opportunity, Sales Step must be one of Scope or Proposal or the Opportunity Value must be more than or equal to $10000 (irrespective of Sales Step) or the value selected in Time to Purchasing Decision must be “<3 Months” (irrespective of Sales Step and Opportunity Value) ⤵

This one would look like this:

(Sales Step = “Scope” OR “Proposal”) OR Opportunity Value >= “10000” OR Time to Purchasing Decision = “< 3 Months”

Custom Expression (AND/OR)

This option allows you to build your own expression that is evaluated in your specified order and which can use any combination of parentheses ⤵

When selecting Custom Expression, the initial expression will be the same as the one you last used i.e. all ANDs or all ORs. Each field is represented by a number which is the order that the fields appear in your filter. In our example, 1 is the Sales Step field, 2 is Opportunity Value and 3 is Time to Purchasing Decision.

Click on the pencil symbol to edit your expression, changing the ANDs/ORs and adding parentheses to make clear the order in which you want the expression to be evaluated. When you’re happy with your expression, click on the tick symbol to apply it (or the X to cancel your changes) ⤵

This example is asking for all opportunities where either Sales Step is “Scope” or “Proposal” or both Opportunity Value >=10000 and Time to Purchasing Decision = “< 3 Months”. The result is 14 Opportunities ⤵

(Sales Step = “Scope” OR “Proposal”) OR (Opportunity Value >= “10000” AND Time to Purchasing Decision = “< 3 Months”)

Whereas now it’s asking for all opportunities where either Sales Step is “Scope” or “Proposal” or Opportunity Value >=10000 and, for all opportunities meeting one or more of those 3 criteria, Time to Purchasing Decision must be “< 3 Months”. This variation returns 12 Opportunities ⤵

( (Sales Step = “Scope” OR “Proposal”) OR Opportunity Value >= “10000”) AND Time to Purchasing Decision = “< 3 Months”

Modifying the order in which fields are evaluated

You can modify the order by typing in a field number wherever you need it in your expression ⤵

Our filter is now asking for all opportunities where either Sales Step is “Scope” or “Proposal” or Time to Purchasing Decision is “< 3 Months” and, for all opportunities meeting one or more of those criteria, Opportunity Value must be more than or equal to $10000. This variation returns 10 Opportunities.

Adding Additional Filter Fields

If you are using a Custom Expression and you add additional fields to your filter criteria, they will show in grey until you add them to your expression ⤵

The new fields will be ignored until they are added into your expression ⤵

Text view of Custom Expression

Hover over the i symbol to read a “friendly” text version of your expression ⤵

Validation Errors

When you create a custom expression, you will receive a validation error message if it is not correctly formulated. The table below shows the errors you might encounter ⤵

Validation order	Formula Example	Validation error	Description
1.	1 ANDK 2 OR 3 xxxx 1 AND 2 OR 33 AND 0a0	Wrong expression syntax. Formula accepts only: numbers, AND, OR, '(', ')'	Validation for wrong characters or words
2.	1 AND 2 OR 33 AND 00	Unknown field reference. These fields don’t exist: ‘33', '00’	Validation when the formula includes unknown field reference
3.	1 AND ( OR 3 AND 4)	OR or AND statement is missing field reference	AND or OR statement must have the number or parentheses directly on the left/right side - not a space in between as in this example
4.	1 ( 2 AND 3)	Field reference is missing OR or AND statement	There is no AND or OR between field 1 and the bracket
5.	1 AND 2)) (( 2 AND 3) 1 OR	Wrong expression syntax. Field or parentheses are missing	Missing parentheses or missing fields
6.	1 AND 2 OR 3 OR 4 1 AND 2 OR 3 AND 4	The order of operations isn't clear. Use parentheses to specify the order between AND and OR	Validation when the OR statement must be always in brackets if it is followed by AND statement Valid options are: 1 AND (2 OR 3 OR 4) 1 AND (2 OR 3) OR 4 1 AND (2 OR 3) AND 4 (1 AND 2) OR (3 AND 4)

Saving a Custom Profile View

If you’re going to re-use these criteria, you’ll want to save a custom profile view. This will then be added to your profile dropdown selections along with the default "All User" and "My" profiles.

Selecting the gear icon in the upper right of the Power Panel and choose the "Save As" option to Save this newly created Profile View ⤵

Give your new profile a name and then click “Save”. This means you don’t overwrite your original view and helps build a list of easily-accessed filters/views that you use frequently.

Sharing Your Profile with Other Users

In the Power Panel select if this view should be:

PRIVATE — only you can see it
PUBLIC — all users in Pipeliner can see it and use it
CUSTOM — selected Pipeliner users can see it and use it

NOTE: if you share a profile with other users, when they use it, they will still only be able to see those records that they have rights to based on their user setup.

NOTE: if you share a profile, other users are not able to modify it (though they can Save a Copy) and they will not be able to delete it or remove it from their profile dropdown list. For this reason, only share with users who will find the profile useful and valuable.

Frequently Asked Questions (FAQs)

Can I create custom expressions everywhere in Pipeline?

You can create custom expressions everywhere in the Pipeliner web app. You can’t create them in the Mobile app - though you will be able to access previously created filters using custom expressions.