Skip to main content

Basic aggregation queries

Basic aggregation queries return a single aggregated value for one measure.

There are four aggregation types — Sum, Average, Maximum and Minimum — and this topic covers each.

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)

Sum aggregation

Aggregation type for measure

Aggregation words for query

Sum

sum, total (optional)

This query type returns the sum-aggregated total for one 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 filter the query further with a time filter or exclusion filter.

Important

DEFAULT TIME FILTERING — ALL AGGREGATION 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.

 

Top

Average aggregation

Aggregation type for measure

Aggregation words for query

Average

average, mean (optional)

This query type returns the average value for one measure. For average aggregation, you add just one dimension to the Arria Answers visualization. This can be a time dimension (e.g. Year) or a non-time dimension (e.g. Country). You must remove the other dimensions.

Assuming that Country is the only 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 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 filter the query further with a time filter or exclusion 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.

 

Top

Maximum aggregation

Aggregation type for measure

Aggregation words for query

Maximum

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

This query type returns the maximum value for one 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 filter the query further with a time filter or exclusion 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.

 

Top

Minimum aggregation

Aggregation type for measure

Aggregation words for query

Minimum

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

This query type returns the minimum value for one 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 filter the query further with a time filter or exclusion 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

×