As #WorkoutWednesday has grown over the years it has been fun to see new people get involved and one of those new people is no stranger to the Tableau community. Lorna Eden is a Tableau Ambassador and starting in 2019 is a regular challenge master for #WorkoutWednesday and week 3 is her first challenge. Let’s take a look.

Wow, Ok. And we can see in her requirements that this only TWO (2) sheets! This tells us that this is a panel/trellis/small multiple chart for the main section and then a duplicate of just one of the teams for the legend.

Let’s continue breaking this down. I see, for each team, there are lollipop charts for each round result with up indicating Home games and down indicating Away games. In between the lollipops we have running total lines for each outcome result possibility. Lastly, there are win and loss percentages for each team overall and then broken out by home and away.

When I’m faced with a potential trellis chart, I ALWAYS build with that dimension filtered to just one. Then I’ll get everything just perfect before adding the trellis calcs and messing with table calcs. Oh yes, there will be table calcs 😉

As I do every week, I’m going to show you how I built this so keep in mind there is usually more than one way to complete these challenges. I’ll also tell you right off the bat, for those who regularly read my #WorkoutWednesday blog posts, this was the first one in a long time that I didn’t need to peek! And I’m still pretty stoked about it.

The first thing I needed to do was create a measure column to convert the result of each round into a numeric value. You can do it Tableau fairly easily.

# [Result] #
CASE [win_lose]
WHEN “Win”
THEN 3
WHEN “Draw”
THEN 2
WHEN “Lose”
THEN 1
END

Now in order to get these lollipops to go in the correct direction for the home & away games. I need to get the negative value for all away games and positive value for all home games.  And I want to be separate columns so there will be two different calculations.

# [Home] #
IF [team] = ‘Home’
THEN [Result]
END

# [Away] #
IF [team] = ‘Away’
THEN –[Result]
END

I think I have what I need to start building now. But first I’m going to filter the [Team Name] to just 1 value and it doesn’t matter which one.

Now, I’ll put [Round Number] on columns as a continuous dimension since it’s an integer. Next, I’ll put the [Home] and [Away] measures on rows next to each other

If you are relatively new to Tableau or have never made lollipop charts before; they are dual axis chart type with one mark as a bar and the other as a circle. So from here, we’ll duplicate each pill on rows so we’ll have four pills total.

Looking good so far. I think I’ll go ahead and color them based on the [win_lose] field. Since I’m going using this on 4 marks, I’ll use one of my favorite tricks – “Default Properties.” For each field, you’ll have several options depending on whether it’s a dimension or measure. Since [win_lose] is a dimension, one of the options under default properties is “Color” This is great for setting it once and any time this field used on the color shelf it will be consistent.

From here it’s the same color dialog box you are used to and it looks like Lorna is using a selection of gray colors from the Seattle Gray color palette. I’ll make my selections and add this field to color of each of the four marks cards.

Now, this looks really good and I’m ready to deal with the running total lines. This is one of the [many] great things about Tableau. I can keep adding as many measures to the rows shelf as I feel like and each time I’ll get a new marks card. So if we look at the original I have a running sum for each result. Then I can utilize Measure Names/Measure Values for my additional mark card. The calculations I need to create look like this

# [Win] #
IF [win_loss] = ‘Win’
THEN 1
ELSE 0
END

Then I create two additional calculations exactly like this; one for ‘Loss’ and another ‘Draw’ And once I’ve done that I can select only those new calculations as the Measure Names. Measure Values will go onto rows right smack in the middle of the four pills. Then Measure Names goes on color of that new mark card.

I’ll tell you what, this is coming together nicely. Now the last major thing I need to complete for this sheet is the title area. Now, let’s remember that we’ll eventually be doing this as a trellis chart and therefore we cannot use the worksheet title for this. So that means that we need to use yet ANOTHER pill on rows in order to manipulate the label shelf of the marks card.

So with the text in the upper left corner of each pane I believe that I need to put a mark on the first round number ONLY. There are a couple ways to do this. I chose to use a table calculation, the FIRST() function in fact.

# Team Label #
IF FIRST() = 0
THEN 1 
END

I’m going to put this field as the first pill on the rows shelf and change the mark type to circle.

Lookie there! Now I’ll reduce the size to the smallest possible and reduce the opacity to 0% to “hide” the mark. But I still have the mark card and can utilize the label shelf to add all the necessary fields.

The percentages are based on the total number of games per team

# No. of Games #
{ FIXED [Team Name]: SUM( [Number of Records] ) }

Then I just need to aggregate:

• Total wins
• Home wins
• Away wins
• Total Loss
• Home Loss
• Away Loss

And then create six percentages for the label

At this point, I’ll format the tooltips. And with that, I’m ready to set up the trellis map. And it was serendipitous that this challenge was coming the same week that Kevin Flerlage posted his INCREDIBLE breakdown of just how the math behind the trellis map calculations. It’s really awesome. you should go check it out!

But before I do this, don’t forget we need to make a legend for our dashboard and since this sheet will suffice, I will go ahead and duplicate this sheet and do the trellis on the new sheet.

Now, to make this work, I need to move the [Team Name] field to detail and put my trellis calcs on rows and columns.

Boom! Great challenge, Lorna!

GO FORTH AND VIZ!