Exclusion filters
An exclusion filter is one that excludes chosen dimension instances from your query. At present, these filters work for non-time dimensions (e.g. Country or Product) only. You might use this filter to find the total Sales value after excluding all data for France.
This topic assumes that you're familiar with the sample dataset and the different query types that Arria Answers supports. Therefore, it may help to read Sample dataset guide and Query types before continuing.
This topic is organized as follows:
Starting assumptions
For each example in this topic:
The data is the sample dataset
The date is July 31, 2021
Default Time Filter =
Latest month
Default Currency =
US Dollar (USD)
Keywords and phrases
exclude / excluding / excluding in
except / except for / except for in
not / not for / not in / not including
do not include / don't include
Excluding a single dimension instance
These examples exclude a single instance of the Country dimension:
Query | Answer |
---|---|
Excluding France, what are my Sales? | In July 2021, Sales excluding France are $9.53 million. |
What are my Sales excluding Germany? | In July 2021, Sales excluding Germany are $9.08 million. |
You can still use an instance from another dimension — e.g. Product or Segment— as a non-time filter.
Query | Answer |
---|---|
Excluding France, what are my Sales of Retine? | In July 2021, Sales of Retine excluding France are $2.08 million. |
What are my Online Sales excluding Germany? | In July 2021, Sales for Online excluding Germany are $1.63 million. |
Tip
Put the dimension instances you wish to exclude directly after the exclusion keyword (e.g. excluding).
Excluding multiple instances of the same dimension
You can exclude multiple instances of the same dimension.
This example excludes three instances of the Product dimension.
Query | Answer |
---|---|
Tell me about Sales vs Target Sales but not for Camoide, Nutrali and Karateon. | In July 2021, Sales excluding Camoide, Nutrali and Karateon are higher than Target Sales by $196,000 (3%). |
You can still use an instance from another dimension — e.g. Country or Segment— as a non-time filter.
Query | Answer |
---|---|
Tell me about Sales vs Target Sales for France except for Camoide, Nutrali and Karateon. | In July 2021, French Sales excluding Camoide, Nutrali and Karateon are lower than Target Sales by $131,000 (8%). |
Tell me about Sales vs Target Sales for Online but not for Camoide, Nutrali and Karateon. | In July 2021, Sales for Online excluding Camoide, Nutrali and Karateon are lower than Target Sales by $55,000 (9%). |
Excluding multiple instances of different dimensions
You can exclude multiple instances of different dimensions.
This example excludes three instances — one from Country, one from Product, and one from Online.
Query | Answer |
---|---|
Excluding Canada, Retine and Online, tell me the total value for Sales. | In July 2021, Sales excluding Canada, Retine and Online are $6.98 million. |
Important
The query above excludes all rows for which Country = Canada OR Product = Retine OR Segment = Online.
It does NOT exclude all data rows for which Country = Canada AND Product = Retine AND Segment = Online.
As with any type of exclusion filter, you are not limited to one instance per dimension. Therefore, you could ask:
Query | Answer |
---|---|
Tell me my Sales total, but don't include Canada, France, Retine, Glower, Online and Chemists. | In July 2021, Sales excluding Canada, France, Retine, Glower, Online and Chemists are $2.57 million. |
This query filters out two instances of Country, two instances of Product, and two instances of Segment.