This page explains how a user can filter and limit data in a Looker Explore. For example, a user can filter the Explore's query results to the last three months or for a certain customer. Users can also limit the number of rows that are displayed in an Explore or the number of pivot columns that are displayed in an Explore.
Filtering data
Filters let you restrict the data that you are viewing to items of interest. Any field in your Looker instance can become a filter.
You also don't necessarily need to add a dimension or measure to your results to filter on it. For example, you can create a query that filters the Order Date to the last 90 days, even though your results only show Customer and Number of Orders.
You can use any combination of these filter types in your query:
Basic Filters are the most commonly used; Looker provides appropriate drop-down lists, calendar widgets, and text fields.
Advanced Matches allow for a more advanced condition for a field, such as intricate text searches, or a date range that starts in the past and extends for a certain number of days.
Custom Filters let you specify detailed business logic, combine
ANDandORlogic, or use Looker functions.
Basic filters
There are a couple of ways to add a basic filter:
- In the Field Picker, select the Filter by field button to the right of the field name.
- In the Results tab of the Data panel, select the gear menu button to the right of the field, and then select Filter on
.
Filters appear in the Filters section above your results. To remove a filter, click the X to its right.
Standard filters
Standard filter options vary by filter type. For example, a time dimension lets you select a time range; a numeric dimension provides you with options such as equal to or greater than.
For text dimensions, Looker displays a list of existing data values for the field. As you type, Looker narrows the list to values that include that text.
When you type a filter value for a text dimension, Looker preserves leading or trailing spaces. For example, creating a filter that contains the text " pants" would not match values that did not contain the leading space, such as "pants and leggings". The filter would match any values that contained the space before the word pants, such as "parachute pants".
If you copy and paste a value into a text filter, leading and trailing spaces are trimmed in the process.
To filter on a large set of values, paste a list of values into the filter field. The maximum number of values that you can paste may be limited by your browser or other elements in your network, and may be as small as 2,000.
To enter a special character in a standard filter, first add a leading backslash. For example, to filter on Santa Cruz, CA, you would enter Santa Cruz \, CA.
To add another option to the filter, click the + to the right of a filter. The new option appears as either an OR condition or an AND condition, depending on the type of filter option.
For example, if you start with the filter condition is greater than 5000, and add the condition is less than 2500, Looker adds an OR condition between these statements. If you then add the condition is not equal to 1500, Looker adds an AND condition. The resulting filter includes order counts over 5,000 or below 2,500, and it excludes order counts of 1,500.
When you add more than one filter, the option to specify between AND and OR filter logic and switch between them appears to the left of the filter fields. A new filter group creates a separate set of filters with the option to specify between AND and OR filter logic between groups.
Filters with user attributes
Looker admins can configure user attributes that specify user-specific values. For example, an admin can define a user attribute for a sales region and assign the appropriate values to individual users or groups of users.
User attributes let you automatically customize a Look or dashboard for each user that views it. The Matches a user attribute provides this user-specific flexibility. For example, you can filter a sales region dimension in a Look to equal a sales region user attribute. The Look will filter for the user's specific sales region and automatically adjust to show each user the data for their own sales region.
Advanced matches filters
To add an advanced matches filter, select matches (advanced) from the filter's drop-down menu.
In the text field, enter your filter expression. To view all available filter expressions, see the Looker filter expressions documentation page.
To enter a special character in an advanced matches filter, first add a leading carat (^). For example, to filter on Santa Cruz, CA, you would enter Santa Cruz ^, CA.
Your Looker admin can configure user-specific values called user attributes that let you automatically customize a Look for each user. To reference a user attribute in an advanced matches filter, use the syntax {{ _user_attributes['name_of_your_attribute'] }}.
Custom filters
Custom filters let you write the fields, constants, functions, and operators for your desired filtering. Looker lets you build an expression that evaluates as true or false. When you run the query, Looker only returns rows for which that condition is true.
Adding a custom filter
To add a custom filter, expand the Filters section.
Select the Custom Filter checkbox in the right side of the Filters bar:
Enter a dimension or function. Looker will display a list of functions, operators, and field names to use in your expression. Click on a term in the drop-down 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 Ctrl+Enter for Windows) to run your query with your custom filter applied.
The Creating Looker expressions documentation page explains how to create Looker expressions and how the editor can assist you.
Looker expressions can use as many fields, functions, and operators as your business logic requires. 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 measures in a custom filter, or use any functions that rely on query results. The Creating Looker expressions documentation page explains how to use Looker expressions in custom fields in more detail. The Looker functions and operators documentation 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:
- Click the
Xto the right of the expression to delete it. - Deselect the Custom Filter checkbox to stop using the expression. If you do not close the page, Looker remembers what you typed and your expression reappears if you click Custom Filter again.
Filtering dimensions: restricts raw data before calculations
When you filter on a dimension, you restrict the raw data before any calculations are made.
For example, say you've created an Explore to view how many orders were placed each day. The Explore includes the Orders Created Date and Orders Count fields.
Then, you add a filter on an Order Amount dimension with the condition is greater than 50.
Looker removes all orders that are less than $50 from the data. The measure still counts the remaining orders for each day, but the measure values are much lower.
Filtering measures: restricts results after calculating measures
When you filter on a measure, you restrict the results after the measure has been calculated
For example, say you've created an Explore to view how many orders were placed each day. The Explore includes the Orders Created Date and Orders Count fields.
Then, you add a filter on the Order Count measure with the condition is greater than 350.
Looker first counts all orders for each day. Then, the filter is applied. The filter removes the days that had 350 or fewer orders, leaving only the days that had more than 350 orders.
Limiting data
Looker supports up to 5,000 rows and an unlimited number of columns for unpivoted queries. For browser performance, 50 or fewer columns is recommended. Looker supports up to 200 columns for pivoted queries, but sets a default column limit of 50 columns.
To see a subset of your complete query results, you can set a row limit, a column limit, or both.
Row limits
You can set a row limit of up to 5,000 rows. Looker will warn you if you might be hiding data by setting a row limit that is too low. The sort order is important: Looker first applies the sort and then applies the limit. For example, in a query that contains the fields Users State and Orders Count, sort by Orders Count to see the top five states by the number of orders sold.
For more information about row limits in other parts of Looker, see the What are all the row limits in Looker? Best Practices page.
If you reach a row limit, you cannot sort by row totals or table calculations.
Column limits
If you added a pivot to your report, you can apply a column limit of up to 200. Looker warns you if you might be hiding data by setting a column limit that is too low. The sort order of your pivot is important: Looker first applies the sort and then applies the limit. For example, in a query that contains the field Orders Count and that is pivoted by the Orders Created Month field, sort by Orders Created Month to see the five most recent months when orders were created.
Dimensions, dimension table calculations, row total columns, and measure table calculations outside of pivots are not counted toward the column limit. Pivoted groups count as one column toward the column limit.