string list parameter containing values week, month and quarter; defaulted to month. Note the capitalisation of the display as value. The value itself should all be lowercase as it will be referenced in calculations later.
pTimeFrame
integer parameter ranging from 12 to 36, with a step-size of 6 and defaulted to 24
pMoveAvg
integer parameter ranging from 3 to 12 with a step size of 3 and defaulted to 3
Creating the calculations
The viz needs to display a dynamic date based on thevalue of the pTimePortion parameter.
Display Date
DATE(DATETRUNC([pTimePortion],[Order Date]))
It also displays the moving average of Sales based on the pMoveAvg parameter.
Moving Avg
WINDOW_AVG(SUM([Sales]), -1*([pMoveAvg]-1), 0)
Note, as the moving average is to include the Sales value of the ‘current’ date, then we need to subtract 1 from the pMoveAvg parameter. Ie if the pMoveAcg parameter = 3, then we want to calculate the moving average over the ‘current’ mark plus the previous 2 marks, so -2 needs to be fed into the calculation.
Finally, we need to restrict the dates being displayed in the viz. For this I calculated
ie only include records where the Order Date is greater than pTimeFrame weeks/months/quarters prior to the Latest Date.
Building the viz
On a new sheet, display all the 3 parameters. The add Display Date as a continuous exact date (green pill) to columns and Sales to rows.
Add Moving Avg to rows, make dual axis and synchronise the axis. Adjust colours of the marks to suit.
Add Date to Display to the Filter shelf and set to True. Then add Sales and Moving Avg to the Tooltip of the All marks card, and adjust Tooltip accordingly.
Update the title of the sheet so it references the various parameters
Finally, tidy up by
removing row and column dividers
hiding the right hand axis (right click, uncheck show header)
editing the Display Date axis, so the title of the axis references the pTimePortion parameter (Note – I did find this gets ‘lost’ when publishing to Tableau Public, so I had to re-edit my viz after publishing to reapply this setting).
Then add to a dashboard, and use a horizontal layout container to organise the parameters across the top. My published viz is here.
Yoshi set the challenge this week to build a dashboard which looks like the visual you might get as part of a Pulse metric.
Define the parameters
Create a parameter to define the ‘reporting’ date
pBaseDate
date parameter defaulted to 21 Sept 2025
Create a parameter to capture the number of previous weeks the ‘forecasting’ values should be calculated against
pWeeks
integer parameter defaulted to 8, with a min value of 5 and max of 15, incremented every 1 step
Building the KPI card
This section displays the month to date sales and comparisons to previous month, based on the value in the pBaseDate parameter. We need several calculations
MtD Sales
IF [Order Date] >= DATETRUNC(‘month’, [pBaseDate]) AND [Order Date]<=[pBaseDate] THEN [Sales] END
format to $ with 0 dp.
Prev MtD Sales
IF [Order Date] >= DATEADD(‘month’, -1, DATETRUNC(‘month’, [pBaseDate])) AND [Order Date]<= DATEADD(‘month’, -1, [pBaseDate]) THEN [Sales] END
MtD Sales Diff
SUM([MtD Sales]) – SUM([Prev MtD Sales])
custom format to +”$”#,##0;-“$”#,##0
MtD Sales % Diff
[MtD Sales Diff] / SUM([Prev MtD Sales])
custom format to +0.0%;-0.0%;0%
On a new sheet add MtD Sales, MtD Sales Diff and MtD Sales % Diff to Text. Change the mark type to shape and set to be a transparent shape (refer to this blog to understand how to set this up). Adjust the layout and style of the text on the Label. Set the sheet to Entire View and align the text to the left. Update the title of the sheet and remove the Tooltip.
Building the Line Chart
Before building the viz, we’ll start by building the calculations and checking them through a tabular display.
On a new sheet, show the pBaseDate and pWeeks parameters. Then add Order Date as a discrete exact date (blue pill) to Rows and add MtD Sales to Text. The MtD Sales values should only display against the dates from 1st to 21st Sept.
For each day, we need to calculate the 25th percentile of the Sales value using the gp model against the same day of the week for the previous data.
These 2 values give the ‘range’ we want to check the actual MtD Sales value against. Add both values into the table, and update the table calculation setting of each so they are computing explicitly by Order Date
However we only want the calculations to be based on the last x weeks, so we want to filter the display.
Add this to the Filter shelf and set to True. This will adjust the 25th & 75th Percentile values as they are only considering data within the display (eg compare the highlighted values for 01 Sept against those in the image above)
But when it comes to ‘plotting this data’ on a chart, we only want to display the data for the current month (based on pBaseDate), so we need to apply a second filter that restricts the dates displayed further, but that does not eliminate the data in such a way that we lose reference to the previous x weeks.
We can do this using a filter based on a table calculation
Filter: Dates for Chart
LOOKUP(MIN([Order Date]),0) >=DATETRUNC(‘month’, [pBaseDate]) AND LOOKUP(Min([Order Date]),0) <= [pBaseDate]
The LOOKUP function is a table calc that is basically returning the same value for Order Date and comparing it against the base date. But because it is a table calc, when applied as a filter, it will only be applied after other computations.
Add this to the Filter shelf and set to True. Then adjust the table calc so it is explicitly computing by Order Date and then re-edit the filter again so it is just considering True values (changing the table calc setting, resets this).
Let’s start to build the viz :
Duplicate the tabular sheet above. Move Order Date to Columns and change to be continuous (green pill), Move MtD Sales to Rows and move Measure Values to Rows. Move the Measure Names field to the Colour shelf of the Measure Values marks card. Unstack the marks ( Analysis menu > stack marks > off) – if you can’t see 2 areas, swap the order of the pills in the Measure Values box so the 25th Percentile is listed first. Adjust the colours of the marks so the 25th percentile is white and the 75th percentile is pale blue (#e6f2fe). Ensure the opacity of these marks are 100%. Set the colour of the Mtd Sales line to bright blue.
Note – by moving the pills around and having already explicitly set the table calculation settings, we know the fields will be computing correctly. If you wish, you can build the viz from scratch, but you will need to explicitly set all the table calc pills to compute by Order Date again.
Make the chart dual axis and synchronise the axis. Right click the right hand axis and move marks to back to make the line display in front.
Custom format both of these to dd/mm and then add both to the Detail shelf of the All marks card. Set to be continuous exact date (green pills).
Add 2 reference lines to the Order Date axis, which reference these pills.
Add pBaseDate to the Detail shelf of the All marks card too, and add an additional reference line to that field. In this instance format the reference line and apply a custom format to the date to be dd/mm
Adjust the Tooltip via the All marks card. Remove all gridlines, zero lines and row/column dividers. Hide the right hand axis and the Order Date axis. Remove the title from the left hand axis.
To show the summary of how the MtD Sales value for the pBaseDate compares to the range, we will use the caption feature of the worksheet, which can reference fields, but we need these fields to essentially be ‘constants’ for every row of data, so we need some new fields.
Sales for Base Date
WINDOW_MAX(SUM(IF [Order Date] = [pBaseDate] THEN [Sales] END))
format this to $ with 0 dp.
25th Percentile for Base Date
WINDOW_MAX(IF MIN([Order Date]) = [pBaseDate] THEN [25th Percentile] END)
75th Percentile for Base Date
WINDOW_MAX(IF MIN([Order Date]) = [pBaseDate] THEN [75th Percentile] END)
Switch back to the tabular view of data and add these 3 fields. As they’re all table calculations, you need to set them to be computing by Order Date only (as we did above).
You should find that the values for the latest row (highlighted below) are displayed against every row in the 3 additional columns
With this, we can now work out the ‘text’ we want to disply in the caption
Expected Range Text
IF [Sales for Base Date] > [75th Percentile for Base Date] THEN ‘above’ ELSEIF [Sales for Base Date] < [25th Percentile for Base Date] THEN ‘below’ ELSE ‘within’ END
Add this to Rows, check the table calc settings, and then adjust the pBaseDate value so you can see the text change.
Now switch back to the line chart, and display the Caption (worksheet menu > show caption). Add Sales for Base Date and Expected range Text to the Detail shelf of the All marks card, adjusting the table calc settings as we’ve done before.
Then edit the caption and remove all text and update, referencing the various fields and parameters.
Building the bar chart
On a new sheet, add Segment to Rows and MtD Sales Diff to Columns. Sort descending. Create a new field
Diff is +ve
[MtD Sales Diff]>=0
Add to Colour and adjust accordingly. Show mark labels and set to match mark colour. Format the display to remove all column/row dividers, gridlines and zero lines. Display the RowAxis Ruler as a thicker grey line.
Then add all the components to a dashboard, using containers and padding to organise the display. Make sure to display the caption for the line chart worksheet.
It was Sean Miller’s turn this week to set the challenge, which had an added twist – to complete only using the web authoring feature in Tableau Public, something I’d never tried before.
Most challenges use a version of Superstore Sales which I usually already have on my laptop, due to the multiple instances of Tableau Desktop installed, but this time, I had to get the data from data.world. I saved this locally then logged into Tableau Public online and started the process to build a viz online, by clicking the Create a Viz (Beta) option.
When prompted I then chose to upload from computer, the data source file I’d downloaded, and once done I was presented with the online canvas to start building
Unsure as to whether there was any auto save facility online, I decided to save the workbook pretty much immediately, although I was first prompted to navigate to the Data Sourcetab and create an Extract before I could save.
Now I was ready to start building out the requirements.
I started with the trend chart. The grain of the chart was at the month level, so I first created a specific field to store the Order Date at this particular level
Order Date Month
DATE(DATETRUNC(‘month’,[Order Date]))
I then added this to the Columns shelf as a continuous (green) exact date, and added Sales to the Rows shelf. I changed the mark type to Area, set the Colour to a blue, dropping the opacity to 50% in an attempt to match Sean’s colouring and adjusted the tooltip.
I then added another instance of Sales to the Rows shelf next to the existing one, changing the mark type of this instance to Line and setting the Colour to a darker blue at 100% opacity. I then used the context menu on this pill to set a Quick Table Calculation of type Moving Average.
By default this sets the average to the 3-month rolling average as per the requirement. I adjusted the tooltip, made the chart dual axis and synchronised the axis, then removed Measure Names from the Colour shelf that had automatically been added.
My next step at this point was to work on the filtering requirement. To help with this, the first thing I did was to Duplicate as crosstab (right click on the chart tab). This created a tabular view of the data so I could work on getting the calculations I need for the filtering. I flipped the columns and rows, so my starting point was as below.
I chose to use parameters to capture the min and max dates that the user selects on the dashboard.
pMinDate
Date parameter defaulted to 01 Jan 1900
And I also created pMaxDate exactly the same way.
I then needed fields to store the relevant dates depending on whether a selection had been made or not
Min Date Selected
IF [pMinDate]=#1900-01-01# THEN {FIXED : MIN([Order Date Month])} ELSE [pMinDate] END
The FIXED statement sets the date to the minimum month in the data set, if the parameter is set to its default. The below doed similar to get the maximum date in the data set.
Max Date Selected
IF [pMaxDate]=#1900-01-01# THEN {FIXED : MAX([Order Date Month])} ELSE [pMaxDate] END
Using these dates, I then created a field to determine whether the month was within the min & max dates
Is Month Selected?
[Order Date Month]>=[Min Date Selected] AND [Order Date Month]<= [Max Date Selected]
Pop this field onto the table view, and show the parameters and set them to some other values eg Min Date = 01 Jun 2018 and Max Date = 01 Sept 2018. You’ll also need to adjust the table calc setting of the Moving Average Sales pill to compute by Is Month Selected as well.
You’ll see True is stamped against the rows which match the date range. Now, if you make a note of the Moving Average values against these rows, and then add Is Month Selected? = True to the Filter shelf, you should get just the True rows displaying, BUT the Moving Average values are now different. This is because, with this type of field as a filter, the filter is being applied BEFORE the table calculation is being applied – the table calculation essentially has ‘no knowledge’ of the rest of the rows of data as they have been filtered out. Solving this issue is the crux of this challenge.
Remove Is Month Selected? from the filter shelf. We’re going to create a new field to filter by instead
FILTER
LOOKUP(MIN([Is Month Selected?]),0)
This field uses the LOOKUP table calculation, and is basically looking at the value of the Is Month Selected? field in it’s ‘own row’ (denoted by the 0 parameter).
Pop this onto the table view, and you can see that it’s essentially a duplicate of the Is Month Selected? field.
Now add the FILTER =True field to the Filter shelf instead, and this time the rows will once again filter as required, but the Moving Average values will be the same as those in the unfiltered list, which is what we need.
This works because the field we’re filtering by is a table calculation. When added to the filter shelf, these types of fields are applied at the ‘end’ of the processing; ie the table calculation is computed against all the rows in the data AND THEN the filter gets applied. As a result the Moving Average values are retained.
This is all part of Tableau’s Order Of Operations and is a fundamental concept when working with filters.
Armed with this knowledge, we can now apply the FILTER=True to the Filter shelf of the chart we built.
Now we can start to work on the bar charts, which will show Sales along with Average Monthly Sales. For this 2nd measure, I need to work out the number of months captured between the min & max dates.
No. Months
DATEDIFF(‘month’, [Min Date Selected],[Max Date Selected])+1
And with this I can now create
Avg Monthly Sales
SUM([Sales])/MIN([No. Months])
The bar chart viz can then be quickly created by adding Sub-Category to Rows and both Sales and Avg Monthly Sales to Columns, and sorting by Sales descending. The colours of each bar are adjusted to suit.
The bars also need to react to the dates selected, but we can simply add the Is Month Selected? = True to the Filter shelf for this, as none of the values displayed on this chart are reliant on table calculations.
To remove the gridlines from both charts, select Format > Workbook from the menu, scroll down the Format dialog displayed on the right and set Gridlines to Off
Finally, title both sheets appropriately, and then add to a dashboard. We need to add 3 actions to this dashboard – 2 parameter actions and 1 filter action.
Create a parameter action which will on Select, set the pMinDate parameter by passing the MinimumOrder Date Month, and will reset back to 01 Jan 1900 when unselected.
Then create another instance, which sets the pMaxDate parameter instead, passing the Maximum Order Date Month.
Finally, create a Filter Action which on Select of the bar chart, filters the trend chart
And with that, the challenge should be complete. My published viz is here.
Donna + DataViz 