Happy new year! And just like that, without missing a beat, we’re back at with the first challenge of a new year of #WorkoutWednesday.

Luke is kicking off the challenges this year and he’s got a good one for us. So let’s take a look.

Okay, the first thing I notice about this is that we’re going to be dealing with set actions with this one. Now, when it comes to set actions, I’ve written about them in previous challenges but if you’ve never played with set actions, you need to first read the set actions bible, a post by the undisputed queen of set actions, Linsdey Poulter. Please, go read it.

So one thing that I noticed about Luke’s challenge right off the bat is that row headers are all on the same header regardless of what you’ve clicked on. That is nothing that I’ve seen before so I’m going to need to work through that somehow.

I think the first thing I want to do is create as much of this as I can using Lindsey’s method and see if I can’t just modify it to put in the same format as Luke’s. Maybe that will work, maybe not. Either way, I’m guaranteed to learn something!

It actually looks like this is extremely similar to Lindsey’s example so I’m going to spare you showing you how followed Lindsey’s step by step tutorial because you really need to go read her set actions post. It’s that good.

Okay so I got all the way here:

As you can see, it’s pretty close but by no means meets the requirements. At first I thought maybe I could just adjust the column width but sadly that didn’t work. It’s time to take a peak at a final solution for guidance.

If you’re new to #WorkoutWednesday, please know that there NOTHING wrong with reverse engineering. We, in the community, encourage you do so.

So, I chose to take a look at Curtis Harris’s solution because he and I tend to approach these challenges, and Tableau in general, very similarly. And it turns out that he unioned the dataset to itself! That’s, honestly, all I needed to see to crack the rest.

To do this you need to edit your datasource and then create a union just as you normally would but with the same table name. Doing so will create a new column called [Table Name] and the members of this column are:

• Orders
• Orders$

The dollar is there simply to denote the records that came from the second table in the union.

Okay, now we’re ready to built out the rest of the final solution.

I’m going to put [Category] on rows as we normally would, my set is going to be the same as I already had

So what is Luke wanting us to do here? When I select a [Category] I want to see all the children of said category (which I can already to based on Lindsey walkthrough) but I also want to see each parent. Additionally, for the parents, when I select one, I want to see a down triangle in front of the word and for the other parents, I want to see a right arrow. This can easily be accomplished now that I’ve a duplicate table unioned together because I can return a category from one table and the subcategory from the other

IF [Category Set] and [Table Name]=”Orders” THEN “▼ “+[Category]
ELSEIF [Category Set] and [Table Name]=”Orders$” THEN ” “+[Sub-Category]
ELSE “► “+[Category]
END

I’m dynamically adding a column comprised of two different columns depending on what my selection is. Pretty cool right?

Now, I can duplicate this to get the sales number, except I need to modify the “ELSE” statement because if just say “ELSE [Sales]” then I’ll get the sum of ALL rows from both tables which is no good. So this is what I wrote to get around it

IF [Category Set] and [Table Name]=”Orders” THEN “▼ “+[Category]
ELSEIF [Category Set] and [Table Name]=”Orders$” THEN ” “+[Sub-Category]
ELSE
( IF [Table Name] = “Orders” then [Sales] END )
END

So by default, Tableau will sort these alphbetically and spaces come before ASCII characters so I just need to sort my dimension field descending by my new sales measure and we’re good to go!

Lucky for me, there’s no tooltip to worry about. And it’s just simply formatting from this point!

Hooray for set actions! Click for the interactive version or to download my workbook.

GO FORTH AND VIZ!