Running Backwards and Forwards: the Running Total Tool

[ChristineB]

Rather than develop complex processes to calculate cumulative totals, the Running Total tool can help you compute these calculations quickly and easily. What’s more is that this tool is not only useful for calculating accumulation in a dataset, but it has also proved effective in calculating running reductions, or withdrawals, in datasets as well.

To provide some context around the use of the Running Total tool, I’ve included a workflow with this article that I’ll refer to throughout this piece. In this particular example, items from a warehouse need to be distributed to different stores. The stock of each item is prioritized at each store. For added color, let’s pretend that the National Hockey League (NHL) is planning on where to stock Stanley Cup paraphernalia. The teams in the face-off? The Chicago Blackhawks and the Carolina Hurricanes (a fan can dream!). The NHL has ten items of swag to stock in 26 different stores across the country. Different items, however, are stocked in different stores. Item 1, Stanley Cup T-Shirts, will be stocked with top priority given to the stores in Chicago and Fayetteville, NC. The stores in Seattle and Houston are of the lowest priority; if there’s not enough stock, then these stores will not receive any T-Shirts. Follow along with the workflow in Alteryx as I run(no pun intended!) through the two uses of the Running Total tool for this example.

Running Forwards

The Running Total tool is probably best known and most used to calculate cumulative sums in a field. Moving row by row, the tool adds the value of a cell to the sum of those above it. This tool can be configured to create running totals for groups or categories of data for one or multiple fields, which is useful for consolidating the number of calculations to include in the workflow. I use these capabilities in the “Running Forward” calculation in the blue box in the workflow. For this particular case of “Running Forward”, or accumulation, I calculate the running totals of each item that a warehouse supplies for two fields: the Total Required for the current shipment and the Total Amount Sold Last Quarter (see blue box in workflow). This type of calculation could be useful for the supplier to compare the differences in stock needs in the current shipment and the previous quarter. This type of information can be analyzed in a variety of ways, but I chose to insert a Multi-Row Formula and Summarize tool to investigate the percent differences per store per item in the number of items to be stocked and how many previously sold.

Running Backwards

While the Running Total tool is an obvious choice for calculating accumulation, I’ve also used it as a component for calculating totals that “run backwards” to answer questions of when demand surpasses the amount of something in supply. Now, you’re probably scratching your head and thinking “Wait, this is the exact opposite of what the Running Total tool does!” Well, you’re right! But, using the Running Total tool in conjunction with a Multi-Row Formula tool provides the perfect reverse engineering scheme to accomplish “running reductions”.

The green box in the attached workflow demonstrates how the running total of the number of each item can be used as a withdrawal from the total amount available of each item. The cumulative totals generated by the Running Total tool are used as a sort of test against the total number of an available item. As an example of this process, look at the data coming out of the Running Total tool for Stanley Cup T-Shirts (Item 1). Does the cumulative total of the number of required T-Shirts exceed the number of T-Shirts in stock? To answer that question for T-Shirts and the rest of the items, I apply that logic with a conditional statement in a Multi-Row Formula. Stores whose required stock needs are not met are revealed after using a Filter tool. Looks like everyone gets the number of T-Shirts they need, but some stores will not receive their required number of Items 6, 7, 8 and 9. If those are the Stanley Cup parkas, my guess is that our friends in Texas and Florida won’t miss them too much, anyway.

So, where can you find this time-saver of a tool? The Running Total tool is located under the Transformation tool group and is best described using @danielkresina’s observation: it’s the tool whose icon has a guy running on it. Configure the tool on your canvas, run the workflow, and celebrate the ease of calculating running totals. Happy Data Blending!

[dataMack]

The perfect tool for Excel junkies that are making the transition to better solutions.