# Aggregate expressions

> Record expressions take only the value of the record itself as input. In comparison, aggregate expressions can be used to make calculations over a specific set of records. For example, to calculate the total **Amount** of all records, or the total number of records in the dataset. An aggregate expression can be compared to the `GROUP BY` function in SQL.

## Introduction

Record expressions take only the value of the record itself as input. In comparison, aggregate expressions can be used to make calculations over a specific set of records. For example, to calculate the total **Amount** of all records, or the total number of records in the dataset. An aggregate expression can be compared to the `GROUP BY` function in SQL.

## Aggregate on root level

An aggregate expression calculates a value for each unique value at the aggregate level. By default, this is on the **Root** level, i.e. one value for the entire table.

![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-54644-dbf4c740-3f128f5e.webp)

Below is an example of an aggregate expression to calculate the sum of all amounts in the Invoices dataset.

![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-55886-ff4b2751-03780bb4.webp)

The aggregate expression `sum(records.amount)` is evaluated as described below:

1. Take all records in **Invoices**.
2. For each record, pick the value of the **Amount** datasource attribute.
3. Return the sum of all these amounts.

The expression calculates a single unique value, but this value is added to every record in the dataset. Below is an example of a dataset containing 4 rows.

   ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-58845-d8078078-d6ddafd0.webp)

## Aggregate on sub-level

Instead of calculating an aggregate over the whole dataset, you can also calculate a value over groups of records.

   ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-55887-03e358e2-5630a94f.webp)

Below is an example of an aggregate expression to calculate the total amount per supplier.

   ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-53503-8011cec0-de804fb6.webp)

An aggregation level is added, and the scope of the expression is set to this new level. Below is an example dataset.

   ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-58224-0cc7cc92-db330f21.webp)

The aggregate expression is calculated once for each individual value within the scope level. This value is added to each record within this scope. So **Total amount** is calculated once, then copied 4 times to each record. **Total amount per supplier** is calculated 3 times. Since there are 2 records with “Supplier A”, its value is set to both records. Since there is only one record for “Supplier B” and “Supplier C” the value is assigned to the corresponding record.

### Defining a Sub-level

Follow these steps to define an aggregate expression on sub-level.

| Step | Action |
| --- | --- |
| 1 | Right-click on **Root** in the **Settings** panel in the **Edit Expression Attribute** dialog. |
| 2 | Select **Add level**. |
| 3 | Click on **…** level and select the datasource attribute on which you want to group values. In this case **Supplier**. |
| 4 | Right click on the selected attribute level and select **Set as expression level**. |

:::note
The selected level is indicated by a arrow.
:::

## Tree structure

In an aggregate expression, it is also possible to access other levels than the selected aggregation level. We can use `p` (for parent) and `c` (for child) to traverse up and down the tree structure.

:::note
A node has one parent, so `p` returns a single node, but can have multiple children, so `c` returns a list of nodes.
:::

### Parent aggregation level

   ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-58245-6674eef9-3022ca1d.webp)

Below is an example of an aggregate expression based on a parent level.

   ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-58849-49d3d78f-79445827.webp)

The expression `percentage(sum(records.Amount) / sum(p.records.Amount) * 100)` calculates a percentage for each supplier:

1. the sum of the amounts associated with a supplier is divided by the sum of all amounts.
2. the scope is shifted to the parent level, i.e. root.
3. the result is multiplied by 100 to cast it to a percentage.

### Child aggregation level

   ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-53792-23102c35-72807652.webp)

Below is an example of an aggregate expression based on a child level.

   ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-58209-db7ef103-d0af0337.webp)

The `count(c)` expression returns a list of all suppliers per **Supplier type**. Since each child is a supplier, the number of children `c` can be counted.

### Creating conditional checks

For record expressions the `if()` function can be used for conditional checks. However, this function can only be applied per record. It is not possible to check a condition within a group of records using an if-statement. The tree structure can be used to loop over (specific parts of) your data, for example to express properties.

   ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-54176-c3474380-dd080a39.webp)

The following functions can be used for these type of calculations.

| Function | Description |
| --- | --- |
| `exists(Scope, Condition)` | Returns `true` if at least **one Condition in Scope** evaluates to `true`. |
| `forall(Scope, Condition)` | Returns `true` if **all Conditions in Scope** evaluate to `true`. |

See illustration below for an example.

   ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-54704-e95eec33-a5b5497b.webp)

For example the expression

`exists(records, find(lower(Supplier), 'consulting')`

checks for each supplier type if there are invoices from a supplier with ‘consulting’ as part of the name.

:::note
`lower` is used to convert the supplier names to lower case and make the check case insensitive.
:::

## Filtering

When aggregating data, calculations are sometimes made depending on a specific part of your data. In this case, the `filter()` function can be used.

   ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-58753-4b97e9f3-24f28e6c.webp)

Below is an example of an aggregate expression based on a filter.

```
count(
    unique(
        filter(
            records,
            supplier_type = "Machinery"
        ).Supplier
    )
)
```

From inside outwards, the expression is built up in the following manner:

1. Retrieve all records within the current scope.
2. Filter these records so only the ones which have a **Supplier type** of *Machinery* remain.
3. For each filtered record, return the value of the **Supplier** datasource attribute.
4. Remove all duplicate values from the list, so we have only one item per unique value.
5. Count the number of items remaining on the list.

   ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-58858-0907d0d4-a53c64d6.webp)

In the example, the **Case owner** level is set as the expression level. This means the scope of the expression is to calculate a value for all the unique values of **Case owner**. For each **Case owner** value, `records` is the list of records belonging to this Case owner.

The expression returns a value for each **Case owner**, which signifies the number of unique **Suppliers** with the type *Machinery*.

### Other Filter Options

The menu in the **Settings** panel contains options to add filtering for aggregates.

   ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-54248-7500d72d-3854556f.webp)

Below is a description of the options of the **Settings** menu.

| Option | Description |
| --- | --- |
| Enable filter expression | Enables you to enter an expression as a filter. |
| Enable filter controls | Opens the **Edit Filter Controls** dialog where you can select an available filter control from the list. |
| Enable compare period | Enables you to add a compare period or an expression to define a number of previous periods to filter. |
| Apply end-user filters | Enables you to apply available end-user filters. |

:::note
When you use the filter options from the **Settings** menu the result of the filter is displayed in the **Value** panel. See illustration below for an example.
:::

   ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-57557-09f3c42a-7f938b15.webp)

## Sorting

Sorting can be used to determine the highest or lowest value or to process records in a certain order.

When you need the first or last record for your expression, you can sort on a level in the following ways:

* **A..Z** and **Z..A** will sort on value.
  + On strings, this will sort in alphabetical order.
  + On numerical formats (double, integer) this will sort on a numerical value.
  + On time-related formats (datetime, date) it will be sorted in chronological order.
* **0..9** and **9..0** will sort on the number of records in the group.
* **Expression** allows you to fill in an expression, which will be sorted on value as described above.

For each of these sorting options, it is possible to set the order to either ascending or descending.

See the illustration below for an example of sorting where the first user working on each case is determined.

  ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-54072-27559a3e-23d17c3b.webp)

The aggregate expression `first(records.User)` determines the first `User` of each `Case ID`. `Event end` is sorted in ascending order.