Aggregating Data

Owned by Pawel Huszcza (Unlicensed)
Last updated: Nov 22, 2024 by Zoe Baldwin

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 available to use on /wiki/spaces/HELP12/pages/9615616123s and /wiki/spaces/HELP12/pages/9615616204s.

Aggregating with a Pipe

To configure a pipe for aggregation

  1. Click on a pipe to open its properties.
  2. In the Aggregate Attributes section, click the plus button to add a new attribute.
  3. Complete the details:
    1. 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.
    2. Attribute: select the attribute you wish to perform the function on.
    3. Name: name your new attribute.
  4. 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. 
  5. Click  Apply and Close to save your changes.
  6. The table connected to the end of the pipe can now access your attribute using its name. 


You cannot refer to any attributes in the source table for which there is no grouping or aggregate attribute.


Aggregating with a Table

  1. Click on the pipe connecting to an Aggregate Table to open its properties.
  2. 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.
  3. Click  Apply and Close to save your changes.
  4. The Aggregate Table has access to all attributes on the incoming pipe.
  5. 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 /wiki/spaces/HELP12/pages/9615607696.
      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 /wiki/spaces/HELP12/pages/9615607458 built into PhixFlow to select the required value.

Aggregate Examples

See /wiki/spaces/HELP12/pages/9615611316.

Aggregate 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.

CountThis 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. There is no single attribute function equivalent of the distinct pipe function.

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

The percent function is only available for PhixFlow instances running on an Oracle database.

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 table with 2 fields:

Number
Label
12A
14B
13C
10A
5A
2

B

and you create a grid view:

  • dragging in the attribute Label - which automatically becomes a grouping attribute when one or more of the other attributes are aggregated
  • dragging in the attribute Number, and applying the aggregate function Percent
  • setting the rounding on the view attribute Number to 2 decimal places

This will give the view:

Number
Label
48.22A
28.57B
23.21C
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.

For the aggregate functions, Cumulative Sum and Cumulative Percent, see /wiki/spaces/HELP12/pages/9615606618.