Query types

Arria Answers supports many query types. This topic provides examples and guidance.

The topic assumes that you're familiar with the sample dataset and key terms such as measure, dimension, dimension instance, and time dimension. Therefore, it may help to read Sample dataset guide and Glossary before continuing.

We have organized the topic as follows:

Starting assumptions

For each example query:

  • The sample dataset is used

  • Aggregation type for measures = Don't Summarize

  • The date is July 31, 2021

  • Default Time Filter = Latest month

  • Default Currency = US Dollar (USD)

Basic aggregation queries

This type of query aggregates the data for one measure.

There are five aggregation types:

Sum aggregation

Required aggregation type

Aggregation words for query

Don't summarize or Sum

sum, total (optional)

This query type returns the sum-total for a measure.

Query

Answer

What are my Sales?

In July 2021, Sales are $12.75 million.

You can make the query more precise by adding dimension instances:

Query

Answer

What are my Sales of Retine?

In July 2021, Sales for Retine are $3.03 million.

What are my Sales of Retine in Canada?

In July 2021, Sales in Canada for Retine are $358,000.

You could further filter the query using a time filter.

Important

DEFAULT TIME FILTERING — ALL QUERY TYPES

When the query has no time filter, Arria Answers uses the default time filter. Per the starting assumptions, the Default Time Filter = Latest month and the date is 31st July 2021. Therefore, each answer returns data for July 2021.

The default time filter works only because the Month and Year fields are selected. If the relevant date fields weren't selected, the query What are my Sales? would return the sum-total for all sales figures in your dataset.

Average aggregation

Required aggregation type

Aggregation words for query

Don't summarize or Average

average, mean

This query type returns the average value for a measure. For average aggregation to work, you must select only one dimension field for use with Arria Answers. This single dimension can be a non-time dimension (e.g. Country) or a time dimension (e.g. Year).

Assuming that Country is the only selected dimension:

Query

Answer

What are average Sales for France ?

Overall, the only value for average Sales in France is $189,000.

What are average Sales for Canada ?

Overall, the only value for average Sales in Canada is $185,000.

Important

The query must include one dimension instance — that is, one instance of the selected dimension.

Assuming that Year is the only selected dimension:

Query

Answer

What are average Sales for 2020?

In 2020, the only value for average Sales is $182,000.

What are average Sales for 2021?

In 2021, the only value for average Sales is $190,000.

You could further filter the query using a time filter.

Note

You must set the measure's aggregation type to Don't summarize or Average in Power BI. Note that setting the type to Average changes the measure's name — e.g. from Sales to Average of Sales.

Maximum aggregation

Required aggregation type

Aggregation words for query

Don't summarize or Maximum

biggest, highest, largest, max, maximum, top

This query type returns the maximum value for a measure.

Query

Answer

What are my highest Sales?

In July 2021, the maximum value of Sales is $1.11 million.

What are the largest Sales of Glower?

In July 2021, the maximum value of Sales for Glower is $0.98 million.

What are the top Sales of Glower in France?

In July 2021, the maximum value of Sales in France for Glower is $0.62 million.

You could further filter the query using a time filter.

Note

You must set the measure's aggregation type to Don't summarize or Maximum in Power BI. Note that setting the type to Maximum changes the measure's name — e.g. from Sales to Max of Sales.

Minimum aggregation

Required aggregation type

Aggregation words for query

Don't summarize or Minimum

bottom, lowest, min, minimum, smallest

This query type returns the minimum value for a measure.

Query

Answer

What are my lowest Sales?

In July 2021, the minimum value of Sales is $4,500.

What are my smallest Sales of Ointmo?

In July 2021, the minimum value of Sales for Ointmo is $4,500.

What are my bottom Sales of Ointmo in France?

In July 2021, the minimum value of Sales for Ointmo in France is $12,000.

You could further filter the query using a time filter.

Note

You must set the measure's aggregation type to Don't summarize or Minimum in Power BI. Note that setting the type to Minimum changes the measure's name — e.g. from Sales to Min of Sales.

Mode aggregation

Required aggregation type

Aggregation word

Don't summarize

mode

This query type returns the mode — i.e. the most common value within the queried dataset. The first example below uses mode in the query. The second and third examples show different ways of phrasing the same query.

Query

Answer

What is the mode of Sales?

In July 2021, the mode values of Sales are $17,000 and $19,000.

What is the most common value of Sales?

In July 2021, the mode values of Sales are $17,000 and $19,000.

What value of Sales occurs the most?

In July 2021, the mode values of Sales are $17,000 and $19,000.

You can make the query more precise by adding dimension instances and/or a time filter:

Query

Answer

Mode for Sales in France last year?

In 2020, the mode values of Sales in France were $10,000 and $14,000.

Mode for Sales of Retine in France last year?

In 2020, the mode value of Sales for Retine in France was $125,000.

Important

There can be more one mode value — see the second last example above. Also, the mode calculation is performed on rounded values (i.e. those displayed in the answers to queries), not the raw values in your dataset.

 

Top

Group-by queries

Required aggregation type

Aggregation words for query

Don't summarize or Sum

sum, total (optional)

Group-by queries group the data for one measure by one dimension.

Query

Answer

Tell me about my Sales by Product?

In July 2021, the top Product is Glower with $3.27 million (26%), and the bottom Product is Ointmo with $0.71 million (6%) of Sales.

Tell me about my Profit by Segment?

In July 2021, the top Segment is Departmental with $1.4 million, and the bottom Segment is Online with -$111,729 of Profit.

You can make the query more precise by adding dimension instances.

Query

Answer

Tell me about my Sales in France by Product?

In July 2021, the top Product is Retine with $0.86 million (27%), and the bottom Product is Ointmo with $25,000 (1%) of Sales in France.

Tell me about my Profit for Retine by Segment?

In July 2021, the top Segment is Brand Store with $160,000, and the bottom Segment is Online with -$57,838 of Profit for Retine.

Note

You could further filter the query using a time filter.

Average group-by queries

Required aggregation type

Aggregation words for query

Don't summarize or Average

average, mean

For average aggregation to work, you must select only one dimension field for use with Arria Answers.

Assuming that Product is the only selected dimension:

Query

Answer

Tell me about average Sales by Product?

Overall, the values for average Sales by Product are Glower with $232,000, Ointmo with $218,000, Retine with $182,000, Karateon with $182,000, Camoide with $153,000 and Nutrali with $139,000.

The answer gives the average value for each product in the data, and presents the values in descending order.

Note

You must set the measure's aggregation type to Don't summarize or Average in Power BI. Note that setting the type to Average changes the measure's name — e.g. from Sales to Average of Sales.

 

Top

Top or bottom queries

Top or bottom queries require one dimension and one measure.

You use these queries to identify the top- or bottom-performing dimension instance for your chosen measure.

Top queries

Required aggregation type

Aggregation words for query

Don't summarize or Maximum

biggest, highest, largest, max, maximum, top

This query type uses maximum aggregation. You might use this to identify your top-selling product.

Query

Answer

Which Country is top for Sales?

In July 2021, the top Country is Germany, accounting for $3.67 million (29%) of Sales.

Which Product is top for Profit?

In July 2021, the top Product is Glower, accounting for $0.61 million (31%) of Profit.

Note

You must set the measure's aggregation type to Don't summarize or Maximum in Power BI. Note that setting the type to Maximum changes the measure's name — e.g. from Sales to Max of Sales.

You can make the query more precise by adding dimension instances:

Query

Answer

Which Country is top for Sales of Glower?

In July 2021, the top Country is Germany, accounting for $0.99 million (30%) of Sales for Glower.

Which Product is top for Profit in France?

In July 2021, the top Product in terms of Profit in France is Nutrali.

Note

You could further filter the query using a time filter.

Bottom queries

Required aggregation type

Aggregation words for query

Don't summarize or Minimum

bottom, lowest, min, minimum, smallest

This query type uses minimum aggregation. You might use this to identify the country with the worst sales.

Query

Answer

Which Country is bottom for Sales?

In July 2021, the bottom Country is the United States of America, accounting for $1.54 million (12%) of Sales.

Which Product is bottom for Profit?

In July 2021, the bottom Product is Karateon, accounting for $41,000 (2%) of Profit.

Note

You must set the measure's aggregation type to Don't summarize or Minimum in Power BI. Note that setting the type to Minimum changes the measure's name — e.g. from Sales to Min of Sales.

You can make the query more precise by adding dimension instances:

Query

Answer

Which Country is bottom for Sales of Retine?

In July 2021, the bottom Country is the United States of America, accounting for $284,000 (9%) of Sales for Retine.

Which Product is bottom for Profit in France?

In July 2021, the bottom Product in terms of French Profit is Karateon.

Note

You could further filter the query using a time filter.

Top or bottom queries with time dimension

You can also write top or bottom queries with a time dimension. This means writing month, quarter, or year (note: you cannot use day) in the query instead of a non-time dimension such as as Country or Product.

When you use month or quarter in the query, it's best practice to also specify the year.

Query

Answer

Bottom month in 2021 for Online Profit?

In 2021, the bottom month is July, accounting for $112,000 (16%) of Profit for Online.

What is the top quarter in 2021 for Sales of Ointmo in Germany?

In 2021, the top quarter is Quarter 2, accounting for $0.96 million (44%) of Max(Sales) in Germany for Ointmo.

 

Top

Target-based variance queries

Required aggregation type

Aggregation words for query

Don't summarize or Sum

sum, total (optional)

These queries compare two measures over a given time period. These queries return four types of responses: (1) the first measure is higher, (2) the first measure is lower, (3) the measures are nearly equal, and (4) the measures are equal.

The examples use versus to ensure a comparison occurs. Alternatives: vs, compared to, in comparison with, and in comparison to.

Query

Answer

Tell me about Sales versus COGS?

In July 2021, Sales are higher than COGS by $2.29 million (18%).

Tell me about Sales versus Target Sales?

In July 2021, Sales are lower than Target Sales by $0.53 million (4%).

Important

You must set the aggregation type for both measures to either Don't Summarize or Sum in Power BI.

You can make the query more precise by adding dimension instances:

Query

Answer

Tell me about Sales versus Target Sales for Retine in Canada?

In July 2021, Sales in Canada for Retine were higher than Target Sales by $43,000 (12%).

Note

You could further filter the query using a time filter.

 

Top

Time-based variance queries

Required aggregation type

Aggregation words for query

Don't summarize or Sum

sum, total (optional)

These queries ask how much a measure has changed over a time period. These queries return three types of responses: (1) measure increased, (2) measure decreased, and (3) measure remained stable.

There are two types of time-based variance:

Period-on-period variance

These queries ask how a measure has changed period-on-period, where period can be month, quarter or year.

These queries must include change, changed, vary, or varied. They must also contain a "period on period" or "period over period" phrase (where period = month, quarter, or year). Please note that neither "period to period" nor "period-on-period" work.

Query

Answer

How have my Sales changed month on month?

Sales increased by $6.1 million (100%) this month compared to last month.

How have my Sales changed quarter on quarter?

Sales decreased by $610,000 (5%) this quarter compared to last quarter.

How have my Sales changed year on year?

Sales decreased by $3.83 million (7%) this year compared to last year.

Note

Period-on-period variance queries use equivalent values for the given time period. For example, if the date is 31st July 2021 and you ask how sales have changed year on year, Arria Answers compares total sales for 1st January 2021 through 31st July 2021 to total sales for the same time period in the previous year (1st January 2020 through 31st July 2020).

You can make the query more precise by adding dimension instances:

Query

Answer

How have my Sales of Karateon in the United States of America changed year on year?

Sales in the United States of America for Karateon increased by $193,000 (7%) this year compared to last year.

Note

You receive an error message when there is no data for one of the time periods you wish to compare.

Period year-on-year variance

These queries ask how a measure has changed for a specific month or quarter on a year-on-year basis.

Query

Answer

How did Sales of Karateon change in June year on year?

Sales for Karateon decreased by $37,000 (13%) in June this year compared to the same period last year.

How did Sales of Retine in France change in Q1 year on year?

Sales for Retine in France decreased by $0.51 million (92%) in Q1 this year compared to the same period last year.

Note

For this type of query you must include a "P year on year" phrase, where P can be either a specific month (e.g. July) or a specific quarter (e.g. Q1). You can use the phrase "year over year" instead of "year on year" if preferred.

 

Top

Drilldown variance queries

Required aggregation type

Aggregation words for query

Don't summarize or Sum

sum, total (optional)

This is a more sophisticated type of variance query. It doesn't just identify a variance trend; it also drills down into the data and provides more detailed information about drivers and offsets of the trend.

Note

Drilldown works with both target-based variance and time-based variance queries. For target-based queries, you must set the aggregation type for both measures to either Don't Summarize or Sum in Power BI.

Here is a standard target-based variance query:

Query

Answer

Tell me about Sales versus Target Sales in Canada?

In July 2021, Sales in Canada were lower than Target Sales by $111,000 (5%).

With slight rephrasing, you can ensure drilldown analysis is performed:

Query

Answer

What drove Sales versus Target Sales in Canada?

In July 2021, Sales in Canada were lower than Target Sales by $111,000 (5%), driven by Ointmo, Karateon and Nutrali and offset by Retine and Camoide.

The answer identifies the variance trend AND identifies which product sales drove or offset the trend. Ointmo, Karateon and Nutrali are drivers of the parent variance (their sales are also below target), while Retine and Camoide are offsets.

Tip

Other valid ways of phrasing the above query:

  • What caused the Sales versus Target Sales variance in Canada?

  • Why the Sales versus Target Sales trend in Canada?

You could include another dimension instance in the query to drill down further. For example:

Query

Answer

What drove the Sales versus Target Sales trend for Retine in Canada?

In July 2021, Sales in Canada for Retine were higher than Target Sales by $43,000 (12%), driven by Brand Store and offset by Channel Partners.

Order of drilldown dimensions

These queries identify a parent variance trend, then perform a drilldown analysis using one dimension.

The drilldown dimension cannot be a dimension already represented in your query. For example, the following query includes France, which is an instance of the dimension Country. Therefore, the parent variance cannot be broken down by Country.

Query

Answer

Why did Sales in France change in Q1 year on year?

Sales in France decreased by $0.5 million (14%) in Q1 this year compared to the same period last year, driven by Retine, Camoide and Nutrali and offset by Karateon and Glower.

There are two other dimensions in our sample data: Product and Segment. Why, then, is drilldown analysis performed using Product rather than Segment? This happens because, when the sample project was set up, the Product field was selected before Segment.

The dimension fields were selected in this order: (1) Country, (2) Product, (3) Segment. The drilldown analysis doesn't use Country because a country has been specified in the query. Therefore, it uses the next dimension, i.e. Product.

To change the order, simply deselect the dimension fields (located in the Fields pane within Power BI), then reselect them in the following order: (1) Country, (2) Segment, and (3) Product. Once done, the drilldown analysis will use Segment before Product:

Query

Answer

Why did Sales in France change in Q1 year on year?

Sales in France decreased by $0.5 million (14%) in Q1 this year compared to the same period last year, driven by Brand Store, Online and Chemists and offset by Departmental and Channel Partners.

 

Top