Using Multiple Aggregate Functions Within the Same Measure

Published on November 27, 2015

The default aggregation function in Arcadia Instant is summation. However, you can easily change it to optional aggregation operations, such as count, string concatenation, and common statistical measurements. You can also use multiple aggregations of the same measure on a shelf.

In this post, I’ll show how to you use the following aggregates of the same measure, i.e. expenditure, in a single view.

  • Total expenditure of an industry,
  • Average expenditure of an industry per year, and
  • Maximum expenditure of an industry in a year.

Dataset
I’ll be working with the dataset Expenditures for Companies with Employees by Industry Sector [Data source], which shows information of the expenditure across different industries in the US from 2004 to 2013.

Prerequisites
Access the dataset Expenditures for Companies with Employees by Industry Sector. If you do not have this dataset, you can import it into Arcadia Instant by following these steps:
Importing data from flat files
Creating datasets from imported data
Creating data connections

Use multiple aggregates of a single measure
To begin, move the expenditure field to the Measures shelf, modify its aggregation, and repeat these steps for each aggregation and for each measure.

  1. Create a Table Chart with the following fields on the Dimensions and Measures shelves.

rtaimage

  1. Place industry on the Dimensions shelf.
  2. Place expenditure on the Measures shelf and change the variable display from sum(expenditure) to Total Expenditure by writing the following expression in the Enter/Edit Expression window.

rtaimage01

  1. Place expenditure on the Measures shelf and change its aggregation from sum to average by writing the following expression in the Enter/Edit Expression window.

rtaimage02

  1. To show the maximum expenditure in a year, place expenditure on the Measures shelf and change its aggregate function by writing the following expression in the Enter/Edit Expression window.

rtaimage03

Additionally, you can also visualize the year with the maximum expenditure in a particular industry.

  1. To show the year with maximum expenditure, place expenditure on the Measures shelf and change its aggregate function by writing the following expression in the Enter/Edit Expression window.

rtaimage04

  1. Click Refresh Visual.

The following chart appears on the main screen. It shows three aggregates of the same measure (expenditure)—total, average, and maximum—in each industry row.

rtaimage05

  1. [Optional] Alternatively, you can create calculated fields with each aggregation by editing attributes of the dataset and then use those calculated fields as measures on the Measures shelf.