It was Ann’s turn this week to post the weekly #WOW challenge. There’s a fair bit going on here, so let’s get cracking.

Building the main chart

There’s essentially 3 instances of this chart. I’ll walk through the steps to create the Sales version. All the fields just need to be duplicated to build the Orders & Quantity versions.

First up we need a parameter to store the date the user selects. This needs to be a date parameter that allows all dates and is set to 8th May 2019 by default: Order Date Parameter

Based on this parameter value, we need to work out the day of the week of the parameter date, the date 12 weeks ago, and then filter all the dates to just include the dates that match the day of the week. So we need

Day of Week

UPPER(DATENAME(‘weekday’,[Order Date Parameter],’Monday’))

(the UPPER is necessary for the display Ann has stated).

Dates to Include

[Order Date]>=DATEADD(‘day’,-84,[Order Date Parameter])
AND [Order Date]<= [Order Date Parameter]

This identifies the dates in the 12 week period we’re concerned with.

I played around with ‘week’ and ‘day’, as I noticed when playing with Ann’s published solution that sometimes there were 12 dates displayed, other times there were 13, but this is just down to how the number of days in a month fall, and whether there’s actually orders on the days.

Weekdays to Include

[Day of Week] = UPPER(DATENAME(‘weekday’,[Order Date],’Monday’))

This identifies all the dates that are on the same day of the week as the Order Date Parameter.

Add both Dates to Include and Weekdays to Include to the Filters shelf and set both to True.

Add Order Date to Rows and set to be a discrete exact date. Add Sales to Text. Sort Order Date by Sales DESC

The colouring of the cells is based on 4 conditions

• being the max value
• being above the average value
• being the min value
• being below the average value

I used table calcs to work this out, giving each condition a numeric value

Colour:Sales

IF SUM([Sales]) = WINDOW_MAX(SUM([Sales])) THEN 1
ELSEIF SUM([Sales]) = WINDOW_MIN(SUM([Sales])) THEN 4
ELSEIF SUM([Sales]) >= WINDOW_AVG(SUM([Sales])) THEN 2
ELSE 3
END

Add this to the Colour shelf and change it to be a continuous (green) pill, which will enable you to select a ‘range’ colour palette rather than a discrete one. Temperature Diverging won’t be available for selection unless the pill is green; on selection, the colours will automatically be set as per the requirement. Change the mark type to Square.

We also need to identify an above & below average split so create

Sales Header

UPPER(IF [COLOUR:Sales]<=2 THEN ‘Above
Average’
ELSE ‘Below
Average’
END)

Note the carriage return/line break, which is necessary to force the text across 2 lines.

Add this to the Rows shelf in front of Order Date, and format to rotate label

Finally we need to show a triangle indicator against the selected date.

Selected Date

IF [Order Date]=[Order Date Parameter] THEN ‘►’ ELSE ” END

I use this site to source the shapes I need.

Add this to Rows between Sales Header and Order Date

Format to remove all column & row lines, then add row banding set to the appropriate level, and a mid grey colour

Finally Hide Field Labels for Rows, format the font of the date and set the tooltip.

Now we need to set the title to include the rank of the selected date.

Selected Date Sales Rank

IF ATTR([Order Date])=[Order Date Parameter] THEN RANK_UNIQUE(SUM([Sales]))END

Add this to the Detail shelf, and the field will then be available to reference when you edit the title of the sheet

Name this sheet Sales Rank or similar.

You can now repeat the steps to build versions for Orders (COUNTD(Order ID)) and Quantities (SUM(Quantity)).

Dynamic Title

To build the title that will be displayed on the dashboard, create a new sheet, and add Order Date Parameter and Day of Week to the Text shelf. Then format the text to suit

Building the Dashboard

The ‘extra’ requirement Ann added to this challenge, was to display a ‘grey shadow’ beneath each of the rank tables. This is done using containers, setting background colours and applying padding. When building this took a bit of trial & error. Hopefully in documenting I’ll get the steps in the right order…. fingers crossed…

On a new dashboard, set the background colour to a pale grey.

Add a vertical container.

Add the Title sheet into the container, and remove the sheet title

Add a blank object into the container, beneath the Title sheet.

Add another blank object into the container, between the Title and the blank, set the background of this object to dark grey, reduce the padding to 0 and the edit the height to 2.

This will give the impression of a ‘line’ on the dashboard

Now add a horizontal container beneath the ‘line’ and the blank object at the bottom. You may need to adjust the heights of the objects

Set the outer padding of this object to 5.

Add a blank object into this horizontal container. Blank objects help when organising objects when working with containers, and will be removed later.

Add another horizontal container into this container next to the blank object. Set the background to a dark gray and set the outer padding to left 10, top 5, right 5, bottom 0.

Into this dark grey layout container add the Sales Rank sheet. Set the backgroud of this object to white, and the outer padding as left 0, top 0, right 0, bottom 4. Make sure the sales rank sheet is set to Fit Entire View.

Add another horizontal container to the right of the Sales Rank sheet, between that and the blank object. Set the background to the dark grey, and outer padding to left 5, top 5, right 5, bottom 0.

Add the Orders Rank sheet into this container, again set to Fit Entire View, set the background to white and outer padding to left 0, top 0, right 0, bottom 4.

Add another horizontal container, this time between the Order Rank sheet and the blank object. Set the background to dark grey, and outer padding to left 5, top 5, right 10, bottom 0.

Add the Qty Rank sheet into this container, again set to Fit Entire View, set the background to white and outer padding to left 0, top 0, right 0, bottom 4.

Now delete the blank object to the right, and delete the blank object at the bottom. Also delete the container in the right hand panel that has been automatically added and contains all the legends etc.

Set the dashboard to the required 700 x 450 size.

Select the ‘outer’ horizontal container that has all the charts in it, and Distribute Contents Evenly

You may need to adjust the widths of the columns within the ranking charts to get everything displayed in the right way.

But fingers crossed, you should have the desired display.

Calendar icon date selector

The final requirement, is to show the date selected on click of a calendar icon. This is managed using a floating container to store the Order Date Parameter, and using the Add Show/Hide Button option of the container menu.

Select Edit Button and under Item Hidden choose the calendar icon you can get off the site Ann provided a link for.

You’ll just then have to adjust the position of the container with the parameter and the button to suit.

Phew! all done. My published viz is here.

Note – I did find after publishing on Tableau Public, I had some erroneous horizontal white lines displaying across my ranking charts. I’m putting this down to an issue with rendering on Public, as I can’t see anything causing this, and it’s not visible on Desktop.

Happy vizzin’!

Donna