DISCLAIMER: Tableau is not a Trivia management system… do this at your own risk 🙂
So in September 2019 I decided to launch #TableauTriviaToday which consists of a questions and answers game that anyone in #datafam community could participate via Twitter. But… why?
Well, first because Trivia games are just fun! Second, because it would be even funnier to try to implement its logistics in my favorite tool, Tableau. The whole development was based on building a dataset of questions and answers in Google Sheet, connecting it to Tableau, build the business rules for the Trivia itself in Tableau and, finally, using Tableau to collect the responses from participants.
1 | THE TRIVIA DATASET
As I wanted this to be 100% automatic, I built the dataset in Google Sheets and it had the following columns:
Trivia ID | From | To | Question | Category | A | B | C | D | Correct
Trivia ID – the unique identifier of the question
From / To – The date and time when the question would be active in the dashboard. For example, From 09/01/2019 13:00:00 To From 09/02/2019 12:59:59 (adjusting the timezone to show a new question at 9AM DC time in United States)
Question – The text of the question
Category – Just a fun way to group the questions together under a category
A/B/C/D – The text of the alternatives
Correct – The value for the correct answer
Of course I had to write down each question, their alternatives, trying to find some hidden features in Tableau Public that would be fun to dig out. Next step was to connect the data into Tableau itself.
2 | THE RULES OF THE GAME (p1)
After loading the dataset into Tableau, then the hard work began. The first step was to pivot the Alternatives columns. For that, in the Data Source pane, I selected all 4 columns, right clicked and pivot. Named the new column “Alternatives” and the one with the texts “Alternative texts”.
Note: of course I could have built the dataset that way, but it is way easier to maintain the questions if I have the dataset at the question level in comparison to have it at the alternative level.
Here is my very clean and organized data after just loaded to Tableau:
Back to the idea of having this 100% automatic, the main calculation I had to built in order for Tableau to select the trivia of the day was this one:
NOW() >= [From] AND NOW() < [To]
Translation to humans: Hey Tableau, compare the date and time you get from NOW() function against the From and To dates. If NOW() is between those dates, pick that question!
Put that calculation in Filter, pick True value and you should be able to build the two charts, one for the Question and one for Alternatives.
But, I only wanted to show the question/alternatives when the user filled up his/her twitter handle. For that I had to create the parameter (very simple String one that contains only a @ as value), then create a calculated field that had only the parameter name on it (Twitter Field) and the rule to test it looks like this:
IF ISNULL([Twitter Field]) THEN FALSE ELSEIF [Twitter Field] == '@' THEN FALSE ELSEIF [Twitter Field] == '' THEN FALSE ELSE TRUE END
Translation to humans: Tableau, if Twitter Field is empty, or only has @ or only has a blank, then consider all of that False. If anything else is there, it is True.
Note: I know I could validate this field a bit more, but for the purpose of the Trivia I felt this was enough 😉
Now the first trick part. Before placing the Check Twitter field in the filter card, edit your parameter and temporary make it be True (for example, add your twitter handle on it). Then place the field in filter and mark True. Now you can clear the parameter and you will see that the data on worksheet will go away.
Ops… now there was a problem. I can’t show an empty space in the dashboard. I had to show a text telling users to fill up the Twitter field in order to show the question/alternatives of the day. For that I had to do a sheet swap which would act the opposite way (so when it is True it should NOT show the message). So I duplicated both Question of the Day and Alternatives sheets, reversed the filter rule (by Excluding the True value) and added the informative message in the Text card:
Please note a trick in the Rows shelf. There is a “fake row” measure there. This was something I learned from the Tableau Forums: in order to have a sheet swap working, you got to have a measure in the view. So I created a fake row measure with value 0 on it, made it discrete and dragged to rows in all the sheets I would be swapping and, of course, hide it from the view. Check the Tableau documentation on sheet swapping for more details.
After all 4 sheets were created then I was able to stack their pairs in dashboard horizontal containers and finally (1) I got the informative message when it is needed or (2) the questions when the twitter condition was fulfilled.
TO BE CONTINUED…
At this moment I was felling pretty confident I was on right track here. I had questions, answers, warning messages, a parameter controlling it all… Well, now is when the “fun” really really begins. I had to build a routine that combined parameter actions and URL actions to (1) show if the user was correct (or wrong) and (2) to automatically saving the selection in another Google Sheet. And finally, only giving one point to the user if the first answer was correct. All of these advances calcs will be on the next post!