As a part of my 2018 #Vizgoals, I’ve committed myself to completing all 52 weeks of the #WorkoutWednesday challenge. This weekly challenge was started by Andy Kriebel and Emma Whyte in 2017 and is being carried on by Rody Zachovich and Luke Stanke.

The mission is simple: Test your technical Tableau knowledge

I’ve decided that the best way for this learning to stick with me is to blog about my thinking/learning process each week.

The Challenge

Here is what we are to recreate:

With the following helpful hints/requirements

• Data should be aggregated to the month.
• Sales should be for a rolling 3 months
• Order of the categories is dependent on the values at the parameter date. Larger values are stacked on top.
• Pay attention to the color of #WorkoutWednesday title
• Do your best to align the parameter with the x-axis. This way the controller is aligned with the reference line.
• Match the axis labels and axis (non)titles.
• Match x-axis major and minor ticks
• Match the label format.
• Match the colors using the Superfishel Stone color and Seattle Gray palette.
• Your chart should range from 2014-Mar-01 to 2018-Mar-30.
• Make sure your axes are synchronized (of course).
• Share your your visualization with the parameter set to 2018-Feb-01.

That’s all we’re given.

So what I like to do when I start one of these challenges is to look at/play with the visual and pick out all that I can. The idea is to separate “the real challenge(s)” or “the learning” from the stuff I already know how to do. For this one, in plain English, here’s what I get out:

The Known

• A stacked area chart with a rolling 3 month table calc
• Custom axis formatting
• A parameter-driven reference line
• Line end mark labels
• Tooltips are off
• etc…

The Unknown

• As I play with the parameter I realize that the categories dynamically sort in descending order based on sales of the parameter date (mark label)
• Ooohh! And the #WorkoutWednesday title text changes colors based on dynamic sort mentioned above
• Luke told me in the reqs that this is a dual axis, not sure how yet.

Okay let’s set up a sheet by crossing all the “knowns” off the list

Let’s pump the brakes and just slow down now. Let’s take a moment to take a closer look at the original. The first thing that I’m struggling with is how the area colors change to grey after the ref. line. I know there’s an option for “fill above” when editing a reference line but that doesn’t work because that would fill in the entire area including the white space. Hmm?

Well one of Luke’s hints says “sync the dual axis”

Ohh, yes! That makes sense duplicate the axis and move [category] from color to detail to keep the lines. Then I can change that color to the grey. Cool!

To do this, I’m going to need to create a new measure that first compares each month/year of order date to the month date of the parameter, if less than then do the 3-month sum else null, thus stopping it at the ref. line.

IF max([Order Date (Months)])<=DATETRUNC('month',[Select a Date])
 THEN window_sum(sum([Sales]),-2,0)
 END

BINGO!

Now we need to do the mark labels for the end of the colored lines. That can be just a simple IF statement similar to the one I just created. Instead of >=, I’ll just change it to = and I’ll only get the three values.

Now for the dynamic sort…

Hmm? I can’t use a table calc to sort and I can’t use table calc nested within an LOD.

But wait! I don’t need the whole table calc, I just need to get the values for the first three months closest to the parameter month. And I can fix that to the category dimension.

Something like:

{Fixed [Category]:sum(

IF ([Order Date (Months)])<=DATETRUNC('month',[Select a Date])

AND [Order Date]>=DATEADD('month',-2,[Select a Date])

THEN [Sales]

END)}

That’s it for this sheet

Just need to add the title and put it on a dashboard. So I think I’ll need to create a new sheet and use the same sort field I just created and use that as a top N filter =1 for Category. I will create a simple text field “#WorkoutWednesday on the label shelf and put Category on color.

Here’s the final product. Click to interact.

Cheers!