When setting filters in LookML or in the 'Add/edit Dashboard Filters . . ." modal, we can assign a default value for the filter. In both cases, Looker expects a string. Looker will interpret that string and, depending on the filter type (date, string, or number), change the filter menu drop down option for the end user. E.g. if I want to have a date filter on the dashboard automatically present as “in the past 90 days”, typing “90 days” as the default value will produce the desired behavior.
To get the desired dropdown here:
Type in the appropriate String Format from the tables below in your dashboard default value field here:
Or as the default parameter in your LookML filter like this:
filter: date_filter {
type: date
default_value: "6 weeks"
}
For more information, check out our documentation on Looker Filter Expressions.
Desired Dropdown | Required String Format | Example |
---|---|---|
"in the past" | i [timeframes] | 6 weeks |
"in the past (comlete)" | i [timeframes] ago for i [timeframes] | 6 weeks ago for 6 weeks |
"is on the day" | yyyy-mm-dd | 2016-01-23 |
"is in range" | yyyy-mm-dd to yyyy-mm-dd | 2016-01-23 to 2016-03-25 |
"is before" (absolute) | before yyyy-mm-dd | before 2016-01-23 |
"is before" (relative) | before i [timeframes] ago | before 3 weeks ago |
before now | before now | |
before i [timeframes] from now | before 2 weeks from now | |
"is on or after" (absolute) | after yyyy-mm-dd | after 2016-01-23 |
"is on or after" (relative) | after i [timeframes] ago | after 3 weeks ago |
after now | after now | |
after i [timeframes] from now | after 2 weeks from now | |
"is in the year" | yyyy | 2016 |
"is null" | NULL | NULL |
"is not null" | NOT NULL | NOT NULL |
Where
i is an integer
[timeframes] is one of the following: minutes, hours, days, weeks, months, quarters, years
Desired Dropdown | Required String Format | Example |
---|---|---|
"is equal to" | string | foo |
string1, string2 | foo, bar | |
"contains" | %string% | %foo% |
%string1%,%string2% | %foo%, %bar% | |
"starts with" | string% | foo% |
string1%, string2% | foo%, bar% | |
"ends with" | %string | %foo |
%string1, %string2 | %foo, %bar | |
"is blank" | EMPTY | EMPTY |
"is null" | NULL | NULL |
"is not equal to" | -string | -foo |
-string1, -string2 | -foo, -bar | |
"doesn't contain" | -%string% | -%foo% |
-%string1%, -%string2% | -%foo%, -%bar% | |
"doesn't start with" | -string% | -foo% |
-string1%, -string2% | -foo%, -bar% | |
"doesn't end with" | -%string | -%bar |
-%string1, -%sring2 | -%foo, -%bar | |
"is not blank" | -EMPTY | -EMPTY |
"is not null" | -NULL | -NULL |
Note these rules for including special characters in string filters:
Desired Dropdown | Required String Format | Example |
---|---|---|
"is equal to" | x | 42 |
x, y | 12, 42 | |
"is greater than" | > x | > 42 |
"is greater than or equal to" | >= x | >= 42 |
"is less than" | < x | < 42 |
"is less than or equal to" | <= x | <= 42 |
"is between" "[inclusive]" | >= x and <= y | >= 42 and <= 75 |
"is between" "(exclusive)" | > x and < y | > 42 and < 75 |
"is between" "[right exclusive)" | >= x and < y | >= x and < 75 |
"is between" "(left exclusive]" | > x and <= y | >x and <=75 |
"is null" | NULL | NULL |
"is not equal to" | not x | not 13 |
!= x | != 13 | |
<> x | <> 13 | |
"is not between" "inclusive" | <= x or >= y | <= 23 or >= 27 |
"is not between" "exclusive" | < x or > y | < 23 or > 27 |
"is not between" "[right exclusive)" | <= x or > y | <= 23 or > 27 |
"is not between" "(left exclusive]" | < x or >= y | < 23 or >= 27 |
"is not null" | NOT NULL | NOT NULL |
For each old LookML code block in this article and its comments, we just added the New LookML equivalent code.
^ I think this might be a formatting issue
Is that inclusive of the second date?
First date is inclusive, second is exclusive.
See the filter when applied, it says “yyyy-mm-dd until (before) yyyy-mm-dd”.
Do you know if there is a way to make the filter end date inclusive of the date entered i.e., if the user wants data from 01-JAN-2020 to 10-JAN-2020, is there a way of making the end date inclusive. Currently, users are getting confused with the date parameters as in all other systems that we use, the dates are always inclusive.
I know that situation since we had the very same problem, when we introduced Looker.
If you really want you can cheat the filters by implementing a custom filter somewhere using liquid variables (Looker Docs, variables date_start
and date_end
).
Still I strongly advise against that, because we came to the conclusions that
a) it would still be confusing, since the filter UI would imply other behavior (see “until (before)”)
b) it would be a never ending commitment of the LookML devs to implement that behavior on EVERY date/time filter