Tableau Hack: Avoid “*” in Tooltips & Other Interactive Text, Using LOD Calculations

When Tableau 9.0 was released, I looked at Level of Detail calculations and thought, “I’m sure this adds a lot of capabilities to Tableau, but I just can’t wrap my mind around them, yet.” Then I discovered that this new functionality allowed me to avoid what I feel to be one of the least intuitive parts of Tableau for end-users: the tooltip asterisk. When you create interactive text in Tableau, like a tooltip, and the Dimension driving it has more than one response, Tableau simply displays an * in that space.

In the past, I smart dashboard designer would either 1) organize their dashboards or data sources so that they were sure these *s would not pop up, 2) include text explaining what the * meant, or 3) went through very complicated calculations that mimicked LOD functionality that didn’t exist yet. Now in two simple calculated fields you can get Tableau to either display the single value for a field or give you a count of unique values present in the data.

If you’re confused, here are screen capture examples from a dashboard I published earlier this week (full dashboard at the bottom of this post):

tooltip no asteriskIf I had simply put the [Club] dimension into that spot in the Tooltip editor, Nagbe’s would have read “POR,” while Amarikwa’s would have simply displayed an *. Since 160 of the 366 players displayed on this graph played for multiple clubs during that timeframe of my data source (2011-present), this would be far from ideal. Instead I wrote the following calculated fields:

calc fields no asteriskLet’s dive into the first one, since this is the new LOD calculation type. It’s syntax is markedly different from many Tableau calculations, but a simple way to think about the FIXED LOD calculation is that if you want the sum of [A] per [B], then you’re calculation will read: “{ FIXED [B] : SUM([A]) }. In the case above we were looking for the distinct count of clubs per player and so my calculation was: { FIXED [Name.Href] : COUNTD([Club]) }.

True, I could have built this as one longer calculated field, but I thought clubs per player might come in handy outside of tooltips, so I left it on its own then used it in my “no * club field” calculation. This one used three functions in its own right. The uses of ‘+” ‘ are Tableau’s way of requesting concatenation (stringing together different text elements, for those who don’t speak Excel). Tableau concatenation doesn’t allow the mixing of strings and numbers, which is the reason that the second instance of [clubs per player] is wrapped in the STR formula, which changes its data type to string, to match ” Different Clubs” within the if and only if statement here.

This may seem like a lot of trouble for a tooltip element, but keep in mind that you could use this hack for any interactive text element on a dashboard. Since you would rarely want “*” in a text field, this approach opens up some important possibilities. If your data is structured in a certain way, this approach could very well be the most expeditious way to summarize an important dimension in an interactive way, which can be very valuable.

Here’s the full dashboard from the example above:

2 thoughts on “Tableau Hack: Avoid “*” in Tooltips & Other Interactive Text, Using LOD Calculations

Comments are closed.