2019: Week 17 Solution

You can find our full solution workflow below and download it here.

The full solution workflow.

This week there weren’t any specific learning objectives – more just a good chance to apply Tableau Prep to real use cases, such as the issues with choosing a fair voting system. As such there are a ton of different ways of arriving at the final output, none of which are wrong. Apart from perhaps filtering down to 3 lines of data and manually replacing the values with the desired values. That’s kind of wrong and shame on you if you did that.

Regardless, below we’ll still present some of the techniques we used to complete the challenge.

Bordia

Splitting the votes into separate fields

In order to create a [1st Choice], [2nd Choice], & [3rd Choice] field we made use of the LEFT(), MID(), and RIGHT() functions to pull out the first character, 2nd character, and last character of the [Voting Preferences] field respectively.

[1st Choice]	[2nd Choice]	[3rd Choice]
LEFT([Voting Preferences],1) //Gets 1 character from the left of the string.	MID([Voting Preferences],2,1) //Gets 1 character starting from the 2nd character in the string.	RIGHT([Voting Preferences],1) //Gets 1 character from the right of the string.

Assigning and summing points.

We know we want to sum up all the points later, so first we pivot all these new choices fields into a [Choice] field and a [Pivot Names] field. The [Pivot Names] field contains the old field headers. From this we can created a calculated field called [Points] to assign points:

[Points]

CASE [Pivot1 Names]     WHEN "1st Choice" THEN 3     WHEN "2nd Choice" THEN 2     WHEN "3rd Choice" THEN 1 END

Finally we can use an aggregate step to sum up all the [Points] for each [Choice].

FPTP

This is by far the simplest one. Just use the LEFT() function mentioned above to get the top choice from each [Voter] and then use an aggregate step to count the [Number of Records] for each [Top Choice]. Greatest [Number of Records] is the winner.

AV

This is slightly more complicated than FPTP. It begins in the same way, obtaining [Top Choice] and summing [Number of Records], however before declaring a winner we also need to check if they have more than 50% of the votes. To do this we create a 2nd aggregate step which simply counts the number of [Voters] and join this to the outcome of the voting counting aggregate. From here we can create a new Boolean field called [Winner Found?] which contains the following function:

[Winner Found?]

[Votes] > [Total Votes]/2 //Returns TRUE if the given [Choice] has more than half the total votes.

If this is not true for any choice then we need a second AV round where delete the least commonly picked top choice and redo the previous steps. As ‘B’ was least commonly picked first, we can use the REPLACE() function to remove ‘B’ from all the votes and then repeat the previous steps.

[New Voting Preferences]

REPLACE([Voting Preferences],'B',"") //Replaces any instance of the string “B” with nothing, thereby deleting ‘B’ from the voting preferences.

With ‘B’ gone, we should now find that C has 54% of the popular voter and thus is the AV winner.