Skip to main content

Basic aggregation queries

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

There are five aggregation types — Sum, Average, Maximum, Minimum, and Mode — 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

  • Aggregation type for measures = None.

  • Default Time Filter = Latest month

  • Default Currency = US Dollar (USD)

 

Sum aggregation

Aggregation type for measure

Aggregation words for query

None or 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

None or Average

average, mean

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

Tip

You could filter the query further with a time filter or exclusion filter.

Important

You must set the measure's aggregation type to None or Average in TIBCO Spotfire.

 

Top

Maximum aggregation

Aggregation type for measure

Aggregation words for query

None or Max

biggest, highest, largest, max, maximum, top

This query type returns the maximum value for one 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.

Tip

You could filter the query further with a time filter or exclusion filter.

Important

You must set the measure's aggregation type to None or Max in TIBCO Spotfire.

 

Top

Minimum aggregation

Aggregation type for measure

Aggregation words for query

None or Min

bottom, lowest, min, minimum, smallest

This query type returns the minimum value for one 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.

Tip

You could filter the query further with a time filter or exclusion filter.

Important

You must set the measure's aggregation type to None or Min in TIBCO Spotfire.

 

Top

Mode aggregation

Aggregation type for measure

Aggregation word

None

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 multiple mode values. 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