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.
- Create a Table Chart with the following fields on the Dimensions and Measures shelves.
- Place
industry
on the Dimensions shelf. - Place
expenditure
on the Measures shelf and change the variable display fromsum(expenditure)
to Total Expenditure by writing the following expression in the Enter/Edit Expression window.
- Place
expenditure
on the Measures shelf and change its aggregation fromsum
toaverage
by writing the following expression in the Enter/Edit Expression window.
- 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.
Additionally, you can also visualize the year with the maximum expenditure in a particular industry.
- 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.
- 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.
- [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.