Orange polygon containing white sigma symbol.

Summarize Tool

Version:
Current
Last modified: March 17, 2020

Use Summarize to perform various Actions (functions and calculations) on your data. The Summarize tool can...

  • Return the sum for a column of data. The sum is calculated by adding all of the rows in the column.
  • Return the minimum or maximum value in a column.
  • Count the number of rows in a column.
  • Group a column of data by identical values.
  • Concatenate string values.
  • Perform a variety of mathematical calculations.
  • Perform spatial object processing.

See Summary Actions for a complete list.

Tool Components

Screenshot of the Summarize tool on the Designer canvas

The Summarize tool has 2 anchors: 

  • Input anchor: The input anchor connects to the data you want to perform actions on.
  • Output anchor: The output anchor displays only the results of your actions. Use the Join Tool to join the results of a Summarize tool with the original data.

Configure the Tool

The Summarize tool Configuration window has 2 sections: Fields and Actions.

Fields

The names of the data fields from the input, and their associated Data Types, are listed in the Fields section of the tool configuration window.

  1. Select a field to perform an action. Shift + select to choose multiple fields to execute the same actions. Use the Select dropdown above the Fields section to make bulk field selections. Choices include...
    • All: All fields are selected.
    • None: No fields are selected. Any previously selected fields are deselected.
    • Numeric: Only fields with a numeric data type are selected (integers, fixed decimals, floats, doubles).
    • String: Only fields with a string data type are selected.
    • Spatial: Only fields with a spatial data type are selected.
  2. With your fields selected, select the Add dropdown above the Actions section. You will see various actions to choose from. Actions are not available if they are not compatible with the selected Data Types.
  3. Select the action that you want to perform. Your fields will be added to the Actions section.

You can perform multiple actions on a single field. To do so, select and add the field once per each action that you want to perform.

Image showing the Actions section of the Summarize Tool with a single field added multiple times

Actions

The Actions section contains data fields added from the Fields section. Several options are available once fields have been added to the Actions section: 

  • Reorder: Select a field and use the up or down arrow buttons to change the order of the fields. This will also update the field order of the tool output in the Results window.
  • Remove: Select a field and use the remove button to remove the field from the Actions section.
  • Change Action: To change the action, select the dropdown in the Action column and choose a compatible action.
  • Rename Output Field: To rename a field, first select the field and then input a new name into the Output Field Name column.

action Properties

Percentile, Concatenate, and most Finance actions require you to specify additional properties.

  • When additional properties are required, specify these via the Properties section at the bottom of the tool Configuration window. See Summary Actions for information on specific properties.
  • If additional properties are not required, the Properties section will not be available.

Summary Actions

The following list describes the types of summary actions that the Summarize tool can perform:

Summary Actions

Group By: Combines database records with identical values in a specified field into a single record. All of the resulting data from the records in a group are then summarized. Any non-blob or spatial object has this option. If no Group by field is specified, the entire file will be summarized.

Sum: Returns the sum value for the group. The sum is calculated by adding all of the values of a group.

Count: Returns the count of records in the group.

Count Non Null: Identical to Count, except it is only counting those records that are not null. Null means there is no value set for the record. This is different than a zero or an empty string.

Count Distinct: Returns the count of unique records in the group.

Count Distinct Non Null: Identical to Count Distinct, except it is only counting those records that are not null. Null means there is no value set for this record (different than a zero or an empty string).

Min: Returns the minimum value.

Max: Returns the maximum value.

First: Returns the first record in the group, based upon its record position.

Last: Returns the last record in the group, based upon its record position.

Finance

Net Present Value (NPV): Calculates the Net Present Value for a group. Measures the excess or shortfall of cash flows, in present value terms, once financing charges are met. NPV Properties include:

  • Discount Rate (Per Period): The discount rate as a percentage. The default value is 8%.

Net Present Value w/ Dates (XNPV): Calculates the Net Present Value for a group for a series of dates. XNPV Properties include:

  • Finance Rate: The finance rate as a percentage. The default value is 8%.
  • Date Field: The field containing the associated dates.

Internal Rate of Return (IRR): Calculates the Internal Rate of Return for a group. The Internal Rate of Return of an investment is the interest rate at which the costs of the investment lead to the benefits of the investment. This means that all gains from the investment are inherent to the time value of money and that the investment has a zero net present value at this interest rate.

Internal Rate of Return w/ Dates (XIRR): Calculates the Internal Rate of Return for a group for a series of dates. XIRR Properties include:

  • Date Field: The field containing the associated dates.

Modified Internal Rate of Return (MIRR): A modification of the internal rate of return and as such aims to resolve some problems with the IRR. The MIRR is a financial measure of an investment's attractiveness. MIRR Properties include:

  • Finance Rate: The finance rate as a percentage. The default value is 8%.
  • Reinvest Rate: The reinvestment rate as a percentage. The default value is 8%.

Modified Internal Rate of Return w/ Dates (MXIRR): Calculates the Modified Internal Rate of Return for a group for a series of dates. MXIRR Properties include:

  • Finance Rate: The finance rate as a percentage. The default value is 8%.
  • Reinvest Rate: The reinvestment rate as a percentage. The default value is 8%.
  • Date Field: The field containing the associated dates.

Finance definitions are sourced from Wikipedia.

Numeric

Average: Calculates an average value for the group. The average is calculated by taking the sum of all values divided by the total number of values.

Percentile: Calculates the specified percentile value for the group. The percentile is calculated by sorting the data and returning the row value relative to the specified percentile and its position in the sorted array - the largest value is the 100th percentile, lowest value is the 0 percentile, median is the 50th percentile, the 25th percentile is the value in the middle of the median and minimum, etc. Percentile properties include:

  • Specify the percentile to return. The default value is 50%.

Median: Calculates the median value for a group. The median of the group is the middle value when the values are sorted in order. If there is an even number of values there is no number in the middle so the two numbers in the middle are averaged.

Mode: Calculates the mode value for a group. The mode of a set of numbers is the smallest number that occurs most often in a group of values. If all values are unique, the smallest number will be returned.

Standard Deviation: Calculates the standard deviation for the group. Standard Deviation is a measurement variability used in statistics.

Variance: Calculates the Variance for the group. The variance is calculated by taking the Standard Deviation and multiplying it times itself (StdDev2).

Ignore 0's: Calculates the Numeric processes described above ignoring records that have a value of zero.

String

Count Blank: Counts how many records in a group have a blank or empty value.

Count Non Blank: Counts how many records in a group do not have a blank or empty value.

Concatenate: Takes all the records in a group and concatenates the strings. Concatenate Properties include:

  • Start: The character specified will appear at the beginning of the concatenated string. It is left blank by default.

  • Separator: The character specified will appear in between each value of the concatenated string. The default is a comma.

  • End: The character specified will appear at the end of the concatenated string. It is left blank by default.

The start, separator, and end elements must be specified for each field that an action is being applied to. You can enter any character or string, or leave them blank. Supported escape characters include: \n (new line), \t (tab), \r (carriage return), and \s (whitespace character).

Longest: Returns the longest string value of the group.

Mode: Returns the mode of the string values. The mode of a set of string values is the smallest string that occurs most often in a group of values. If all values are unique, the smallest string is returned. The smallest string is the first in ascending sort order.

Shortest: Returns the shortest string value of the group.

Spatial

Combine: Combines the area of all the spatial objects in a group.

Create Intersection: Identifies all of the overlapping areas of a group's spatial objects and creates a polygon that consists only of that overlap. This means that if the group being summarized contains two polygons that overlap and one that does not, nothing will be returned.

 

Create Bounding Rectangle: Identifies the geographic extent of all spatial objects in a group and draws a bounding rectangle to those extents.

Create Convex Hull: Identifies the Convex Hull of a group of points. The Convex Hull polygon is the smallest convex polygon that can be drawn to include a group of points. The polygon will not contain any concave angles, so it will never turn in on itself.

Create Centroid: Identifies the geographic center of a group of spatial objects.

Behavior Analysis

Combine Profiles: Combines all the Profiles in a group into a single Profile per group specified.

Report

Combine Horizontal: Combines all the Report Snippets in a group into a single snippet for the group laid out horizontally.

Combine Vertical: Combines all the Report Snippets in a group into a single snippet for the group laid out vertically.

Tool Examples

Example 1. Simple Sum

Use the Summarize tool to sum the values in a field or column of data. The sum is calculated by adding all of the rows in the column.

  1. In the Fields section, select the column that you want to sum.
    Image showing the Fields section of the Summarize tool Configuration window, with the Spend field selected.
  2. Select the Add dropdown and select the Sum action to add the previously selected column to the Actions section.
    Image showing the Actions section of the Summarize Tool with the Spend filed added.
  3. Run your workflow to see the sum of the selected column in the Results window.
    Image showing the Results window of the Summarize tool with the Sum of the Spend field

Example 2. Group By

Use the Summarize tool to return each unique value in the specified column (field).

  1. In the Fields section, select the column that you want to group by.
    Image showing the Fields section of the Summarize tool Configuration window with the City field selected.
  2. Select the Add dropdown and select the Group By action to add the previously selected column to the Actions section.
    Image showing the Actions section of the Summarize Tool with the City field added
  3. Run your workflow to see each unique value in the selected column in the Results window. Note that the Summarize tool is case sensitive, so ARVADA and Arvada appear as separate values.
    Image showing the Results window of the Summarize tool with each unique value found in the City field of the data input

Additional Examples

To see additional examples of the Summarize tool, open Designer and in the Tool Palette, select Transform > Summarize. You will see a pop-up message that provides additional information about the Summarize tool. Select Open Example to open a sample workflow that showcases several uses of the Summarize tool. Run the workflow and select a tool to view its output in the Results Window.

Was This Helpful?

Running into problems or issues with your Alteryx product? Visit the Alteryx Community or contact support.