I remember it vividly. Waiting to go into a meeting, I was talking to a couple of guys in my team about #WorkoutWednesday:

It’s a chance to practise “real-work” scenarios. The first week was tough, but this week looks like a doddle

I retract that! Lots of it had me stumped. I had to research one component (but got a great result), blundered through a couple of other bits, and gave up at the final hurdle (and downloaded Emma’s workbook). Here’s how it went.

I started on steady ground. The line chart is just a dual axis line / circle combination, with a dot on the end to denote the last [Sales] date. That part, using the LAST() function, has already been picked up on a recent sparklines post on this blog, but note that Andy Kriebel’s post about this challenge highlights a much simper method.

Perhaps the fiddliest part of this sheet was the Title, which had to report the final month of sales. Even then, it was just a quick LoD calc onto Detail, changed to Continuous Month and then referenced in the title itself:

Screen Shot 2017-01-13 at 19.15.04.png

Screen Shot 2017-01-13 at 19.16.45.png

With (Dot) being the name of the Circle component of my dual-axis:

screen-shot-2017-01-13-at-19-16-54

In Andy K’s take on the challenge, he elected to use his newly discovered “Most Recent” label trick to get the dot on the end of the line, but the need to show the last sales value means that the above calc needed to be created in his viz anyway. Having it as a separate mark obviously allows you to control size, shape etc. so that’s a bonus.  It might be handy if, in a future release, Tableau allow us to control the size of Label marks, because then we could achieve a controlled “dot on the end” without the faff of a dual-axis.

Onto the Profit Bar sheet, and here I learned about something I’ve had a vague awareness of, but never really looked into. I set things up and noticed that Emma’s viz seemed to have limited the number of sub-categories returned to 5. OK, easy – Top N filter?

screen-shot-2017-01-13-at-19-30-04

If I select Furniture:

screen-shot-2017-01-13-at-19-31-00

Weird. There are four Furniture Sub-Categories, why do I only have two? Same sort of stuff happens with the other two Categories. Hmm. What’s happening? The filter is only returning Sub-Categories for each Category where that Sub-Category is in the overall Top 5. I couldn’t really understand this, and resolved it by creating a RANK(Sales) calculation, which I put on the Filters shelf:

screen-shot-2017-01-13-at-19-51-59

screen-shot-2017-01-13-at-19-52-24

That worked, so it’s nice, but Emma did it a different way and it’s something I wanted to look into. The difference might look subtle, but it’s not. Here’s how my Filters shelf looks:

screen-shot-2017-01-13-at-19-55-35

Here’s Emma’s:

screen-shot-2017-01-13-at-19-55-43

Now I’m using a different version of the Superstore dataset, but focus on my Category and Emma’s Product Category. What does the difference in blue versus grey represent? Emma’s is “added to context”, mine is not.

I’ve heard about it, but never actually considered what it amounts to. What I know in this case is that Emma could apply a bog-standard Top N filter and it worked, whilst I couldn’t. What does this mean “context” stuff mean then?

As usual, Google tends to return Tableau’s own online help resource, and there’s generally a reason: it’s really good. If you (like me) don’t didn’t know how context filters operate, read this. Just to brand the learning into my brain, I’ll demonstrate the concept like this. Here’s an EU Superstore view:

Screen Shot 2017-01-13 at 20.31.31.png

Nice and basic. The filters appear as they do as I have all Categories selected and a Top N filter of 3 on Sub-Category:

screen-shot-2017-01-13-at-20-33-03

See how there is one Furniture sub-category and two Technology sub-categories? What if I set the Category filter to Office Supplies?

Screen Shot 2017-01-13 at 20.35.02.png

Zilch. What if I now add the Category filter to context?

screen-shot-2017-01-13-at-20-35-38

Hooray! Working as expected! As is often the case, I expect 95% of people other than me already knew this, but this is a significant thing for me, and exactly the reason why #WorkoutWednesday is terrific. And I’m not done yet! Two more to go, firstly: the logos.

In the second paragraph, I mentioned that I needed to research one element, and that element was the logo swapping. I could not have had less of a clue how to do this if I tried, so I just searched and found a great solution from Shawn Wallwork. I copied this step by step, and felt almightily pleased with myself (and grateful to Shawn), because it worked immediately as the guide is so clear.

screen-shot-2017-01-13-at-20-42-43

I learned a bunch from this, and having downloaded Emma’s workbook to investigate the only bit I hadn’t resolved myself / via research, I wanted to compare methods.

Emma did it differently, and fortunately she explained in her calculation comments how it was done.

screen-shot-2017-01-13-at-20-45-09

So she is using Index() on Filters and excluding True instances. Time to check out the considerately commented calculation:

screen-shot-2017-01-13-at-20-47-12

If I write an inline INDEX() calc onto Rows (Compute Using Product Category) and change marks to Text, I can validate that the calc returns a 1 for the individual Categories, but nothing for (All).You can then just allocate a custom shape to each Category, knowing that the (All) option will return nothing.

I like it. It’s arguably easier than Shawn’s solution, but I *think* I prefer Shawn’s method as for some reason it works better in my head.

The last bit stumped me. Big time. How do you set the overall dashboard title as per the challenge?

The dashboard title should change to say ‘All Departments’ when you select ‘All’ in the filter

I tried this:

screen-shot-2017-01-13-at-20-58-18

No joy, as when (All) is selected, I just get “All” in the title. I don’t understand why. It seemed a reasonably logical calculation, but I’m obviously missing a trick. I tried variations of this but got nowhere, and that’s the point where I downloaded Emma’s workbook to see the light:

screen-shot-2017-01-13-at-21-01-43

OK. This obviously leverages the same concept as was applied for the logos. In a roundabout way, it doesn’t seem wildly different to my calc, and it’s in these situations where I’m a bit “meh!” – I cannot see why one method works instead of the other.

Maybe I’ll investigate how a couple of others did this. I noted that Andy Kriebel (of course) and Miguel Cisneros had contributed relatively early, and had tweeted in my timeline about stuff, so I downloaded their workbooks. Andy tackled the title thus:

Screen Shot 2017-01-13 at 21.08.01.png

It’s sort of similar to Emma’s, but as with Shawn’s logo trick, the additional apparent complexity works better in my head. To me, this calc says:

Ignore Product Category on Filters and count up the distinct instances of Product Category in the view. If it’s one, tell me what Product Category it is, otherwise just state All Departments

Maybe it’s the fact I can talk myself through this as I can reference specific Dimensions and aggregation methods, rather than soulless “INDEX()” functions, and that makes it easier for me to comprehend. Andy then added that calc to the Title, and tacked “Sales Report” onto the end.

Still, is there another way? What has Miguel been up to?

screen-shot-2017-01-13-at-21-14-25

Interesting. It’s similar to Andy’s, but without the complexity of the LoD element. But what about the “Sales Report” end to the string?

Screen Shot 2017-01-13 at 21.19.27.png

It’s just tacked on to the end of the Label. As is often the case, that’s three different ways of achieving the same end product.

EDIT! And there are at least four ways to do this. Adam Crahen shared his approach, and it’s a beauty:

AC.PNG

I figure that if you try to take as many of these variations in as possible, then surely one or two will stick. Eventually. That’s why I’ll keep downloading different workbooks to see how different people tackle the same challenge. Love these #WorkoutWednesday challenges so far!

My final effort, which is a mish-mash of my own work and elements of the other submissions can be viewed on Tableau Public here.

Advertisements