IF [Count Regions] = 1 THEN MIN([Region]) + ‘ (‘ + STR([Count Regions]) + ‘)’ ELSEIF ([Count Regions]) = SUM({FIXED:COUNTD([Region])}) THEN ‘All Regions (‘ + STR([Count Regions]) + ‘)’ ELSE ‘Multiple Regions (‘ + STR([Count Regions]) + ‘)’ END
BC – Years
IF [Count Years] = 1 THEN STR(MIN(YEAR([Order Date]))) + ‘ (‘ + STR([Count Years]) + ‘)’ ELSEIF ([Count Years]) = SUM({FIXED:COUNTD(YEAR([Order Date]))}) THEN ‘All Years (‘ + STR([Count Years]) + ‘)’ ELSE ‘Multiple Years (‘ + STR([Count Years]) + ‘)’ END
On a new sheet, double click into the section beneath the marks card and create an ad-hoc dimension called ‘dummy’. Add BC-Region, BC- Ship Modes and BC – Years to the Detail shelf. From the Viz sheet you built the chart on, select each filter and set to Apply to Worksheets and select the new sheet. Show all the filters.
Change the mark type to polygon which will make the displayed mark disappear. Then update the sheet title referencing the breadcrumb fields we created. Test the functionality by changing the filter values. Name the sheet BC or similar.
On a dashboard, use a Vertical layout container and add a text field for the title, the BC sheet underneath (set to fit entire view), then add a horizontal layout container and move/add the filters into it. Then add the Viz sheet below, also set to fit entire view.
Format the horizontal container with a grey background then select the option to add hide/show button to it. The button will automatically be a floating object, so move it to where you want it to be. And then that should be it.
Erica collaborated with Giulio D’Errico for this week’s #WOW2025 challenge, which contained lots of features, though the main challenge was to display filter on Region, which along with the Region name also displayed a KPI indicator that could change based on selection from other parameters.
Defining the parameters
We need 3 parameters for this challenge
pMeasure
strig parameter that lists the two options Profit and Sales; defaulted to Profit.
pProfitThreshold
integer parameter that lists the specified values, defaulted to 2,000
pSalesThreshold
integer parameter that lists the specified values, defaulted to 30,000
Building the core scatter plot
Add Sales to Columns and Profit to Rows and Sub-Category to Detail. Show the 3 parameters.
When pMeasure = Profit, we need to display horizontal reference lines against the Profit axis, and when Sales is selected we need to display vertical reference lines against the Sales axis. We need the following fields to return the user defined thresholds:
Ref – Profit Threshold
IF [pMeasure] = ‘Profit’ THEN [pProfitThreshold] END
Ref – Sales Threshold
IF [pMeasure] = ‘Sales’ THEN [pSalesThreshold] END
We also need to define the average per measure for each region:
IF [pMeasure] = ‘Profit’ THEN [Profit Avg Per Region] END
Ref – Sales Avg
IF [pMeasure] = ‘Sales’ THEN [Sales Avg Per Region] END
Add the 4 Ref – XXX fields to the Detail shelf. Then add 2 reference lines to the Sales axis; one referencing Ref – Sales Avg (coloured as a purple dashed line at 100% opacity) and one referencing Ref – Sales Threshold (coloured as a black dashed line at 50% opacity). Display a custom label and then format the label to be aligned vertically and coloured based on the relevant line, and with a 0% shading. Set the pMeasure to Sales to make these display.
Then repeat, adding 2 reference lines to the Profit axis instead. Change pMeasure to Profit for these to appear.
Change the mark type to square. Each mark needs to be coloured whether it is above or below the average for the measure selected.
Colour
IF [pMeasure] = ‘Profit’ THEN IIF(SUM([Profit]) >= SUM([Profit Avg Per Region]),1,0) ELSE IIF(SUM([Sales]) >= SUM([Sales Avg Per Region]),1,0) END
Set this to be discrete and then add to the Colour shelf and adjust accordingly.
Creating the colour-coded filter
The viz needs to be filtered by Region, but the filter displayed needs to show the region name along with an indicator based on whether the average for the region is above the threshold or not. This took a bit of an effort, but I finally managed it.
We need a field to capture the ‘KPI indicator’ based on which measure was selected. It also needs to be computed per region. We need a FIXED LoD for this, and I made use of coloured unicode characters from this site.: https://unicode-explorer.com/ (large yellow circle and large green circle which you can just ‘copy and paste’ into the calculation)
Region Indicator
{FIXED [Region]: (
IF [pMeasure] = ‘Profit’ THEN IIF(SUM([Profit Avg Per Region])>=[pProfitThreshold],’🟢’,’🟡’) ELSE IIF(SUM([Sales Avg Per Region])>=[pSalesThreshold],’🟢’,’🟡’) END )}
I then created
Filter – Region
[Region Indicator] + ‘ ‘ + [Region]
Add this to the Filter shelf, select all values and show the filter. If you change the pProfitMeasure to Profit and pProfitThreshold to 6,000, you’ll see the KPI indicators change.
Format the Tooltip
The tooltip requires several calculated fields to be created. Certain fields only need a value based on the pMeasure and others have conditional formatting (different colours) applied. For the tooltip, I created all these fields:
Add all these fields to the Tooltip. Update the Tooltip to reference the fields and the pMeasure parameter, colouring the text as required. Some of the fields will only display based on the filters selected, so they get places side by side with no spacing.
Finalise the viz by updating the title to reference the pMeasure and the Label – Region field. Remove all gridlines, axis rulers, zero lines etc. Colour the background of the sheet to pale blue.
Building the Overall Indicator
For the bonus challenge, we need to display an indicator that is green if all the regions are green and yellow if at least 1 region is yellow.
On a new sheet, add Region and Region Indicator to Rows and show the 3 parameters.
We’re going to create a field that will return 1 if the Region Indicator is yellow and 0 otherwise.
Region Indicator – Is Below
IIF( [Region Indicator] = ‘🟡’,1,0)
Set to be discrete and add to Rows. Change the pProfitThreshold to 6000 to see the flag change.
Create
Overall Region Indicator
IIF(WINDOW_MAX(MAX([Region Indicator – Is Below]))=1,’🟡’, ‘🟢’)
This says, if the maximum value of the rows ‘in the window’ is 1, then display a yellow indicator, else green. Add to Rows, and adjust the pProfitThreshold to see the behaviour.
Create a field
Filter – Index = 1
INDEX() = 1
Add to Filter shelf and set to True
Move Region to Detail. Remove Region Indicator and Region Indicator – Is Below. Adjust the table calculation setting of Overall Region Indicator to compute using Region only, and do the same for the tableau calculation on Filter – Index = 1 (re-edit the filter after changing, so only True is selected).
Note – originally I used a transparent shape mark type and displayed the indicator as a label, but after publishing to Tableau Public, the indicator became distorted, so I adjusted how this was built.
Set the mark type to circle and add Overall Region Indicator to Colour. Adjust to be green or yellow, depending on the colour of the KPI indicator (you’ll need to change the pProfitThreshold value to ensure you set the colour for both the yellow and green options).
Finally create
Tooltip – Overall Indicator
IIF([Overall Region Indicator]=’🟢’,’All regions meet the ‘ + [pMeasure] + ‘ target’, ‘At least one region does NOT meet the ‘ + [pMeasure] + ‘ target’)
Add this to Tooltip and then set the background colour to pale blue.
Building the dashboard & adding dynamic zone visibility
Using layout containers, add the viz to a dashboard. Use a horizontal layout container to arrange the parameters, the Region filter and the Overall Indicator sheet. I used a floating text object to display the ‘legend key’ again copying come unicode characters from the website referenced earlier.
Then create 2 new boolean calculated fields
Is Profit
[pMeasure] = ‘Profit’
Is Sales
[pMeasure] = ‘Sales’
Select the Sales Threshold parameter object, then update the visibility so it only displays if Is Sales is true (via the Layout > Control visibility using value option)
Repeat the same for the Profit Threshold object, but reference the Is Profit field instead.
Now as you switch the measure between Sales and Profit, the relevant threshold parameter will display. My published viz is here.
Sean set this table calculation based challenge this week. The key to this was for the reference line & bands to change if any of the non-date related filters were change, but for the values to remain constant regardless of the timeframe being reported. Based on this, I figured a table calculation filter would be necessary for the timeframe filter, as these get applied at the end of the ‘order of operations’.
I used the data set Sean provided in the challenge, to ensure I would get the same values to verify my solution. After connecting to the dataset, I did set the date properties on the data source so the week started on a Sunday, again to ensure the numbers aligned (in the UK, my preferences are set to Mondays).
Building out the calculations
Since we’re working with table calcs, I’m going to build the calcs into a table first, so we can verify the figures are behaving. Start by adding Order Date to Rows as a discrete (blue) pill at the Week level. Then add Sales.
Create a new field
Median
WINDOW_MEDIAN(SUM([Sales]))
and add this into the table. The value should the same for every row.
Create a new field
Std D
WINDOW_STDEV(SUM([Sales]))
and add this too – again this should be the same for every row.
But we want to to show the distribution based on +1 or -1 standard deviations, so create
Std Upper
WINDOW_AVG(SUM([Sales])) + [Std D]
and Std Lower
WINDOW_AVG(SUM([Sales])) – [Std D]
and add these to the table.
Each mark needs to be coloured based on whether it is greater than the upper band, so create
Sales above Std Upper
SUM([Sales]) > [Std Upper]
and add to the table on Rows
Add Category, Sub-Category and Segment to the Filter shelf, and show the filters. Adjust them to see that the table calculation fields change, although still remain the same across every row.
To restrict the timeline displayed, we need a parameter
pWeeks
integer parameter defaulted to 18
Show this parameter on the canvas. Then, to identify the weeks to keep, create a new field
Index
INDEX()
and convert it to discrete, then add to Rows to create a ‘row number’ for each row.
But, I want the index to start from 1 from the end of the table, so adjust the tableau calculation on the Index field so that is it computing explicitly by Week of Order Date and is sorted by Min Order Date descending
The first row in the table is now indexed with the greatest number, and if you scroll down, the last row should be indexed with 1.
With this we can now created
Filter – Date
[Index]<=[pWeeks]+1 AND [Index]<>1
I was expecting just to have [Index}<=pWeeks, but Sean’s solution excluded the data associated to the last week, I assume as it wasn’t a full week, so the above calculation resolved that.
Add this to the Filter shelf and set to True.
Changing the value in the pWeeks parameter only should adjust the number of rows displayed, but the values for the table calculated fields shouldn’t change.
Building the viz
On a new sheet, add Order Date as a continuous (green) pill at the week level to Columns and Sales to Rows.
Add another instance of Sales to Rows. Change the mark type of the Sales(2) marks card to circle. Make the chart dual axis and synchronise axis.
On the All marks card, add Median, Std Upper and Std Lower to the Detail shelf.
Add a reference line to the Sales axis, referencing the Median value. Set it to be a thicker darker line.
Then add another reference line but this time set it to be a band and reference the Std Lower and Std Upper fields, selecting a pale grey fill.
On the Sales(2) marks card, add Sales above Std Upper to the Colour shelf, and adjust to suit. Add a border to the circles. If required, adjust the colour and size of the line on the Sales marks card too.
Add the Category, Sub-Category and Segment fields to the Filter shelf, and show the filters. Show the pWeeks parameter. Then add Filter-Date to the Filter shelf. Adjust the table calculation as described above, then re-edit the filter to just show the True values
Finally tidy up by
remove row and column dividers
hide the right hand axis (uncheck show header)
edit the date axis and delete the title
Then add all the information to a dashboard and you’re good to go!
Sean set this week’s challenge to give an alternative solution to displaying a table of details rather than the traditional ‘pancake table’ (his words not mine 🙂 ).
The main crux of the challenge relates to the dashboard actions and interactivity, so I’ll be brief(ish) in describing how to build the charts.
Creating the line chart
Add Order Date to Columns at the month-yearcontinuous (green pill) level. Add Sales to Rows. Format Sales to $ with 0 dp. Remove the title on the Order Date axis. Update the Tooltip to give an instruction to ‘click the line to filter’. Rename the sheet Sales Trend or similar.
Creating the bar chart
Add Sub-Category to Rows and Sales to Columns. Sort by Sales descending. Hide the Sub-Category row heading label (right click > hide field labels for rows). Update the Tooltip to give an instruction to ‘click the bar to filter’. Rename the sheet Sales by Sub Bar or similar.
Creating the Tree Map
Add Segment and Ship Mode to Detail and Sales to Size. Move Segment to Colour and reduce opacity to about 60%. Move Ship Mode to Label and then add additional Segment and Sales pills to Label. Add a table calculation against the Sales pill on the Label shelf, so it is applying a percentage of Total by Segment only.
Add another instance of the Sales pill to Label and then update the layout of the label.
Move the Segment pills on the marks shelf so they are positioned below the Ship Mode to ensure the tree map is segmented based on the Ship Mode (there should be four blocks divided by the thicker white lines).
Update the Tooltip to give an instruction to ‘click the treemap to filter’. Rename the sheet Treemap or similar.
Build the Details table
On a new sheet add Order ID, Customer Name, Order Date (as a discrete exact date – blue pill), Ship Date(as a discrete exact date – blue pill) and Product Name to Rows. Add Sales to Text. Format Profit to $ with 0 dp and drag onto the canvas over the columns of Sales numbers, and release the mouse when the Show Me option appears. Add Discount into the Measure Values section. Change the aggregation to Average and then format to be % to 0 dp. Rearrange the order of the pills in the Measure Values section as required. Add Segment, Sub-Category and Ship Mode to the Detail shelf. Update the title to reference these 3 pills. Hide the Tooltip. Rename the sheet Details or similar.
Building the additional calculations needed
In clicking around Sean’s solution, I was finding what I had initially built wasn’t quite doing what Sean did. If I clicked on the bar chart and then the tree map, the details were only filtered based on the tree map and vice versa. There were ways to solve this, but this then resulted in other issues, in that after closing the details table, the charts remained filtered, but it wasn’t obvious as nothing was highlighted. Basically what I’m trying to say, is the filtering seemed like it should be straightfoward, but wasn’t. I ended up using a combination of parameters and filter actions.
So we’ll start by dealing with the parameters we need.
Create the following parameters
pSelectedDate
date parameter defaulted to 01 Jan 1900
pSelectedSegment
string parameter defaulted to <emptystring>
pSelectedShipMode
string parameter defaulted to <emptystring>
pSelectedSubCat
string parameter defaulted to <emptystring>
Then create the following calculated fields
Filter: Date
[pSelectedDate] = #1900-01-01# OR [pSelectedDate]=DATETRUNC(‘month’,[Order Date])
add this to the Filter shelf on the bar chart, tree map and details sheets and set to True.
Filter: SubCat
[pSelectedSubCat]=” OR [pSelectedSubCat]=[Sub-Category]
add this to the filter shelf on the line chart, tree map and details sheets and set to True
Filter: Segment
[pSelectedSegment]=” OR [pSelectedSegment]=[Segment]
add this to the filter shelf on the line chart, bar chart and details sheets and set to True
Filter: Ship Mode
add this to the filter shelf on the line chart, bar chart and details sheets and set to True
We also need a parameter to capture when we want to show the details table.
pClickMade
boolean parameter defaulted to False.
and to supplement it, we need a calculated field to use to set this parameter to true
Click Made
TRUE
Add Click Made to the Detail shelf of the line chart, bar chart and tree map.
We’ll set these parameters later.
Building the Close icon
The ‘close’ cross when the details sheet is displayed is another sheet. On clicking on it, we will want to set the pClickMade parameter to False so the Details will no longer show. For this we will need
Close
FALSE
Add this field to the Detail shelf on a new sheet. Change the mark type to shape and change the shape to a X. Set the colour to black and set to fit entire view. Hide the Tooltip. Name the sheet Close or similar.
Building the dashboard and interactivity
Using layout containers, arrange the line chart, bar chart and tree map into a dashboard. Use padding and background colours to get the layout as desired.
The add the Details sheet as a floating object and position over the top of the other charts. Set the background to white and add a black border. Also float the Close sheet into position too. Hide the title and also add a black border.
Select the Close sheet object, and then from the Layout tab in the left hand nav, check the Control visibility using value checkbox and select the pClickMade parameter
It should disappear if the parameter is still set to false. Repeat the same process with the Detail sheet object.
Now create the following dashboard parameter actions
Filter Month
On select of the Sales Trend sheet, target the pSelectedDate parameter, passing in the value from the Order Date. When the selection is cleared, reset to 01 Jan 1900.
Filter SubCat
On select of the Sales by Sub Bar sheet, target the pSelectedSubCat parameter, passing in the value from the Sub-Category. When the selection is cleared, reset to <emptystring>.
Filter Ship Mode
On select of the Treemap sheet, target the pSelectedShipMode parameter, passing in the value from the Ship Mode. When the selection is cleared, reset to <emptystring>.
Filter Segment
On select of the Treemap sheet, target the pSelectedSegment parameter, passing in the value from the Segment. When the selection is cleared, reset to <emptystring>.
Drill to Details
Via the menu of the Sales by Sub Bar, Sales Trend, and Treemap sheets, target the pClickMade parameter passing in the value from the Click Made field. When the selection is cleared, set the value to False.
Close Details
On select of the Close sheet, target the pClickMade parameter, passing in the value from the Close field. When the selection is cleared, keep the value.
If you start clicking around, you should find that all these actions do provide some level of filtering, but if you for example, click on the bar (to filter the line and treemap), and then click on a section in the tree map and use the ‘Drill down to details’ menu option, the details table has lost the filtering of the bar chart as the bar has become unselected when the treemap chart was clicked.
To resolve this, apply filter actions to the line chart, bar chart and tree map objects (the quickest way to do this is just select the object on the dashboard and click the ‘filter’ icon in the context menu.
If you do this on all 3 sheets and then look at the list of dashboard actions you’ll see 3 ‘Filter x (generated)’ entries.
By applying this mix of filtering through ‘default’ dashboard filter actions in conjunction with parameters, I think you have a more complete and understandable experience. And you will have to explicitly unselect each of the marks you clicked on to remove that filter. I added instructions on the dashboard to aid with this.
I set this week’s challenge and I tried to deliver something to hopefully suit everyone wherever they are on their Tableau journey. The primary focus for this challenge is on table calculations, but there’s a few other features/functionality included too.
I love football – all the family are involved in it in some way, so thought I’d see what I could get from a different data set this week. The data set contains the results of matches played in the English Premier League for the last five seasons – from the 2020-21 season to the current season 2024-25. Matches up to the end of 2024 only are included.
As I wrote the challenge in such a way that should allow it to be ‘built upon’ from the beginner challenge, I’m going to author this blog in the same way. I’ll describe how to build the beginner challenge first, then will adapt /add on to that solution. Obviously, as in most cases, this is just how I built my solution – there may well be other ways to achieve the same result.
Beginner Challenge
Modelling the data
The data set provided displays 1 row per match with the teams being displayed in the Home and the Away columns respectively, and the FTR (full time result) column indicating if the Home team won (H), the Away team won (A) or if the match was a draw (D).
The first thing we need to do is pivot this data so we have 2 rows per match, with a column displaying the Team and another indicating if the team is the Home or Away team. To do this, in the data source window, select the Home and the Away columns (Ctrl-Click to multi-select – they’ll be highlighted in blue), then from the context menu on either column, select the Pivot option.
This will duplicate the rows, and generate two new fields called Pivot Field Names and Pivot Field Values. Rename the fields by double-clicking into the field heading
Pivot Field Names to Homeor Away
Pivot Field Values to Team
Creating the points calculation
We need to determine how many points each team gained out of the match which is based on whether they won (3pts), lost (0pts) or drew (1pt). Create a calculated field
Pts
IF [Home or Away] = ‘Home’ AND [FTR] = ‘H’ THEN 3 ELSEIF [Home or Away] = ‘Away’ AND [FTR] = ‘A’ THEN 3 ELSEIF [FTR] = ‘D’ THEN 1 ELSE 0 END
Creating the cumulative points line chart
We want to create a line chart that displays the cumulative number of points each team has gained by each week in each season.
Start by adding Wk to Columns and Pts to Rows as these are the core 2 fields we want to plot. But we need to split this by Team and by season, so add Team and Season End Year to the Detail shelf.
This gives us all the data points we need, but at the moment, it’s currently just showing how many points each team gained per week in each season.
To get the cumulative value, we add a running total quick table calculation to the Pts field.
which gives us the display we need
While we can leave the SUM(Pts) field as is in the Rows, I tend to like to ‘bake’ this field into the data set, so I have a dedicated field representing the running total. I can create this field in 2 ways
Create a calculated field called Cumulative Points Per Season which contains the text RUNNING_SUM(SUM([Pts])). Add this field to Rows instead of just Pts.
Hold down Ctrl and then click on the SUM([Pts]) pill in Rows and drag into the left hand data pane and then release the mouse. This will automatically create a new field which you can rename to Cumulative Points Per Season. The field will already contain the text from the calculation used within the quick table calculation, and the field on Rows will automatically be updated.
Filtering the data
Add Team to the Filter shelf and when prompted just select a single entry eg Arsenal.
Show the Filter control. From the context menu, change the control to be a single value (list) display, and then select customise and uncheck the show all value option, so All can’t be selected.
Format the line
To make the line stepped, click the path button and select the step option
To identify the latest season without hardcoding, we can create
Is Latest Season
[Season End Year] = {MAX([Season End Year])}
The {MAX([Season End Year])} is a FIXED LOD (level of detail) calculation, and is a shortened notation for {FIXED: MAX([Season End Year])} which basically returns the latest value of Season End Year across every row in the data set. This calculation returns true if the Season End Year value of the row matches the overall value.
Add this field to the Colour shelf and adjust colours to suit. Also add the same field to the Size shelf
The line for the latest season is being displayed ‘behind’ the other seasons. To fix this, drag the True value in either the colour or size legend to be listed before the False value. Then edit the sizes from the context menu of the size legend, and check the reversed checkbox to make True the thicker line.
If the thick line seems too thick, adjust the mark size range to get it as you’d prefer
Label the line
Add Season End Year to the Label shelf. Align middle right. Allow labels to overlap marks. And match mark colour.
Create the Tooltip
We need an additional calculated field for this, as we want to display the season in the tooltip in the format 2024-2025 and not just the ending year of the season.
Season Start Year
[Season End Year]-1
Add this field to the Tooltip shelf, and then edit the Tooltip to build up the text in the required format. Use the Insert button on the tooltip to add referenced fields.
Final Formatting
To tidy up the display we want to
Change the axis titles
Right click on each axis and Edit Axis then adjust the title (or remove altogether if you don’t want a title to display)
Remove gridlines and axis ruler
Right click anywhere on the chart canvas, and select Format. In the left hand pane, select the format lines option and set grid Lines to None and axis rulers to None.
Set all text to dark purple
Select the Format menu option at the top of the screen and select Workbook. The under the All fonts section, change the colour to that required
Update the title to reference the selected team
double click in to the title of the sheet and amend accordingly, using the Insert option to add relevant fields.
Intermediate Challenge
For this part of the challenge we’re looking at using a dual axis to display another set of marks – these ones are circular and only up to 1 mark per season should display. As this now takes a bit more thought, and to help verify the calculations required, I’m going to build up the calculations I need in a tabular form first.
Defining the additional mark to plot
On a new sheet add Team, Season End Year and Wk to Rows. Set the latter 2 fields to be discrete (blue) pills. Add Cumulative Points Per Season to Text. Add Team to Filter and select Arsenal.
We need to identify the date of the last match played by each team, so we can use an LOD for this
Latest Date Per Team
{FIXED [Team] : MAX([Date])}
Add this to Rows as a discrete (blue pill) exact date. For Arsenal, the last match was on 27 Dec 2024, whereas for Chelsea it’s 22 Dec 2024.
With this, we can work out what the latest points are for each team in the current season.
Latest Points Per Team
WINDOW_MAX(IF MIN([Date]) = MIN([Latest Date Per Team]) THEN [Cumulative Points Per Season] END)
Breaking this down : the inner part of the statement says “if the date associated to the row matches the latest date, then return the points associated with that row”. Only 1 row in the table of data has a value at this point, all the rest of the rows are ‘nothing’. The WINDOW_MAX statement, then essentially ‘floods’ that value across every row in the data, because the ‘value’ returned by the inner statement is the maximum value (it’s higher than nothing). Add this field into the table.
We’re trying to identify the week in each season where the points are at least the same as the latest points. We’re going to capture the previous week’s points against every row.
Previous Points
LOOKUP([Cumulative Points Per Season],-1)
This is a table calculation that returns the value of the Cumulative Points Per Season from the previous row (-1). If we wanted the next row, the function parameter would be 1. 0 identifies the ‘current row’.
Add this to the table.
We can see the behaviour – The Previous Points associated to 2025 week 18 for Arsenal is 33, which is the value associated to Cumulative Points Per Season for week 17. But we can also see that week 38 from season 2024 is being reported as the previous points for week 1 of season 2025, which is wrong – we don’t want a previous value for this row.
To resolve, edit the table calculation of the Previous Points field and adjust so the calculation for Previous Points is just computing by the Wk field only.
With this we can identify the week in each season we want to ‘match’ against. In the case of the latest season, we just want the last row of data, but for previous seasons, we want to identify the first row where the number of points was at least the same as the latest points; the row where the points in the row are the same or greater than the latest points, and the points in the previous row are less.
Matching Week
//for latest season, just label latest record, otherwise find the week where the team had scored at least the same number of points as the current season so far IF MIN([Is Latest Season]) AND LAST()=0 THEN TRUE ELSE IF NOT(MIN([Is Latest Season])) AND [Cumulative Points Per Season]>= [Latest Points Per Team] AND [Previous Points] < [Latest Points Per Team] THEN TRUE ELSE FALSE END END
Add this to Rows and check the data. In the example below, in Season 2020-21, in week 26, Arsenal had 37 points. The previous week they had 34 points. Arsenal’s latest points are 36, so since 37 >=36 and 34 < 36, then week 26 is the matching week.
Looking at season 2023-24, in both week 15 and 16, Arsenal had 36 points. But only week 15 is highlighted as the match, as in week 14, Arsenal had 33 points so 36 >=36 and 33 <36, but for week 16, as the previous week was also 36, the 2nd half of the logic isn’t true : 36 is not less than 36.
So now we’ve identified the row in each season we want to display on the viz, we need to get the relevant points isolated in their own field too.
Matching Week Points
IF [Matching Week] THEN [Cumulative Points Per Season] END
Add this to the table
We now have data in a field we can plot.
Visualise the additional mark
If you want to retain your ‘Beginner’ solution, then the first step is to duplicate the Beginner worksheet, other wise, just build on what you have.
Add Matching Week Points to Rows to create an additional axis. By default only 1 mark may have displayed. Adjust the table calculation setting of the field, so the Latest Points Per Team calculation is computing by all fields except the Team field.
Change the mark type of the Matching Week Points marks card to Circle and remove Season End Year from the Label shelf (simply drag the pill against the T symbol off off the marks card)
Size the circles
We want the circles to be bigger, but if we adjust the Size, the lines change too, as the Size is being set based on the Is Latest Year pill on both marks cards. To resolve this, create a duplicate instance of Is Latest Year (right click pill and duplicate). This will automatically create Is Latest Season (copy) in the dimensions pane. Drag this onto the size shelf of the Matching Week Points marks card instead to make the sizing independent (you will probably find you need to readjust the table calculation for the Matching Week Points pill to include Is Latest Season (copy)). Then adjust the sizes as required.
Label the circles
Add Latest Points Per Team to the Label shelf of the Matching Week Points marks card. Adjust the table calculation setting, so the Latest Points Per Team calc is computing just by Wk only, so only the latest value displays.
Then format the Label so the text is aligned middle centre, is white & bold and slightly larger font.
Adjust the Tooltip text on the Matching Week Points mark, so it reads the same as on the line mark. You will need to reference the Matching Week Points value instead.
Then make the chart dual axis and synchronise the axis. Remove the Measure Names field that has automatically been added to the All marks card
Remove Label for Latest Season
We don’t want the season label to display for the current year, so create a new field
Label:Season
IF NOT([Is Latest Season]) THEN [Season End Year] END
Replace the Season End Year pill on the Label shelf of the Cumulative Points Per Season marks card, with this one instead.
Final Formatting
To tidy up
Remove right hand axis
right click on axis and uncheck show header
Remove 165 nulls indicator
right click on indicator and hide indicator
Remove row & column dividers
Right click on the canvas and Format. Select Format Borders and set Row and Column Divider values to None
Advanced Challenge
For the final part of the challenge we want to add some additional text to the tooltip and adjust the filter control. As before either duplicate the Intermediate challenge sheet or just build on.
We’ll start with the tooltip text.
Expand the Tooltip Text
For this, we’ll go back to the data table sheet we were working with to validate the calculations required.
We want to calculate the difference in the number of weeks between the latest week of the current season, and the week number of the matching record from previous seasons. So first, we want to identify the latest week of the current season, and ‘flood’ that over every row.
Latest Week Number Per Team
WINDOW_MAX(MAX(IF ([Date]) = ([Latest Date Per Team]) THEN [Wk] END))
This is the same logic we used above when getting the Latest Points Per Team, although as this time the Wk field isn’t already an aggregated field like Cumulative Points Per Season is, we have to wrap the conditional statement with an aggregation (eg MAX) before applying the WINDOW_MAX.
Add this to the table.
And then we need, the week number in each season where the match was found, but this needs to be spread across every row associated to that season.
Matching Week No Per Team and Season
WINDOW_MAX(IF [Matching Week] THEN MIN([Wk]) END)
Add to the table, but adjust the table calculation so the field is computing by all fields except the Season End Year.
Now we have these 2 fields, we can compute the difference
Week Difference
[Latest Week Number Per Team] – [Matching Week No Per Team and Season]
Add to the table. With this we can then define what this means for the text in the tooltip
Less/More
IF [Week Difference]<0 THEN ‘more’ ELSEIF [Week Difference]>0 then ‘less’ ELSE ” END
and also build the tooltip text completely
Tooltip- other seasons
IF NOT(MIN([Is Latest Season])) THEN IF [Week Difference] = 0 THEN ‘It took the same amount of weeks to accrue at least the same number of points as the current season’ ELSE ‘It took ‘ + STR(ABS([Week Difference])) + ‘ weeks ‘ + [Less/More] + ‘ to accrue at least the same number of points as the current season’ END END
Add this to the Tooltip shelf of the Matching Week Points marks card. Adjust the Tooltip to reference the field.
Adjust the Tooltip – other seasons table calculation, so the Latest Points Per Team nested calculation is computing for all fields except Team (this will make the circles reappear)
and also adjust the Latest Week Number Per Team nested calculation to compute by all fields except Team. This should make the text in the tooltip appear.
Filtering by teams in the current season only
We need to get a list of the teams in the current season only, which we can define by
Filter Team
IF {FIXED [Team]: MAX([Season End Year])} = {MAX([Season End Year])} THEN [Team] END
Breaking this down: {FIXED [Team]: MAX([Season End Year])} returns the maximum season for each team, which is compared against the maximum season in the data set. So if there is a match, then name of the team is returned. Listing this out in a table we get Null for all the teams whose latest season didn’t match the overall maximum
We can then build a set off of this field. Right click on the Filter Team field Create > Set and click the Null field and select Exclude
Then add this to the Filter shelf, and the list of teams displayed in the Team filter display will be reduced.
And with that the challenge is completed. My published viz is here.
Erica set the latest challenge, testing us on our ability to master tricky filter scenarios – in this case either show the info for one specific value of a field, or only show the other values, but allow them to be filtered themselves too. The challenge had two parts – the main challenge and a bonus option. I managed to complete both, so will blog both too.
Main challenge – Building the basic viz
On a new sheet add Region and Category to Rows and Sales to Columns. Add Region to Colour and adjust accordingly.
Sort Region by Sales descending
and then click the descended sort button on the toolbar to sort the Category field by Sales too.
Format Sales to be $ with 0 dp. Remove column dividers, and widen each row slightly.
Main challenge – Apply the filtering
Create a parameter
pRegionType
string parameter with 2 options : Not West and West, defaulted to Not West
Create a calculated field to determine whether to display the West Region only, or the other Regions
Filter Region West or Not v1
([pRegionType] = ‘West’ AND [Region] = ‘West’) OR ([pRegionType] = ‘Not West’ AND [Region] <> ‘West’)
Add this to the Filter shelf and set to True. This is essentially the ‘first level’ filter. Show the parameter and switch between the two values to see the behaviour
Now we need a ‘second’ filter, to allow the relevant Regions to be selected. For this, add Region to the Filter shelf, but select the Use all option
and then show the Region filter list on the canvas, and adjust the settings so only relevant values are displayed
This means when the pRegionType parameter is West, only West will be displayed in the Region filter, but when Not West is selected, all regions except West will display, and the filter can be interacted with in the normal manner.
Main challenge – Building the dashboard
Arrange the viz and the parameters on the dashboard as required, using layout containers, padding and background colours to help organise the content and display required.
We only want the Region selection filter to display when the pRegionType parameter is set to Not West. We can use dynamic zone visibility for this. Create a calculated field
DMZ – Display Filter Control
[pRegionType] = ‘Not West’
and then on the dashboard, select the Region filter and check the Control visibility using value option and select the DMZ – Display Filter Control field.
Bonus Challenge – Building the Viz
Recreate the viz as described above (or duplicate the sheet of the original viz, and remove all the pills from the Filter shelf.
Bonus challenge – Apply the filtering
Create a parameter
pSelectedRegion
string parameter, defaulted to <empty string>
This parameter is going to contain a string that can contain one or more Regions in a delimited format eg | East | or |East||South| etc. The contents of this string will determine how we filter the chart to mimic the required behaviour.
Firstly, we want the ‘1st level’ filter to determine whether we’re displaying just the West Region or all the other Regions.
Filter Region West or Not v2
(CONTAINS([pSelectedRegion],’West’) AND [Region] = ‘West’) OR (NOT CONTAINS([pSelectedRegion], ‘West’) AND [Region] <> ‘West’)
Add this to the Filter shelf and set to True. Show the pSelectedRegion parameter. With the parameter empty, the WestRegion should not display.
Type the word West into the parameter. Now the just the West Region should display.
And if you enter additional text alongside the word ‘West’, still the ‘West’ Region should display
But if you remove the ‘West’ text, all the Regions should display whatever the text is contained.
This behaviour is essentially simulating that of the ‘West’ | ‘Not West’ parameter selection in the previous version.
Now we want to control the 2nd level of filtering where the same parameter is used to drive which of the ‘other’ Regions display.
Filter Other Regions v2
CONTAINS([pSelectedRegion], ‘West’) OR NOT CONTAINS([pSelectedRegion],[Region])
Set the pSelectedRegion parameter to empty so all Regions are displayed. Add Filter Other Regions v2 to the Filter shelf and set to True.
Enter the text East into the parameter. The East option should disappear.
Add the text ‘South’. That too should disappear
Add the text ‘West’ and only the West Region will show
Play around entering multiple combinations of Regions. Ultimately if the text ‘West’ is present anywhere in the parameter string, only the West Region will display. If West is not present, then any other Region in the string will not be presented in the display. All sounds a bit backwards, but it works 🙂
So now we need to actually control how the pSelectedRegion parameter will get populated. And this will be via a parameter action fired from the selection made from a ‘custom’ legend sheet.
Bonus challenge – Building the filter control
On a new sheet, add Region to Rows and manually type in MIN(0.0) into Columns. Change the mark type to shape. Add Region to Label and show the labels (widen each row slightly). Edit the MIN(0.0) axis to be fixed from -0.1 to 0.5 which will shift the display to the left.
Sort the Region field by Sales descending.
Hide the axis, stop the Tooltip from displaying, hide the Region header, remove all gridlines/ axis rulers/ zero lines, row/column dividers. Set the background colour to light grey.
The Colour and the Shape (filled or unfilled) is determined based on the entries we have captured in the pSelectedRegion parameter, but the logic for each attribute is different.
Colour v2
If [pSelectedRegion] = ‘|West|’ THEN ‘West’ ELSE [Region] END
Show that parameter and make it empty. Add Colour v2 to the Colour shelf. Adjust colour to suit if not already set.
Then enter the text |West| – all the symbols should now all be Navy (or whatever colour you have chosen for West).
For the shape, create
Shape v2
IF CONTAINS([pSelectedRegion] , ‘West’) AND [Region] = ‘West’ THEN ‘Fill’ ELSEIF CONTAINS([pSelectedRegion], ‘West’) AND [Region] <> ‘West’ THEN ‘Empty’ ELSEIF ([Region] <> ‘West’) AND [pSelectedRegion]=” THEN ‘Fill’ ELSEIF ([Region] <> ‘West’) AND NOT CONTAINS([pSelectedRegion],[Region]) THEN ‘Fill’ ELSE ‘Empty’ END
and add to the Shape shelf. Note – this logic took a lot of trial and error to get the desired result.
Whenever the text West exists in the parameter, then the West Region should be a filled circle and all the other regions should be empty (the first 2 lines of the logic statement). If the parameter is empty, we want all the regions (except West) to be filled (so West will be empty). And if the parameter contains a Region(s) that isn’t West, we want that Region to be empty as well – only non-West Regions that aren’t in the parameter should be filled.
To control the text being passed into the pSelectedRegion parameter, we need a field
Region for Param
IF CONTAINS([pSelectedRegion],’West’) THEN ” //West has been selected again so reset parameter to empty ELSEIF CONTAINS([pSelectedRegion], [Region]) THEN REPLACE([pSelectedRegion], ‘|’ + [Region] + ‘|’ ,”) //selected region is already in the parameter, so remove it ” ELSE [pSelectedRegion]+ ‘|’ + [Region] + ‘|’ //append current region selected to the existing parameter string END
Add this to the Detail shelf.
Finally, we will want to ensure the marks aren’t highlighted on selection, so create fields
True
TRUE
False
FALSE
and add these to the Detail shelf too.
Bonus challenge – adding the interactivity
Build the dashboard again using layout containers and background colours and padding
Create a dashboard parameter action
Set Region
On selection of the Filter Control viz, set the pSelectedRegion parameter passing in the value from the Region for Param field. Set the field to <empty string> when deselected
Create a dashboard filter action
Deselect Marks
On select of the Filter Control viz on the dashboard, target the Filter Control sheet itself, passing in the specific fields of True = False.
And this should complete the required elements. My published viz is here.
Yoshi set this week’s challenge to build a page navigator, but there was so much more in it too, so this could be a bit lengthy 🙂
Note, I’m blogging based on the full ‘advanced’ challenge, to include an ‘apply all’ button as well. I built the following sheets to build this via and I’ll talk through the basics of each of them in turn
List of State names
Bar chart of total State sales
Line chart of monthly State sales
Jitter plot of State sales by order
Navigation page number buttons
Back arrow
Forward arrow
Filter summary
Apply button
Preparing the data
The data being presented is only applicable to the states of the US. In the latest versions of Superstore, information for both Canada and the US is included, so I started by adding a data source filter to include only Country/Region = United States (right click data source -> Add Data Source Filter).
Building the list of State names
Add State/Province to Rows, and apply a sort to sort by the field Salesdescending
Add State/Province to Text and to Colour. Adjust font to be bold and widen each row.
Create a new field
Index
INDEX()
and add to Rows before State/Province. Set the table calculation to be explicitly computing by State/Province. Index is essentially ranking each State from 1 to 49, as we’ve already sorted the listing of the states.
The requirement is to show up to 7 states on a page, so create
Page No
INT(((INDEX()-1) /7)) +1
Set to be a discrete field and add to Rows in front of Index. Again explicitly set the table calculation to compute by State/Province. This shows us which states are on which page.
We’re going to identify the page we’re on based on a parameter
pPageSelected
integer parameter defaulted to 1
Show the parameter, then create a new field
Is Selected Page
[pPageSelected] = [Page No]
Add to the Filter shelf. Initially select All. Then adjust/verify the table calculation is explicitly set to compute using State/Province. Then edit the filter to just show values that are True.
Adjust the pPageSelected parameter to test the functionality.
Hide the Page No, Index, and State/Province field from Rows (uncheck show header). Remove column dividers and don’t show the tooltip. Name this sheet States.
Building the bar chart
Note – to get the labelling and the spacing between the bars, this isn’t a ‘standard bar chart’. This is a technique that has been included in previous WOW challenges.
On a new sheet, add State/Province to Rows and sort by Sales descending. Add Sales to Columns and Add State/Province to Colour. Add a grey border to the bars (via Colour shelf).
Double click into Rows and manually type MIN(1.0) and change the Mark Type to bar. Add Sales to Size, then click on the Size button and adjust the size from Manual to Fixed and align right.
Add Sales to Label and align top left. Adjust the Tooltip. Add Index to the front of Rows and adjust the table calculation to be computing by State/Province.
Add Page No to the front of Rows and adjust to computing by State/Province.
Set the page to Fit Width. Show the pPageSelected parameter and add Is Selected Page to the Filter shelf and set to True. Verify the table calculation is set to compute using State/Province. If not adjust, and then recheck the filter is just showing True value.
Change the parameter to show page 2. You’ll notice the axis has now adjusted from 0 – $80,000 whereas on page 1 it went up to $450,000. We want to retain the axis scale across the pages. For this, create
Max Sales
WINDOW_MAX(SUM([Sales]))
Add this to the Detail shelf and ensure the table calculation is computing by State/Province.
Add a reference line to the bottom Sales axis (right click axis > add reference line) and set it to cover the entire table, using the average Max Sales value. Don’t show any label., tooltip or line
The axis will now have readjusted and display up to 450,000 regardless of the page you’re on.
Adjust the Min(1.0) axis to be fixed from -0.5 to 2 to add some white space around the bars.
Hide both axis, the Page No, Index and State/Province fields in Rows. Remove all gridlines, zero lines, axis rulers & tick marks. Remove column dividers. Add Row Banding.
Name the sheet Total Sales.
Building the line chart
On a new sheet, add State/Province to Rows and sort by Sales descending. Add Index and Page No to Rows too and adjust both table calcs to be explicitly computing by State/Province.
Add Order Date to Columns and adjust to be at the continuous Month/Year level (green pill ). You’ll notice the page numbering & indexes start to look odd – ie multiple states have same index.
Adjust the Order Date field to Show Missing Values, and our numbers are all aligned again.
If we just add Sales to Rows though, the indexes all mess up again due to the there being no values for some points.
To fix this create
Sales to Plot
ZN(LOOKUP(SUM([Sales]),0))
This returns 0 if there is no value for the date / state combination. Add this to Rows instead and adjust the table calculation so it is computing by both State/Province and Month Order Date.
Edit the Sales to Plot axis, so it is displays an independent axis range for each row or column – this makes the records near the bottom show peaks, rather than just a straight line.
Add Is Selected Page to the Filter shelf and set to True. Verify the table calculation is set to compute using State/Province. If not adjust, and then recheck the filter is just showing True value.
Add State/Province to Colour. Adjust Tooltip. Reduce the Size of the line.
Hide both axis, the Page No, Index and State/Province fields in Rows. Remove all gridlines, zero lines, axis rulers & tick marks. Remove column dividers. Add Row Banding.
Building the Jitter Plot
On a new sheet, add State/Province to Rows and sort by Sales descending. Add Index and Page No to Rows too and adjust both table calcs to be explicitly computing by State/Province. Add Sales to Columns and Order ID to Detail. Change mark type to circle.
Readjust the table calc settings of Page No& Index to also include Order ID, but also set the leval at State/Province.
Add State/Province to Colour, and reduce the opacity.
To get the marks to not overlap so much, create a new field
Jitter
RANDOM()
add add to Rows as a dimension. Again adjust the table calcs so Jitter is also included in the settings.
Add Max Sales to Detail and adjust the table calc settings to be computing over all 3 fields – State/Province, Jitter & Order ID.
Add a Reference line to the Sales axis across the entire table, using the average of Max Sales and don’t display any label/tooltip or line.
Add Is Selected Page to the Filter shelf and set to True. Verify the table calculation is set to compute using all fields and at the level of State/Province. If not adjust, and then recheck the filter is just showing True value.
Adjust the Tooltip. Hide both axis, the Page No, Index and State/Province fields in Rows. Remove all gridlines, zero lines, axis rulers & tick marks. Remove column dividers. Add Row Banding.
Name the sheet Sales by Order.
Building the Navigation Number Buttons
On a new sheet add State/Province to Rows and sort by Sales descending. Add Index to Rows and set the table calc to compute by State/Province. Move Index to Columns and State/Province to Detail.
Change the mark type to square and add Index to Label, aligning middle centre.
Create a new field
Colour – Page No
[Index] = [pPageSelected]
and add to the Colour shelf. Verify table calc is set to compute by State/Province. Adjust colours to suit and add a dark border.
We only want to show the indexes relating to the number of pages we have, which in turn is going to be based on what the data has been filtered by. So firstly we want to understand what the maximum number of pages is
Max Pages
IF SIZE()%7 = 0 THEN INT(SIZE()/7) ELSE INT(SIZE()/7)+1 END
If the number of results (ie number of states after filtering has occurred – the SIZE()) is exactly divisible by 7 (%7 = 0) then divide the results by 7 to get the max number of pages, otherwise, increment this value by 1. Eg if 14 results, it’ll be 2 pages, but 15 results will require 3 pages.
Now we know that, we can create
Pages to Show
INDEX() <= [Max Pages]
Add this to the Filter shelf.Set the True, Then adjust the table calc settings to be explicitly computing by State/Province for all nested calcs too.
Re-edit the filter to ensure it just shows True results.
Hide the Index from Rows and don’t show row/column dividers. Don’t show the tooltip. Name the sheet Page Nos.
Building the back arrow
On a new sheet, show the pPageSelected parameter, and change the mark type to Shape.
Create a new field
Show Page Back
[pPageSelected]>1
Add to the Shape shelf. If pPageSelected = 1, then False should display and adjust the shape to use a transparent shape (refer to this blog on how to set this up). Change the pPageSelected parameter to 2 and adjust the shape of the True option to be a filled arrow. Change colour to black.
On the dashboard ,we will need to define what page is being navigated to on click, so we need
Page Back
IF [pPageSelected]>1 THEN [pPageSelected]-1 END
Add this to the Detail shelf as a dimension.
Name the sheet Page Back.
Building the Forward Arrow
This is slightly more tricky than the back arrow, as we need to know how many pages are being displayed to know when we no longer need to show the arrow.
On a new sheet, add State/Province to Detail and sort by Sales descending. Remove the Lat/Long fields that automatically get added and change the mark type to shape. Create a new field
Show Page Forward
[pPageSelected]<[Max Pages]
and add to the Shape shelf. Set the table calc to be computing by State/Province explicitly. Set the mark type for ‘True’ to be a filled arrow and adjust colour to black.
Show the pPageSelected parameter and set to 7. Adjust the ‘False’ option to be a transparent shape.
Once again, on the dashboard, we will need to define what page is being navigated to on click, so we need
Page Forward
IF [pPageSelected]<[Max Pages] THEN [pPageSelected]+1 END
Add this to the Detail shelf as a dimension, and verify table calc is set to compute by State/Province explicitly.
We only want 1 arrow to show at most, so add Index to filter. Set to 1, then adjust table calc so it is set to compute by State/Province explicitly, and then re-edit filter to just select 1 again. Name the sheet Page Forward
Building the Filter Summary
On a new sheet add Category, Segment and Ship Mode to the Detail shelf and change the mark type to polygon..
Edit the Title of sheet and update as required
Name the sheet Filter Summary
Building the Apply Button
The basic outline for this is documented in this Tableau KB article here.
Create a calculated field
Apply
‘Apply Filters’
and add to Rows on a new sheet.
Add Category, Segment and Ship Mode to the Detail shelf and to the Filter shelf (set to All for each). Change the mark type to polygon. Right click the work ‘Apply’ in the column header and select hide field labels for rows.
Right click on the words ‘Apply Filters’ and select Format – set the shading of the header to teal.
As well as applying filters when the button is clicked, the page needs to reset to the first page. For this create
Reset Page 1
1
Add this to the Detail shelf as a dimension.
Adjust the size and colour of the font. Remove row dividers. Set the background of the worksheet to light grey. Remove the Tooltip. Name the sheet Apply Button.
Creating the dashboard
Now we have all the components, we can arrange the objects on a dashboard.
I added the 4 sheets making up the main viz int a horizontal container. All the sheets had the titles hidden, were set to fit entire view and had 0 padding, which gives the illusion of them all being a single viz. I added some outer padding to the container itself.
I used another horizontal container positioned above this one to add text boxes to give the viz headings.
Another horizontal container was placed above the title one. IN the left hand side I placed the Filter Summary viz., and in the right, I added a vertical container.
The vertical container had a blank and then a horizontal container underneath the blank object. The horizontal container then stored the page back, the page nos and the page forward sheets.
Another horizontal container was place above all this and I add the Apply Button sheet. I then moved the 3 filter objects automatically added to the sheet into this horizontal container too. I set the background of this container to light grey
Adding the interactivity
Multiple dashboard actions are needed to get the page to function as required. Now, I did have issues getting somethings to behave as I wanted, and I believe it was something to do with the order in which the actions were added. I can’t prove this… all I know is that I spent a long time trying to figure out why the filters I selected were getting reset when I pressed a page number, but removing all actions and adding again worked…
You need these actions
Apply Filters
Filter action that on select of the Apply Button sheet, targets all other sheets. Clearing the selection keeps filtered values. Category, Segment and Ship Mode should be passed through as selected fields.
Set Page No from Square
Parameter action that on select of the Page Nos sheet, sets the pPageSelected parameter passing in the value from the Index field that is not aggregated. Clearing the selection, keeps the current value.
Reset to Page 1
Parameter action that on select of the Page Nos sheet, sets the pPageSelected parameter passing in the value from the Reset Page 1 field that is not aggregated. Clearing the selection, keeps the current value.
Prev Page From Arrow
Parameter action that on select of the Page Back sheet, sets the pPageSelected parameter passing in the value from the Page Back field that is not aggregated. Clearing the selection, keeps the current value.
Next Page From Arrow
Parameter action that on select of the Page Forward sheet, sets the pPageSelected parameter passing in the value from the Page Forward field that is not aggregated. Clearing the selection, keeps the current value.
With these actions, you should be able to test the functionality, but you will find some fields become greyed out/ need clicking twice. We need to automatically ‘deselect’ them on click. For this I applied the basic principles discussed here.
Create new calculated fields
True
TRUE
False
FALSE
Add both these fields to the Detail shelf of the Page Back, Page Forward, Page Nos, and Apply Button sheets. Then add a dashboard filter action for each sheet.
Deselect Apply
On select of the Apply Button sheet on the dashboard, target the Apply Button sheet itself (ie not the object on the dashboard), passing the selected fields of True = False. Show all values when the selection is cleared.
Repeat the above for the Page Back, Page Forward and Page Nos sheets.
Hopefully with all this you have a fully functioning dashboard. My published viz is here.
For the challenge this week, Kyle asked us to recreate the visualisation above using an adapted version of Superstore which had a customer count metric for 3 dimensions (Category, Segment and Region) along with ‘no’ dimension (null) pre-aggregated at a Yearly or Monthly Level.
By this I mean that, at a Yearly level, when the date was 1st Jan 2019 say, a row of data existed for the (distinct) customer count of all the combinations of the 3 dimensions and null. In total 80 rows for the one date.
As the data was pre-aggregated, it made no sense to say the customer count for Technology is the sum of all the rows where Category = Technology and this would mean data was being double counted.
Pivoting the data also wouldn’t yield the desired result. So the aim of this challenge was to be able to identify the relevant rows of data that needed to be displayed based on the options selected by the user.
Building the calculations
Parameters will be driving the user selections, so these need to be set up
pDateGrain
string parameter with a list of 2 options: Monthly and Yearly. Defaulted to Monthly.
pColour
string parameter with a list of 4 options : Category, Region, Segment, None. Defaulted to Segment
Similarly, create pXAxis and pYAxis parameters similar to above, but default both to None.
On a new sheet build a tabular view with
Table Names, Category, Segment and Region on Rows
Order Date set to discrete (blue pill) exact date on Columns
Customer Count on Text
Show all 4 parameters created
The rows of data need to be filtered by Table Name (as defined by the pDateGrain parameter) and a combination of Category, Segment and Region based on the options selected in the other 3 parameters.
To filter by the Table Name we need
Filter – Date Grain
[pDateGrain] = [Table Names]
Add this to the Filter shelf and set to True.
Change the pDateGrain parameter to Yearly as there is less data to see/check.
Based on the options selected in any of the other 3 parameters, we need to find matching rows.
For example, if pColour is Segment and the other parameters are None, we are looking for the rows where the Segment column is not null, but the Region and Category columns are (we would be after the same rows if pXAxis was set to Segment, and the other parameters were None or, if pYAxis is Segment and the other parameters were None).
In this case, we’re looking for 3 rows of data – those highlighted below
If instead any two of the parameters were set to Segment and Category and the other None, then we’d be looking for rows where Segment is not null, Category is not null and Region is null. This would be 9 rows in total (a snippet of which is shown below).
We also need to deal with scenarios where all three parameters were set to something different, or all set to None as well as handle if multiple parameters are set to the same thing.
Now to do this, I ended up building a single field to use as filter that contains all the scenarios. As I was building it up, I figured there should be a slicker way, and there is (check out Kyle’s solution), but if your brain is wired the same way as mine, then you’ll end up with this
Filter – Rows to Include
IF [pColour] = ‘None’ AND [pXAxis] = ‘None’ AND [pYAxis] = ‘None’ THEN //no options selected IF ISNULL([Region]) AND ISNULL([Category]) AND ISNULL([Segment]) THEN TRUE END ELSEIF (([pColour] <> ‘None’ AND [pXAxis] = ‘None’ AND [pYAxis] = ‘None’) OR ([pColour] = ‘None’ AND [pXAxis] <> ‘None’ AND [pYAxis] = ‘None’) OR ([pColour] = ‘None’ AND [pXAxis] = ‘None’ AND [pYAxis] <> ‘None’)) THEN // one of the 3 options selected, so work out which dimension IF [pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’ THEN IF ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END ELSEIF [pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’ THEN IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END ELSEIF [pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’ THEN IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END END ELSEIF (([pColour] <> ‘None’ AND [pXAxis] <> ‘None’ AND [pYAxis] = ‘None’) OR ([pColour] <> ‘None’ AND [pXAxis] = ‘None’ AND [pYAxis] <> ‘None’) OR ([pColour] = ‘None’ AND [pXAxis] <> ‘None’ AND [pYAxis] <> ‘None’)) THEN // two options selected, so work out which dimensions we need IF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) THEN IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END ELSEIF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END ELSEIF ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN IF NOT ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END //or the two options selected are the same dimension ELSEIF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) THEN IF ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END ELSEIF ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) THEN IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END ELSEIF ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END END ELSE //all three selected, but they could be all the same dimension or 2 of the three the same IF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN //all three different IF NOT ISNULL([Region]) AND NOT ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END ELSEIF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END ELSEIF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) THEN IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END ELSEIF ([pColour] = ‘Region’OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) THEN IF NOT ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END ELSEIF [pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’ THEN IF ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END ELSEIF [pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’ THEN IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END ELSEIF [pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’ THEN IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END END
END
Blimey! A bit monolithic I know, but it just grew organically as I tried out the different scenarios step by step. Unfortunately the above doesn’t copy over the formatting nicely, as there are nested (tabbed) IF statements which makes it (a bit) easier to read.
Suffice to say, I’m not going to walk through step by step, but it’s checking for all the different permutations are discussed above, and marking the relevant rows as True. This field can then be added to the Filter shelf and set to True.
Kyle’s solution, essentially replaces this one calculated field, with 3 calculated fields – 1 per parameter – which are all then added to the filter shelf. It’s much neater 🙂
So now we’ve identified the rows we want based on parameters, but there is also the ability to filter the rows further based on the values of the Category, Segment or Region.
Add each of the 3 fields to the Filter shelf and select the All option, then show the filters on the view. For each of the Category, Segment and Region filters, set the option to show Only Relevant Values. This will prevent the NULLs from showing as an option when the relevant dimension is listed as one of the parameter selections
As you can see from the above image though, Region is only showing Null, and this is because in the example above, Region isn’t selected as an option for the pColour, pXAxis or pYAxis parameters. When it comes to the dashboard, we don’t want the Region filter to be visible in this case. To help with this, we need 3 further calculated fields.
Show Filter – Region
[pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’
This returns True if one of the 3 parameters contains the value ‘Region’. Similarly, create Show Filter – Category and Show Filter – Segment fields.
The final calculated fields we need are to help build the ‘cross tab’ view.
X-Axis
CASE [pXAxis] WHEN ‘Category’ THEN [Category] WHEN ‘Region’ THEN [Region] WHEN ‘Segment’ THEN [Segment] ELSE ” END
Y-Axis
CASE [pYAxis] WHEN ‘Category’ THEN [Category] WHEN ‘Region’ THEN [Region] WHEN ‘Segment’ THEN [Segment] ELSE ” END
Colour
CASE [pColour] WHEN ‘Category’ THEN [Category] WHEN ‘Region’ THEN [Region] WHEN ‘Segment’ THEN [Segment] ELSE ” END
Now we’ve got all the fields needed to build the viz.
Building the viz
The quickest way is to duplicate the sheet we’ve built, as all the filters need to apply, so
Duplicate the sheet
Remove all the fields from Rows
Change the Order Date field on Columns to be continuous (green pill)
Add X-Axis to Columns
Add Y-Axis to Rows
Move Customer Count to Rows
Add Colour to the Colour shelf.
Adjust the colours to suit.
Change the value of the option in the pColour parameter, and readjust the colours. Repeat so that colours are set for Category, Segment and Region.
Add Colour to the Label shelf
Remove all gridlines, axis and zero lines. Remove the Y-Axis and X-Axis row/column labels by right clicking the text and selecting Hide field labels for rows/columns. Edit the Order Date axis (right click axis -> Edit) and remove the axis title.
Add Order Date to Tooltip and format it to the ‘March 2001’ date format. Adjust the tooltip as below
Hiding the filters
Add the viz to a dashboard and arrange the parameters and filter controls in the relevant location. I used layout containers to help with the organisation.
Select the Category filter and on the Layout tab, select the Control visibility using value checkbox and select the Show Filter – Category field.
Repat the same steps for the Region and Segment filters, selecting the equivalent calculated fields.
In this week’s challenge, Erica set us the task of building a filter that only contained a subset of the dimension values – ie a set of core values always had to remain in the view, and weren’t available to be filtered out.
Erica advised there were hints available, and that she had solved the problem herself via an existing Tableau Knowledge Base article.
The requirements stated Sets were involved and so I attempted down this path, creating a set to store the ‘core’ cities (as per the requirements), and then using a combined set of all cities and ‘core’ cities to just display the values not in ‘core’. However I couldn’t get things working, so I checked out the hints.
The first hint alluded to 2 sheets, which initially I thought one for the ‘core’ cities and one for the rest, but quickly realised this would only work if there hadn’t been the additional ‘bonus’ requirement to sort the data based on the sales (ie the core cities and the rest could become interspersed in the viz).
So after further fiddling, and unsuccessful ideas, I ended up referencing the KB article and built out a solution. After publishing, my good friend and fellow #WOW participant Rosario Gauna, published her solution which she managed in a single sheet, and in a manner that was much more elegant. So it’s a double solution guide today – what I did based on the KB article and a recreation of Rosario’s solution (so I have this to reference and remind myself if ever I have the need to recreate).
Solution 1 – The 2 sheet solution
Firstly, create a set called Key Cities (right click on City > Create > Set) and select the 5 cities listed in the requirements.
Key Cities
On a new sheet, add State/Province to the Filter shelf and choose Ohio, then add City to Rows and Sales to Columns and sort descending. Add Key Cities to Colour and adjust accordingly.
Call this sheet Sales by City
On a new sheet, add State/Province to Filter and select Ohio, then add City to Rows. Call this sheet FilterSheet.
Duplicate the City field (right click field in the data pane and select Duplicate). This will create a new field called City (copy) in the data pane.
Add City (copy) to the Filter shelf of the Filter Sheet sheet, select the 5 core cities and then check the Exclude checkbox.
Add theoriginal City field to the Filter shelf as well and select All. Show the filter on the sheet, and adjust so it displays Only Relevant Values
The list of options in the filter list should only show the cities in Ohio that aren’t one of the five key states.
Set the City filter to Apply to Worksheets > Selected worksheets and select the Sales by City worksheet
Customise the City filter in the Filter Sheet sheet so that the All option does not display. From the context menu of the City filter control, select Customise and ensure Show ‘All’ Value is unchecked.
Navigate back to the Sales by City sheet and show the City filter values, ensuring all are displayed. This list will include the key cities, but don’t worry. Uncheck a value that isn’t in the list of key cities eg Bowling Green. The city will disappear from the viz, but if you navigate to the Filter Sheet, you should also see the value is unselected in that list too.
This is the filtering behaviour we’re after – selections made to the City filter on the Filter Sheet affect the values in the City filter on the Sales by City sheet.
Now we need to address the sorting.
Again I think I ended up doing something a bit more complicated than needed – check out the sorting described in the 2nd solution, as that would apply here too – it just isn’t what I did at the time.
Firstly, we need a parameter to determine which sort selection to use
pSort
string parameter containing two list entries Key Cities and Sales, defaulted to Key Cities
I decided I wanted to sort by a number, which for Sales was fine, but when Key Cities was selected, I needed to ensure the values for the Key Cities were always greater than the maximum value for the non key cities. For this I needed to get a handle on the value of sales for the non key city that had the largest sales.
Max Non Key Sales
{FIXED : MAX(IF NOT [Key Cities] THEN ({FIXED [State/Province], [City] : SUM ([Sales])})END)}
If the city is not a key city, then get the total sales for each State & City (potential that a city can exist in multiple states, hence the need to declare the State), and then return the max of those.
To see what this is doing, on a new sheet, add State/Province to Filter and select Ohio, then add Key Cities and City to Rows and Sales to Text and sort by Sales descending
We’re looking for the value 8203 as this is the largest sales for the cities not tagged as a Key City.
Add Max Sales Non Key City to the view…. the value doesn’t match what we expected.
This is because a FIXED level of detail (LOD) calculation works across the entire data set, so the fact we’ve filtered by Ohio is being disregarded. To resolve this, set the State/Province field on the Filter shelf so it is Add To Context
This pill will change to grey, and the values should update, as now the LOD is being applied after the context filter has been applied.
With this we can work out a sort field
Sort
CASE [pSort] WHEN ‘Sales’ THEN SUM([Sales]) * -1 ELSE (IF ATTR([Key Cities]) THEN SUM([Sales]) + SUM([Max Sales Non Key]) ELSE SUM([Sales]) END) * -1
END
If we’re sorting by Sales then use the total Sales value * -1, otherwise, if we’re sorting by Key Cities then, if the City is a key city, then add the total sales to the max sales value, otherwise just use the total sales value. Multiple the result by -1. By adding this value, it ensures the values for the Key Cities are always larger than those for the non key cities. The -1 means the sort will be descending.
Test this out, by adding the Sort field as a discrete (blue) field to the Rows of the test sheet we’ve been using above. Ensure the Sort field is listed first, and move the Key Cities field to be third. Show the parameter control, and test switching between the options. The values in the Sort field are always in an ascending order, but the displayed Sales values will be ordered depending on the sort option chosen
Back on the Sales by City sheet, add the Sort pill to the Rows before the City pill, and add the State/Province filter to context.
Hide the Sort field.
The labels need to be displayed inside the bars, so for this we need a dual axis.
Add another copy of Sales to the Columns. On the second Sales marks card, set the option to Show mark labels from the Label shelf. We need the text of the label to be different to the existing bars, so create a duplicate of the Key Cities field, so we have Key Cities (copy) and add this to the Colour shelf of the second marks card. Adjust the colours accordingly to white and black.
Change the mark type of the 2nd marks card to Gantt bar, reduce the opacity of the Colour to 0% and reduce the Size to as small as possible. Adjust the alignment of the Label to left middle, and set the font to be bold and match mark colour.
Make the chart dual axis and synchronise the axis. Set the mark type of the first marks card back to a bar if it changes.
Remove all row and column dividers, and hide the top axis. Hide the City column label too. Edit the bottom axis, and fix to start from 0 and end automatic. Adjust the tick marks to display every 5000 values.
Add this sheet to a dashboard. Remove the colour legends that automatically get added and remove the City filter control too. Leave the sort parameter.
Then add the Filter Sheet as a Floating object and position bottom right. The City filter for this sheet should also automatically display. If it doesn’t show it (click the context menu of the Filter Sheet object > Filters > City).
Change the City filter to be a multiple values dropdown control and set it to be fixed (unselect the Floating option on the context menu).
Now hide the title on the Filter Sheet object and resize to make it teeny tiny, so you can’t see anything
Now you have the core objects needed for a functional dashboard – you’ll just want to take some time moving them into place, and excluding other Cities.
So shout out again to Rosario Gauna, as this is actually her solution!
We’ll build this out in a table first, so we can see what’s going on.
On a new sheet add State/Province to Filter and select Ohio, then add City and Key Cities (the set created above) to Rows. Add Sales to Text.
What we’re going to do is create another set which will just contain the cities not identified as key cities. For this, we need to store against every row (including the key cities) the name of a City that isn’t a key city. For those that already aren’t a key city, that is just its own City name, but for those that are key cities, we want to store a non key city…. sounds confusing right…. let’s build this up.
Firstly, let’s just get those non key cities
Non Key City
IF NOT [Key Cities] THEN [City] END
Add this to the sheet. It shows NULL against all the Key Cities and the City value for all the others
We’re going to use this to set a ‘default; value against the key cities.
Min Non Key City
{FIXED: MIN([Non Key City ])}
Theis returns the value from the Non Key City field which is alphabetically first. Using MAX would work just as well. When we add this to the sheet, we also need to set the State/Province filter to be Add to Context, otherwise we get a City from the whole data set, and not just Ohio.
We can now create a field that will just contain a distinct list of the non key cities
Other Cities
IF NOT [Key Cities] THEN [City] ELSE [Min Non Key City] END
Add this to the sheet
For every City, the Other Cities field contains a non key city value. Now we have this, we can create a set from this field
Other Cities Set
Ensure all values are selected – don’t worry that you can see cities that aren’t relevant at this stage
Add Other Cities Set to the Filter shelf and also add to the Rows shelf next to the Key Cities field. From the context menu of the Other Cities Set on the Filter shelf, select Show Set. The list of non key cities should be displayed. If there’s more than you expect, ensure the control is set to All Values in Context.
If you uncheck Bowling Green from the list, all the key cities and Bowling Green will disappear, but we don’t want this. We only want the row where City=Bowling Green to disappear. For this we need
Records to Keep
[Key Cities] OR [Other Cities Set]
Add this to the Filter shelf and set to True. Remove the Other Cities Set from the Filter shelf (the list should remain). Now if you remove Bowling Green, only that row should disappear, and if you uncheck all, so no city is selected, the key cities should remain
Adjust the display so the rows are sorted by Sales descending (either use the Sort button in the toolbar, or set the sort on the City field).
Then create a field
Sort Order
CASE [pSort] WHEN ‘Key Cities’ THEN [Key Cities] ELSE TRUE END
Add this to the Rows in front of City, and show the pSort parameter (see above for details if you haven’t created this yet). When pSort is set to Key Cities, manually change the order of the values displayed so True is listed before False. The rows are displayed in descending Sales value for each Sort Order Value. When Sales is selected to sort by, the Sort Order is true for all rows.
Now we’ve got all the components needed to build the viz, and you should be able to adapt the steps above to get it to work. The key difference is using the Records To Keep field on the Filter shelf, displaying the members of the Other Cities Set to control the filtering, and managing the sort using the Sort Order field instead.
My workbook showing this solution is published here.
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 