Aggregate expressions

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.

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

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.

Aggregate on sub-level

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

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

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

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

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

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

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

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.

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.

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.

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.

   
   

   

   
   

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.

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.

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.

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