Ads Top

10 Obscure Tableau Tips

My brother recently posted a blog sharing Ten Tips Plus My Favorite Tip of All Time. If you haven’t read it, then you should definitely check it out. Additionally, others have written great blogs and given presentations sharing a variety of tips, including Jeffrey Shaffer, Andy Kriebel, Ann Jackson, Lorna Brown, and many others. If you haven’t watched the Jeff vs Andy or Ann vs Lorna tip battles from Tableau Conference, then be sure to watch them—I promise you’ll be glad you did.

Considering so many people have provided such great tips and tricks, I thought I’d take a slightly different approach to this tips blog. Instead, I’m going to share with you some of my favorite, yet somewhat obscure tips. Most of these are tips that you might not need every day, but are incredibly handy when do you do need them. And, for good measure, I’ve thrown in a couple everyday-use tips that I use all the time.

1. Hiding Marks
This is a trick I just recently learned from Wendy Shijia when she wrote a guest blog on Drawing Curves on a Map. Let's start with the following line chart.


In this example, you can see that I've created a dual axis to highlight the minimum and max values. To do this, I've first created a calculated field that leverages LODs to get the min and max values.

High/Low

// Is this month's total sales the overall min, max, or neither?

IF SUM([Sales]) = MIN({FIXED : MIN({FIXED DATETRUNC('month', [Order Date]): SUM([Sales])})}) THEN

    "Min"

ELSEIF SUM([Sales]) = MAX({FIXED : MAX({FIXED DATETRUNC('month', [Order Date]): SUM([Sales])})}) THEN

    "Max"

ELSE

    "Neither"

END


Note: This is use case # 5 on my blog, 20 Uses for Level-of-Detail Calculations, so feel free to check out the blog if you'd like more details on how exactly this works.

I then create a calculated field like this which I used on the second axis.

High/Low Sales

// Sales for only the high and low points.

IF [High/Low] <> "Neither" THEN

    SUM([Sales])

END


Because this measure only has values for the min and max, it only shows marks for those two. This works great, but there is a handy alternative that requires fewer steps.

Instead of the calculated measure, we can just use the Sales measure (same as the first axis). Then we drag High/Low to the color card. After changing to a dual axis and synchronizing the axes, we get this:


We then simply right-click on the "Neither" item on the color legend and choose "Hide". This will hide those marks, leaving only the min and max.


While the original solution was pretty easy in this case, this trick can be quite powerful, particularly in situations where there are complex calculations, such as those used in Wendy's blog.

Note # 1: If you don't want to color using this field, you can drag it to the color card, hide the item, then drag the pill to the detail card. The item will remain hidden, but the field will no longer control the color.

Note # 2: To bring back these hidden marks, click the Analysis menu, then choose Reveal Hidden Data.

2. Resize a Dashboard without Resizing the Objects
It can be very frustrating to spend hours on a dashboard only to run out of space. Of course, you can always resize the dashboard, but that will also resize and reposition all of the objects. This is particularly problematic when you’re using floating objects. Fortunately, Paul Cawford has built a tool to help with this. Paul created a VB Script file that allows you to add height or width to a Tableau workbook without changing the sizes or position of any of the objects. This tool has been a lifesaver for me a number of times, so I want to make sure everyone knows about it. You can find more details as well as the VB Script files on the Paul’s forums post, Resize a Tableau Dashboard without Moving Objects.

3. Format a Field in a Tooltip or Label
OK, now for one of those everyday-use tips. When editing a label or tooltip text, you often find yourself changing formatting. With text, you need to select the entire string before formatting or else you’ll only format part of the text.


But, with fields, you can save a couple of clicks. Instead of selecting the entire text, you can simply click anywhere in the name of the field. This will create a sort of highlight on the entire field:


From here, you can change formatting as desired and those formatting changes will apply to the entire field, even though you haven’t selected all of the text.

4. Mute Your Background Image
I generally recommend that you avoid using background images in your visualization. However, when used well, they can be incredible, such as this visualization by Judit Bekker.


We actually hosted Judit for a guest blog post recently, 10 Design Tips & Tricks for Better Dataviz Storytelling (If you haven’t read it, stop what you’re doing and read it right now—it’s an incredible resource for helping you improve your design skills using relatively simple methods.) In this blog, she mentioned how she darkened the background a bit to make the text more readable.

This is what I call muting the background. If your text is dark, you can lighten the background and, if your text is light, you can darken the background. There are a couple of ways to do this. The most obvious would be to edit the image with image editing software such as Photoshop, but you can do this right within Tableau. Start by placing your image object on a dashboard.


Then float a blank over top of the image (a container will work as well). Use the Layout pane to set the blank’s left and top positions to 0. Then set the width and height to the same as the dashboard. Then, also using the layout pane, change the background to either white or black (white to lighten it and black to darken it). Then change the opacity. For example, here’s the image above using black and white with 70% opacity.



Using this method, you can easily tune the level of opacity, without the need to edit the actual image. 

5. Make a Date when MAKEDATE Is Not Supported
Here’s another less-obscure one. When using a live connection, there are a number of data sources that do not support MAKEDATE or MAKEDATETIME. So, here’s an alternative for such situations. Let’s say that you have three fields containing Month, Day, and Year.


To create a date without using MAKEDATE, we can simply format it as a string, then convert to a date.

DATE(STR([Month]) + '/' + STR([Day]) + '/' + STR([Year]))

Note: Be sure to format your date according to your locale. For example, if your system expects dates in the format D/M/YYYY, then create the string in that format, rather than what I’ve shown above.

If you need a date/time, then your calculated field would be something like this:

DATETIME(STR([Month]) + '/' + STR([Day]) + '/' + STR([Year]) + ' ' + STR([Hour]) + ':' + STR([Minute]))

6. Copy and Paste Calculated Fields
Do you know the difference between duplicating a calculated field and copying & pasting it? Did you think they are the same? They are actually slightly different and knowing the difference can be a big timesaver. For example, take these two calculated fields.

Quantity x 2
// Multiply Quantity by 2
[Quantity]*2

Quantity x 2 x 2
// Multiply [Quantity x 2] by 2.
[Quantity x 2]*2

If we duplicate these, we get the following:

Quantity x 2 (Copy)
// Multiply Quantity by 2
[Quantity]*2

Quantity x 2 x 2 (Copy)
// Multiply [Quantity x 2] by 2.
[Quantity x 2]*2

Notice that the formulas are exactly the same. But, if you copy the calculated fields then paste them, you’ll get this:

Quantity x 2 (1)
// Multiply Quantity by 2
[Quantity]*2

Quantity x 2 x 2 (1)
// Multiply [Quantity x 2] by 2.
[Quantity x 2 (1)]*2

The first stays the same, but Tableau recognizes that you also copied and pasted the field referenced in the second calculation and automatically updates it to reference the new field. While this seems like a minor tip, it can be quite powerful. Often, particularly when performing very complicated calculations, you’ll end up with a set of calculated fields that all relate to each other. If you need a new set of these and you duplicate them, you then have to go back and update each calculation to reference the new copy of previous calcs, which can be a painful process. I’ve run into this quite a bit when building sankeys and the copy/paste technique proves to be a big help.

7. Use Table Calcs to Trick the Order of Operation
This is something I've shared on other blogs, but it has so many use cases that I'm going to share it again. I learned this trick from the great Pooja Gandhi and it’s one of my all-time favorite tricks. Take the following workbook, for example. We have a list of cities ranked by sales, using the RANK function (a table calculation).


The dashboard has a filter on state. Below I’ve selected Pennsylvania.


This works great, but what I really want is to maintain the overall rank of the cities. Philadelphia, for example, is ranked # 5 overall and Chester is ranked # 191. So, how do we filter the list without re-ranking the cities? The answer lies in Tableau’s Order of Operations.


Our state filter is a dimension filter (highlighted in yellow above), but the RANK function is a table calculation (highlighted in blue). As we can see, the dimension filter computes before the rank. Tableau is, therefore, removing the data from the view, then ranking the cities that are left. What we want is to rank the cities first, then filter. And the only way to do that is to force our filter to be a table calc filter (orange highlight). To do this, we can create a calculated field like this:

State TC
// Force state to be a table calculation...
// ...so that it computes after the rank.
LOOKUP(MAX([State]), 0)

LOOKUP returns a value in a different row, but we’ve set the offset to 0, meaning that it will just return the value of MAX(State). The key here is that this is a table calculation, so if we use it as a filter, it will compute after the RANK table calc.

So, we replace our filter on State with State TC.


And, as you can see, it now maintains the overall rank, instead of ranking the filtered values.

This, of course, is just one example of this technique, but this problem is much more common than you might expect and I find myself using this trick quite frequently. They key is to understand the Order of Operations and to analyze the order in which the components of your view are computing. Once you know that, you can bend the Order of Operations to your will!

8. Bring Your Own Bins
A few years ago, I found myself needing to create a reference line on a histogram. It was then that I realized that Tableau bins do not allow this. At that point, I went searching for a solution and found it on the Wiki section Jonathan Drummey’s website. On this page, he provides a calculated field, with the help of Joe Mako, for BYO Bins. I won’t repeat it here since Jonathan’s site already includes the details, but I just want to amplify this trick since it allows you to do all kinds of things that you cannot do with standard bins. In fact, after discovering this trick, I rarely ever use standard bins at all.

9. Excel Tables & Named Ranges Act Like Separate Tables
In Excel, you can format sections of columns and rows as either a table or configure it as a named range. For example, the following sheet has two separate sets of unrelated data—a list of Russian literature and sales by sub-category.


In Excel, I’ve formatted each of these as tables, named “Russian_Literature” and “SubCategory_Sales”, respectively.

Now, if I connect to this spreadsheet in Tableau, it will give the option to connect to the entire sheet or each individual table.


This technique also works with Excel named ranges.

10. Use Custom SQL with Excel
Before 2019.1, you could write custom SQL against an Excel data source using the legacy connection type. But, due to the fact that Microsoft no longer supports the drivers needed for the legacy connection, this is no longer an option. However, there’s a neat little trick we can use to allow custom SQL against an Excel file—we can create a linked table in Access. To do this, we need to first create a database, then we use the External Data menu as shown below.


In the “Get External Data” dialog, we select our Excel file then choose the “Link to data source by creating a linked table” option.


Click OK then follow the wizard to import/link your table.


This will create a “linked table” which will link back to the source Excel file. As changes are made in Excel, they are automatically reflected in the database. But, because this is now part of an Access database, we can connect to it in Tableau and write custom SQL.


A couple of notes on this approach. First, Access does not use ANSI standard SQL, so there are some strange differences in Access SQL, such as the # signs used around dates above. Second, the data preview pane may fail to load because the SQL is a bit too complex for Access, but it should work okay once you start to build your sheets—just be careful as you can exceed the complexity limits of Access.

……………………………

So, there are my 10 somewhat obscure tips. You may not use them every day, but I certainly hope that they come in handy when you need them. Thanks for reading!!


Ken Flerlage, January 11, 2021



No comments:

Powered by Blogger.