By Kristi Smith
Note: Looker, now part of Google Cloud’s Looker Studio, remains a powerful BI tool for creating dashboards. While this guide focuses on combining filters in Looker, the steps also apply to Looker Studio environments.
Matches Advanced Filters
I’m constantly impressed with the advanced filter functionality in Looker. It’s so convenient…and fairly intuitive, once you get the hang of the syntax. As always, if you run into an issue or need more context on how to combine filters in Looker, Looker’s documentation is a great place to start.
Advanced filters allow you to specify an expression for a single field to filter a query in Looker. I like to refer to this as the “natural language” filter: you can use advanced filter expressions on string, date & time, boolean, number and location attributes.
I tend to use advanced filters most when working with dates. For example, an advanced filter of “this year, last year, 2 years ago” and pivoting on the year dimension will easily allow a developer to create a multi-line chart, with each year represented by a different line.
Absolute date filters use the specific date value to generate query results, which is useful when you have specific date ranges to work with. The last example in this blog begins with an absolute date filter. Relative date filters are useful when creating queries with rolling date values. Seriously, creating rolling date visualizations are super easy in Looker when taking advantage of advanced filters. For example 30 days ago for 30 days is essentially saying include the previous 30 completed days and exclude the current day.
Custom Filters
If the natural language filter isn’t quite giving me what I want, then I’ll look into using custom filters. Custom filters allow a developer or power user to get a bit more creative with filters that may or may not be available with some of the simpler filter types. If you have complex business logic, custom filters can be your saving grace. A classic use case for using a custom filter is filtering on a specific day of the month. For example, let’s say I’m interested in looking at the 15th day of the month:
- Expand the filter section and select the Custom Filter checkbox in the upper right-hand corner.
2. Begin typing your expression. Looker will help by prompting you with functions, operators and field names that are available for the expression. If you hover over an option you receive a pop-up to the right providing more detail about what the function and operators do. Again, Looker’s documentation on this topic is quite helpful, so I encourage you to keep this handy.
3. The expression you create must evaluate to true or false, if the expression returns true then it is included in the result set. Since I’m interested in the 15th day of the month, my filter ends up looking like this:
You can combine expressions with the use of logical and comparison operators to get as simple or complex as necessary. An easy example is shown below using the AND operator that returns the first 10 days of each month:
Combining Natural Language and Custom Filters
It’s possible that a custom filter or advanced filter alone will not deliver the result you are striving for. In those situations, just combine both to satisfy the requirement. “By your powers combined…”
Below is a prime example of using both types of filters to achieve a desired outcome. My last customer needed to create a report that displayed data for the current month through the previous day (yesterday). This was quite easy using the advanced absolute date filter: “2019–09–01 to today”.
This 100% gave her what she was looking for, but you’ll notice she was using an absolute date filter and hard coding the first day of the month. I wanted to see what other options were available to make this filter more dynamic, so she wouldn’t have to update this report every month. I tried other variations of the natural language filter, such as “this month to today” and “current month to today,” but unfortunately, Looker did not like that syntax. In the end, I used a combination of custom and natural language filters.
I applied a month filter and set it to “this month” and created the following custom filter:
The key takeaway from this example is to recognize that the now() function returns the current date and time.
As I mentioned at the beginning of this blog, the filtering capabilities in Looker are quite impressive. So the next time you are working with date filters I encourage you to have a bit of fun and try to get creative with your solution. You may end up impressing yourself and Captain Planet!
Red Pill Analytics is a unique analytics intelligence firm, experienced in advising organizations on overall data strategy. Our knowledge spans a variety of offerings across all of the major public cloud providers. From proof-of-concept to implementation to training your users, we can help. If you are interested in guidance while working with any of your data projects, feel free to reach out to us on our website or find us on Twitter, Facebook, and LinkedIn.
