By default, scroll bars in Tableau only control one sheet at a time. In this video, learn how to get around this and use a single scroll bar to keep multiple sheets in sync.
Hi. This is Ryan with Playfair Data TV. And in this video, I’m going to show you a trick that allows you to scroll across multiple worksheets at the same time so that they are in sync.
First, let me show you why this is needed. This is just one of infinite examples, but let’s say I’ve got these two separate worksheets. The first is a text table showing us the current month’s sales per sub-category in the Sample Superstore data set. And you don’t need to know this. The data doesn’t matter too much. But this happens to be the month of December sales.
I’ve then got this ranged dot plot that I show you how to build in another video, How to Make Ranged Dot Plots in Tableau, showing something similar, where the circle represents the text on the first sheet, so that’s the current month’s sales per sub-category. But this visualization has a lot more context. It’s colored by month over month performance. And the gray line in the background is showing us the full performance range per sub-category. Again, if you’re interested in that one, check it out on a different video.
But here’s the issue. Once I go to add these both to a dashboard, because they are on separate sheets, I’ll end up with something like this. I put both individual sheets inside of a layout container and just distributed those items evenly, trying to give them both 50% of the container. And I attempted to make them line up across the rows.
As you can see, that layout container is too short to show me all 17 sub-categories comfortably. So instead, I get these scroll bars. But here’s the issue. If I scroll on one, the other one does not scroll. That’s what I’m going to show you how to do so that you can keep both of these in sync. It’s going to be a lot better user experience to your audience that’s looking at this, and it’s going to help them line up the dimension members across charts.
This tip is a little bit easier if you have a unique row identifier associated with each of your dimension members. But I realize that that is not always the case in a real world scenario. So I’m also going to show you a trick to get around that. We’re going to create a calculated field that uses a table calculation called INDEX. So I’ll just call this INDEX(), and that is the entire function. It’s just the word Index, I-N-D-E-X, open parentheses, close parentheses.
I think of the INDEX() calculated field or table calculation being synonymous with row number. So first thing I’m going to do is click OK. And by default, that creates a continuous measure because the index or row number is quantitative, by default. It gets classified as a measure.
We need to use this in front of this discrete Sub-Category pill on the Rows Shelf. But in Tableau, discrete fields are always drawn before continuous fields. So in order to get INDEX() in front of Sub-Category, I need to change it to be discrete, which you can do by right-clicking on the newly created calculated field and click Convert to Discrete.
It’s very subtle, but that equal sign and number sign is now blue instead of green, which will allow me to add it as the first pill on the Rows Shelf. And as I said, by default, in this case, that INDEX() function is computing from top down. So we get a unique row identifier. We just essentially faked a unique row ID, which will be helpful for this tutorial.
So I’ve got rows 1 through 17. I’m going to do the same thing on my ranged dot plot, just put INDEX() in front of Sub-Category. There’s that unique row identifier once again. I’ve also shown you on another video how to use this table calculation of INDEX() to filter out rows. It’s a little more predictable because as long as you know this is computing from top to bottom in this case, you can do things like, say, show me only index 1 through 5. Throw that on the Filters Shelf. It’s Boolean, so it’s very efficient, and it keeps only the first 5 rows of data, regardless of if they’re sorted in descending or ascending order.
Now I’m going to show you how that type of filtering would work. We need another calculated field. And I’ll just say that we want to keep five rows on the view. So we’ll call this Five Rows. And the formula is that table calculation again, INDEX(), is going to be greater than or equal to 1– so in other words, the first row. And that INDEX(), or in this case, row number, needs to be less than or equal to 1 plus 4.
And you might be wondering why I didn’t just type a 5 there. Because I’m just for now showing you how this is going to work. In the next step, we’re going to parameterize that number 1, allowing the user to choose whichever set of five rows that they would like to keep on the view. I’m going to click OK. We see a new calculated field show up. It has an icon preceding it that says T pipe F, telling us it is a Boolean. So here, that statement is going to be true, or it’s not.
I’m going to drag that to the Filters Shelf and choose True. Click OK. Then we’re left with five rows on the view. That’s exactly what I was looking for. Let’s go ahead and do the same thing on the text sheet. Drag Five Rows to the Filters Shelf and choose True. Click OK. Once again, we’re left with five rows on the view.
So to start to show you that this is working, let’s go back to our dashboard. And now we’ve got plenty of room to go ahead and fit the entire view because we’re only looking at five rows at any time. So for each one of these, I’ll change the fit to Entire View, which will make them taller. And now they’re lining up nicer as well, from left to right. So that’s already a good start.
But the next thing I’m going to show you is how to actually scroll through the remaining rows. So for that, I’m going to go back to a sheet. And this requires a new parameter. So I’m going to create a parameter and call this Scroll.
And your gut might be to set up a data type of Integer because we’re going to allow the user to choose, essentially, which row number to start that filter on. However, String data types have a little bit of more formatting flexibility in parameters. One of the options that I really like with a data type of String, is you can choose to show buttons and a scroll bar.
So you could go with Integer. It’s a little bit easier, more common sense, and in fact, it’s even a more efficient option for you. But I’m going to go with String because it’s going to create a slightly better user experience, which you’ll see here in just a moment. So I’m going to choose String.
I have a very specific list of options. I’ve got 17 rows altogether. So I have to go in here and type all of the options– so 6, 7, and I need to go all the way up to 13, because that will allow my user to start the filter on any row up to 13. Once they get to 13, then you add 5. That will capture the remaining 17 rows in this table.
So a little bit tedious to setup one time if you’re wanting to create this better user experience, but once you’ve set it up once, we’re good to use this from now on. There’s my entire parameter. I’m now going to go back to my Five Rows calculated field and replace those 1’s with my newly created Scroll parameter. And the trick to this, because we chose a data type of String, is I need to convert them to integers.
So I’m going to type the function INT to convert my Scroll to an integer. So INT, Scroll, and then I also want to replace the second occurrence of the value 1 with my newly created Scroll parameter. And now this is parameterized. So this is going to keep all of our rows that are greater than or equal to the number 1 currently– that’s the current value of the Scroll parameter– and all the rows that are less than or equal to the Scroll value, which is 1 plus 4.
So at first, we shouldn’t see anything change because we’re just replacing the first five rows with the first five rows. I’m going to click OK. Sure enough, we do not see those rows change. But now if I show the parameter control, and I change this number, if I choose 2, we should see the table update to rows 2 through 6. If I choose 3, it updates to 3 through 7. So everything seems to be checking out.
This filter is also being applied to our ranged dot plot. Let’s go see if that’s working over there. Yep. We’re on number 3. So we’re looking at rows 3 through 7. So you can already start to see why this is going to work. We’re seeing the same rows being filtered out across both of our sheets.
But a couple of other little tips for you– this is called a Parameter Control in the top-right corner. By default, because we chose a data type of String, we’re seeing this dropdown menu. Tableau thinks that these are words that you’re choosing from. One of the options for formatting that you can access by clicking this down arrow in the top-right corner of the parameter control, then click Slider– there’s those buttons I was telling you about and this nice scroll bar. You also have the option to get rid of what’s called the Readout, showing us what number we are on.
Now that we’ve got this Slider selected, if you click this down arrow again, you can customize it. So one of the things I will deselect is Show Readout. And now as I scroll to the right, it’s going through all my values. If I choose 13, there’s the bottom of my table. I can also use these buttons, if I would like, to click over and scroll through the table.
So let’s get back to the default view, the top five rows, and finalize this by just going to the dashboard, clicking on either of the objects and hovering over Parameters, and I can choose to show that Scroll parameter control right here in line with the two sheets. Go ahead and change that formatting again and make this floating so that I can center it.
Also, now that I’m confident– so first, I’ll quality check this one more time, but you can see it’s working. We’re now scrolling across multiple sheets at the same time in Tableau. Now that I know it’s working, there’s several things on this view I no longer need. I don’t need to show the index number. I’d actually prefer to hide that.
So I’m going to right-click on the first column and deselect Show Header. And I’m going to do that in both cases. Also, I know now that the left side lines up perfectly with the right side, so these sub-category names are repetitive. So I’m also going to right-click on that header and deselect Show Header. And if I really wanted to make this match, maybe I will right-click on this right sheet and click Format to add in some row banding so that that banding is intact all the way across the table.
And of course, you could take this a step further. There’s still some extra lines and things that I would get rid of in real life. But just to show you that this is now working and we’ve made this nice, cohesive multi-table scroll, we’ve got everything in place. I can either push these buttons and move through the rows five at a time, or I can just left-click on the scroll bar and drag that to whatever starting point I would like. But whichever one I choose, we’ve just tricked Tableau into allowing us to scroll across multiple sheets at the same time.
This has been Ryan with Playfair Data TV – thanks for watching!