Each Sunday Andy Kriebel (VizWiz) and Andy Cotgreave (Gravy Anecdote) post a link to a chart, a file with the underlying data. Then people build makeovers and share them over twitter and pinterest. Find more info here.
This week’s starting point:
Tooltips are absolutely essential for his week’s original, so go check it out!
I love Figure Out the Lyrics quizzes on Sporcle, so my first makeover was a simple bar chart cheat sheet of words by number of songs. It was okay, but not cool enough to merit a post. But THEN I decided to re-build the wheel, and turn this data set into MY OWN QUIZ BANK. Crazy!
Figuring out how to do this got really tricky; grids, conditional formatting, and mutli-value parameters were all thoroughly googled. I feel like I did a nice job because oodles of noodles went into figuring out how to build it, but the final summary is delightfully tidy!
Color choices are not my normal taste. I picked them to reference the original, and this viz is goofier than most. Hit or miss?
Play all 100 quizzes on my makeover here!
Here’s the step-by-step:
- Rename [Word Count] as [Word Index] (count was a confusing word choice for me!)
- Turn [Word Index] and [Song Rank] into discrete dimensions
- [Song Rank] onto filter; [Song Rank] = 1, [Word Index] onto Column and Row, [Word] onto Text mark
- Create a Grid!!!
- Separate tabs that filter on [word index] works but is MAJORLY INEFFICIENT
- Bins DO NOT WORK
- Groups work but require manual setup of 40+ groups I’M TOO LAZY FOR THIS
- calcs do it with MAGIC OMFG
- Column – Edit in Shelf – [Word Index]%10
- Row – Edit in Shelf – int([Word Index]/10)
- Create Parameter [Guesses] (string, empty, all) & Show Parameter Control
- Set [Guesses] = ‘The a’ for now
- Create Calculated Field [Show Guesses]
- CONTAINS (LOWER([Guesses]), LOWER([Word]))
- [Show Guesses] onto Color mark
- Edit colors, Gray palette, Set True to the darkest color, and False to the lightest
- Format menu > Shading, Set Worksheet to the same color as False, hiding the words not in the parameter. Set Banding and Headers to none. (The headers will be hidden, you don’t have to, but if you don’t your hidden headers will be UGLY.)
- Adjust sizes, etc.
- Create Dash
- Add a container for [Song Rank] and [Guesses] and then fix the height to minimize distortion
- Hide headers, add a title, etc.
- Go back and fix the tooltip so that it stops giving the answers!