...
| Insert excerpt | ||||||||
|---|---|---|---|---|---|---|---|---|
|
Overview
Aggregate functions, often grouped by other attributes, give totals, max, min, average values etc. for each key value they are provided. For example, you can sum the amount of all the lines on an invoice based on an invoice number.
Aggregate functions are
...
| Function | Description | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| average | This function returns the arithmetic average of the set of numbers. It takes one argument (a name of a field) of numeric type: Long, Float or Integer. It is only available if field on which it is applied, is of such type. | ||||||||||||||||||||||
| maximum | This function returns the maximum numeric value out of the set of numeric, dates as well as string and values. It takes one argument (a name of a field) of type, either: Long, Float, Integer or a String. In case of a String, it will return a "maximum string", that is a string that would be the last string out of the whole set of strings, if they were sorted in alphabetical order. In case of a date - it will return the latest date. | ||||||||||||||||||||||
| minimum | This function returns the minimum numeric value out of the set of numeric, dates as well as string values. It takes one argument (a name of a field) of type, either: Long, Float, Integer or a String. In case of a String, it will return a "minimum string", that is a string that would be the first string out of the whole set of strings, if they were sorted in alphabetical order. In case of a date - it will return the earliest date. | ||||||||||||||||||||||
| sum | This function returns the sum calculated by suming all the values in a given set of values. It is applicable only (and only then it is available) if the name of the field passed to this function represents numeric field of type: Long, Float or Integer. | ||||||||||||||||||||||
| percent | This function expects a name of numeric field to calculate percentage of sum of numerical values of each group in relation to a whole set of grouped values. It is therefore necessary to use this function in conjunction with grouping attribute, as otherwise only default grouping would be applied and this would present no meaningful result, as it would always default to 100% (each record would have been in its own group). If however we assume having a file with 2 records, like so:
and we also assume grouping by "Label", our percent function would return (if applied o "Number" attribute), the following result in a view (assuming we also selected Label attribute to be shown in our view and rounding to 2 decimal places):
The above are percentages of values assigned to given grouping parameters (A, B, C) as a percentage portion of the whole, computed as sum of all such values. | ||||||||||||||||||||||
| distinct | Based on a grouping factor, this function will list number of occurances of distinct records. | ||||||||||||||||||||||
| count | Counts the number of elements. |
...
available to use on Views and Pipes.
Aggregating with a Pipe
To configure a pipe for aggregation
- Click on a 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: name your new attribute.
- Aggregation on a pipe takes place across all groups of data. Therefore you can define which attribute(s) you wish to group on by adding these to sort/group, or if no grouping is provided aggregation takes place across all records.
- 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.
| Note |
|---|
You cannot refer to any attributes in the source table for which there is no grouping or aggregate attribute. |
Aggregating with a Table
- Click on the pipe connecting to an Aggregate Table to open its properties.
- Aggregation takes place across all groups of data. Therefore you can define which attribute(s) you wish to group on by adding these to Sort/Group, or if no grouping is provided aggregation takes place across all records.
- Click
to save your changes.Insert excerpt _finish _finish nopanel true - The Aggregate Table has access to all attributes on the incoming pipe.
- In the Attribute expressions,
- Use functions to aggregate values e.g. sum(in.Amount) will sum up all amounts within a group.
- Where multiple values exist for a group they will be treated as an array.
Use a full stop suffix after the attribute name followed by a number to obtain the record from a group in that position.
For example in.Name.1 will take the first name in a group.
Alternatively, use the Functions built into PhixFlow to select the required value.
Aggregate Examples
Aggregate Functions
| Anchor | ||||
|---|---|---|---|---|
|
For the aggregate functions, Cumulative Sum and Cumulative Percent, see View Attribute.
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|