Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
StephaneP
Alteryx
Alteryx

I still remember the first time I wanted to work out totals and subtotals with Alteryx. I felt helpless.

 

StephaneP_0-1647878971226.png

 

It was a bit like having my hands tied behind my back and being asked to press CTRL+ALT+DELETE.

 

StephaneP_1-1647878971238.jpeg

 

It's a completely different approach to what you would do in Excel, which explains the minor discomfort at the start. Once mastered however, it can be much more dynamic and maintainable over time. Especially since with the Table tool you can also format the results dynamically.

 

To save you time, here is an overview of the main techniques for creating the different types of vertical or horizontal, totals or subtotals.

 

StephaneP_2-1647878971881.png

 

Remember, there is never only one way to do something in Alteryx. Pick ideas from these examples and depending on the level of dynamism or robustness create the most suitable solution for you. Except for simple cases, the general principle is to separately calculate the totals or subtotals and then recombine them in the right order. Dealing with this recombination order is the main problem to manage. Note that in each example I have placed a Table tool at the end of the chain, to show you examples of static or dynamic formatting. It is important to realize what you can do and that it is not as complicated as you may have thought. You just need to know how!

 

All the examples presented here can be found in the workflow at the end of the article.

 

A. Total column per row

 

Example 1: Inserting a Total by Row column

StephaneP_3-1647878972056.png

 

This can be done in a hard coded way via the formula tool, or in a dynamic way via a very practical combination of tools that are used in several different use cases: Transpose + Cross Tab.

 

StephaneP_0-1647883798073.png

 

We can exploit the options available under Method for Aggregating Values at the bottom of the Cross Tab menu and a little-known trick is that it allows you to automatically add sums for rows or columns.

 

StephaneP_5-1647878972111.png

 

Note that this technique adds prefixes (Sum_ Count_... depending on the option). Hence the presence of the Dynamic rename tool, which allows you to dynamically remove this prefix.

 

StephaneP_6-1647878972128.png

 

The advantage of this combination is that it does not depend on the number of columns in the input if the option Dynamic or unknown columns is selected in the 1st  Transpose tool.

 

StephaneP_7-1647878972144.png

 

If tomorrow a new column appears and needs to be summed, it will be summed by this method.

 

B. Total Line by Column

 

Example 2: Insert a row of Total per column

 

StephaneP_8-1647878972317.png

 

Here again is a static version (we aggregate separately and then consolidate the data and totals) and the same dynamic, combined technique, is used as in the previous example.

 

StephaneP_9-1647878972351.png

 

Example 3: Insert a subtotal for a HARD CODED subcategory

 

StephaneP_10-1647878972529.png

 

The idea is to insert a subtotal based on a predetermined hard coded combination of values. It is therefore necessary to manage the list of values (here treated as a list in the filter), to sum the different components and then to order the results correctly.

 

StephaneP_11-1647878972563.png

 

Note the option in the Union tool which allows you to manage the order of vertical consolidation of the different inputs and allows you to correctly recombine your different components.

 

StephaneP_12-1647878972578.png

 

Note: By default, the Union tool sorts the entries alphabetically. With this tip you can manage the order manually, by renaming the entry input links. When you create the connection, Alteryx automatically creates input links labelled #1, #2 etc. #1 will be the 1st block then #2 and so on. You can rename them by clicking on the entry input link and thus visually manage the order of combination in the workflow.

 

Example 4: Insert dynamic subtotals based on the values of a particular column

 

StephaneP_13-1647878972701.png

 

A great classic is to generate a subtotal based on a category/characteristic present in a column. Here we want to generate a grouping based on the Dept (Mkt or Prod) column, and of course it is dynamic! We also want to generate the Grand Total.

 

To achieve this, we will:

  • Take a photo of the order of the detail lines
  • Generate 1 branch per aggregation level with the order of the new subtotals
  • Add, via a formula, the missing columns or values and add a value to them (e.g.: for the Grand Total we don't have a value for the Dept or Sub Dept). This allows you to have homogeneous table structures that you can easily consolidate
  • Manage the data bloc order based on the order of the initial rows, adding subtotals in the right places

 

image015.png

 

This method works no matter how many rows per Department or how many Departments are present in your data.

 

The fact of "taking a photo" of the order of the rows avoids sorting problems if you were to use only the Dept/Sub Dept labels. Adding "Total" at the end is not always relevant.

 

Example of a problem: If I had 2 Departments labeled "MKT" and "MKT T2," after sorting in alphabetical order the sub-totals would be placed after all the detail lines. The display would be wrong.

 

Picture1.png

 

Note that the formatting can also be made dynamic if you normalize the names used. Here for example, it is the lines which contain "Total" in the 5 characters on the right which carry a conditional colour coding. I also made special cases of colour by department to better identify them, but it is not obligatory if you want it to remain dynamic.

 

Example 4 BIS: Insert dynamic subtotals based on the values in a particular column. Another type of formatting.

 

StephaneP_15-1647878972903.png

 

Same idea as before, we want to automate the generation of subtotals and totals based on the values of a column, here Category (Private or Public). There are 2 nuances compared to the previous example:

  1. The order here is formalized via a created column that carries this information. This makes sense when you start to have many levels, so as not to get lost and to be able to put everything back together when you’re done. There is therefore a dedicated step per level of aggregation. In the previous example this was managed by a manual combination or Union to get the right order, but this tends to be less maintainable at large scale.
  2. The output formatting is also different, to show you other tricks (for example how to make text disappear by switching the colour of a text to match it with the background colour).

 

StephaneP_16-1647878972952.png

C. Running Total

 

Example 5: Automatically generate a Running Total grouped by rows

 

StephaneP_17-1647878973148.png

 

There is a slight difference here as we are going to generate running totals. We want to have 2 of them: one that is a global accumulation, and the other that is an accumulation within each Department. We will use a tool built for this function, the: Running Total.

 

StephaneP_18-1647878973160.png

 

It has an option which allows you to segment your total by the value of a column.

 

StephaneP_19-1647878973171.png

 

Note here that to highlight the subtotals, the formatting is mainly managed via the column formatting rules. Several table, row and column formatting rules are stacked. You must learn to master the order of priorities in the table tool.

 

Note on the Running total by column: In Alteryx to make a running total by column you must first switch the columns to rows via a Transpose, apply the Running Total, then switch back to columns via a Cross Tab.

 

D. Final note on formatting

 

If you want to format your final tables, the Table tool isn’t the only option available. You can output:

  • into already formatted Excel tables (the equivalent copy/pasting values)
  • into a data sheet and then build tables and charts from this in Excel
  • to Dataviz tools (Tableau, PowerBI, Qlik… with the Alteryx connectors)

 

The main strength of the Table tool is to generate this same report en masse by applying it to different business areas or different output formats (pdf, xls, mail, ppt, word, html...) and to manage dynamic formatting.

 

Most of the time the formatting can be added after the construction of the data table, but for advanced dynamic formatting it is sometimes necessary to add in advance technical columns or rows containing parameters. These parameters can be used by the conditional formatting to know where to apply certain logic (e.g., level in the hierarchy, anomaly on the row, periodic or accumulation column...). You can have these values available to use in the table tool but not display them in the output.

 

StephaneP_20-1647878973353.png

 

Et voila! I hope that now you can build these little additions without even thinking. Enjoy!

 

Check Challenge #324: Total and Subtotals

Comments
IraWatt
17 - Castor
17 - Castor

Amazing article @StephaneP, there is questions on the community about this all the time. I'll need to start linking to this blog now. 

marca_at_cue
7 - Meteor

Extremely helpful!   Thank you!

gregx
8 - Asteroid

isn't it a joke that subtotals are in the challenge here? :D

gregx
8 - Asteroid

Definitely WRONG approach @StephaneP . You can't just add 'total' word and then sort ascending (or desc, it doesn't matter).

It worked only for your perfectly prepared example, with perfectly different categories (MKT and Prod)

Do the same with the following categories: MKT, MKT T2

after sorting you will get:

MKT
MKT T2
MKT T2 Total
MKT Total

 

your approach is not correct Stephane Portier 2022 :D

 

StephV
Alteryx Alumni (Retired)

Hi @gregx,

 

Even though we value your contributions and your feedback, our Alteryx community members deserve to feel comfortable here. We therefore encourage you to be careful about the tone of your messages. It is important to maintain a level of respect for all members.

 

You can review our Community Guidelines here.:"Respect people's time and attention by asking well-thought-out questions and sharing what you've learned so far in your experimentation or prior research. Offer honest help, keep your tone positive, your comments constructive. "

 

Thanks for your contribution, and for your help in keeping the community a friendly, positive, and productive environment for all members. 

gregx
8 - Asteroid

@StephV Would you be as kind as to explain what you are referring to? I pointed out the solution is wrong and it's a misleading information that affects the community.

Bonediggler
9 - Comet

Awesome article!  Helped a lot.

Tam
9 - Comet

@StephaneP  Great job. 

StephaneP
Alteryx
Alteryx

Hello everyone,

For your information, Example 4: Insert dynamic subtotals based on the values of a particular column part have been updated January 16th, 2024, to take into account case mentioned by gregx in this feed. A photo of the line order is taken before adding the subtotal, so you know where to add it whatever is the particular alphabetical letters order.

Article and workflow have been updated.

 

Enjoy

Conny_Rabe
5 - Atom

@StephaneP  Can you share the updated workflow in this article? I'm looking exactly for the subtotals that don't sort so nicely based on alphabetical order ;)

StephaneP
Alteryx
Alteryx

Ouuups, you are right @Conny_Rabe ,

 

My bad. Apologies for it.
I ask the community to update it. It should be done quickly.
In the meantime you can have it from the French article. Comments are in French but same wokrflow. Here: Comment générer et formater des totaux et cumuls

 

Have a good day