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.

If you need to enter a special character in your filter, such as a comma (,) or double-quote ("), you can do so by putting a backslash (\) in front of the special character. E.g.: Smith\, Walter.

When you type into the filter for a text dimension, Reckon Insights notices any leading or trailing spaces. E.g.: ” Smith” would not match “Smith” but would match "Smith & Smith". However, if you copy and paste into the text filter, leading and trailing spaces are trimmed during this process. The maximum number of values you can paste may be limited by your browser or other elements in the network, and may be as low as 2,000.

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


Unlike a basic filter, to include a special character option in a text filter when using matches(advanced), you must prefix the special character with a different escape character: ^ . Special characters that can be included, but must be escaped, are: % " - _ , ^
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.

When specifying dates using the format YYYY-MM-DD, ensure month and day have two digits to prevent the date being interpreted as an equation: 2220-1-1 will result in 2018, rather than 2220-01-01

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

How did we do?

Drill-through options - Reckon Insights Creator

Working with Boards - Reckon Insights

Related Articles

Introduction - Reckon Insights - Creator
Introduction - Reckon Insights - Viewer
Downloading content - Reckon Insights
Powered by HelpDocs (opens in a new tab)

Powered by HelpDocs (opens in a new tab)