Continuing on from last year, August is #WorkoutWednesday takeover month, with challenges posted by invited guests. First up, for Week 32 was this challenge from Klaus Schulte to create a step area chart WITHOUT the use of data densification (ie duplicating the data set).

WARNING! This blog describes my approach to solving the challenge which I can only describe as trying to find your way through a maze for the first time – you take a few wrong turns, double back on yourself several times, but eventually get to the the centre, feeling quite satisfied. However, you later find, that if only you’d turned right rather than left to start with, there would have been a much quicker direct route to get to the end. My approach to this challenge ended up being quite cumbersome due to the path I started down. Klaus’ solution is much simpler 🙂

There were minimal clues in the challenge requirements, apart from the fact that table calcs were going to be required and, as stated, you couldn’t duplicate the data set. So I started by viewing Klaus’ solution on Tableau Public to see what I could glean by mousing over the chart. From doing this I understood that

1. The stepped area chart had to be managed using a single axis, since the dual axis would be needed for the ‘bonus’, highlighting the max & min drop.
2. The stepped area chart was indeed an ‘area’ mark type (and not carefully disguised bars or similar), since when you hovered over a mark, an indicator appeared at both the top and bottom of the chart, and if you selected anywhere, the whole section was highlighted

Stepped Area Chart

So where to start…. I decided to watch the relevant section of the TC Europe talk Klaus referred to in the challenge intro, in which he showed a way of doing this by duplicating the data, to see if there were any clues there. By doing this, I understood that

1. The difference between an area & line chart was that with an area chart, there is no Path shelf, so you can’t control how the marks will be joined up. An Area chart will just join the marks from left to right. This was already familiar, but the reminder was useful to jog the memory.
2. Whilst Klaus’ solution was using a duplicated data set, he incorporated a method of offsetting the point to plot forward (+0.499) or backward (-0.499) depending on what data set was being referenced. This reminded me of something I’d done in a previous #WorkoutWednesday challenge a long time ago, where I needed to display a side by side bar chart

So I figured I needed to find a way to ‘expand’ the data to give me 2 rows per month by using some offset logic, which I did by creating new fields

Month Position To Plot

DATE(IF MONTH([Order Date]) = 12 THEN DATETRUNC(‘month’,[Order Date])
ELSE
IF DAY([Order Date]) <=15 THEN DATETRUNC(‘month’,[Order Date]) ELSE DATEADD(‘month’,1,DATETRUNC(‘month’,[Order Date]))-1 END
END)

This sets the date to be 1st Dec if the Order Date was December (as I didn’t want an extra point), otherwise, if the Order Date was before 15th of the month, the date would be the 1st day of the month, otherwise it would be the last day of the month.

Sales In Month

{FIXED [Category], DATETRUNC(‘month’, [Order Date]): SUM([Sales])}

I needed this LoD to store the total value of the monthly sales. Without it, due to my Month Position To Plot field above, the Sales would only show the partial sales in the month.

This made my data look like

Plotting this on a chart, setting the [Month Position to Plot] to be a continuous (green) pill set to the Day level, and choosing the Area mark type, and I got a stepped area chart 🙂 The days where the marks are plotted end up being so close together, that the vertical line looks straight to the eye.

Side notes

1. I initially created a numeric field based on the month’s number offset by +/- 4.99 to plot, instead of the date field [Month Position To Plot], assuming I might be able to use custom formatting to show the date on the axis. When I eventually found this wasn’t possible, I confirmed the axis on Klaus’ solution was actually associated to the main view, and not part of another view carefully placed on the dashboard. I found this by clicking on the small + icon that appears when hovering by the date. This isn’t a feature you’re encouraged to use, as it makes the view go all bonkers 🙂
2. I used the alias feature to display the Category field in uppercase, as there were only 3 values, so it wasn’t overly cumbersome to type them in.
3. The date axis was fixed to a suitable start & end to allow a decent amount of spacing between each category. I also set the axis tick months to start on 01 Jan 18 and recur every 11 months, so only Jan & Dec would display.

So this didn’t seem too bad – I’d got a step area chart using a single axis – everything was all good. On to the next bit…

Tooltips

The tooltip needed to show the category, the month/year, the sales value and the value of the change from the previous month.

Because of the way I’d gone about structuring my data, to work out the change I actually needed to get the value of the next row, and find the difference between the two. This may sound counter-intuitive since we’re asked to find the change from the previous month, but it was necessary due to that path I had chosen to build my area chart 🙂

Sales Next Value

LOOKUP(SUM([Sales In Month]),1)

This table calculation needed to be applied for each category (ie in the Edit Table Calculation window, all dimensions except the Category needed to be selected in the Compute Using section). The difference was just then comparing the two fields

Sales Diff

[Sales Next Value] – SUM([Sales In Month])

But whilst this was getting me the numbers I needed, I was stumbling into another issue.

On Klaus’ viz, the point at the top and bottom of each vertical line, represented the same month, whereas the point at the start and end of each horizontal line was the same month for me 😦

So I had to 1) build another calculated field to shift the months so I could label the points correctly, and 2) since every row didn’t show the Sales Difference (the rows with $0 above), I need another table calc to get the info relating to the previous row. This gave me

Labels

So having sorted the tooltips, my focus now changed to the labels that were displayed which were

• the points which had the biggest rise (the maximum change) or biggest fall (the minimum change) per category
• the points at the start and end of each year (if they didn’t correspond to being the max/min change above).

You can use WINDOW_MAX() to find the maximum difference for each category, and then compare this to the actual difference to find the matching row. However, yet again, due to my set up, the matching row wasn’t on the right point, I had to shift it again using a LOOKUP() function to find the previous row.

I had to repeat the exercise using WINDOW_MIN to find the minimum difference, and then apply similar logic using FIRST() and LAST() to identify the start and end points. It was all very convoluted and involved multiple table calculations, but it produced the output I needed. (I’ve included my data table sheet in my published workbook, for reference).

Highlighting biggest rise and smallest drop

So now onto the 2nd part of the challenge. As I mentioned at the start, I knew a dual axis would be involved to achieve the lines.

My initial thought was to use the ‘waterfall’ type technique involving a gantt mark type, sized by the difference.

However, I spotted that the blue and red vertical lines were thicker than the grey vertical lines which were also visible, which meant gantt wouldn’t be an option, as I couldn’t use the Size shelf for two purposes.

I recalled the fact you can place multiple measures on the same axis and use the line to join them up vertically (I did this creating a candlestick chart for #WorkoutWednesday2019 week 13, solution here), so started trying this out. But in this case I couldn’t just use any existing fields, as I’d ‘duplicated rows’, so had use some logic to create further fields

Sales in Month Dual Axis

IF [Sales Diff]<>0 THEN SUM([Sales In Month]) END

Sales Next Value Dual Axis

IF [Sales Diff] <> 0 THEN [Sales Next Value] END

These were basically just giving me values for every other row (so de-duplicating the data I’d expanded). I could then plot these two measures on the same secondary axis, remove Measure Names from the Colour shelf, and by setting the mark type to line, I got what I needed. I applied logic to create a field to set the colour based on whether the change was the max or min or not, and also created a field to set the size again based on whether it was max/min or not.

I then applied the dual axis function, not forgetting to synchronise the axis, tidied up the formatting and added to a dashboard. My published version is here.

But you don’t want to do any of that 🙂

You want to have a read of Rody Zakovich‘s blog from October 2017 – Create a step chart in Tableau without data modelling, and just change the mark type to Area…all the vertical dates are the same, so no messing around to get the data for the labels/tooltips …. simples really… *sigh*

Happy vizzin!

Donna