Building an Automatic Trivia APP (part 3)

To recap part 1 and 2 of this documentation process, here is how the Data pane looks so far:

This final part of the series will focus on the final UI touches and counting the points for the correct answers.

5 | ADDING CONFIRMATION MESSAGES AND UI AIDS

As mentioned at the end of part 2 post, we got to let the user know that his/her answer was recorded. For that I wrote the following calculated field:

IF [Your Answer Calculation] <> 0 AND [Twitter] <> '@' THEN 'THANKS FOR PARTICIPATING! YOUR ANSWER WAS RECORDED'

ELSEIF [Your Answer Calculation] == 0 AND [Twitter] == '@' THEN  'FILL UP YOUR TWITTER TO PARTICIPATE' 

ELSEIF [Your Answer Calculation] == 0 AND [Twitter] == ' ' THEN  'FILL UP YOUR TWITTER TO PARTICIPATE' 

ELSE ' '
END

Basically here I am testing the Your Answer Calculation parameter: 0 means that there is no attempt to answer so anything different from that I can then show the Thank you message. I am also validating the Twitter parameter so that a real user is answering the trivia (and I know I could validate this even more).

I then created a new worksheet and named it Thanks and put this calculated field in Text shelf. Then I stacked that sheet at the bottom of the Dashboard:

To finalize the UI I wanted to show some icons to identify the correct and wrong answers. Taking advantage that Tableau is capable to work with unicode characters then I wrote the following calculation to assign the correct and wrong icons:

IF [Your Answer] = [Correct] AND [Your Answer] = [Alternatives] AND [Your Answer Calculation] <> 0 THEN '✅'

ELSEIF [Your Answer] = [Correct] AND [Your Answer] <> [Alternatives] AND [Your Answer Calculation] <> 0 THEN '❌'

ELSEIF [Your Answer] <> [Correct] AND [Your Answer] = [Alternatives] AND [Your Answer Calculation] <> 0 THEN '❌'

ELSEIF [Your Answer] <> [Correct] AND [Your Answer] <> [Alternatives] AND [Your Answer Calculation] <> 0 THEN '❌'

ELSE ''
END

This showcases another example of checking a set of conditions to show an specific icon. Also taking advantage that Tableau reads the calculated fields from to down I was able to set the condition for the green check mark to be first if the most restrictive condition happens: The Your Answer parameter needs to be equal to the value in Correct column; The Your Answer parameter needs to be equal to the picked Alternative; And the Your Answer Calculation needs to be different than 0.

I stacked this field in the Alternatives sheet Rows, just after the Alternatives pill. Adjusted the column size to be just the size needed. This is the way it looks when the selected answer was incorrect:

And this is how it shows when correct answer is picked:

Note: I’ve also updated the Color Alternatives calculation when I got to this stage:

IF [Your Answer] = [Correct] AND [Your Answer] = [Alternatives] AND [Your Answer Calculation] <> 0 THEN True

ELSEIF [Your Answer] = [Correct] AND [Your Answer] <> [Alternatives] AND [Your Answer Calculation] <> 0 THEN False

ELSEIF [Your Answer] <> [Correct] AND [Your Answer] = [Alternatives] AND [Your Answer Calculation] <> 0 THEN False

ELSEIF [Your Answer] <> [Correct] AND [Your Answer] <> [Alternatives] AND [Your Answer Calculation] <> 0 THEN False

ELSE False
END

6 | CALCULATING THE POINTS FOR EACH PARTICIPANT PER TRIVIA

The responses are being recorded in a second Google Sheet (via Google Forms) so first I needed to connect to the responses sheet. For that I joined the 2 Google Sheets together via the Trivia field:

To calculate the point I had to pick the first response (by its timestamp) for each Trivia by each participant. The following LOD did the trick for me:

[Timestamp]={FIXED [Twitter],[Quiz] : MIN([Timestamp])}

I put that on Filter shelf of the Rank worksheet. Also had to add it to Context as I will have another LOD to calculate the points of the remaining responses in the view, again, for each Participant and Trivia/Quiz. Here it is:

{ FIXED [Twitter],[Quiz] : AVG(
    IF [Correct]=[Response] THEN 1
    ELSE 0
    END)
}

So now I was able to start building the view, which is pretty simple dot chart:

Then I realized I would need to calculate the total for each Participant to be able to sort the list. That was a simple LOD too:

-{ FIXED [Twitter] : SUM([Points])}

Changed the measure to Discrete (blue pill) and dragged next to Twitter on rows:

And finally formatted the negative PTS number to not show the – sign:

But… hum.. all those dots don’t show the correct and incorrect answers. For that I built this calculation to define the colors of dots:

[Correct]=[Response]

Dragged to color shelf and done!

Here is the final look of my Data pane with the calculations described in the 3 parts of this series:

7 | FINAL LEARNINGS & REMARKS

Here are some things I learned now that TableauTriviaToday is over:

  • The value for NOW() function/calculation only updates when TableauPublic server refreshes the data from Google Sheet. That forced me to have to update the viz every day at 9am to have the trivia ready with new question and alternatives. The TableauPublic server does the automatic refresh once a day, but only at 5pm DC time.
  • Although the Trivia was supposed to be a fun game, some people started to give up once they hit incorrect answers. I feel that is a lost opportunity because (1) it was never a competition and (2) every day was a different question that can be used to learn something new, even if you get it wrong. I got 2 trivia answers wrong myself and I learned from people pointing out when that happened.
  • For the future I would suggest adding a registration process for participants. That way (1) people would be able to select their user from a drop-down menu which would reduce the quantity of typos people commit in manual entry forms, (2) the dropdown filter menu experience is way better than parameters in Tableau when the vizz is opened in mobile devices, (3) it would allow people to have a numeric code to enter to confirm that they are who they say they are, so nobody would enter in the game using other people accounts, and (4) finally it would prevent people typing fake accounts just to try the alternatives before using their real accounts for the correct one (yes, sadly this happened).
  • I am not making the template open to public because it is linked to my account in Google Sheets. If you need help reproducing it feel free to contact me!

One thought on “Building an Automatic Trivia APP (part 3)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s