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.
Image by Susan M. Cipriono
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: