Synchronized Scrolling in Tableau

At Tableau Conference 2023, Kevin and I presented our 2023 edition of How to Do Cool Stuff in Tableau. It had been a year since we had been able to present together in front of a live audience and we were so excited for it. To make things even better, we were presenting twice, one of which was being broadcast live on Salesforce+ (you can watch it on demand as well).

 

We arrived about 40 minutes early for our second session and were humbled to see a long line had already formed. Our location was intended to handle an audience of 400, but as the line continued to grow, the amazing production crew realized that people would have to be turned away and started working to make more space available. In the end, they were able to add three additional spaces as well as standing room in the back so that anyone who wanted to watch would be able to. We want to give the crew a HUGE THANK YOU for acting so quickly to make room for everyone in line. They were a truly amazing group of people who made our speaking experience so easy and enjoyable!

 

Once everyone was seated, we looked out on the audience and were filled with gratitude to everyone who came—so much so, that we got a bit choked up as we began our presentation. If you saw either of our presentations live or watched online, we just want to thank you for your support! This community is truly amazing, and we are incredibly humbled to have so many people come to see us. Thank you! Thank you! Thank you!

 

Selfie Before our Second Session at Tableau Conference 2023

 

Synchronized Scrolling

If you saw the presentation, you know that I did an entire section on “Synchronized Scrolling”. After the session, we heard a lot of people who loved the technique we shared. So, I decided I better get a blog written for anyone who hasn’t seen it.

 

So, what do I mean by “synchronized scrolling”? Well, let’s start with an example. What if we wanted to create the following?

 

 

This dashboard shows three metrics—Sales, Profit, and Profit Ratio—for each State/Province (using Superstore). For each metric, we have the total and a sparkline by month highlighting the minimum and maximum values. The key to this dashboard is that it’s scrollable, allowing you to see all of the states/provinces.

 

Building It

If you think through how you’d build this in Tableau, you’re probably already starting to see the problem. On the surface, it looks relatively simple. But, in reality, this dashboard is quite hard to build in Tableau. To understand why, let’s try to create it. We’d probably start out by building something like this.

 

 

We’ve placed Month on the columns shelf and Measure Values on the rows shelf and have included Sales, Profit, and Profit Ratio on the Measure Values Shelf. This has allowed us to create the sparklines, but there is one big problem—they all share a common axis that runs from approximately 0 to 20,000. Profit Ratio is a percentage—a value between 0 and 1—so when plotted on this axis, it looks like a straight line. To address this, we can normalize each measure to be a value between 0 and 1 as shown below.

 

Month Sales

// Sales by state/province, month

{FIXED [State/Province], [Month] : SUM([Sales])}

 

Sales 0 to 1

// Normalize the sales to a value between 0 and 1

// Formula: (Value - Min)/(Max - Min)

(SUM([Sales]) - MIN({FIXED : MIN([Month Sales])}))

/

MAX({FIXED : MAX([Month Sales])} - {FIXED : MIN([Month Sales])})

 

These are a bit complex, but they allow us to force each measure to values between 0 and 1 with the smallest value becoming 0 and the largest becoming 1. Using those, our sparklines look much better.

 

 

But we have another problem to overcome. We’ve been able to show Total Sales using a discrete pill, but how do we add Total Profit after the Sales sparkline and Total Profit Ratio after the Profit sparkline? We can’t insert additional discrete pills between our three measures, so we’re kind of stuck—so close, yet so far.

 

One method we could try is the brilliant technique pioneered by Sam Parsons. Sam’s technique allows you to hack Tableau to allow for variable cell sizes for your measures. I can’t overstate the brilliance of this technique as it provides us with so much more flexibility for this type of table. If you haven’t watched Sam’s video, then stop what you’re doing right now and watch it. And be sure to subscribe to Sam’s YouTube channel as he has lots of other great stuff on there as well.

 

But, as you can see in one of Sam’s examples below, the technique is not for the faint of heart.

 

 

We have a ton of different fields on the columns shelf, plus a tricky little table calc on the rows shelf. While it might be possible to modify our sheet to use this technique, it’s a bit too tricky so I’d like to move onto something else.

 

Map Layers

Let’s try another technique. In a Data Coach YouTube video, Tableau 2020.4: How to use the new map layers feature (and go beyond just maps!), Luke Stanke describes how we can hack Tableau’s map layers feature to make it plot more than just maps. Others have applied this same technique, including Sam Parsons, who used it to create his Iron Viz 2021 entry, Rivers of Time.

 

Map layers are generally used for maps, but we can trick Tableau by finding the X and Y coordinates of various charts then converting those coordinates into geographic coordinates (using functions like MAKEPOINT and MAKELINE). With these being geographic coordinates, we can now create as many layers as we want, without being constrained by the limitations of dual axes provided for non-maps. Pretty clever, huh?

 

I used this method to create my dashboard and it worked brilliantly!

 

 

This required the use of nine different map layers—one for each of the totals, one for each sparkline, and one for the min/max values on each sparkline. To give you a feel for what’s required, here’s the calculation for the sales sparkline.

 

Geo - Line Sales

// Geography for the sales sparkline.

MAKEPOINT(

    [Multiplier]*([Month Sales]-[Sales Avg])/([Sales Avg]),

    [Long Sales]+1+[Month Num]*[Long Step]

)

 

Note: Multiplier, Long Sales, and Long Step are parameters that allow us to place this information in the correct location on our “map”.

 

This technique is insanely cool and has very few limitations. But it’s difficult to implement. I’m quite experienced with Tableau and this took me over two hours to create. Like the previous method, this is not for the faint of heart.

 

Synchronized Scrolling

We’ve tried multiple ways to get this to work using a single sheet and, while we’ve had some success, it was quite difficult, and I’m just not convinced that these techniques are all that viable in the real world. So, I’m now ready to give up on the single sheet and see if we can do it with multiple sheets. So, we create three sheets—one for each measure (note: we’ve hidden the header for State/Province on the Profit and Profit Ratio sheets as we only want to display that once).

 

 

Then we bring them together on a dashboard.

 

 

The problem is that each sheet has its own scrollbar and, when you scroll that sheet, the other two do not scroll with it. Thus, we must find a way to make the sheets scroll together. Enter the concept of “synchronized scrolling”.

 

Paging & Fake Scrollbar Methods

One method for doing this is borrowed from the web. When you have a long table of data on a website, you’re often presented with Next and Back buttons that will take you to the next or previous “page” of data. Fortunately, we can also do this in Tableau as shown below:

 

 

Next and Back are separate sheets that are triggering parameter actions which force the table to jump to the next or previous page. Making this work requires the following:

 

1) A numeric ID for each row (I’ve called the field simply #). I’ve done that here using an INDEX calculated field.

 

2) A parameter called Page Size that tells us the number of rows that can fit on each page. In my example, I’ve set this to 14.

 

3) A parameter called Page that indicates which page we are on. In my example, when Page = 1, then it shows IDs 1-14. When Page = 2, then it jumps to show IDs 15-28, and so on.

 

4) Parameter actions that increase the value of Page when you click Next and decrease it when you click Back.

 

5) A calculated field to filter the view to the right set of IDs based on all the above.

 

With all this in place, all of our sheets “scroll” in a synchronized manner.

 

Another variation of this technique comes from Lindsay Betzendahl’s blog, Tableau Scrolling II. Instead of the Next and Back buttons, Lindsay creates a sheet that looks and acts like a scrollbar.

 

 

This is then added to the dashboard. When you click on part of the scrollbar sheet, it fires parameter actions and causes the sheets to scroll forward in smaller segments.

 

 

The setup for this method is very similar to the paging method shared previously.

 

While these methods seem to work well, they are also flawed. Both rely on us being able to calculate that ID for each row and I found that this is not as easy as it seems. When a State/Province has no value for a given month, it wreaks havoc on the table calculation—a problem that can be quite difficult to solve. In the end, I had to create a data scaffold to ensure that every State/Province had data for all months. Additionally, like previous methods, this is a lot of setup and it’s not particularly straightforward.

 

A Better Way!

At this point in our presentation, I sighed deeply, and Kevin chimed in saying “There’s gotta be a better way!”.

 

Clip from Friends Where Joey Tribbiani Plays “Kevin” on an Infomercial

 

Fortunately…there is, Kevin!!

 

What if I told you we could do this in about 30 seconds, without all the complexity required in the methods shared above? Does that sound good? Well, stick around for the next blog in this series when I’ll share that method!!

 

………………………

 

Just kidding…did you think I’d actually do that to you?

 

We’ll start with our dashboard containing all three sheets.


 

Now we’ll increase the size of the dashboard until the scrollbars disappear.


 

Then we create a new Story and add the dashboard to it.

 

 

The dashboard is too tall to fit into the story, so what does Tableau do? It creates a single scrollbar to scroll the entire dashboard. Finally, we right-click the Story Point caption and choose “Edit Height”, changing it to 1.

 

 

The story points will essentially disappear, leaving you with a simple scrollable dashboard!!!

 

 

Boom!! That’s all there is to it!!

 

A Couple of Flaws :(

Okay, I’d love to say that this method is perfect and without flaws, but that’s not really the case. So let me share a few potential flaws to this approach.

 

1) The entire dashboard scrolls. In my case, that means the column headers scroll off the page. That’s not a huge deal but imagine a scenario where you have a dashboard with BANs at the top and a table below. You probably want those BANs to always show at the top, but they’ll scroll right off the page using this technique.

 

2) Tableau dashboards have a maximum height of 10,000px (unless you hack the XML). So, if you have a ton of rows in your table, that might not be enough height to make the scrollbars disappear.

 

3) You are a little more limited in how you format a story as well as how you perform various interactions. You just don’t have quite as much control over a story as a dashboard by itself.

 

4) A more obscure flaw is the fact that parameters have their own “state” when on a story. In other words, changing the value of a parameter on dashboard/sheet that is in a story will not change the value of that parameter on other dashboards/sheets. This is an odd behavior that I’ve never completely understood, but it’s something to be wary of when using this method.

 

If none of the above flaws are an issue for you, then this method will work quite nicely and save you a ton of time along the way!

 

Thanks so much for reading! If you get an opportunity to try this technique in your work, I’d love to hear about it. And, if you have any questions or comments, please let me know.

 

Ken Flerlage, May 22, 2023


3 comments:

  1. This is so clever! Thanks for sharing this. It's crazy that nobody has come up with this solution until now. Maybe because Stories have never been widely adopted by the users.
    BTW, there is a good reason why parameters can have different states on each Story Point. The intention of Stories, when they were introduced, was to be able to show different scenarios on the same dashboard, without the need to change filter and parameter settings during the presentation.

    ReplyDelete
    Replies
    1. Well, it does have a few drawbacks, but still pretty handy in certain situations. I didn't know that about stories, but it certainly makes sense! Would be nice if that were an option you could modify since the usage of stories seems to have changed somewhat since it was released.

      Delete
  2. These are all great ideas! Thanks so much for posting. There's another easy workaround method I found that seems to work, and for multiple windows, some scrolling some not, rather than just one synchronous scrolling. I published the dashboard I wanted to be synchronous then linked to it's webpage in a window of a second shared dashboard. I had to cover up the web header with a floating object, but it seems to work most of my applications. Just remember to give all your users permissions for both dashboards :)

    ReplyDelete

Powered by Blogger.