Time filters
This topic explains how to use time filters in your queries.
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:
Important
The content applies to every query type except time-based variance queries, which apply time filters in a unique way.
Starting assumptions
For each example in this topic:
The data is the sample dataset
The date is July 31, 2021
Default Time Filter =
Latest monthDefault Currency =
US Dollar (USD)
Time-point filters
A time-point filter is used to query the data for a specific day, month, quarter, or year.
Day
These examples show how to query the data for a specific day.
Query | Answer |
|---|---|
What were my Sales on the 1st? | On 1st July 2021, Sales were $1.44 million. |
The above query includes a day but does not specify a month or year. Nonetheless, the answer returns data for the most recent instance of that day at the time of the query. The query was made on 31st July 2021; therefore, "the 1st" = "1st July 2021".
Note
The above is only possible because Default Time Filter = Latest month and because all date fields — DAY(Date), MONTH(Date), QUARTER(Date), and YEAR(Date) — are selected.
If DAY(Date) was selected but MONTH(Date) was not, the default time filter couldn't work. The query What were my Sales on the 1st? would return a sum-total of all sales that occur on the 1st (regardless of month or year).
The same principle works if the query includes a day and month but no year:
Query | Answer |
|---|---|
What were my Sales on 1st July? | On 1st July 2021, Sales were $1.44 million. |
What are my Target Sales for 1st August? | On 1st August 2020, Target Sales were $68,000. |
Remember, the answer returns data for the most recent instance of the given date at the time of the query. Both queries above were made on 31st July 2021; therefore, the first query returned data for 1st July 2021 whereas the second returned data for 1st August 2020.
To get different answers, specify the year:
Query | Answer |
|---|---|
What were my Sales on 1st July 2020? | I'm sorry, there doesn't appear to be any data for Sales on 1st July 2020. |
What are my Target Sales for 1st August 2021? | On 1st August 2021, Target Sales will be $84,000. |
Tip
You can write days as cardinal or ordinal numbers. For example, 5 June is a valid alternative to 5th June.
Month
These examples show how to query the data for a specific month. They were produced on 31st July 2021.
Query | Answer |
|---|---|
What were my Sales for February? | In February 2021, Sales were $5.06 million. |
What are my Target Sales for September? | In September 2020, Target Sales were $5.51 million. |
What were my Sales for February 2020? | In February 2020, Sales were $6.21 million. |
What are my Target Sales for September 2021? | In September 2021, Target Sales will be $5.17 million. |
Note
When your query specifies a month but not a year, the answer returns data for the most recent instance of that month at the time of the query. However, the app cannot do this unless the MONTH(Date) and YEAR(Date) fields are selected.
If YEAR(Date) was selected but MONTH(Date) was not, the query What were my Sales for February? would return a sum-total sales figure for all February rows in your dataset.
Quarter
These examples show how to query the data for a specific quarter. They were produced on 31st July 2021.
Query | Answer |
|---|---|
What were my Sales for Q1? | In Q1 2021, Sales were $15.75 million. |
What are my Target Sales for Q4? | In Q4 2020, Target Sales were $20.75 million. |
What were my Sales for Q1 2020? | In Q1 2020, Sales were $18.56 million. |
What are my Target Sales for Q4 2021? | In Q4 2021, Target Sales will be $19.9 million. |
Note
When your query specifies a quarter but not a year, the answer returns data for the most recent instance of that quarter at the time of the query. However, the app cannot do this unless the QUARTER(Date) and YEAR(Date) fields are selected.
If YEAR(Date) was selected but QUARTER(Date) was not, the query What were my Sales for Q1? would return a sum-total sales figure for all Q1 rows in your dataset.
Year
These examples show how to query the data for a specific year.
Query | Answer |
|---|---|
What were my Sales for 2020? | In 2020, Sales were $89.62 million. |
What are my Target Sales for 2022? | In 2022, Target Sales will be $91.63 million. |
Time-interval filters
A time-interval filter is used to query the data between two time points. These time points can either be specific days, months, quarters, or years. The start and end points are inclusive.
For these examples, assume all date fields — DATE(Date), MONTH(Date), QUARTER(Date), and YEAR(Date) — are selected.
Query | Answer |
|---|---|
What were my Sales from 1st May to 7th May? | From 1st May 2021 to 7th May 2021, Sales were $4.39 million. |
What were my Sales from August to September? | From August 2020 to September 2020, Sales were $14.89 million. |
What were my Sales between Q1 and Q2? | From Q1 2021 to Q2 2021, Sales were $40.23 million. |
What are my Sales between 2020 and 2021? | From 2020 to 2021, Sales are $175 million. |
The two most common ways of adding a time-interval filter are from X to Y or between X and Y, as shown above.
Alternatively, you can give the queries in this form:
Query | Answer |
|---|---|
What were my Sales from 7th May back to 1st May? | From 1st May 2021 to 7th May 2021, Sales were $4.39 million. |
What were my Sales from September back until August? | From August 2020 to September 2020, Sales were $14.89 million. |
What were my Sales from Q2 back to Q1? | From Q1 2021 to Q2 2021, Sales were $40.23 million. |
What are my Sales from 2021 back until 2020? | From 2020 to 2021, Sales are $175 million. |
Important
All examples were produced on 31s July 2021. When the time points in your time-interval filter lack specificity — see the first three examples above — Arria Answers uses the most recent instance of that interval at the time of the query.
Arria Answers alerts you when your time-interval filter covers periods with no data:
Query | Answer |
|---|---|
What are Target Sales from June 2022 to June 2023? | From June 2022 to June 2023, Target Sales will be $55.91 million. Not all months within the time period have data. |
All examples use the sample dataset set, which includes data for 2020–2022. Therefore, the $55.91 million figure is from a sub-period (Jun 2022 to Dec 2022) within the queried period (Jun 2022 to Jun 2023). The answer hints at this.
Deictic expressions
A deictic word or expression is one whose meaning is dependent on the context in which it is used.
An example of a deictic time expression is last quarter. The expression is deictic because its precise meaning is unclear unless you have knowledge of the current quarter as a point of reference.
This section shows how to use deictic words or expressions in your queries.
Deictic time-point filters
For these examples, all date fields — DATE(Date), MONTH(Date), QUARTER(Date), and YEAR(Date) — are selected.
REMINDER: All examples were produced on 31st July 2021.
Query | Answer |
|---|---|
What are my Sales today? | I'm sorry, there doesn't appear to be any data for Sales on 31st July 2021. |
What were yesterday's Sales ? | On 30th July 2021, Sales were $0.78 million. |
What were my Sales last month? | In June 2021, Sales were $6.14 million. |
What were the last quarter's Sales? | In Q2 2021, Sales were $24.48 million. |
What were my Sales last year? | In 2020, Sales were $89.62 million. |
What are tomorrow's Target Sales? | On 1st August 2021, Target Sales will be $84,000. |
What are my Target Sales next month? | In August 2021, Target Sales will be $9.17 million. |
What are my Target Sales for the next quarter? | In Q4 2021, Target Sales will be $19.9 million. |
What are next year's Target Sales? | In 2022, Target Sales will be $91.63 million. |
Tip
The queries that use last or next could be rewritten using previous or following instead.
Deictic time-interval filters
For these examples, all date fields — DATE(Date), MONTH(Date), QUARTER(Date), and YEAR(Date) — are selected.
REMINDER: All examples were produced on 31st July 2021.
Query | Answer |
|---|---|
What are my Sales for the last 3 days? | From 28th July 2021 to 30th July 2021, Sales were $1.93 million. |
What are my Sales for the last 3 months? | From April 2021 to June 2021, Sales were $24.48 million. |
What are my Sales for the last 3 quarters? | From Q4 2020 to Q2 2021, Sales were $59.72 million. |
What are my Target Sales for the next 3 days? | From 1st August 2021 to 3rd August 2021, Target Sales will be $220,000. |
What are my Target Sales for the next 3 months? | From August 2021 to October 2021, Target Sales will be $21.42 million. |
What are my Target Sales for the next 3 quarters? | From Q4 2021 to Q2 2022, Target Sales will be $60.33 million. |
Again, the answer tells you when your time-interval filter covers periods with no data:
Query | Answer |
|---|---|
What are my Target Sales for the next 2 years? | From 2022 to 2023, Target Sales will be $91.63 million. Not all years within the time period have data. |
Querying the whole dataset
When you include "overall" in your query, Arria Answers analyzes the whole dataset, no matter how you've set the default time filter.
As with all other examples in this topic, assume that:
The data is the sample dataset
The date is 31st July 2021
Default Time Filter =
Latest month
Compare and contrast these examples:
Query | Answer |
|---|---|
What are my Sales? | In July 2021, Sales are $12.75 million. |
What are my total Sales? | In July 2021, Sales are $12.75 million. |
What are my overall Sales? | Overall, Sales are $258.72 million. |
The first two queries have no time filter, so the default filter applies and both answers return the Sales total for July 2021. The third query — which contains the "overall" keyword — returns the Sales total for the overall dataset.
There are other ways to query the whole dataset. In this scenario, you could change the default time filter to None before entering a query (e.g.What are my Sales?) Often, however, the "overall" method is more efficient. Why change the settings when you don't need to?
Important
When your query includes "overall" AND another time filter, Arria Answers uses the other filter. For example, it processes the query What were overall Sales in May 2021? as What were Sales in May 2021?
