In an exceptional situation, I've been asked to produce a fancy table in Tableau - like putting all the subtotals at the end, instead of interspersed, like normal. Crazy, right?
It turns out, this is harder than one might think; probably even impossible if you don't pre-process the data ahead of time before bringing it into Tableau, 'cause apparently Tableau has some pretty stern opinions about how tables should look.
The use case here is to produce a table which has columns of historical weeks of shipments, followed by future weeks of demand, followed by monthly subtotals, some actual and demand quarterly totals, a historical quarterly total, plan totals, and performance to plan and prior quarter. Essentially, it's the same numbers, just summarized across different sized and overlapping segments of time.
In short, midway through that use case, Tableau is like "well, fuck all that" and flips the table and goes home to what I imagine is a pretty awesome mancave to brood about how I just insulted its whole raison d'être.
Enter Alteryx.
In this example, I need to arrange my "Actuals" a few ways:
- first in weekly columns (column numbers 1~13),
- then I show the weekly UCD columns (column numbers 21~33),
- then I display the Monthly Actual Totals (#s 41,42,43),
- then the QTD Actuals (# 44),
- Prior QTD Actuals (#45), QTD / Plan (#51), Prior Attainment(#52) and finally the UCD Qtr Total (#53).
For this I use formula tools after forking the data. I use the same Formula tools to simultaneously code the [Column Labels]. I'll put both the [Column Number] pill in the Column shelf first, followed by the [Column Header] pill and then hide the headers for [Column Number] so that they don't show up in the viz.
Notice how it doesn't really matter that there are gaps in the [Column Number]; All Tableau cares about is the fact that they're all numbers and thus they can be sorted numerically.
I filter the data where necessary, thereby duplicating only the data I want, by forking it and then I union the repeated data back together to allow for subtotals to co-exist in the same table.
Now, there is one important risk to this kind of solution: When I duplicate data, I run the risk of having someone come along, not understanding what's going on, and mis-interpreting the results. In this case, one must invoke either the [Column Header] or [Column Number] dimension to de-dupe the data. While, this isn't unfamiliar territory for any analyst that pulls in multiple snapshots in one table, it is worth mentioning. To alleviate some of this risk, I have a disclaimer in a special warning calculation at the top of the list of dimensions within the Tableau workbook as a courtesy to my fellow analysts that might open the hood on this workbook.