This week Ann set the challenge and suggested she had ‘distilled it down a bit’ so it wouldn’t take as long to solve….hmmmm… not sure if that really worked out for me…. I certainly managed to crack through it to begin with, but the requirement to ‘show the data when there were missing values’ seemed to end up being trickier than I thought it would be, and the trying to display a date on the tooltip for those days…. well, that was just so freakin’ hard! Given this was ‘distilled down’, I thought I must have been missing something pretty obvious to be struggling so much. I played round with so many different table calcs to try to get this to work, but ended up admitting defeat and peaked at Ann’s solution.
The missing dates bit… that ended up being a bit odd. I rebuilt my chart multiple times, starting with a table of data as a ‘check’ and then carefully converting that into the required Viz. Eventually it just seemed to work, which left me very puzzled, as I was pretty certain what I ended up with, I’d had previously… I can only assume there is something about the order of the steps I took that may have caused the problem.
As I write this blog out, I’ll be rebuilding as I go, to generate the various screen shots, so fingers crossed, if you follow step by step, you’ll get a working solution 🙂
Right let’s get onto it…
Building out the data we need
As I’ve done before, I’m going to build up the fields I need into a tabular format, so I can ratify I’ve got everything correct before I build up the viz.
First off, we need a couple of parameters to drive the inputs for the chart :
Week Ending On
A date parameter set to 24th Oct 2019, that allows all values
Include X Prior Weeks
an integer parameter set to 10 by default
From the requirements, we need to display data for the 7 days of the week up to the Week Ending On date, along with data for each of the 7 days in the previous number of weeks as stated in the Include X Prior Weeks parameter.
So first up we need to work out when the ‘latest’ week starts
Start of Selected Week
DATEADD(‘day’,-6,[1a.Week Ending On])
Then I want to group all the Order Dates into their respective weeks, where each week needs to start on the same day of the week as the Start of Selected Week field. eg if Start of Selected Week is Friday 18th Oct, then I want all the dates of the previous x weeks to be grouped to the Friday of the week they’re in. So all the dates from Friday 11th Oct to Thurs 17th Oct are grouped as Fri 11th Oct, dates from Friday 4th Oct to Thurs 10th Oct are grouped as Fri 4th Oct, and so on.
Now the ideal way I think I should be able to do this is use a DATETRUNC function passing in the name of the day of the week, the week should start, which is based on day associated to the Start of Selected Week field. So I wanted to write something like this:
DATETRUNC(‘week’, [Order Date],DATENAME(‘weekday’,[Start of Selected Week]))
but Tableau doesn’t like the fact the 3rd parameter is a calculation rather than simply the text ‘Friday’.
So instead I had to build up the logic as follows
Order Date Week
CASE DATEPART(‘weekday’, [Start of Selected Week])
WHEN 1 THEN DATETRUNC(‘week’, [Order Date], ‘sunday’)
WHEN 2 THEN DATETRUNC(‘week’, [Order Date], ‘monday’)
WHEN 3 THEN DATETRUNC(‘week’, [Order Date], ‘tuesday’)
WHEN 4 THEN DATETRUNC(‘week’, [Order Date], ‘wednesday’)
WHEN 5 THEN DATETRUNC(‘week’, [Order Date], ‘thursday’)
WHEN 6 THEN DATETRUNC(‘week’, [Order Date], ‘friday’)
WHEN 7 THEN DATETRUNC(‘week’, [Order Date], ‘saturday’)
This Order Date Week is essentially the field that represents each line on the final viz.
I also need a field that I can used to represent the x-axis on the final viz, which is labelled based on the days of the week, but (as per the challenge), is starting at a different day.
To do this, I chose to ‘baseline’ all the dates in each week, to be aligned to the ‘current’ week. What I mean by this is that I want to create a field whereby if the Order Date is a Friday, the field will store the date of the Friday of the ‘latest’ week, in this case Friday 18th Oct. Any Order Date on a Saturday will be ‘aligned’ to Saturday 19th Oct and so on.
Order Date Baseline
DATE(DATEADD(‘day’, 7 * DATEDIFF(‘week’,[Order Date Week],[Start of Selected Week]), [Order Date]))
This finds the number of weeks between the week the order date is in and the start of the ‘latest’ week, which is then multiplied by 7 to convert to a days difference, which is then added onto the Order Date itself.
So from below, you can see both Fri 13th Sept & Fri 20th Sept map to 18th Oct; Tue 17th Sept and Tue 24th Sept both map to 22nd Oct etc.
I now need another field to restrict the dates being displayed to those within the timeframe dictated by the Include X Prior Weeks parameter.
Dates to Include
[Order Date]>=DATEADD(‘week’,-1 * [Include X Prior Weeks], [Start of Selected Week])
AND [Order Date] <= [Week Ending On]
So let’s start building the check table of data as follows:
- Order Date Week on Rows as discrete, exact date
- Order Date Baseline on Columns as discrete, exact date
- Dates To Include on Filter shelf, set to True
- Sales on Text
You can see we have some gaps where there are no Sales, we need these to display 0, which we do using
Inc Null Sales
This is a simple table calc that is ‘looking up’ its own value, and if it’s null, sets itself to 0 otherwise uses it’s own value.
So let’s put this on the Text shelf instead
We’re also going to need a field to define the ‘latest week’, as this is coloured differently on the viz
Is Latest Week
[Order Date Week]=[Start of Selected Week]
Bring this into the table
We’ve now got the core things we need to build the majority of the chart.
Building the chart
Firstly, duplicate the table above, then move the pills round as follows:
- Move Is Latest Week to Colour shelf, and adjust colours accordingly
- Move Order Date Week to Detail shelf
- Move Inc Null Sales to Columns shelf
- Swap the Colour Legend so True is first (makes the latest line to the front)
- Format the Order Date Baseline field to custom formatting of dddd which will just the display the day of the week rather than the full date (sneaky huh?)
Then tidy up the formatting to remove gridlines, get the axis in the right format, change the font of the axis labels etc.
And that’s the main viz… now we need to sort the tooltips.
On the tooltip we need to display
- The order date
- The sales value or ‘no sales’ if there are no sales
- The start and end day of the week
Let’s start backwards.
Day of Week Start
DATENAME(‘weekday’,[Start of Selected Week])
Day of Week End
DATENAME(‘weekday’, [Week Ending On])
Put these on the Detail shelf (as we’re going to need them for the title too).
Add the Sales onto Tooltip and format it to $ with 0dp. This will display the Sales value when there is one, and ‘nothing’ if there are no sales (as opposed to the 0 that the Inc Null Sales field will show.
To get the ‘no sales’ text we need
Tooltip: No Sales
IF IFNULL(LOOKUP(SUM([Sales]),0),0)=0 THEN ‘no sales’ END
Note – this is what I did and is in my published solution, but I could have just checked whether Inc Null Sales = 0 – doh!
Add this onto the Tooltip and position directly adjacent to the SUM([Sales]) field, so either one will be displayed (they can never both display).
Right, now we need to get the date.
The problem we have is that like the no sales, if there’s no orders on a date there’s no date to display either, but unlike the ‘no sales’ value, we haven’t got a single default value like 0 to use. Instead we need to work out what the missing date should be.
I tried various things for this, but finally had to look at Ann’s solution to get this, which is :
Tooltip: Order Date
IFNULL(DATEADD(‘day’,1,LOOKUP(ATTR([Order Date]),-1)),DATEADD(‘day’,-1,LOOKUP(ATTR([Order Date]),1))))
Urgh! Horrid right!… so what is this saying…
- If the current order date doesn’t exist, then
- lookup the previous order date and add 1 day to it, but if that is also null then
- lookup the next order date and take 1 day off it
So this is only working on the assumption that there are not 2 days in a row with no orders.
Add this to the Tooltip and format accordingly to get the layout required.
Finally amend the sheet title to match the title displayed. The title can reference the Day of Week Start and Day of Week End fields along with the Week Ending On and the Include X Prior Weeks parameters.
When you then add onto the dashboard, make the parameters floating and position them top right.
Hopefully, you’ve ended up with a working solution. I know I approached mine slightly differently from Ann’s, so it’s worth checking hers out too.
My published viz is here.
Happy vizzin’! Stay Safe!