12 Replies Latest reply on Mar 18, 2019 6:11 AM by Simon Runc

    Educational Brain Teaser: Scatter Plot+

    Simon Runc

      Inspired by Łukasz Majewski resurrection of the Educational Brain Teaser a few weeks back (check his out here Educational Brain Teaser - Custom Territories ...well worth taking a look)...I came across an interesting problem, which seemed a perfect fit for a Brain Teaser.


      Attached is a packaged workbook (just blank with the data, and parameter you'll need; in 10.5), and also just the data (in Excel) if you can't open 10.5 files.


      The Problem

      We have exam results for Maths and Physics, over 3 different exams (Exams 0, 1, and 2) for 9 Students. The Exam Numbers (0,1,2) are in the order the Exams were taken, and what we need to create is a Scatter Plot for Maths vs Physics Results for Exam number 0...simple so far!


      Now on selection of a Student (identified by Student ID), via a parameter, we need their Exam 0 result highlighted by a black dot, and also show their other 2 results (Exams 1 and 2), with a line connecting them together.


      Here is a GIF showing what the final viz should look like and how it should work.


      Exam Results.gif


      There is no need to do anything to the data (...you can if you really can't work out a way with it in it's current form, but there is no need). As a bonus, without fixing the X and Y axis range manually, how did I get the Axis in the above GiF to not keep resizing, as different students are selected.


      To me this problem encompasses 2 of the key themes I think about when working with Tableau.



      For the impatient ones, here is the solution in the thread.


      Re: Overlaying a path onto a scatter plot



      Please post your best attempts, comments, where you get stuck...etc. in the thread; There (as always) are a few ways to solve this problem, so will be interested to see how you all approach it!


      I'll post a written up solution at the end of next week...Happy Vizzing!

        • 1. Re: Educational Brain Teaser: Scatter Plot+
          Łukasz Majewski

          Hi Simon,


          Interesting - I wish I had time to crack this one

          Why is this not in Viz Talk by the way? It disappeared from "Resources" drop down, I guess.




          • 2. Re: Educational Brain Teaser: Scatter Plot+
            Yuriy Fal

            Simon, nice teaser, thank you.





            1 of 1 people found this helpful
            • 3. Re: Educational Brain Teaser: Scatter Plot+
              Simon Runc

              Good work Yuri. Yes this one is one of those fun little puzzles!

              • 4. Re: Educational Brain Teaser: Scatter Plot+
                Yuriy Fal

                BTW, it looks like we've navigated the different routes

                (you've taken it easy, i've got it a little bit complicated).

                I'll post my workbook following you.




                • 5. Re: Educational Brain Teaser: Scatter Plot+
                  Simon Runc

                  Haha...I expected nothing less from yourself!!


                  I'll post my solution on Monday, and will be intrigued to see what you got up to!!

                  • 6. Re: Educational Brain Teaser: Scatter Plot+
                    Simon Runc

                    Hope you all had fun with this one!


                    The reason I thought this would be fun and educational is that is encompasses 2 key considerations, when creating the "Viz you want" in Tableau. These are;

                    • The number of marks we want drawn, and how we can control that
                    • The options at our disposal for making a mark on the canvas


                    Firstly lets look at the number of marks we need.


                    So we only need plotted here the Exam 0 results, for each of the 10 students, and also Exam 1 and 2 for the selected Student. So we want 12 marks. We can filter our data down to this, using this formula


                    [Filter to Required Rows]

                    //Only need marks for Exam 0 OR Exams 0,1,2 for Selected Student

                    [Student ID] = [Select Student]


                    [Exam Number] = 0


                    Where [Select Student] is our parameter, used to select the student of interest


                    We can then use this on the filter shelf, along with Exam Number and Student ID on the Detail/Colour Shelf in order to get the number of marks we want

                    The Exam Number Size is just a calculated field I used to make the Exam 0 marks larger

                    [Exam Number Size]

                    IF [Exam Number] = 0 THEN 1 ELSE 0 END


                    So far so good!


                    Now we need to draw the lines connecting Exam 0,1, and 2 for a selected student. We are going to need to do this on a second axis (we can use CTRL and drag on the AVG(Maths) pill to create a 2nd version of it), however we want the number of marks (level of detail) to be different here, as we only want the 3 exams for the selected student. To do this I created this calculated field

                    [Selected Subject (1/0)]

                    IF [Student ID] = [Select Student] THEN 1 ELSE 0 END


                    and bring this into the detail for that Axis. However we do still one mark we don't want (the one marked with a red square in the image below). This is the aggregated average of all the non selected students Exam 0 result.


                    We can remove this by Hiding it (we can't filter it out, as these rows contain the Exam Zero results needed for the left side of out scatter). We can hide it from the Colour Shelf. Another method (which I used in the thread) was to colour this mark white so it's invisible!


                    Once we've removed this, we can change the mark type to a line, and bring Exam number onto Path (as the Exams are numbered 0, 1, 2 Tableau will draw the lines as we want)



                    We can then dual the Axis (and Sync them), and we are almost there


                    However we don't have the smaller black circle for Exam 0 for the selected student. This brings us on to the "The options at our disposal for making a mark on the canvas" part.


                    We don't have any rows of data left (we've already used the Exam 0 selected student for the larger gray mark, and the line), and we've used up both of our dual axis...so it's at this point I run through all the way Tableau lets us mark a canvas and see if I can think of way to get one of these to do the job. Here I think about Reference Lines, the Mark Types (especially Shapes) and Labels (including the use of custom formatting or Ascii symbols/shapes).


                    In this example I realized that a centered label goes right over the middle of a mark, so created this calculation and brought it onto the label

                    [Circle Mark Label for Exam 0/Selected Student]

                    IF [Student ID] = [Select Student] AND

                        [Exam Number] = 0 THEN '⬤'



                    As there is no else part, any other marks (that aren't the selected student) equate to NULL and Tableau doesn't plot or label NULLs. There are a whole host of very useful Ascii characters that can be used in creative ways.


                    So now we have the result we wanr



                    Bonus Question...how do we maintain the Axis Range (to the maximum extent of any point, even if it's not currently in view), and keep the symmetrical?


                    We can do this by creating calculations to determine the extent of the data (for Maths and Physics), and use these to generate reference lines and then turn off the Line and Label for the reference line. I did a recent 5 min talk on this at the last Tiny Tableau Talk (meet up)


                    Tiny Tableau Talks Feb Talk 4 Simon Runc - YouTube 


                    I created the following 4 calculations

                    [Maths - Fake Ref Line +]



                    [Maths - Fake Ref Line -]



                    [Physics - Fake Ref Line +]



                    [Physics - Fake Ref Line -]



                    I've used the ABS MAX, so that my Axis remain symmetrical, which (I think) is visually better for this kind of comparative/quadrant analysis. As these are FIXED LoDs they are computed before any regular dimension filters are applied, so remain the Global Min and Max, regardless of the data in view


                    We bring them onto the detail shelf, so we have access to them in the reference line set up....here's what it looks like before we set the reference line, lines to None



                    One final super-bonus I thought of was use the Exam Number on the size tile for the lines, which has the rather nice effect of giving some, extra, visual direction to the path


                    Exam Results Final.gif


                    Hope you find that useful, and can see how we can "generalise" the thinking here to solve many Tableau problems.


                    Will be interested to see any other solutions, as I can think of a few other ways we could achieve a similar result.

                    4 of 4 people found this helpful
                    • 7. Re: Educational Brain Teaser: Scatter Plot+
                      Yuriy Fal

                      Hi Simon,


                      The Text Label instead of a black dot

                      is what i've missed completely

                      (i'm not Rody after all :-)


                      So i went a complicated route,

                      which is to densify (pad) the data

                      to draw the additional "Exam" Marks --

                      and to do 'All-Table-Calcs' on top of that.


                      Please find the attached,

                      though it's of little value,

                      just to show up :-)




                      2 of 2 people found this helpful
                      • 8. Re: Educational Brain Teaser: Scatter Plot+
                        Shinichiro Murakami

                        Thank you, Simon


                        I was lazy enough and here is another approach with not keeping the line gray.

                        I tried a little bit, but it seemed like keeping line gray would take longer and indeed, there were tricky part to use "text"...


                        Mine might be too easy, but for beginners who has similar requirement.


                        With change the color to single thin ==> thick and size, it's relatively easy to wee 0 to 2 historical track.




                        1 of 1 people found this helpful
                        • 9. Re: Educational Brain Teaser: Scatter Plot+
                          Simon Runc

                          Very smart Yuri!


                          It's all about getting that extra mark...by hook or by crook!!

                          • 10. Re: Educational Brain Teaser: Scatter Plot+
                            Simon Runc

                            Thanks Shin,


                            Yes from a "doing what it's supposed to do" perspective, this does the job. If I had this requirement for client work, this is probably the option I'd go for (is the "juice worth the squeeze" as they say, for the "extra black mark" version?)


                            Good solution.

                            • 11. Re: Educational Brain Teaser: Scatter Plot+
                              Riday Patel

                              That was amazing brain teaser,had fun solving it.

                              • 12. Re: Educational Brain Teaser: Scatter Plot+
                                Simon Runc

                                Glad you enjoyed it ...it's all about the mark count!!