The aggregate function feature allows users to add calculations into Harmoni that use values from an analysis as the inputs. The function can be defined either in the Project Tree, or in the analysis itself, creating results in real time and enhancing analytical capabilities.

Sometimes known as Peers or Market average, this feature is predominantly useful in creating averages across variables, for example, countries or brands. The aggregate function allows you to create results showing the average, max, min and sum for percentages, numbers and averages.

Project Owner
Creator
Explorer

Coming Soon

This feature is not available in Harmoni. It it undergoing user acceptance testing and will be available once all tests are finalized.

1. Create a New Aggregate Item

Harmoni works with respondent level data. All results are calculated by rolling up from the respondent level. This is just what you need when working with market research data, but sometimes, you want to apply calculations to the aggregate results, for example averaging across market averages. Harmoni's aggregate function allows you to do this.

There are two options to add in an aggregate function:

Project Tree: Add an aggregated item to the project tree using the new function option in the design menu.
Analysis Table: Add an aggregated item directly into an analysis table. Once added to the table, the item is also visible in the project tree.

Project Tree

Follow these steps to add an aggregated item into the project tree:

Switch to edit mode (Project Owners click the edit pen, Explorers/Creators click the design menu)
Click the new function option from the design menu
Define and create the calculation

Note: If you select the axis where you plan to add the aggregate element first, then click new function, Harmoni automatically populates the Select Elements box in the definition with all the elements in the axis.

Analysis Table

To add an aggregated item into an analysis, a Creator or Explorer must...

Create an analysis
Highlight two or more elements (or the whole axis)
Click the fx option in the modify menu
Define and create the calculation

Note: Selecting one element will not display the function icon. You must select two or more elements to display it in the modify menu.

2. Define the Aggregate Function

Once the editor opens, you can enter the details required for the calculation. You need to...

Name the new item
Select the function type: Avg, Sum, Min, or Max.
Choose the calculation type containing the function input: Numbers, Percentages, or Averages.
Choose the items to include in the calculation e.g., specific brands or markets.
Once you have completed the definition, click the CREATE button. You must have two or more elements in the Select Elements box before you can save the aggregate construction.

New Function Settings

Setting	Description
Label	The named item appears as an element in the axis. It can be helpful to include the calculation type in the label.
Function Type	Select the type of calculation you require. You can create an average, sum the items, or show the minimum or maximum value.
Based on Calculation Type	Bases the aggregation on this calculation type. For example, if you select the function type, Avg, and select Percentages to base it on, the element displays the average across the percentages. To display the aggregated item in the analysis table, this calculation type must be selected.
Select Elements	Include all the elements you want aggregated. If you select the axis before clicking new function or fx, all elements are added to the list. If you select two or more elements then click new function or fx, only these elements are included. Use the delete option to remove items.

Select Elements

The are several options to add elements to the Select Elements box.

Prior to clicking new function or fx...

Select the whole axis either in the project tree or the analysis, then click the new function option, and all elements are automatically added.
Select two or more elements from the axis, either in project tree or the analysis, then click the new function option, and the selected elements are automatically added.

Once the editor is open you can...

Drag and drop elements from the project tree into the Select Elements box.
Remove elements from the Select Elements box by using the trash can icon next to each element or Remove All.

Name the aggregate function
Select the Function Type
Select the Calculation Type
Select the elements to be included in the aggregation
Click CREATE

Note: You can't drag measures into the Select Elements box, but you can add the aggregate to a standard axis, e.g. Top 10 Markets and cross it with the measure or measure group to see the aggregated result across the markets.

Update the Aggregate Function

To make changes to the construction, select an aggregate element and click Library/Editor in the action pane. The editor opens and you can update the settings. Once complete, click the UPDATE button to save the changes.

Select an aggregate element
Click Library/Editor to open the editor
Make the required changes
Click UPDATE

3. Aggregation Examples

There are multiple combinations of the function type and calculation type. Let's look at a few examples of how to use them to create an aggregate function and how to display the calculation types in the analysis.

Average, Min, and Max

In a series of rating questions we may want to better understand the range of scores, by identifying the min and max, and also identify the average average score. To do this, we can create min, max and averages in the Ratings grid.

Using new function create three average aggregates for AVG, Min and Max
Drag Top 5 Markets to the down dimension
Drag the Satisfaction Grid across
Change the calculation type to AVG
Select the Total from the Elements to display averages for each market
The Min, Max and AVG are calculated for each market

Averages across a Measure Group

You can create an aggregate in a standard axis and then cross it with another item to see the aggregate result, for example, across a measure or a measure group.

The aggregate is added to a copy of Top 5 Markets
Select AVG in function type
Select Averages for calculation type
Drag in Top 5 Markets across
Drag in a measure group down
Change the calculation type to averages
The average across all the averages is displayed across the markets

An Explorer cannot add an aggregate function to a constructed item owned by the Project Owner. They can construct or copy an axis, creating their own item and add an aggregate function here.

Averaging Percentages

Here, we'll create an average percentage across Top 3 box for satisfaction. We will be able to see the average percentage across all the Top 3 box scores for each market.

The aggregate is added to Satisfaction - Top 3
Select AVG in function type
Select Percentages for calculation type
Drag in Satisfaction - Top 3 down
Drag in Top 5 Markets across
View the average percentage across all satisfaction attribute per market

Aggregate Numbers Sum

In this example, we'll create an summation of all the respondents who visit parks when visiting New Zealand. In Harmoni, if you create a combined item, it won't count the respondents twice, even though they visit multiple parks. This way you see the percentage of respondents who visited parks. But, using the aggregate sum and displaying numbers, you can see the actual number of visits to parks. This would be easy to calculate in Excel after exporting the data, but in Harmoni you can cross it with any item to recalculate the number of visits on the fly.

The aggregate is added to Activities in NZ
Select Sum in function type
Select Numbers for calculation type
Drag in Activities in NZ
You can see the number for the summed visits
Below is the count in the combined item
Change the calculation type to numbers
Drag in items to view the results across different variables

4. Limitations when Working with Aggregated Data

Aggregated functions by definition are not stored at the respondent level. This means there are some restrictions on what is available.

You cannot use an aggregate function as a filter, i.e. drag it to the filter drop zone.
You cannot use an aggregated element as a reference in a time series axis.
Drill through is not available for aggregated items.
You cannot create aggregate functions based on another aggregate function.
You cannot use an aggregate function as a global filter category or option.

Where to from here?

Learn more about Harmoni

Aggregate Functions - Coming Soon

In this article

1. Create a New Aggregate Item