Summarize tool icon

Summarize Tool

Last modified: November 08, 2021

Docs are available before the release of Designer Cloud so you can get a sneak peek. This content might change between now and the official release.

One Tool Example

Summarize has a One Tool Example. Go to Sample Workflows to learn how to access this and many other examples directly in Alteryx Designer Cloud.

Use Summarize to perform various operations (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.

Go to Summary Operations for a complete list.

Tool Components

Thumbnail

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: Select Columns and Operations.

Select Columns

The names of the data columns from the input, and their associated data types, are listed in the Select Columns section of the configuration window.

  1. Select a column to perform an operation. Use Shift + select to choose multiple columns to execute the same operations on. Use Select by Data Type to make bulk selections. Choices include...
    • All: Select all columns.
    • None: Select no columns. Any previously selected fields are deselected.
    • Numeric: Select only columns with a numeric data type (integers, fixed decimals, floats, doubles.)
    • String: Select only columns with a string data type.
    • Spatial: Select only columns with a spatial data type.
      Thumbnail
  2. With your columns selected, select Add Operations. You can choose from several operations. Keep in mind that operations are not available if they are not compatible with the selected data types.
  3. Select the operations you want to perform. Your operations are added to the Operations

You can perform multiple operations on a single column. To do that, select and add the column once for each operation that you want to perform.

Thumbnail

Operations

The Operations section contains data columns added from the Select Columns section. Several options are available once you've added columns to the Operations section:

  • Reorder: Select and hold the reorder icon and drag the operation to where you want it in the list, or select the column and use the up or down arrow icons to change the order of the columns. This also updates the column order of the tool output in the Results window.
  • Delete: Select a column and use the trash-can icon to delete the operation.
  • Change operation: To change the operation, select the dropdown in the Operations column and choose a compatible operation.
  • Rename output column header: To rename an output column, select the column and then input a new name into Output Column Name.

Operation Parameters

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

  • When additional parameters are required, specify these via the Parameters section at the bottom of the tool configuration window. Go to Summary Operations for information on specific parameters.
  • If additional parameters are not required, the Parameters section isn't available.

Summary Operations

This list describes the types of operations that the Summarize tool can perform:

Summary Operations
  • Group By: Combine database rows with identical values in a specified column into a single row. All of the resulting data from the rows in a group are then summarized. Any non-blob or spatial object has this option. If no Group By column is specified, the entire file is summarized.
  • Sum: Return the sum value for the group. The sum is calculated by adding all of the values of a group.
  • Count: Return the count of rows in the group.
  • Count Non Null: This operation is the same as Count, except it is only counting those rows that are not null. Null means there is no value set for the row. That is different from a 0 or an empty string.
  • Count Distinct: Return the count of unique rows in the group.
  • Count Distinct Non Null: This operation is the same as Count Distinct, except it is only counting those rows that are not null. Null means there is no value set for this row. That is different from a 0 or an empty string.
  • Count Null: This operation is the same as Count, except it only counts those rows that are null. Null means there is no value set for the row. That is different from a 0 or an empty string.
  • Min: Return the minimum value.
  • Max: Return the maximum value.
  • First: Return the 1st row in the group, based on its row position.
  • Last: Return the last row in the group, based on its row position.
Finance
  • Net Present Value (NPV): Calculate the net present value of an investment. NPV is a measure of future cash flow over the life of an investment. NPV Parameters:
    • Discount Rate (Per Period): The discount rate as a percentage. The default value is 8%.
  • Net Present Value w/ Dates (XNPV): Calculate the Net Present Value for an investment with dates. XNPV Parameters:
    • Finance Rate: The finance rate as a percentage. The default value is 8%.
    • Date Column: The column that contains the associated dates.
  • Internal Rate of Return (IRR): Calculate the internal rate of return for an investment. IRR is the expected annual compound rate of return that an investment will earn.
  • Internal Rate of Return w/ Dates (XIRR): Calculate the internal rate of return of an investment with dates. XIRR Parameters:
    • Date Column: The column that contains the associated dates.
  • Modified Internal Rate of Return (MIRR): Calculate the modified internal rate of return of an investment. This is a modification of IRR and as such aims to resolve some problems with the IRR. MIRR Parameters:

    • 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): Calculate the modified internal rate of return for an investment with dates. MXIRR Parameters:

    • 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 Column: The column that contains the associated dates.
Numeric
  • Average: Calculate an average value for the group. The average is calculated by taking the sum of all values and then dividing by the total number of values.
  • Percentile: Calculate the specified percentile value for the group. The percentile is calculated by sorting the data and then 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: Calculate 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 2 numbers in the middle are averaged.
  • Mode: Calculate 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 is returned.
  • Standard Deviation: Calculate the standard deviation for the group. Standard deviation is a measurement variability used in statistics.
  • Variance: Calculate the Variance for the group. The variance is calculated by taking the standard deviation and multiplying it times itself (StdDev2).
  • Ignore 0's: Calculate the numeric processes described above ignoring rows that have a value of 0.
String
  • Count Blank: Count how many rows in a group have a blank or empty value.
  • Count Non Blank: Count how many rows in a group do not have a blank or empty value.
  • Concatenate: Take all the rows in a group and concatenate the strings. Concatenate Properties include:
    • Start: The character specified appears at the beginning of the concatenated string. It is left blank by default.
    • Separator: The character specified appears in between each value of the concatenated string. The default is a comma.
    • End: The character specified appears at the end of the concatenated string. It is left blank by default.
Spatial
  • Combine: Combine the area of all the spatial objects in a group.
    Thumbnail
  • Create Intersection: Identify all of the overlapping areas of a group's spatial objects and create a polygon that consists only of that overlap. That means, if the group being summarized contains two polygons that overlap and one that does not, nothing is returned.
    Thumbnail
  • Create Bounding Rectangle: Identify the geographic extent of all spatial objects in a group and draw a bounding rectangle to those extents.
    Thumbnail
  • Create Convex Hull: Identify 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 doesn't contain any concave angles, so it never turns in on itself.
    Thumbnail
  • Create Centroid: Identify the geographic center of a group of spatial objects.
    Thumbnail

Tool Examples

Example 1. Simple Sum

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

  1. In Select Columns, select the column that you want to sum.
    Image showing the Summarize Select Columns section with the column to sum highlighted.
  2. Using the Add Operation dropdown, select the Sum operation to add the selected column to the Operations table.
    Image showing the Operations table with an action added.
  3. Run your workflow to see the sum of the selected column in the Results window.

Example 2. Group By

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

  1. In Select Columns, select the column that you want to group by.
    Image showing the Summarize Select Columns section with the column to group by highlighted.
  2. Using the Add Operation dropdown, select the Group By operation to add the selected column to the Operations table.
  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.
Was This Page Helpful?

Running into problems or issues with your Alteryx product? Visit the Alteryx Community or contact support. Can't submit this form? Email us.