| Insert excerpt | ||||||||
|---|---|---|---|---|---|---|---|---|
|
Overview
Aggregate functions aggregate data, often grouped by other attributes, to give totals, max, min, average values etc. for each key value they are provided. For For example, you can sum the amount of all the lines on an invoice based on an invoice IDnumber.
Aggregate functions are available in to use on stream views and pipes.
Aggregation on a Pipe.
Pipe aggregation is useful for simple aggregations. To configure a pipe for aggregation
- Click on the pipe to open its properties.
- In the Aggregate Attributes section, click the plus button to add a new attribute.
- Complete the details:
- A Function: select the function you wish to perform e.g. sum. A complete list is provided in the function section at the end of this page.
- Attribute: select the attribute you wish to perform the function on.
- Name: Aggregation produces a valuename your new attribute.
- Click
to save your changes.Insert excerpt _finish _finish nopanel true - The table connected to the end of the pipe can now access your attribute using its name.
Example
Functions
This is the list of available aggregate functions.
| Function | Description | ||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Average | This function returns the arithmetic average value of an attribute from records with the same key value. In detail, it returns the sum of all values for a key value, divided by the number of values that are not null. It is only available for numeric attributes. | ||||||||||||||||||||||||
| Count | This function returns the number of non-null values of an attribute from records with the same key value. | ||||||||||||||||||||||||
| Distinct | This function returns the number of unique values of an attribute from records with the same key value. | ||||||||||||||||||||||||
| Maximum | This function returns the maximum value of an attribute from records with the same key value. It is available for numeric, string and date/time attributes. Strings are ordered alphabetically, with the shortest strings first. | ||||||||||||||||||||||||
| Minimum | This function returns the minimum value of an attribute from records with the same key value. It is available for numeric, string and date/time attributes. Strings are ordered alphabetically, with the shortest strings first. | ||||||||||||||||||||||||
| Percent |
This function can be applied to a numeric field, to return the percentage of the sum of values for each key value in relation to the total sum of values. You should use this function in conjunction with one or more grouping attributes - each combination of values from these grouping attributes gives a key value. For example, you have a stream with 2 fields:
and you create a grid view:
This will give the view:
| ||||||||||||||||||||||||
| Sum | This function returns the sum of all values of an attribute from records with the same key value. It is only available for numeric attributes. |