The theme of this month’s IronQuest was games. After my last couple of projects, I had promised myself not to spend too long on this one. I failed miserably! So this will be a story of failures and lessons learnt, plus some highlights of the final dashboard at the end.

See the result here

Lessons learnt, all about what you can’t do in Tableau Public:

● No access to APIs
● No access to any live data
● No way to programmatically force a data source update (equivalent of hitting F5). This is also needed, for example to recalculate a pseudo random number on launching the viz so that each new user starts with a different word
● Data is only refreshed once a day. For example, you can access a Google Sheet (see Ken Flerlage’s Tableau Public Stats Service), but any changes will be ignored until the next day
● Related to the last two is that there is no access to live time, which I wanted to use for a countdown as well as for randomising data
● The age-old problem of Tableau Public moving things around a bit and resizing them, especially floating objects

Having said all that, here are the techniques I used in the final viz:

● Actions. Lots and lots of actions. Mostly parameter actions (parameters being the program variables of the Tableau “programming language), set actions, including the new ones that came out last year in 2020.2 allowing you to add / remove individual members, and filter actions for the good old “remove highlight on selection” trick
● Combined sets, allowing you to perform AND, OR and XOR operations on two sets
● Transparent shapes. Once you discover them, you can’t stop using them!
● Densification – creating data out of next to nothing
● REGEX. For the first time! I was dead chuffed about this one
● Line break algorithm. We see these being used so often today, it is interesting to have to go back to first principles to implement it. Needed here for the longer phrases
● Excalidraw. This is a collaborative online sketching tool.
● Figma. This time not so much used for the planning, more for the graphical elements, such as the buttons

Many roads to nowhere – the limitations of Tableau Public

Tableau Public is a wonderful resource and the vibrant Tableau community would probably have difficulty functioning effectively without it. However, it does have some noticeable limitations.

APIs

My first idea for a game for this challenge was noughts and crosses (tic tac toe on the other side of the pond). To be able to concentrate on the interface, I planned to use a free API to which I could send the current game status and get a suggested move back. Also because I simply wanted to learn how to use APIs with Tableau. I came across this service.

With this, I just needed to create a custom URL based on the current position and the next player, which would have been easy enough to do with parameters and I would get a response in the format  {“game”:”—X—–“,”player”:”O”,”recommendation”:4,”strength”:-80} from which I could easily extract the next move using string calculations. The problem was that, as I discovered, there is no way for Tableau to read the response out of the box and certainly not on Tableau Public. I would need:

• To create a script, probably in Python
• Therefore learn a bit of Python! I’d like to do this anyway, but it was too much for this project
• Host the script somewhere. Not sure If I could abuse this blogging server for that. More investigation required!
• Have access to the API in real time.

The last point was the final nail in the coffin. Tableau Public works in principle on data extracts that you upload with the workbook (twbx files). It can only refresh any data source once a day. I understand why this is so, but it’s a bit of a pain anyway.

And with that, the noughts and crosses idea was dead.

Impressively, Joshua Milligan managed to build the whole game logic into Tableau itself. You can see his blog here.

Countdown timer

To add a bit of pressure on the player, I wanted to add a countdown or sand glass motive onto the dashboard, but that would require access to live time, which would require access to a live data source, which is not available on Tableau Public. It is also essentially breaking the way that Tableau interactivity is designed to work, which is purely based on active user triggers, whether mouse or keyboard-based (ignoring animations using pages).

Randomising numbers

Unless one uses a true random number generator, e.g. base on lava lamps, the best one can expect using calculations is pseudorandom numbers. If these can be based on something based on user interaction (e.g. mouse movements or very precise time of an interaction), then these are in most cases sufficient. I created an algorithm based on – amongst other things – the second of the minute that the user clicks on a button. This works OK for all but the very first word generated by the game, but I could not find a way to get the first word to be random. On desktop, hitting F5 to refresh the data source works, but that does not work on Tableau Public and in any case cannot be triggered programmatically.

The king is dead, long live the king! Ooops, he’s dead again!

So the idea of a hangman game popped into my head. “Can’t be too difficult” I thought. Wrong!

The concept I had was to somehow recreate people’s usual experience of playing hangman, i.e. hand drawn on a piece of paper (as I had intended with noughts and crosses. I saw one of Andy Kriebel’s “Watch me viz” videos on YouTube (which I VERY strongly recommend having a look at!) based on the weekly Makeover Monday challenge where he used excalidraw for layout planning. This is a free online tool that generates very sketchy-looking drawings. Perfect! I had this concept in my head anyway, so this was perfect timing. This tool drove the whole aesthetic of the final dashboard.

Data

I know words. I have the best words!

I confess that I love some of the ways one can play with British English and I’m a huge fan of Cockney Rhyming Slang. It is extremely creative and constantly evolving. However, it has very many rude words, especially references to body parts, so I had to spend time “cleaning the data”. I hope I didn’t miss anything!

I wanted three sets of words, British English, British Idioms and Cockney Rhyming Slang. I found various sources for each, but I also wanted to provide meanings for the idioms and Cockney, so that I could display them at the end of each round of the game.

I spent many hours getting the dictionaries in the form needed, built simply into four columns of ID, Category, Word (includes phrases) and meanings.

He wrote me a beautiful letter and we fell in love

I also needed a simple list of all letters of the (current) alphabet that are the basis of the sets needed for the game logic: letters in the answer, letters guess correctly and letters guessed incorrectly.

I needed to work out the positions of each letter in the answer with an ever-changing word or phrase length. The answer to this was data densification! See Making waves with Fourier Series, Part 2 for details of how this works.

Game logic

There are three key concepts at work:

• Sets of letters based on the database field [Letter]
• Triggers based on player guessing letters or clicking on buttons
• Counters, for numbers of guesses, wins, losses and the position of the words to be guessed along the pseudo random list of words

Sets

I created two sets, one containing all the words in the answer ([p.Word_Quiz)]) based on the condition: CONTAINS([p.Word_Quiz],[Letter]) and another based on the letters the player has entered ([p.Word_Guess]): CONTAINS([p.Word_Guess],[Letter]).

If you click on the down-arrow on a set, you find the option to create a combined set, which gives you the options as below:

You can see the options nicely shown in the Venn diagrams. The logic here creates a list of incorrect guesses based on all the letters guessed minus letters in the answer. This is used, for building the hangman scaffold as well as for working out when the player has lost

Triggers (dashboard actions)

The game makes use of parameters to act as variables and is moved forward by user actions, i.e. clicking on a letter or a button.

On each letter selection: Update the guessed word parameter by adding the selected letter, [p.Word_Guess]+[Letter]

This triggers some Boolean calculations such as

and

These are then used, for example, to filter when the “New Game?” button is showed as well as incrementing the games won or lost counters. The counters can only be updated on a player interaction, so these are also implemented as parameter actions based on the new game button (which is actually just a sheet with loads of calculated fields under Detail).

The New Game? button itself triggers a number of actions:

• Increment the games won/lost scores
• In case the player has given up, the parameter [p.Give up?] is set to false
• The player’s guess, [p.Word_Guess] is set to empty
• Updates the position in the pseudorandom list of words
• Updates the answer [p.Word_Quiz] from this list ready for the next game

Pseuds Corner

Creating logic that would create a different set of pseudorandom words at the start of each game (each time someone loaded the game on Tableau Public) was a bust. See issues with Tableau Public above. This means that everyone gets the same first word to guess. Very frustrating.

However, I was able to artificially add some player interaction to force the position on the pseudo random list of words to be moved forward by the number of seconds in the current time. This gives 60 variations of the game, which I decided was sufficient.

Wrapping my words around REGEX

The players guess is initially presented as a string of underscores, spaces and the occasional symbol such as apostrophe. The problem was that phrases could be up to 50 characters long and I didn’t want breaks in the middle of words, so I had to work out a simple word wrap algorithm.

I decided I wanted a width of 20 characters, giving me three lines, so the first job was to find the position of the last space before character 20. REGEX to the rescue! This is the first time I’ve used REGEX, so I was quite pleased.

The logic here is:

• Find out how many spaces are in the first 20 characters using REGEX to remove all non-spaces and calculate the length of what’s left, N.
• Find the position of the Nth space
• Use the letters up to this position

As this is being calculated along the position of the letters, which have been densified, I need to also densify this calculation:

I always seem to forget this step when using densification and I waste a lot of time before I remember why I’m not seeing any numbers in my tables!

I then strip the answer of the letters in the first line above and repeat the process.

It has given me a new appreciation of the much more complex word wrapping that we see all the time in flowing text!

It took me four years to paint like Raphael, but a lifetime to paint like a child

I created all graphical elements using excalidraw as I wanted that “drawn on a napkin” look.

I didn’t want a stick figure, but I also didn’t want anything that someone might feel was in poor taste, so I went for a rag doll look. My first attempt looked too much like a baby rag doll, so I lengthened the arms and legs to get a more “adult” rag doll figure:

The image was saved at each stage of the build to represent each wrong answer. My initial plan was to include an x- and y-coordinate for each piece, along with a scaling factor so that everything was always placed correctly, but ended up by simply extending the canvas of each image so that they were all the same size – much simpler!

The letter grid, where the player select their letters, is plotted using row and column numbers that I simply added to the Excel sheet of the letters. To add to the sketchy feeling of the aesthetic, I moved these all around just a little bit using random numbers in Excel:

I added a copy of that letter grid on top with the strike-throughs where the player had guessed a letter, highlighting the correct guesses in ball-point pen blue

Easter Egg

During the development, it was useful to have various fields such as the current correct answer displayed on the dashboard. In the spirit of the game theme of the IronQuest challenge, I decided to keep the answer available on the dashboard, but hidden. Click on the transparent shape close to the exclamation mark in the title to see it!

Now, NOW() means now!

The best we can expect with Tableau is a pseudo random number. In most cases – and certainly here – that it absolutely sufficient. I used the pseudorandom number solution proposed many years ago by Joshua Milligan here.

Pseudorandom numbers require a seed, ideally one that is unique each time you use the formula. The seed in Joshua’s solution is based on time, which on Tableau Desktop is perfect as each second, you’ll get a different randomisation. However, on Tableau Public, the function NOW() is pretty meaningless as data sources are only updated once per day. The result of this in the Hangman game is that everyone will have the same list of words to guess.

I then stumbled upon the Date Updater extension, which now works on Tableau Public. Now, NOW() means now! This extension works by updating a parameter. I created one for this purpose called p.Current_Time and updated various calculations to use this instead of NOW().

To use the extension, simply drag the extension icon onto the dashboard and select the Date Updater extension. Once on the dashboard, the extension asks you for the parameter to be updated. The extension hides itself when the dashboard is published.

The core of my randomization is [Random 0-1], which, obviously, generates a random number between 0 and 1. Below you can see the old and modified versions:

This then feeds into two randomization functions in the dashboard, one to randomize the lists of words and one to pick a random starting point in the list when the game starts (from 0-59 based on the seconds of the current time).

The relationships between the calculations are detailed below. The blue arrows indicate use in a calculated field, green arrows are actions from user interaction.

This all ended up a bit more complicated than I thought I would need. And, on reflection, if I had not struggled so much with randomization and had found the date updater extension much earlier, I’d probably have ended up with something much, much simpler!

Addendum: Now, NOW() means now! added