Support Portal

Filtering and Limiting Data

Analytics lets you limit the data you see based on criteria you specify. For example, you might want to filter the results to the last three months, or for a certain customer. You can also limit the number of rows displayed, or the number of pivot columns displayed. This tutorial introduces you to filtering and limiting data in Analytics.

Filtering Data Overview

You can restrict the data you’re viewing to items of interest by adding filters. For example, you might limit the results to certain dates, customers, locations, or anything else that is part of your data. Any field can become a filter.

You don’t necessarily need to add a dimension or measure to your results in order to filter on it. For example, you can create a query that filters the Order Date to just the last 90 days, even though your query only shows Customer and Number of Orders.

There are several different types of filters:

  • Basic Filters - Most of the time you’ll use this type of filter. In these cases Analytics provides appropriate dropdown lists, calendar widgets, and text fields you can use to put in your restriction.
  • Advanced Matches - Sometimes you’ll want to specify a filter with a more advanced condition for a field, like intricate text searches, or a date range that starts in the past and goes for a certain number of days. You can often achieve your goal with this type of filter.
  • Custom Filters - When you need to specify more detailed business logic - especially when you need to combine AND and OR logic together, or want to use Analytics functions - you can use custom filters.

You can use any combination of these filter types in your query.

Basic Filters

There are several different ways to add a basic filter:

You’ll see that a filter appears in the Filters section above your results. To remove a filter, click the X to its right.

Standard Types of Filter Options

The available options for the filter depend on its type.

For example, a time dimension will have options to select a time range, while a numeric dimension will have options like equal to or greater than.

For text dimensions, Analytics helps you choose filter values by displaying a list of existing data values for that field. As you type, Analytics narrows the list to just those values that include that text.

Advanced Matches Filters

Adding an advanced matches filter starts the same way as a basic filter:

Next, select matches (advanced) from the dropdown menu of your filter:

This will give you a text field to type in a filter expression. To remove the filter, click the X to its right. Clicking the help link takes you to a page listing the various filter expressions you can use.

Use Case: Shipment Event History Combined

To filter on an intersection of two or more shipment events, use the advanced matches filter. 

The field 'Event History Combined' includes all the events a shipment had in its timeline in one field in an alphabetical order.

In this example, 281  had the events: Delivered, Delivery requested at the carrier's, First delivery attempt succeeded, First hub scan, Hub scan, Out for delivery, Shipment created, Warehouse pick-up. The events are in an alphabetical order not in a chronological order. 

To filter only on the shipments that have for example the event 'delivered to a pickup point' AND 'delayed', you need to use matches filter advanced: 

%Delayed%Delivered to a pick-up point%

'%' means that before, in between and after the two events, more events can be listed. It is important that also the filtered events are written in an alphabetical order and do not have any spelling mistakes

Custom Filters

Custom filters let you create filters with custom conditions that might not be available with the other, simpler filter types. These conditions can be simple or complex. You write the fields, constants, functions, and operators that express the filtering that you want.

When you add a custom filter, Analytics displays an editor for you to build an expression that evaluates as true or false. When you run the query, Analytics will only return rows for which that condition is true.

Adding a Custom Filter

To add a custom filter, expand the Filters section and click the Custom Filter checkbox in the upper right:

To create an Analytics expression for your custom filter, start typing a dimension or function. Analytics will display a list of functions, operators, and field names that you might want to use in your expression. Click on a term in the dropdown to add it to your expression. When finished, your expression must evaluate to true or false.

Click Run (or use the keyboard shortcut command-enter for Mac or control-enter for Windows) to run your query with your custom filter applied.

The Creating Analytics Expressions page explains how to create Analytics expressions and how the editor helps you.

Analytics expressions can use as many fields, functions, and operators as your business logic requires. Just keep in mind that the more complex your condition, the more work your database must do to evaluate it, which may lengthen query times.

Because custom filters are used to create a database query, you cannot refer to measure in a custom filter, or use any functions that rely on a finished result set. The Analytics Functions and Operators page shows you the available functions and identifies which can be used in a custom filter.

Removing a Custom Filter

To remove a custom filter, you can:

  • Click the X to the right of the expression to completely delete it.
  • Deselect the Custom Filter checkbox to stop using the expression. As long as you do not close the page Analytics will remember what you have typed, and your expression will re-appear if you click Custom Filter again.

The Difference Between Filtering Dimensions and Filtering Measures

Filters are applied differently to dimensions and measures.

Filtering Dimensions: Restricts Raw Data Before Calculations

When you filter on a dimension, you are restricting the raw data before any calculations are made. For example, if you want to see how many orders were placed each day for the previous seven days, you could create an Explore that looks like this:


Then you decide you only want to count orders over $50, so you add that filter:


You will still see results for each day, but the order counts are much smaller. All of the orders that are less than $50 are removed from the data, and what remains is counted by the measure for each day.

Filtering Measures: Calculates First, Then Restricts the Results

When you filter on a measure, however, you are restricting the results after the measure has been calculated. For example, start from the same Explore used in the prior section:


This time, filter your results to only see the days where you had more than 325 orders. You get these results:

 

All of the orders are counted for each day and then the filter is applied. The filter removes the days that had 325 or fewer orders, leaving the remaining four days that had more than 325 orders.

Limiting Data

Sometimes you only want to see a subset of the complete results from your query. You can do this in Analytics by setting a row limit, column limit, or both. Analytics supports a row limit of up to 5,000 and recommends a column limit of 50 or less.

When you set a row limit Analytics will only display up to the number of rows you have set. Analytics will warn you if you might be hiding data by setting a row limit that is too low. Your sort order is important in these situations; Analytics first applies the sort, and then applies the limit. For example, if you only want to see the top five states by number of orders sold, make sure you’re sorting by orders.

Note that if you reach a row limit you will not be able to sort row totals or table calculations.

If you’ve added a pivot to your report, you can also set a column limit. Analytics will warn you if you might be hiding data by setting a column limit that is too low. Again, the sort order of your pivot is important, because Analytics first applies the sort, and then applies the limit. For example, if you want to see the five most recent months when orders were created, make sure you’re sorting by the order created month.

Conclusion

Now that you know how to limit your results to the data you’re interested in, learn how table calculations can help you with your analysis.

Using Table Calculations