Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Tool Mastery

Explore a diverse compilation of articles that take an in-depth look at Designer tools.
Become a Tool Master

Learn how you can share your expertise with the Community

LEARN MORE

Tool Mastery | Running Total

Kenda
16 - Nebula
16 - Nebula
Created
Running Total.png

Thisarticle is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Running Total Tool on our way to mastering the Alteryx Designer:

Trying to convert all of your old, mundane Excel workbooks into Alteryx workflows? The Running Total Tool could be the key to your success! You know, it’s that tool in the Transform category with the little running man picture on it.

Media1.gif

Configuring the Running Total tool is very simple. There are two sections you will see in the Configuration window.

  • The Group By section is optional. All of your fields will appear here. Selecting any fields here allows you to create sums of groups of records. We will see an example of this later on that will make more sense.
  • The Create Running Total section is mandatory: you must select at least one of the checkboxes. Only numeric fields will appear as options. Any field you select here will have its rows summed.

1.png

Follow along in the attached 2018.1 workflow, Running Total Tool Mastery.yxzp, to get a better understanding of how this tool works. Below I will talk about examples from the workflow from left to right.

The Running Total tool calculates a cumulative sum on a numeric field per record in a file.

This tool will easily sum up each consecutive row based on the field(s) selected.

In the spirit of the 2018 World Cup, the first example contains final score data from each of the historical World Cup games.

Configuring the Running Total tool is quite intuitive. Say first we want to see how many total goals have been scored in all of the World Cup games throughout time. Simply select the ‘Total Score’ field in the configuration under the Create Running Total section and voilà!

2.png

3.png

Alteryx will add the total points scored this World Cup with the total points scored in all the previous World Cups to create a running total.

If you add a Filter tool before or after the Running Total tool and filter on Year, you could easily see how many goals were scored by the end of the 20th century, for example.

Note: Alteryx will create a new field for the running total. It will have the same name as the field that you are summing except with the prefix ‘RunTot_’ added to the beginning.


If you want to see sums for multiple fields, you still only need one tool. In this case, merely select each of the fields that you want added in the configuration.

Here, we can see the total goals scored historically and the total amount of people who have attended a World Cup by each point in time.

4.png

5.png

Now, how does that optional Group By configuration work? The Running Total tool enables you to create sums grouped by string fields. This means, for each unique value of [Field 1], the running total field will start over the sum. As @BenMoss put it in this discussion post, “Essentially you should read this as ‘provide me a running total for each group within these selected fields.’” Let’s look at an example.

The following examples use a sample dataset acquired from https://www.ibm.com/communities/analytics/watson-analytics-blog/sales-products-sample-data/.

In this example, we want to see the quantity of products sold by country by quarter. The tool configuration would look like this:

6.png

Retailer country string field is selected here because we want to see the cumulative sum for each individual country. Additionally, the Quarter field is selected to Group By so that we can see the totals for each of the 4 quarters separately each year for each country.

7.png

8.png

As you can see, the summation started at the beginning of Australia on row one then started over once it got to a new quarter (row 338 here). If you’re interested in seeing the percent change in quantity sold from quarter to quarter by country, the attached workflow adds a Summarize and Multi-Row Formula tool to quickly make this calculation.

You might be interested in quarterly summaries only, not transactional data. In this instance, a Summarize tool can be used just before the Running Total tool.

9.png

If a Summarize tool is used first, you still want to Group By your desired fields in the Running Total tool in order to get the correct totals. Otherwise, the tool will still sum every individual line until it reaches the last record. In this final example, Retailer type, Product line, and Year are being grouped and Revenue and Quantity are both being summed.

For even more information on this tool, check out the awesome article by @ChristineB called ‘Running Backwards and Forwards: the Running Total Tool.’ In here, she even dives into how you can run backwards (calculate reductions/withdrawals) with this tool.

For a more interactive approach to continue learning about the Running Total tool, I suggest you check out this live training video. It provides some really great insight as to why someone might want to use this tool, explains all of the details on it, and walks through an example.

By now, you should have expert-level proficiency with the Running Total Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.

Stay tuned with our latest posts everyTool Tuesdayby followingAlteryxon Twitter! If you want to master all the Designer tools, considersubscribingfor email notifications.

Attachments