Time filters

This topic explains how to use time filters in your queries. The topic assumes that you're familiar with the sample dataset and the different query types that Arria Answers supports. It may help to read Sample dataset guide and Query types before proceeding.

We have organized the topic 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 query:

  • The sample dataset is used

  • The date is July 31, 2021

  • Default Time Filter = Latest month

  • Default 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 the default time filter is set to Latest month and because all date fields (Day, Month, Quarter, and Year) are selected.

If the Day field was selected but the Month field 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 and Year fields are selected in Power BI.

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 and Year fields are selected in Power BI.

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.

Tip

You must write year values in full — e.g. 2022 rather than 22.

 

Top

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 — Day, Month, Quarter, and Year — 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 2019 and 2020?

From 2020 to 2021, Sales are $175 million.

The two most common ways of adding a time-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 2020 back until 2019?

From 2020 to 2021, Sales are $175 million.

Important

If the time points used in your time-interval filter lack specificity, the answer returns data for the most recent instance of the interval at the time of the query.

For example, the filter "from August to September" queries the data from August 2019 to September 2020. This is because the query was made on 31st July 2021, meaning the most recent instance of August to September was in 2020.

NOTE: The app cannot infer time periods in this way unless the relevant date fields are selected in Power BI.

 

Top

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.

Time-point filters (deictic)

For these examples, all date fields — DayMonthQuarter, and Year — are selected in Power BI.

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 my Sales yesterday?

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 my Sales in the last quarter?

In Q2 2021, Sales were $24.48 million.

What were my Sales last year?

In 2020, Sales were $89.62 million.

What are my Target Sales for tomorrow?

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 were my Target Sales for next year?

In 2022, Target Sales will be $91.63 million.

Note

The queries that use last or next could be rewritten using previous or following instead.

The same applies to the time-interval examples below.

Time-interval filters (deictic)

For these examples, all date fields — Day, Month, Quarter, and Year — are selected in Power BI.

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 Sales for the last 2 years?

From 2019 to 2020, Sales were $89.62 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.

What are my Target Sales for the next 2 years?

From 2022 to 2023, Target Sales will be $91.63 million.*

Important

* These answers are correct but potentially misleading because the sample dataset does not include data for 2019 or 2023. We are working to improve the answers for this type of scenario.

 

Top