Basic Aggregation on Alteryx

In this blog I'm going to take you through the basic methods for aggregation in Alteryx using the Summarize tool.

To aggregate is to take data from multiple sources and summarize it in some way, whether that be calculating the total or finding out an average.

For this example I've made a very simple data set relating to sales of various items of stationary/electronics/furniture across a number of stores in an imaginary business. It's generally wise to start with a data cleaning step using the select tool in Alteryx just to make sure that all of the fields are using the correct data type before going into the summarize tool.

Pick the select tool from the toolbar and add it to your flow.

Click on the select tool in your flow to open this table on the left hand side of the screen

The most important thing is to ensure that numerical values that will be used for calculations are listed as a numerical type and not a string. A double is a type of numerical value and in this case, all of the numerical fields are connected as doubles. It is now time to open the Summarize tool.

Click and drag the Summarize tool into your workflow, clicking on the logo to open the settings on the left hand side.

The first actions we're going to look at are the basic Sum, Average & Count capabilities of the Summarize tool

The Sum action:

Select a numerical value (in this case a double), click the add dropdown and press Sum

The Sum action adds together all of the values from within the field you have selected, in this case I have selected Total Sale Value which, when summed, will give me the total value of all the sales in the database.

Run the sequence with Ctrl + R and you should see the Sum of values in the display field

If you do the same thing but with the Average you will get the average of all sales but if you select Count you will just see the total number of records within in that field rather than the result of any calculations.

The next interesting action to look at is the Group By action which, when used in conjunction with Sum, Avg & Count tools can separate the results of those calculations into sub-groups from the data set.

For example:

Add Store Location with the group by action and total sale value with the Sum action selected

If you use the Group By action in conjunction with the Sum action and run the sequence you should see a table showing the total sales broken down into their respective store locations.

Here you can see the Total Value field broken down by Store Location

The Summarize tool has far more actions beyond this but these are just the most common, basic actions you're most likely to encounter when using Alteryx.

I hope this blog was helpful!

Author:

Ross Killington

View Profile