Let's get interactive!
Recently, this viz made the rounds on social media for beautiful and simplistic user experience
Hey #datafam! Here’s my new @Tableau creation, a line+slope combo visualisation. The slope chart dynamically updates based on whether you click or hover over a date! Thanks @KevinWee90 for the inspo sharing a static chart example from WSJ earlier.
🔗: https://t.co/z9tugLyyPq pic.twitter.com/WUD7gdz3uC— Jacob Rothemund (@jacro28) June 9, 2023
As described, Jacob had created a line chart with a dynamic slope line using parameter actions showing the variance between 2 points. It was such a great viz that Lorna was inspired to turn it into the #WorkoutWednesday challenge for 2023, week 25. Check out my solution below!
MAKE THE CHART
The first part is fairly straightforward. It’s a rolling 6-week average sales chart by category.
MAKE THE PARAMETERS
In order to visually show the user the two points they’ve selected I’ll use reference lines driven through parameters and I’ll create 2 of them
- Anchor date
- Comparison date
To start off I’ll pick to random default dates and I’ll make them interactive in just a bit.
So now the view looks like this and I’m ready to make this dynamic using parameter actions!
ADD PARAMETER ACTIONS
I’ll select “Worksheet” in the menu bar and add a parameter action. Similar to the way we set up our parameters, earlier we’ll need to create one for updating the Anchor Date on Select (click) and another one for updating the comparison date on Hover
This is really coming together nicely! And you can see that we’ve a lovely interactive analysis happening so now it’s time to put the “challenging bits”
ADD SLOPE POINTS
In order to add the slope points we need to isolate the rolling 6-week average sales for each parameter date. Then we’ll tell Tableau to draw a line between those two measures. Yes, we’re going to draw a line between two measures rather than between two dates, which is how Tableau creates a line chart by default. So first things first, let’s isolate each date’s rolling avg sales
//Comparison Date Sales
IF [Comparison Date] = attr(DATETRUNC('week',[Order Date]))
THEN WINDOW_AVG(SUM([Sales]), -5, 0)
END//Anchor Date Sales
IF [Anchor Date] = attr(DATETRUNC('week',[Order Date]))
THEN WINDOW_AVG(SUM([Sales]), -5, 0)
ENDSo what’s happening here? Well, we’re asking Tableau where the parameter date equals the order date truncated at the week level to return the overall average of the six previous week’s sales. Now, you may also be wondering why the ATTR() function is involved. Well because we’re involving a table calculation that is aggregated, we have to aggregate all elements of the calculation. We don’t want to use the MIN() or MAX() because there are only 1 date that matches each. The ATTR() function is how we can aggregate a dimension at a specific partition that we are looking for. Here is a helpful Tableau Knowledge Base article that explains the attribute aggregation further.
Now that we’ve isolated each now we can create our slope line chart by using Measure Values as our secondary axis. So I’ll drag Measure Names to Filters and select the two sales measures we created above. By default, Tableau will won’t be able to connect the dots because it’s trying to draw a line based on date which makes sense most of the time. For this use case, we actually need Tableau to draw the line between measure names. To do that, I’ll drag Measure Names to the path card of the marks card and voila!
Now we can make it a dual axis and synchronize and we’re 90% there! Let’s drag Category to color; now we need to figure out the labels. So this is a % Change calculation that follows this formula:
(X2 – X1) / X1
So we’ll just sub out those variables for our respective Sales values from above. But before we do that there are some extra elements we need to account for. Currently, each sales value is on a different partition and therefore Tableau will return a null value because it can’t do the operation. To correct this, we need to wrap each value in a WINDOW_MIN function. This puts each value on each row so Tableau can do the math. The other thing we need to account for is that we only want the values to appear ONLY on the comparison date. To do that we’ll return the % change on the comparison date rows. The final calculation will look like this:
//% Change
IF [Comparison Date]=ATTR(DATETRUNC('week',[Order Date]))
THEN
( WINDOW_MIN([Comparison Date Sales]) - WINDOW_MIN([Anchor Date Sales])) / WINDOW_MIN([Anchor Date Sales])
ELSE NULL
ENDAnd now we’ve all the pieces we need to complete the dashboard. We just need to do some final formatting and clean up the tooltips.
GO FORTH AND VIZ!
Check out the interactive visualization below
