Week 4 of #WOW2020 saw one of the new contributors, Sean Miller, provide his first challenge, based on providing a technique to improve the user experience when working with date inputs.

The date inputs allow a user to select from 2 ‘static’ options (Last 14 Days or Last 30 Days), a custom Last N Days option, where the user is then prompted to define how many days….

… along with a Custom Dates option, which when selected, prompts the user to define the start & end dates

Both the N Days parameter and the Start & End dates only appear when the appropriate selection is made. This makes the displayed interface much cleaner (as it’s less cluttered), and, more importantly, makes the action the user is then expected to make, much more obvious.

So let’s get started.

Define the parameters

The first step in this challenge is to define all the parameters needed, these being :

Date Selector

A string parameter just set to contain the value Last 14 Days

This parameter will be set via a Parameter Action, so there is no need to define this a list with all the options.

N Days

An integer parameter defaulted to 60

Start Date

A date parameter defaulted to 01 Jan 2019

End Date

Another date parameter defaulted to 31 Dec 2019

Build the Sales over time chart

The Sales over Time trend is simply Order Date as a continuous pill at the Day level, against SUM(Sales). Sales is duplicated as a dual axis, allowing one axis to be set to mark type Area, and the other to Line. The colours of each mark are slightly different, to give the ‘edged’ area look

The requirement stated the ‘Last…’ selections should be anchored to the latest date in the dataset, so we need to determine what this is:

Latest Date

{FIXED:MAX([Order Date])}

To restrict the data displayed, we need something we can filter on:

In Timeframe

CASE [Date Selector]
WHEN ‘Last 14 Days’ THEN [Order Date]>DATEADD(‘day’,-14,[Latest Date])
WHEN ‘Last 30 Days’ THEN [Order Date]>DATEADD(‘day’,-30,[Latest Date])
WHEN ‘Last N Days’ THEN [Order Date]>DATEADD(‘day’,-1*[N Days],[Latest Date])
ELSE [Order Date]>=[Start Date] AND [Order Date]<=[End Date]
END

In Timeframe can be added to the Filter shelf and set to True.

Based on the defaults selected, at this point, you should be displaying information for the last 14 days.

Expose the parameters onto the display, and test out the rest of the logic by changing the value of the Date Selector (type in one of the other options – eg Last 30 Days, Last N Days, Custom Days).

BANs

The 3 numbers displayed are the measures Sales, Profit and Profit Ratio, where Profit Ratio is a calculated field of

SUM([Profit]) / SUM([Sales])

formatted to be a percentage of 1 decimal place.

Then

1. Add Measure Names to Columns
2. Add Measure Names and Measure Values to the Text shelf
3. Add Measure Names to the Filter shelf and select just the 3 measures we’re interested in.
4. Reorder the columns to match the requirement
5. Change the formatting of the Measure Names and Measure Values on the Text shelf to set the colour & size of the font to suit, and align middle, centre
6. Format the display to remove the row lines
7. Hide the Measure Names heading (right-click the Measure Names pill on the Columns shelf and unselect Show Header).
8. Add In Timeframe = True to the Filter shelf to restrict the data.
9. Format Sales and Profit accordingly.

Measure Selector

I built the measure selection view using the technique I’ve already blogged about related to #WOW2020 Week 1 : Can you sort dimensions with a single click? This is the 3rd week out of 4 so far that I’ve employed this technique, which is great, as it means it’s a concept that I’ve started to remember 🙂 The blog also describes

1. how to set the parameter action which is needed to alter the Date Selector parameter
2. how to invoke a filter action using a True and False calculated field to prevent the un-selected measures from ‘fading out’.

By this point, all the sheets should be on the dashboard with everything formatted and positioned where it needs. So now for the final task:

Make the Parameters Hide & Show

This part of the challenge was something totally new to me, so I did what I would usually do, and Googled it.

Hiding Parameters & Filters in Tableau – v2 by Andrew Pick @ The Information Lab

This is the primary post I followed to achieve the result for this challenge, so I’m not going to document it all here, when it’s already written down 🙂 It’s a bit fiddly to get the parameters positioned in exactly the right place. From other posts I found, this technique is also sometimes referred to as ‘parameter popping’.

My published workbook can be found here.

Happy vizzin’ & poppin’

Donna