Create a Random Starting Point on Tableau Public
In December 2020, Klaus Schulte and I collaborated on a fun project where we recreated the Spot It (known as Dobble in Europe) card game in Tableau. The object of the game is simple. You turn over two cards and players race to find the one and only object that is on both cards. The game itself is a ton of fun and the mathematics behind it are simply mind-blowing. The viz turned out great and it was a ton of fun to build. I encourage you to check out the final Spot It vs Dobble viz (note: in Jul 2022, there was some issue with images on Tableau Public, so parts of this viz may look a little odd).
Klaus brought up one issue before we shared the viz with the public. The game is played the same way every time...meaning, the first two cards are the same two cards no matter how many times you play the game. In theory, players could have memorized that the matching object in the first two cards was the zebra and the matching object in the second two cards was the key and so on. Klaus recommended that we use some sort of randomization to change the cards and the order in which they appear. I tried grabbing the seconds of the time figuring that would be fairly random - DATEPART('second', NOW()). However, that didn't work when published to Tableau Public. I quickly tried a few things, but never really dug in. We didn't really push the issue and figured that people wouldn't likely play it enough times to memorize the order.
Fast forward to early 2022 when I started playing around with my Luck of the Irish viz. I created the original version of this one month into using Tableau (March of 2018). It contained 25,000 three-leaf clovers and 1 four-leaf clover. The object was to find the single four-leaf clover. Again, the problem is that the game never changed...you play it once and you're done. This always bugged me so I decided to create thousands of variations. This was pretty easy, but even though you could play thousands of unique games, the games were, again, played in the exact same order. So you could, in theory, memorize where the four-leaf clover was located. Every time you came into the viz, you'd see the four-leaf clover would be in the same position as it was the last time you opened the viz...and the second game would be the same, and the third, etc.
So, I decided I would try to randomize the position of that four leaf clover. I created a RANDOM calculation in Tableau to get a value of 0 to 1. I then multiplied that figure by 25,000 (I have 25,000 clovers) which would give me some value between 0 and 25,000. I then set my four leaf clover to be equal to that figure. I published it up to Tableau Public and started testing it. It didn't work. The four leaf clover was in the same spot every time I opened up the viz. Not really taking time to think, I tried just obtaining the seconds of now - DATEPART('second', NOW()). This would yield a different number of seconds each time someone accessed the viz. Nope, that didn't work either. It always reflected the second that I published it. I mentioned this to Ethan Hahn at work, and he said, how about grabbing the username...well, that's not available on Tableau Public (it is on Server and Online).
This sort of triggered me to say, wait! Data sources on Tableau Server and Tableau Online are always being updated whether it is a live data source or an extract with a scheduled refresh. But Tableau Public doesn't really allow for that. First, you can't use a live data source and the only way to update an extract is via a Google Sheet, which will update once a day. Well, I figured I'd try a Google Sheet.
I created a new Google Sheet and entered "=rand()" into the first cell (this would provide a random number between 0 and 1). I opened Tableau and connected to this Google Sheet. I then published it to Tableau Public. When doing so, I made sure to check the box to "keep my data in sync with the data source and embed my credentials" as shown below. This is what forces Tableau Public to refresh your data source daily.
I then went to Tableau Public, viewed the viz, and saw a seemingly random figure...let's call it 0.42. When you are logged into your own account and are looking at a viz that is set up with a refreshable Google Sheet, you will see a manual refresh button below the viz (shown below - sometimes you need to refresh the page to see it). Clicking this button will force a refresh of the data source - you don't have to wait a full day.
So I clicked it and guess what happened? Nothing. That random number didn't change; it was still 0.42. This was mind boggling to me. I didn't get it at all. This sent me down a long road, one that I'm trying to save you from, of investigating every single possibility (I just had to figure this out). I even spoke with Ken and he built a random number generator in Google Sheets using Python.
I eventually dropped it and forgot about it for a couple of days. But it was still bothering me, so I went back and added a few more options to my Google sheet. I added options for the hour, minute, and second of NOW() within the Google sheet. Before publishing my changes, however, I looked at the original version I had published a couple of days ago...you know the one with the random 0.42 that didn't update? Well, that number was different! It was now 0.78. It updated!!!! Why did that happen? Well, I did some testing and it appears that it simply takes a bit of time. If you publish something to Tableau Public then refresh it 30 seconds later, it likely won't update. I couldn't find any true consistency, but during testing, sometimes it didn't update ten minutes later, but if I refreshed an hour later, it updated that random number every single time.
So here is the key, random numbers will update as long as the data source is refreshed. On Tableau Public, the only ways to make this happen is to refresh the data in Desktop and republish the viz OR to utilize a Google Sheet data source and set it to stay in sync (as noted above). Through additional testing, I found that it doesn't matter where your calculations reside. You can put the random figure in the Google Sheet or you can just build them into your Tableau workbook. In fact, you don't even need true data in the Google Sheet. You can connect to a Google Sheet as one data source, then connect to a totally different data source, create your random calculations with that data source and it will still update. The key is that you connect to a Google Sheet and allow your data source to be refreshed.
Now, there are plenty of limitations to this because Google Sheets update only once a day and you have no control over when that happens. My ultimate goal would have been to get a random number every single time someone accessed the viz - I don't know how you might do that within Tableau Public. But, you can get a truly random number every single day. In my NEW AND IMPROVED Luck of the Irish viz, I wanted the user to be able to play thousands of games, so I just used my random number as a starting point. I created a dynamic parameter of 1 to 25,000 (remember there were 25,000 clovers) and set the random number to be the default value of that dynamic parameter. The user plays the first game (based on the random number) then they can click the arrow on the parameter to play the next game. Sure, if they leave the game and come back in within the next 24 hours, the game sequence will be the same. But the next day, it will all change.
Although I built my Luck of the Irish viz first, I used this technique over and over again to provide randomization in my Vegas Vacation Cheapo Casino viz. In this viz, you can play 5 different "randomized" games just like in the movie.
One quick side note, Jeff Shaffer mentioned to me that another option is to utilize the Javascript API. You embed the viz and just pass the random variable in as a parameter. Then, every time the viz loads, it will get a new number in the parameter.
Okay, this blog post will likely not help you in your day to day work, but its something that has come up time and time again, so I figured I'd share. Thanks for reading!
No comments: