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 the 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

  • 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 four aggregation types:

Sum aggregation

Required aggregation type

Aggregation words for query

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.

Tip

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

Average

average, mean (optional)

This query type returns the average value for a measure. For average aggregation, 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 Avg(Sales) for France ?

Overall, the only value for Avg(Sales) in France is $189,000.

What are Avg(Sales) for Canada ?

Overall, the only value for Avg(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 Avg(Sales) for 2020?

In 2020, the only value for Avg(Sales) is $182,000.

What are Avg(Sales) for 2021?

In 2021, the only value for Avg(Sales) is $190,000.

Tip

You could further filter the query using a time filter.

Important

You must set the measure's aggregation type to Average in MicroStrategy. This creates, for example, an Avg(Sales) measure that you must use in your query. You can give this measure an alias if you wish.

Maximum aggregation

Required aggregation type

Aggregation words for query

Maximum

biggest, highest, largest, max, maximum, top (optional)

This query type returns the maximum value for a measure.

Query

Answer

Max(Sales) value?

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

Max(Sales) value for Glower ?

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

Max(Sales) value for Glower in France?

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

Tip

You could further filter the query using a time filter.

Important

You must set the measure's aggregation type to Maximum in MicroStrategy. This creates, for example, a Max(Sales) measure that you must use in your query. You can give this measure an alias if you wish.

Minimum aggregation

Required aggregation type

Aggregation words for query

Minimum

bottom, lowest, min, minimum, smallest (optional)

This query type returns the minimum value for a measure.

Query

Answer

Min(Sales) value?

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

Min(Sales) value for Ointmo ?

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

Min(Sales) value for Ointmo in France?

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

Tip

You could further filter the query using a time filter.

Important

You must set the measure's aggregation type to Minimum in MicroStrategy. This creates, for example, a Min(Sales) measure that you must use in your query. You can give this measure an alias if you wish.

 

Top

Group-by queries

Required aggregation type

Aggregation words for query

Sum

sum, total (optional)

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

Query

Answer

Tell me about 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 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 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 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.

Tip

You could further filter the query using a time filter.

Average group-by queries

Required aggregation type

Aggregation words for query

Average

average, mean (optional)

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

What is Avg(Sales) by Product?

Overall, the values for Avg(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.

Important

You must set the measure's aggregation type to Average in MicroStrategy. This creates, for example, an Avg(Sales) measure that you must use in your query. You can give this measure an alias if you wish.

 

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

Maximum

top, biggest, highest, largest, max, maximum (optional)

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

Query

Answer

Top Country for Max(Sales)?

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

Important

You must set the measure's aggregation type to Maximum in MicroStrategy. This creates, for example, a Max(Sales) measure that you must use in your query. You can give this measure an alias if you wish.

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

Query

Answer

Top Country for Max(Sales) of Glower?

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

Tip

You could further filter the query using a time filter.

Bottom queries

Required aggregation type

Aggregation words for query

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

Bottom Country for Min(Sales)?

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

Important

You must set the measure's aggregation type to Minimum in MicroStrategy. This creates, for example, a Min(Sales) measure that you must use in your query. You can give this measure an alias if you wish.

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

Query

Answer

Bottom Country for Min(Sales) of Retine?

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

Tip

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 rather than 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

What is the bottom month in 2021 in terms of Min(Profit) for Online?

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

What is the top quarter in 2021 for Max(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

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 Sum in your BI tool.

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%).

Tip

You could further filter the query using a time filter.

 

Top

Time-based variance queries

Required aggregation type

Aggregation words for query

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.

Important

Period-on-period variance queries use equivalent values for the given time period. For example, if the date is 31st July 2021 and you query 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.

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.

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.

Important

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

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.

Tip

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 Sum in MicroStrategy.

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

Drilldown variance 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 data was configured, the Product field was prioritized above Segment.

The dimension fields were prioritized as follows: (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.

You can change the priority order using the Arria Answers wizard. Change the priority order for dimensions to: (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