Continuing with ‘Community Challenge’ month, it was the turn of Will Perkins to set the challenge for this week; a challenge inspired by Google’s stock tracker.

By interacting with the published solution and reading the requirements, I deduced that I was likely to need 3 sheets – 1 for the Region headings & KPIs, 1 for the trend line chart, and 1 to drive the timeframe selections. The trend line chart looked like it was going to involve a dual axis combining a line and and area chart, along with ‘filled’ reference bands, although exactly how it would work I wasn’t entirely sure initially. Finally, there was going to be some ‘parameter actions’ action along with the ‘true = false’ trick to ensure selected marks didn’t remain highlighted.

But before we can tackle the actual chart build, we need to nail some of the calculations involved.

Identifying the date range to highlight

The intention of the chart is that on initial load, it has highlighted the timeframe for the last 14 days up to ‘today’. As this chart is being built with a static data set, which only has data up to the end of 2021, I chose to ‘hardcode’ my ‘today’ value into a parameter. This is so that in a year’s time when I might look at this again, I won’t be presented with a broken looking viz.

pToday

Date parameter defaulted to 20 Sept 2021

The user also has the ability to highlight/select dates on the chart itself, which will define a start and end date range. So we also need some additional parameters to capture this information.

pStartRange

Date parameter defaulted to 01 Jan 1900

Similarly you’ll need a pEndRange parameter too, also defaulted to 01 Jan 1900.

Later on we’ll define parameter actions which will ‘set’ these values based on user interaction.

With these fields, we can then define calculated fields to store the start and end dates depending on whether we’re using the defaults due to initial load (ie 14 days to today), or a user selected range.

Selected Range Start Date

IF [pStartRange] = #1900-01-01# THEN DATE(DATEADD(‘day’,-14,[pToday]))
ELSE DATE([pStartRange])
END

Selected Range End Date

IF [pEndRange] = #1900-01-01# THEN DATE([pToday])
ELSE DATE([pEndRange])
END

We’re going to be plotting Order Date on our axis at the day level, and so to simplify things IMO, I created

Order Date Day

DATE(DATETRUNC(‘day’,[Order Date]))

which I then reference in the following calculated field, which is just to capture all the days within the range selected

Selected Dates To Plot

IF [Order Date Day]>= [Selected Range Start Date] AND [Order Date Day]<=[Selected Range End Date] THEN [Order Date Day] END

We can now start to build out the basic chart

Plotting Order Date Day and Selected Dates to Plot side by side you can see the date axis differ, with only the dates from 06 Sep – 20 Sep 21 displaying on the right hand side. The marks type for Selected Date to Plot is set to Area, and to get the marks to join up, you need to turn Stack Marks Off (Analysis -> Stack Marks -> Off menu).

Defining the Timeframe to Display

We’re going to use another parameter to store the timeframe value

pTimeframe

String parameter defaulted to 6 MONTHS (note the case – it’s simpler to match it to the display format that’s going to be used)

We then need a calculated field to tell us what to do with this value

Timeframe to Display

CASE [pTimeframe]
WHEN ‘1 MONTH’ THEN [Order Date Day]>=DATEADD(‘month’,-1,[pToday]) AND [Order Date Day]<= [pToday]

WHEN ‘6 MONTHS’ THEN [Order Date Day]>=DATEADD(‘month’, -6, [pToday]) AND [Order Date Day]<= [pToday]

WHEN ‘YTD’ THEN [Order Date Day]>=DATETRUNC(‘year’,[pToday]) AND [Order Date Day]<= [pToday] WHEN ‘1 YEAR’ THEN [Order Date Day]>= DATEADD(‘year’,-1,[pToday]) AND [Order Date Day]<= [pToday]
ELSE [Order Date Day] <= [pToday]
END

This field will return true for all the dates that fall within each statement and false otherwise.

Add this field to the Filter shelf and select True.

You can test how the left hand side of the chart is affected by manually typing the different values into the parameter

Colouring the chart

The line and area charts are coloured based on whether dates fall in the selected range and whether the difference between the sales values at the start and end of the selected range is positive or not. We need several more calculated fields to work this out.

We firstly need to capture the min and max dates of the selected area for each region. Now, you initially might think that the Selected Range Start Date and Selected Range End Date fields already have these values. However there isn’t always a sale in every region for these dates. You could argue, that in that case, the sales value for that date should be 0 (ie there were no sales on that day), but to match the solution (and it was easier), we just get the min and max dates within the selected range that have a sales value for each region.

Min Selected Date Per Region

{FIXED [Region]: MIN([Selected Dates to Plot])}

Max Selected Date Per Region

{FIXED [Region]: MAX([Selected Dates to Plot])}

Pop these out into a quick view, and you can see how the dates differ per region compared to the default start & end date values

Now we want to work out the sales value on these dates

Min Date Sales

{FIXED [Region]: SUM(IF [Order Date Day]=[Min Selected Date Per Region] THEN [Sales] END)}

Max Date Sales

{FIXED [Region]: SUM(IF [Order Date Day]=[Max Selected Date Per Region] THEN [Sales] END)}

and then we can work out the difference and the % difference

Range Sales Diff

SUM([Max Date Sales])-SUM([Min Date Sales])

custom formatted to +”$”#,##0.00;-“$”#,##0.00 to show a ‘+’ prefix for positive values

Range Sales % Diff

[Range Sales Diff]/SUM([Min Date Sales])

custom formatted to ▲0.0%;▼0.0%

Now we can compute a field to use to colour the line/area chart

Colour – Trend

IF MIN([Order Date Day]) >= [Selected Range Start Date] AND MIN([Order Date Day])<= [Selected Range End Date] THEN

IF [Range Sales Diff]>= 0 THEN 1 ELSE -1 END
ELSE 0
END

If we’re within the selected date range, then test to see if the value is positive (set to 1) or negative (set to -1), otherwise we’re outside the selected date range, so set to 0

Go back to the trend chart and add this field to the Colour shelf of the All Marks card (so it gets added to both sets of marks). Change it to be a discrete (blue) pill and the adjust the colours accordingly. At this point you may want to change the background colour if you’re using a white line. I’m just setting it to a light grey at this point, but eventually it’ll get set to black.

Adding the highlight band

This took a lot of thinking. I knew I’d need a reference band, but it took some time to figure out how to get the backgrounds coloured differently, since you only have the option to fill between the band with one colour.

The trick is to make use of the two date axes we have and to apply a band per pane.

But we need some more fields to make this happen.

Ref Line Start Date -ve

IF [Range Sales Diff]<0 THEN [Selected Range Start Date] END

Ref Line End Date -ve

IF [Range Sales Diff]<0 THEN [Selected Range End Date] END

Add these fields to the Detail shelf of the Order Date Day card and set to be continuous (green). Then add a reference band to this axis, applying the settings as below (note, the Line is a white dotted line, so isn’t showing up in the field setting, though you can see it on the viz).

Because the reference band has been set at the pane level, and the reference line dates are only relevant if the difference is negative, then the band is just showing on one row.

We then do something very similar, but this time we get some dates only if the difference is positive.

Ref Line Start Date +ve

IF [Range Sales Diff]>=0 THEN [Selected Range Start Date] END

Ref Line End Date +ve

IF [Range Sales Diff]>=0 THEN [Selected Range End Date] END

Add these as continuous pills on the Detail shelf of the Selected Dates to Plot card, and add another reference band to this axis instead.

Now you can set the chart to be a dual axis, synchronising the axes, and removing the Measure Names field from the All Marks card which will have automatically been added

This is the core viz, that will need further formatting before its ready to put on the dashboard – remove gridlines, borders etc, set background, remove headers. NOTE– You’ll need to manually re-sort the Regions before the field is hidden.

The KPI table

We need to build a ‘fake table’ for this, by putting Region on Rows and typing MIN(0) on Columns, then adding the Range Sales Diff and Range Sales % Diff fields to the Text shelf. We need an additional field to colour the text though.

Colour – KPI

[Range Sales Diff]>=0

Finally, I capitalised the Region values by using Aliases. This is a quick method when there aren’t many values, but otherwise I would usually create a field with UPPER([Region}).

Once again don’t forget to sort the Regions, and the apply relevant formatting.

The Timeframe Selector

Will states you can use a separate data source for this, so create the list in Excel

and then copy and paste (via the Data > Paste) menu into your workbook

On a new sheet add Date Range to Columns and Date Range to Text. The size and colour of the text differs based on which one has been selected. So create a field

Timeframe Selected

[Date Range] = [pTimeframe]

and add this field to both the Size and Colour shelves. You’ll need to adjust the settings, and hide headers, remove gridlines etc. Try to avoid touching the Text formatting directly, as you might find the Size then doesn’t adjust.

Adding the interactivity

You’ll need to use layout containers to organise all the objects on the dashboard. Then you can add the various dashboard parameter actions needed

Selecting the timeframe

Add a parameter action that on select passes the Date Range field into the pTimeframe parameter

Selecting the date range to highlight

You’ll need 2 parameter actions for this, one that passes the minimum Order Date Day selected into the pStartRange parameter, and the other that passes the maximum Order Date Day selected into the pEndRange parameter.

Deselecting the highlighted marks

By default when you click on a mark/select marks in Tableau, they are highlighted/selected and the other marks are faded, until you ‘click’ again. To stop this from happening I use a ‘true = false’ trick, that has become very common in #WOW challenges, and I’ve blogged many times before.

Create calculated fields

True

True

and

False

False

and add these to the Detail shelf of the All Marks card on the trend line chart.

Then on the dashboard add a Filter action that on select targets the sheet directly, mapping the true field to the false field. As this can never be ‘true’ the filter doesn’t apply, and the marks become unselected.

Repeat the same on the Timeframe Selector sheet.

Hopefully that’s covered all the core points. My published viz is here.

Happy vizzin’! Stay Safe!

Donna