Aggregate data from a dataset

You can use aggregation datasets to count the number of items in an existing dataset and perform calculations on numerical data in datasets. For example, you can use an aggregation to return a count of the number of customers per country or region, or to calculate the average or total order value per customer.

Before you can create an aggregation dataset, you need a dataset that identifies the cases or tasks that you want to count or perform calculations on. You can use a default dataset or create a new dataset as the parent, and apply filters to limit the items included in the aggregation. For more information, see Configure datasets.

Tip:

An advantage of using a default casetype dataset is that the columns are updated automatically whenever you add or change the casetype attributes.

Create an aggregation dataset

To create an aggregation dataset, create a new dataset as normal and set the Type to Aggregation. Then, select the dataset containing the data you want to aggregate as the Parent dataset.

You can then use the dataset designer to add groupings and configure the counts and calculations, as described below.

Add groupings

You can either count or perform calculations on all the items in a dataset, or you can break a dataset down into groups and count the number of items per group or perform calculations on the items in a group.

To group items in the dataset, add a Group by column from the toolbar and then use the properties pane to identify the column containing the data you want to group by. For example, to group a list of Customer cases by the each customer's Country attribute, select the Country column. You can group items by any columns that have been added to the parent dataset, including case, activity, or task metadata.

You can add multiple groupings to a dataset in order to break the aggregation down by several dimensions. For example, you might want to see a count of Customer cases broken down by country and then by referral source, so that you can compare the number of customers referred by social media campaigns in each country. To achieve this, add a grouping using the Country attribute first, followed by a grouping using the Source attribute. To break the data down first by source and then by country, change the order of the grouping columns.

Count dataset items

If you have not added any groupings, a count simply returns the total number of rows in a dataset. For example, you can use a count to report the number of products in your inventory or the number of overdue mandatory tasks in your application. If you have added groupings, a count returns the total number of rows in each group, such as the number of expense claims per employee.

To count the number of items in the parent dataset or in each group in the parent dataset:

1. Add an Aggregation column from the toolbar.
2. Select the aggregation column to open the configuration pane.
3. From the Column list, select the column in the parent dataset that you want to use as the basis of the count. For example, to count every case in the dataset, you can use the case ID or another mandatory field. If you only want to count items where a particular piece of data has been supplied (such as customers with an email address), select the column for that attribute.
4. Set the Operation to Count. There is no requirement to set a data type for counts.
5. If you have added groups, you can sort the groups by the count. To order groups from least to most, select Ascending. To order groups from most to least, select Descending.

Tip:

If the count for a grouping is zero, a row is included for the grouping but the count cell is empty.

Perform calculations on dataset items

You can perform calculations on columns containing numerical data. For example, you might want to calculate the average value of employee expense claims grouped by department, or the total amount spent per customer in the last year.

To perform a calculation on data from the parent dataset:

1. Add an Aggregation column from the toolbar.
2. Select the aggregation column to open the configuration pane.
3. From the Column list, select the column in the parent dataset on which you want to perform the calculation.
4. From the Operation list, select the calculation you want to perform:
   • Sum: Return the total of the values in the parent dataset or group.
   • Average: Return the mean average of the values in the parent dataset or group.
   • Minimum: Return the lowest value.
   • Maximum: Return the highest value.
5. By default the result of the calculation is returned using the data type of the selected column. Use the Aggregation data type list to change the data type. For example, patient age in the parent dataset might be stored as an integer, but when calculating the average patient age you might want to use a double.
6. If you have added groups, you can sort the groups by the calculation result. To order groups from lowest to highest, select Ascending. To order groups from highest to lowest, select Descending. If you have added multiple aggregation columns, use the Sort order field to specify the column you want to sort by first. If you only have one aggregation column, enter 1 in the Sort order field.

Filter aggregation datasets

The items returned by aggregation datasets are either counts or the results of calculations. You can apply conditions to aggregation datasets to filter these items. For example, in a dataset that returns the number of customers per referral source, you may only want to include those sources where the count is greater than 100 to avoid including a "long tail" of results when you display the data using a chart plugin.

To filter the results of an aggregation, click the condition icon and then select the condition to open the properties pane. For more information, see Use conditions to filter a dataset in Configure datasets.

Next steps

You can use template widgets to display the results of counts or calculations in a page or view. For example, you might want to create a chart showing the count of customers grouped by country, or you might want to display total sales figures within a line of text. For more information about displaying data from an aggregated dataset, see Use datasets in your application.