Analysts who are used to building text tables in Excel often get frustrated with Tableau’s crosstab formatting limitations. Learn two ways to make text table formatting more flexible so you can match the financial reports you have around the office.
Hi, this is Ryan with Playfair Data TV. In this video, I’m going to show you two tricks that allow you to align your reports like traditional finance reports. What I mean by that is, it never fails. Whenever I’m working with a finance client, they really want to align the text in different ways. They’ll have a different number of spaces for things like the original metric, subtotals, totals. But Tableau doesn’t allow that flexibility to have those different indentions based on the row number in a dataset. Well, I’m going to show you two ways to get around that, so you can make your Tableau text tables look just like your finance reports.
The first one is a real easy one. Actually, they’re both real easy. But let’s just say that we’ve got a field. You can do this with any dimensions. So any row names that you are using, this technique is going to work. Just so that I can share it and you can follow along, I’m just using the Sample Superstore dataset. And we’ll focus on the Sub-Category dimension. I’m going to double-click on that dimension, which by default, will add it to the Rows Shelf.
And the first trick to creating these different indentions, like on a traditional finance report, is to simply edit the aliases of them. So let’s say, every sub-category that starts with the letter A, we want to have five spaces. To change that, you can simply right-click on each one and click Edit Alias.
And I will bump this up to 1, 2, 3, 4, 5. Click OK. Accessories is now indented five spaces. I’ll do that for Appliances. 1, 2, 3, 4, 5, click OK. I’ll do that for Art. I’m just editing the aliases, bumping them up to five spaces.
If that’s not fast enough for you, there is another place you can edit the aliases, which is by right-clicking directly on the dimension from within the Data pane, and clicking Aliases there. This allows us to, instead of right-clicking on each one individually, we can just highlight it within this interface. Let’s say for the sub-categories that start with B, I’ll bump it up to 10 spaces, so 1, 2, 3, 4, 5, 6, 7, 8, 9, 10.
I’ll do that for Bookcases, as well, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. I must not have gone quite far enough. There we go. We’ll call that good for now. Watch what happens when I click OK; those two sub-categories that start with a B are now indented 10 spaces. The first are indented five spaces.
You might be wondering or thinking to yourself– and that’s bugging me, because I see that I did have that right actually. I thought I might be able to count to 10. So those are lining up pretty good now– five indentions, five spaces for everything that starts with an A, 10 for everything that starts with a B.
So what I was getting to– you might be wondering, well, that was really nice and clever. But if I go to use this sub-category dimension somewhere else, I don’t necessarily always want it to have those indentions. I have a trick for that, as well.
All you have to do is duplicate the original dimension, and the aliases are independent from each other. So here’s the copy version. If I edit those aliases, I can actually clear all the aliases at once by clicking on this button. It’s back to the original, names with no spaces.
If I click OK and add this to the view as a second column, you’ll see that the first column was my dimension with the aliases, those leading spaces. The second is a copy. It has no spaces. So I can use this both ways, with spaces and without spaces.
The second way to do this is to simply put it within your underlying dataset. So take a look at this Excel table. In column A, I’ve got whatever my line items are called. And they have the leading spaces with them. So all I would have to do is then connect to that data source.
That’s an Excel file. It’s called Table with Text Alignment. So you just make your data source have this space in the underlying data. Here’s what it looks like when I’m connecting to it in Tableau Desktop. I could go to a new sheet and drag my Cell Text to Rows, and it will come over with those leading spaces. So two different options for you, but what we just did is we created those custom indentions, like a lot of finance folks like to see it on their reporting.
This has been Ryan with Playfair Data TV – thanks for watching!