Contents
Introduction
It is often necessary to filter data to create an insightful visualisation. You can filter data in each visualisation from within the toolbox.
To access the toolbox, click on the 'edit' icon at the top right of a visualisation.
From within the toolbox, you can access the filters here:
Filters are written using a simple query syntax called DxQL. See Writing a DxQL filter for more information.
For those familiar with a SQL query, the filter box acts as a WHERE statement to filter rows from a dataset.
Writing a DxQL filter
DxQL has a similar syntax to standard SQL, but with some modifications to take away any complexity.
For instance:
State LIKE ‘U%’ AND Total_price >= 50
Returns data where State begins with the letter U and the total paid is greater than or equal to 50
State <> ‘Wyoming’ OR visitors < 100
Returns data from all states except Wyoming or the visitors are less than 100. Data from Wyoming would be returned in this instance if the visitors were less than 100.
For more information about DxQL syntax, see DxQL reference below.
AI powered Query Assist 🪄
Our AI powered Query Assist is designed to help any user quickly and easily write a valid DxQL filter query.
By describing a query in natural language (i.e. plain English) Query Assist will return a valid DxQL query that best matches the request.
Query Assist uses a Large Language Model (LLM) to interperet the provided query, and other contextual information to make a best guess at the relevant DxQL query.
To use query assist, describe your filter, then click the wand button:
For instance:
visitors from states beginning with U
will return the DxQL statement:
state LIKE 'U%'
You can be more creative with your inputs too:
visitors from New England
will return the DxQL statement:
state = 'New Hampshire' OR state = 'Massachusetts' OR state = 'Maine' OR state = 'Vermont' OR state = 'Rhode Island' OR state = 'Connecticut'
Query Assist is aware of the specifics of your dataset, so that queries like the following:
tags related to films
will return more accurate DxQL:
tag LIKE '%Movie%'
Note
It's important to remember that Query Assist is not a chat service. Asking, for instance, for "top 5 popular movies in 2023" will not generate any useful results. It's important to describe the filter well to ensure the best results.
Asking Good Questions
Whilst Query Assist does a good job of inferring what you might mean, there's an art to describing an effective filter to get a useful result. Here are some tips:
Using the precise column names
It may help to use the exact column names when describing what you want to see.
You can find column names in the toolbox by looking in the "Measure" and "Group by" drop-downs. You can also find all column names in the data explorer by navigating to Data>Data Explorer>Meta Data and selecting the relevant dataset.
Here's an example of some Measures for a dataset
asking for
unit discounts greater than $2
is preferable to the more ambiguous
discounts greater than $2
Be as specific as possible
Describing a filter like the following:
Show me low performing brands
May return an unexpected result, whereas requesting the following:
brands with total quantity sold less than 20K
Will return a much more accurate result.
Don't be afraid to try several attempts at getting to what you are after: describing your filter in more explicit terms can make a big difference. In the example above, being more specific about the metric (quantity sold or total price) and being specific about what 'low performance' means to you (i.e. less than 20K).
DxQL Query Correction
Query Assist can also be used to fix any DxQL syntax errors. If you've entered a filter that has returned an error, simply click on the wand button and Query Assist will fix any syntax errors. Some examples below:
Example 1:
line_item_product_type LIKE %books%'
Will be corrected to
line_item_product_type LIKE '%books%'
Example 2:
Line_item_product_type %books%
Will be corrected to:
line_item_product_type LIKE '%books%'
Example 3:
Book products
Will be corrected to:
line_item_product_type = 'Books'
For info around privacy and data usage of our AI powered Query Assist refer to this article.
DXQL Reference
Currently DxQL can only be applied in the filter section of select visualisations. This is equivalent to adding a WHERE statement to a SQL query.
The following operators can be used in a Filter:
Operator | Description | Example |
= | Returns values that exactly match the given number or string. | State = 'Texas' Returns data only from Texas. |
!= or <> | Returns values that do not match the given number or string. | State <> 'Texas' Returns data from all states except Texas. |
< | Less than Greater than Less than or equals to Greater than or equals to | visitors < 100 Returns data where visitors were less than 100 (i.e. 0. - 99) |
LIKE [string]
Wildcards:
| Returns values that match the given string, allowing for wildcards. LIKE is case insensitive. | State LIKE ‘%carolina’ Returns data from North Carolina and South Carolina
State LIKE 'c%' Returns data from all states beginning with 'C'.
State LIKE '%a' Returns data from all states ending in 'a'
State LIKE '____' Returns data from all states with four letters (Utah, Ohio and Iowa)
State LIKE ‘%m%’ Returns all states that contain the letter 'm'. |
IN | Returns values that match any value in a specified list. | us_state IN (‘California’, ‘Florida’)
Returns only data from Florida and California |
NOT IN | Returns values that do not match any value in a specified list. | State NOT IN (‘California’, ‘Florida’)
Returns all states except California and Florida |
IS NULL | Filters anything whose value is null (empty/blank). | State IS NULL
Returns all records where there is nothing in the State column. |
IS NOT NULL | Filters anything whose value is not null (empty/blank). | State IS NOT NULL
Returns all records where there is a value in the State column. |
AND | Used to join two statements where both of the statements must be true. |
|
OR | Used to join two statements where either of the two statements can be true. |
|