Tooltips are not all or nothing per visualization. Learn how to use IF / THEN logic and the IIF function to dynamically change what appears in a tooltip based on different scenarios in your data.
Hi. This is Ryan with Playfair Data TV, and in this video we’re going to be reverse-engineering one of my Tableau Public visualizations to show you how to create dynamic tooltips in Tableau. We’ll be talking about IF THEN logic, ELSEIF, and ELSE statements, as well as the Immediate IF (IIF) function. All of these technical features within Tableau calculations are going to allow us to dynamically display different tooltips within a Tableau worksheet or chart.
So over here in Tableau Desktop, this visualization is called A Tale of 50 Cities and it shows the population changes among the US’s top 50 cities. The reason that I’m choosing this to show you, and we’re going to reverse-engineer this instead of looking at an example from scratch, is there’s literally infinite applications of this. So I’m just going to try to give you one decent one and hopefully you can apply this to something in your own business or some other business question that you’re trying to answer specifically.
But let’s take a closer look at the visualization first. I’ll give you some context on the viz, and then I’ll talk about some wrinkles that required me to setup some dynamic tooltips. I’ll then show you how to reverse engineer those calculations, so that you can do something similar in your own visualizations.
So, again, it’s the population change for the top 50 US cities, that’s what we see here on the rows, and it goes by census. That was my dataset, the US census, which is recorded every 10 years since 1790. So that’s the start of my chart here.
This resulted in a couple of interesting things. I wanted to provide some extra information via the tooltip, things like the city name, the census, year, the population of that census’ year, as well as the census over census change. But a few wrinkles popped up that I wasn’t expecting.
First one that you’re seeing right here is in 1790 that was the first year of the census, so there was nothing to compare it to. Instead of having a null there, or some text that didn’t make sense, I wanted to dynamically populate that so it told the end user that we didn’t have any data before this. So it says ‘First Year of Census’.
Another little wrinkle is that the dataset I was using from the US census was only sharing the top 100 cities each census, so that top 100 would churn a little bit. It wasn’t always the same cities in the top 100 within the US. So the years that I don’t have that information for the city, you can see there in parentheses on the tooltip for Phoenix, it says, ‘Not in the Top 100 Cities’ during that census. So I’ve got a lot of dynamic text going on.
If we take a closer look at the tooltip– I’ll explain what each of the fields is– City and State were dimensions in my dataset. Then I’ve got something that says ‘Year plus the Population or NULL’, and then the Population was a measure in my dataset. And then I’ve got another one that says it’s ‘Census over Census or NULL’, again, because sometimes we had the data, sometimes we didn’t.
I’m going to break down two of these calculations. The first one that we’re going to look at has two outcomes, it’s ‘Year + Population vs. NULL’. And another reason I’m wanting us to reverse engineer this is you can do this on your own. Any of my Tableau Public visualizations are downloadable, so you can download the file. And this is exactly what you would see. Any field that has an equal sign before it, that’s a calculated field that you can just right-click on and click Edit to reverse-engineer how that was done.
So in this first example, I wanted to give you a little bit more information about the I-I-F statement, which stands for Immediate IF statement. This is useful any time you want to use IF THEN logic that only has two outcomes. Remember anytime you see something that’s blue you can click on it and Tableau will give you a definition of what that does, as well as an example of the syntax of how that is used.
What this one does is it looks at the statement that comes before the first comma. So right now, I’m just saying, is the population null? When that statement is true, that’s what you display after the first comma. So if that first statement is true, I want Tableau to say, ‘Not in the Top 100 Cities’.
What comes after the second comma is essentially the ELSE statement. So if the first statement is not true, so it’s false, this is what the other bucket is. This would be like if I wrote, ‘IF the population is null then show Not in Top 100 Cities, ELSE show this second string’. But this is a little bit more elegant way to write this IF THEN logic, but it’s only relevant if you’ve only got two outcomes, which is the case here.
So, again, this will just look at my nulls, and if they are null, that means that I didn’t have data that year. In other words, it wasn’t in the top 100 cities. So that’s what I want to display. When the city was in the top 100 and I have that information, I want to display this, the census number, so the measure value, and then I want to put the word ‘Population’, which is why it’s in quotes. And I’ve converted this entire statement to be a string. That’s what this STR function does.
For my second dynamic tooltip, I had three outcomes. So I had to use IF THEN logic and take this a little bit further. What this one computed, was whether or not it was the first year of the census, whether or not I had both the population and the current census versus the prior census– in other words, I’d be able to compute a census over census change– or whether I’ve got the census over census change as the ELSE statement.
So what does this do? I know one of these lines is kind of long and I’ll have to scroll here, but first of all, if the census year is 1790, that one is an easy one. They’ll all say the same thing. They’re all going to say ‘First Year of the US Census’.
My second outcome is if I am not able to compute the census over census change. So what this formula does is it computes the census over census change. If that is null, that’s what this first function does is null– then I want Tableau to display ‘Census over Census Change N/A’. Otherwise– so if you’re not the first year of the census and your census over census change is not null, then I want it to display ‘Census over Census Change’.
So some of this takes a little bit of logic. In fact, that’s all this is, is logic. But you’ll kind of come across these different scenarios in your dashboard where you can improve the user experience by making the tooltips dynamic to address all of these different situations that can pop up.
Just to show you how the final view turned out, again, if you’re in the year 1790, they will all say ‘First Year of US Census’. If I had the data to compute the census over census change, that word changes from ‘First Year of Census’ to ‘Census over Census Change’, and then it displays the census over census change. And if I don’t have the example, like we had earlier with Phoenix, as of 1880 it wasn’t in the top 100 cities, so the population was replaced with that phrase, ‘Not in the Top 100 Cities’. And because I wasn’t able to compute the census over census change, the tooltip dynamically displays that.
So completely different experiences based on the data that I had, all made possible through dynamic tooltips using the Immediate IF statement and IF THEN logic with ELSE and ELSEIF statements.
This has been Ryan with Playfair Data TV – thanks for watching!