Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
I still remember the first time I wanted to work out totals and subtotals with Alteryx. I felt helpless.
It was a bit like having my hands tied behind my back and being asked to press CTRL+ALT+DELETE.
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.
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
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.
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.
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.
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.
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
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.
Example 3: Insert a subtotal for a HARD CODED subcategory
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.
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.
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
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
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.
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.
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:
- 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.
- 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).
C. Running Total
Example 5: Automatically generate a Running Total grouped by rows
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.
It has an option which allows you to segment your total by the value of a column.
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.
Et voila! I hope that now you can build these little additions without even thinking. Enjoy!
