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

# Educational Brain Teaser: Scatter Plot+

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.

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+

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.

Łukasz

• ###### 2. Re: Educational Brain Teaser: Scatter Plot+

Simon, nice teaser, thank you.

Yours,

Yuri

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

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

• ###### 4. Re: Educational Brain Teaser: Scatter Plot+

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.

Yours,

Yuri

• ###### 5. Re: Educational Brain Teaser: Scatter Plot+

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+

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]

OR

[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 '⬤'

END

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)

I created the following 4 calculations

[Maths - Fake Ref Line +]

{MAX(ABS([Math]))}

[Maths - Fake Ref Line -]

{MAX(ABS([Math]))}*-1

[Physics - Fake Ref Line +]

{MAX(ABS([Physics]))}

[Physics - Fake Ref Line -]

{MAX(ABS([Physics]))}*-1

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

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+

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.

though it's of little value,

just to show up :-)

Yours,

Yuri

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

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.

Thanks,

Shin

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

Very smart Yuri!

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

• ###### 10. Re: Educational Brain Teaser: Scatter Plot+

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+

That was amazing brain teaser,had fun solving it.