Question

Setting Filter Defaults

  • 27 April 2016
  • 6 replies
  • 5118 views

Userlevel 3

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:



LookML
filter: date_filter {
type: date
default_value: "6 weeks"
}


For more information, check out our documentation on Looker Filter Expressions.


Date





















































































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


String














































































































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:



  • To include % or , prefix with the escape character, ^. For example: ^% and ^

  • To include ^, escape it as ^^.

  • To include an item with commas, wrap it in double-quotes. For example: “Santa Cruz, CA”, “San Jose, CA”


Number









































































































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

6 replies

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

Reply