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 monthDefault 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.
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.
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.
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.
