You can use filters inside of the Reporting tool to get specific data. This guide will show you how to use standard and advanced filters.
Table of Contents
Basic Report Filtering
Filters allow you to structure the current report's data based on the settings you set.
To use filters, click on the filter section to expand the filter parameters. Depending on the selected report, the available settings will be different. In the above example, the available filters are; Date, Event, Location Group, Location, and Employee.
Clicking on the setting drop-down will show more options for that parameter.
You can add multiple parameters of the same type by clicking the Plus icon.
Once you have the filter settings set click the Run button. In the above example, the report will show all data for Concession Stand and Grab & Go Locations for the Past 8 Quarters.
Note that moving to another report will not keep the same filter.
Advanced Filtering
Using the matches advanced feature, you can filter set more specific parameters to find information on exact dates, times, and specific words. This part of the article is written based on the Looker support article that Clover Sport uses to host its reporting.
Date Searching
When clicking on a filter you can choose the matches (advanced) option from the drop-down to open an expression field. Using this field you can set advanced parameters to get more exact filters. In the above example.
For the following examples …
- {n} is an integer.
- {interval} is a time increment such as hours, days, weeks, or months.
- The phrasing you use determines whether the {interval} will include partial time periods or only complete time periods. For example, the expression 3 days includes the current, partial day as well as the prior two days. The expression 3 days ago for 3 days includes the previous three complete days and excludes the current, partial day. See the Relative Dates section for more information.
- {time} can specify a time formatted as either YYYY-MM-DD HH:MM:SS or YYYY/MM/DD HH:MM:SS, or a date formatted as either YYYY-MM-DD or YYYY/MM/DD. When using the form YYYY-MM-DD, be sure to include both digits for the month and day, for example, 2016-01. Truncating a month or day to a single digit is interpreted as an offset, not a date. For example, 2016-1 is interpreted as 2016 minus one year, or 2015.
These are all the possible combinations of date filters:
Example | Description |
---|---|
2018/05/29
|
sometime on 2018/05/29 |
2018/05/10 for 3 days
|
from 2018/05/10 00:00:00 through 2018/05/12 23:59:59 |
after 2018/05/10
|
2018/05/10 00:00:00 and after |
before 2018/05/10
|
before 2018/05/10 00:00:00 |
2018/05
|
within the entire month of 2018/05 |
2018/05 for 2 months
|
within the entire months of 2018/05 and 2018/06 |
2018/05/10 05:00 for 5 hours
|
from 2018/05/10 05:00:00 through 2018/05/10 09:59:59 |
2018/05/10 for 5 months
|
from 2018/05/10 00:00:00 through 2018/10/09 23:59:59 |
2018
|
entire year of 2018 (2018/01/01 00:00:00 through 2018/12/31 23:59:59) |
Date filters can also be combined together:
- To get OR logic: Type multiple conditions into the same filter, separated by commas. For example,
today, 7 days ago
means “today or 7 days ago”. - To get AND logic: Type your conditions, one by one, into multiple date or time filters. For example, you could put
after 2014-01-01
into a Created Date filter, then putbefore 2 days ago
into a Created Time filter. This would mean “January 1st, 2014 and after, and before 2 days ago”.
Absolute Dates
Absolute date filters use the specific date values to generate query results. These are useful when creating queries for specific date ranges.
Example | Description |
---|---|
2018/05/29 |
sometime on 2018/05/29 |
2018/05/10 for 3 days |
from 2018/05/10 00:00:00 through 2018/05/12 23:59:59 |
after 2018/05/10 |
2018/05/10 00:00:00 and after |
before 2018/05/10 |
before 2018/05/10 00:00:00 |
2018/05 |
within the entire month of 2018/05 |
2018/05 for 2 months |
within the entire months of 2018/05 and 2018/06 |
2018/05/10 05:00 for 5 hours |
from 2018/05/10 05:00:00 through 2018/05/10 09:59:59 |
2018/05/10 for 5 months |
from 2018/05/10 00:00:00 through 2018/10/09 23:59:59 |
2018 |
entire year of 2018 (2018/01/01 00:00:00 through 2018/12/31 23:59:59) |
Relative Dates
Relative date filters allow you to create queries with rolling date values relative to the current date. These are useful when creating queries that update each time you run the query.
For all the examples below, assume today is Friday, 2018/05/18 18:30:02. Weeks start on Monday unless you change that setting with week_start_day
.
Seconds
Example | Description |
---|---|
1 second |
the current second (2018/05/18 18:30:02) |
60 seconds |
60 seconds ago for 60 seconds (2018/05/18 18:29:02 through 2018/05/18 18:30:01) |
60 seconds ago for 1 second |
60 seconds ago for 1 second (2018/05/18 18:29:02) |
Minutes
Example | Description |
---|---|
1 minute |
the current minute (2018/05/18 18:30:00 through 18:30:59) |
60 minutes |
60 minutes ago for 60 minutes (2018/05/18 17:30:00 through 2018/05/18 18:29:59) |
60 minutes ago for 1 minute |
60 minutes ago for 1 minute (2018/05/18 17:30:00 through 2018/05/18 17:30:59) |
Hours
Example | Description |
---|---|
1 hour |
the current hour (2018/05/18 18:00 through 2018/05/18 18:59) |
24 hours |
the same hour of day that was 24 hours ago for 24 hours (2018/05/17 18:00 through 2018/05/18 17:59) |
24 hours ago for 1 hour |
the same hour of day that was 24 hours ago for 1 hour (2018/05/17 18:00 until 2018/05/17 18:59) |
Days
Example | Description |
---|---|
today |
the current day (2018/05/18 00:00 through 2018/05/18 23:59) |
2 days |
all of yesterday and today (2018/05/17 00:00 through 2018/05/18 23:59) |
1 day ago |
just yesterday (2018/05/17 00:00 until 2018/05/17 23:59) |
7 days ago for 7 days |
the last complete 7 days (2018/05/11 00:00 until 2018/05/17 23:59) |
today for 7 days |
the current day, starting at midnight, for 7 days into the future (2018/05/18 00:00 until 2018/05/24 23:59) |
last 3 days |
2 days ago through the end of the current day (2018/05/16 00:00 until 2018/05/18 23:59) |
7 days from now |
7 days in the future (2018/05/25 00:00 until 2018/05/25 23:59) |
Weeks
Example | Description |
---|---|
1 week |
top of the current week going forward (2018/05/14 00:00 through 2018/05/20 23:59) |
this week |
top of the current week going forward (2018/05/14 00:00 through 2018/05/20 23:59) |
before this week |
anytime until the top of this week (before 2018/05/14 00:00) |
after this week |
anytime after the top of this week (2018/05/14 00:00 and later) |
next week |
the following Monday going forward 1 week (2018/05/21 00:00 through 2018/05/27 23:59) |
2 weeks |
a week ago Monday going forward (2018/05/07 00:00 through 2018/05/20 23:59) |
last week |
synonym for “1 week ago” |
1 week ago |
a week ago Monday going forward 1 week (2018/05/07 00:00 through 2018/05/13 23:59) |
Months
Example | Description |
---|---|
1 month |
the current month (2018/05/01 00:00 through 2018/05/31 23:59) |
this month |
synonym for “0 months ago” (2018/05/01 00:00 through 2018/05/31 23:59) |
2 months |
the past two months (2018/04/01 00:00 through 2018/05/31 23:59) |
last month |
all of 2018/04 |
2 months ago |
all of 2018/03 |
before 2 months ago |
all time before 2018/03/01 |
next month |
all of 2018/06 |
2 months from now |
all of 2018/07 |
6 months from now for 3 months |
2018/11 through 2019/01 |
Quarters
Example | Description |
---|---|
1 quarter |
the current quarter (2018/04/01 00:00 through 2018/06/30 23:59) |
this quarter |
synonym for “0 quarters ago” (2018/04/01 00:00 through 2018/06/30 23:59) |
2 quarters |
the past two quarters (2018/01/01 00:00 through 2018/06/30 23:59) |
last quarter |
all of Q1 (2018/01/01 00:00 through 2018/03/31 23:59) |
2 quarters ago |
all of Q4 of last year (2017/010/01 00:00 through 2017/12/31 23:59) |
before 2 quarters ago |
all time before Q4 of last year |
next quarter |
all of the following quarter (2018/07/01 00:00 through 2018/09/30 23:59) |
2018-07-01 for 1 quarter |
all of Q3 (2018/07/01 00:00 through 2018/09/30 23:59) |
2018-Q4 |
all of Q4 (2018/10/01 00:00 through 2018/12/31 23:59) |
Years
Example | Description |
---|---|
1 year |
all of the current year (2018/01/01 00:00 through 2018/12/31 23:59) |
this year |
all of the current year (2018/01/01 00:00 through 2018/12/31 23:59) |
next year |
all of the following year (2019/01/01 00:00 through 2019/12/31 23:59) |
2 years |
the past two years (2017/01/01 00:00 through 2018/12/31 23:59) |
last year |
all of 2017 |
2 years ago |
all of 2016 |
before 2 years ago |
all time before 2016/01/01 |
String Searches
String searches, like Items and locations, have advanced parameters as well.
In the above example, you can filter a report and remove items from the results. The above example will return results that exclude anything with the word "Hotdog" in it.
Matches in string filters depend on thecase_sensitive
setting in your model file, and on whether your dialect supports case sensitivity. For example, if case_sensitive
is enabled in your model, the expression FOO
will not match the word “food”. If case_sensitive
is not enabled, or if your dialect does not support case sensitivity, the expression FOO
will match the word “food”.
Example | Description |
---|---|
FOO |
is equal to “FOO”, exactly |
FOO,BAR |
is equal to either “FOO” or “BAR”, exactly |
%FOO% |
contains “FOO”, matches “buffoon” and “fast food” |
FOO% |
starts with “FOO”, matches “foolish” and “food” but not “buffoon” or “fast food” |
%FOO |
ends with “FOO”, matches “buffoo” and “fast foo” but not “buffoon” or “fast food” |
F%OD |
starts with an “F” and ends with “OD”, matches “fast food” |
EMPTY |
string is empty (has zero characters) or is null (no value) |
NULL |
value is null (when it is used as part of a LookML filter expression, place NULL in quotes, as shown on the filters documentation page) |
-FOO |
is not equal to “FOO” (is any value except “FOO”), matches “pizza”, “trash”, “fun” but not “foo” |
-FOO,-BAR |
is not equal to either “FOO” or “BAR”, matches any value except “FOO” and “BAR” |
-%FOO% |
doesn’t contain “FOO”, does not match “buffoon” or “fast food” |
-FOO% |
doesn’t start with “FOO”, does not match “foolish” or “food” |
-%FOO |
doesn’t end with “FOO”, does not match “buffoo” or “fast foo” |
-EMPTY |
string is not empty (has at least one character) |
-NULL |
value of column is not null (when it is used as part of a LookML filter expression, place -NULL in quotes, as shown on the filters documentation page) |
FOO%,BAR |
starts with “FOO” or is “BAR” exactly, matches “food” and matches “bar” but not “barfood” |
FOO%,-FOOD |
starts with “FOO” but is not “FOOD” |
_UF |
has any single character followed by “UF”, matches “buffoon” |
Including Special Characters in String Filters
Note these rules for including special characters in string filters:
- To include
%
or_
, prefix with the escape character,^
. For example:^%
and^_
- To include a leading
-
, escape it as^-
. This is only necessary if the-
is the leading character; you do not need to escape-
if it is inside the string. - To include
^
, escape it as^^
. - To include a comma in a string filter, prefix the comma with a backslash character,
\
. For example:Santa Cruz\, CA
. - To include a comma with the matches (advanced) option in a filter, prefix the comma with the escape character,
^
. For example:Santa Cruz^, CA
. - To include a comma in a filter expression in LookML, prefix with the escape character,
^
. For example:field: filtered_count {type: countfilters: [city: "Santa Cruz^, CA"]}