Table of Contents
Using Filters - Reckon Insights
Using Filters
Reckon Insights allows you to limit the data you see in several ways. You can filter results to include or exclude, for example, a specific client, book, or date-range. Filters can be applied to all data the Visualisation has access to, not just that data displayed in the results. You are also able to limit the number of rows or pivot columns displayed.
Filters can always be found at the top of any Visualisation, and each acts upon a single column of data. Most filters have default values, and clearing this value from the filters field, leaving it blank, will remove the effect of that filter so that it is not applied to the results. The exception to this is when a filter has been set to require a value, but supplied no default value. In this case the Visualisation will not run until a value has been supplied. An example of this is the Books filter on the Insights pre-built Visualisations, which has no default value, but is always applied. On the pre-built Visualisations, where there is a Book filter, there will need to be a book selected for results to show.
Simply click the filters drop-down toggle (sideways-pointing triangle) to expand and show all relevant filters on a Visualisation.
Different types of filters will be present, depending on the Visualisation.
There are different types of filters, and they be combined in a query:
- Basic Filters : The most commonly used types of filter. In these cases Reckon Insights provides tools like drop-down lists, date-pickers, and text fields you can use to put in your filter criteria.
- Advanced Matches : Sometimes you’ll want to specify a filter with a more advanced condition for a field, like a complicated date range, or combining AND and OR logic.
- Custom Filters : Custom filters are written in a language called LookML, which is an entire subject of it's own, and outside the scope of this document.
Basic Filters
There are different types of filter, and they are interacted with differently.
There are simple Yes/No fields, numeric fields, pick-lists, date-pickers, and free text and numeric fields, with the combination of these creating extensive filtering functionality.
You can add a filter to a column, and filter the results by that data, by clicking on the columns cog-menu in the data section of the Visualisation and selecting Filter.
Multiple values can be entered into some filters where it is appropriate, and they create an AND or OR condition on that columns data, similar to adding a new row. All of the values in the filter are applied to the column when filtering the results.
To remove a filter, click the X to the right. If there is no X, the filter is required for the Visualisation to run, e.g.: Book Name.
To add another row to the filter, click the ┼ to the right of the X. The new row will automatically appear as an OR condition or an AND condition depending on the type of the comparison in the filter.
Date-pickers
Date-pickers allow you to select all dates:
a specific date:
or dates for a date-range:
and can be absolute:
or relative:
Pick-lists
Pick-lists allow you to select existing values from a list. Click on the x to remove the prompt-text: Select Account(s) x.
Click again to place the cursor in the field, then start typing and the list will populate with any existing values that match what is being typed.
There are also some built in filter pick-list functions that are presented as plain-language phrases, which interact with other filter-fields, and enable the creation of understandable yet powerful queries. There are those that refer to dates or periods, and those that refer to other fields-types.
Yes/No fields
Yes/No fields behave as would be expected, rather like a switch.
Numeric fields
Numeric fields simply take a number, and have no suggestions to offer when there is no value in the filter field.
Text Fields
Text fields such as pick-lists may also take free-form text, which can contain special characters.
Advanced Matches
Some filters have available the selection matches(advanced).
This enables the use of special reserved characters and/or words with special functions in the filters field(s).
Text filters
Collections of text characters are also known as strings, as one of the meanings of the word string means "a linear collection of objects", and text is made by "stringing" together single characters, exactly similar to "a string of pearls".
Reserved characters and words
Character / Word | Name | Function |
_ | underscore | Wildcard character, matches any single character |
% | percent | Wildcard character, matches any number of any character |
, | comma | Value separator character, used to separate value in a list |
- | minus | Negator character, used to exclude item(s) from the search |
^ | caret | Escape character, used to include the reserved characters in the text when using the matches(advanced) option |
EMPTY | EMPTY | Text field is empty (has zero characters) or is null (no value) |
NULL | NULL | Text field has no value |
Note that the - character only needs to be escaped when it is the first (leading) character.
For example: Greenhithe^, Auckland
Example search strings
Example | Description |
oink | Is equal to “oink”, exactly |
oink,squeal | Is equal to either “oink” or “squeal”, exactly |
%oink% | Contains “oink”, matches “doink” and “Pig says oink” |
oink% | Starts with “oink”, matches “oink” and “Oinkyly Doinkyly” but not “doink” or “Pig says oink” |
%oink | Ends with “oink”, matches “doink” and “Pig says oink” but not “oink!” or “Oinkyly Doinkyly” |
P%nk | Starts with a “P” and ends with “nk”, matches “Pig says oink” |
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) |
-oink | Is not equal to “oink” (is any value except “oink”), matches “squeal”, “pigpen”, “crackling” but not “oink” |
-oink,-squeal | Is not equal to either “oink” or “squeal”, matches any value except “oink” and “squeal”, matches “pigpen”, “crackling” |
-%oink% | Doesn’t contain “oink”, does not match “doink” or “Pig says oink”, matches “squeal”, “pigpen”, “crackling” |
-oink% | Doesn’t start with “oink”, does not match “Oinkyly Doinkly” or “oink!” |
-%oink | Doesn’t end with “oink”, does not match “doink” or “Pig says oink” |
-EMPTY | String is not empty (has at least one character) |
-NULL | Value of column is not null |
oink%,squeal | Starts with “oink” or is “squeal” exactly, matches “oink!”, “squeal”, and "oink!, squeal" but not “squeal, oink!” |
oink%,-oink! | Starts with “oink” but is not “oink!”, matches “Oinkyly Doinkly” |
_oink | Has any single character followed by “oink”, matches “doink” but not “Pig says oink” |
Date and time filters
Insights date filters using matches(advanced) can use reserved words, along with numbers and intervals/times, to assemble search strings that are like plain English. OR logic is achieved by adding multiple conditions separated by commas in the same filter, AND logic is achieved by adding extra filters.
In the examples below, the following conventions are used:
<n> : an integer number
<interval> : a reserved word time increment
<datetime> : format as YYYY-MM-DD HH:MM:SS or YYYY/MM/DD HH:MM:SS. The time component is optional, but dropping it specifies midnight.
Reserved Words
Absolute dates | Intervals | Connecting words | ||
now | hour(s) | after | ||
today | day(s) | ago | ||
tomorrow | week | before | ||
yesterday | month | for | ||
Monday | quarter: Q<n> | from | ||
Tuesday | fiscal quarter: FQ<n>, <n> formatted YYYY | from | ||
Wednesday | year: <n>, formatted YYYY | next | ||
Thursday | fiscal year FY<n>, <n> formatted YYYY | this | ||
Friday | to | |||
Saturday | ||||
Sunday | ||||
YYYY-MM-DD HH:MM:SS, YYYY-MM-DD YYYY/MM/DD HH:MM:SS, YYYY/MM/DD |
Examples
{n} {interval} ago
{n} {interval} from now
{n} {interval} from now for {n} {interval}
next {interval}
this {interval}
this {interval} to {interval}
{datetime} to {datetime}
{datetime} for {n} {interval}
Boolean filters (Yes/No)
Filtering on true or false type values in Looker does require you to understand what type of true or false value you’re interacting with. Some fields (Boolean) contain an actual TRUE/FALSE value, and some fields can contain expressions that evaluate to yes/no.
Reserved words
Example | Description |
yes or Yes | Field evaluates to true (can be the result of an expression) |
no or No | Field evaluates to false (can be the result of an expression) |
TRUE | Field contains true (i.e. fields that contain Boolean database values) |
FALSE | Field contains false (i.e. fields that contain Boolean database values) |
Filtering dimensions and filtering measures
Filters are applied differently to dimensions and measures.
Filtering dimensions
When filtering on a dimension, you are restricting the raw data before any calculations are made.
Filtering measures
When filtering on a measure however, you are restricting the results after the measure has been calculated.
Back to Table of Contents - Viewer
Back to Table of Contents - Creator
