Filter Challenge : How well do you know Tableau’s Order of Operations?

It was Erica Hughes’ turn to set the #WOW challenge this week. This ended up being a bit of a journey for me, because of one requirement – to use the max date of the dataset.

I typically use a FIXED LOD to determine the max date, which I did initially in this case, and after building a solution, tripped up when it came to adding some of the filters ‘to context’. The context filter meant my Max Date was changing depending on the filter and subsequently I wasn’t getting the right results for the ‘days since last purchase’.

So I ended up having to put my thinking cap back on, and after a lot of trial and error and reading on the internet (including this article by the Flerlage Twins) , I finally managed to get a solution that involved both LOD calculations (including a rarely used INCLUDE LOD) and Table calculations, and no context filters at all. This was a pretty complex solution. The table of data had to include the Order Date on the Detail shelf, resulting in multiple rows per customer showing, which meant I had to then use an INDEX()=1 filter to only show 1 row for each customer. I then used an additional INDEX() against each customer, so I could filter to show only the customers in position 1-10. My solution to this is published here.

After publishing, I checked Erica’s solution and found how she’d handled the max date requirement. It used a concept I haven’t had to use so far, so I decided to rebuild a less complex solution, which is what I will now blog about.

Capturing the max date of data set which isn’t affected by context filters

The usual way to define the maximum date in the data set is to create a FIXED LOD calculation, and this is still required

Max Date

{FIXED :MAX([Order Date])}

This returns 30 Dec 2021.

The problem is, once a context filter is applied to a sheet, the value of this field can change. For example, if Region is applied as a context filter and set to ‘South’, then what Tableau will do, based on the ‘order of operations’, is first filter all the data to just those records where Region=South. It will then work out the max date of these filtered records, and if there are no orders on 30 Dec 2021 for the South Region, then the value of the Max Date field will differ. This is because context filters get applied before FIXED LOD calculations. We need a way to ensure we can retain the 30 Dec 2021 without hardcoding it.

The solution is to use a parameter.

pMaxDate

A date parameter which is set to use the value of the Max Date field when the workbook is opened.

This is quite a sneaky but clever way to retain this, which is why I’m reworking my solution to use it, so I have something for future reference myself. When the workbook opens, the pMaxDate parameter will get set to 30 Dec 2021 and won’t ever change, whereas the Max Date field will change if context filters are used.

Building the Table

It may seem a bit odd, but I’m going to start with the table of data that’s displayed, as this requires the most calculations, and the most validation.

On a new sheet, add Customer Name, Customer ID to Rows and Sales onto Text. Order by Sales desc. Format Sales to $ with 0dp.

We need to determine the latest order date for each customer in order to work out other information.

Latest Order Date

{FIXED [Customer ID] : MAX([Order Date])}

This finds the maximum Order Date for each Customer.

And with this, we can now work out

Days Since Last Purchase

DATEDIFF(‘day’, [Latest Order Date], [pMaxDate]

Note this is comparing the Latest Order Date to the pMaxDate parameter instead of Max Date.

Format this to custom number, with 0 dp and a suffix of ‘ days’.

Add Latest Order Date to Rows (exact date, discrete) and Days Since Last Purchase into the table.

Now we need to work out

Latest Order Amount

{FIXED [Customer ID]: SUM(IF [Order Date]=[Latest Order Date] THEN [Sales] END)}

For each customer, if the Order Date is the same as the Latest Order Date, then retrieve the Sales value.

Format this to $ with 0 dp and add to the view.

Now add Customer ID to the Filter shelf and set to filter by the Top 10 based on Sales.

To validate the behaviour of the calculations add Region to the Filter shelf and select ‘South’. You’ll notice only 8 rows are shown, and not 10.

This is because the data has been filtered based on the top 10 customers with the most sales first, and then restricted those top 10, to those with sales in the South. Only 8 of the top 10 customers have sales in the South, hence the 8 rows.

To resolve this, we need to add Region to Context (right click on the filter and Add to Context . This has the effect of filtering all the data by Region = South first, and then displaying the top 10 customers by Sales. We now have 10 rows displayed.

Add State to the Filters shelf, and add that to context too. Test the table of results by selecting different combinations of regions and/or states and comparing to the results on Erica’s solution to verify all the calculations are behaving as expected.

Now we’re happy the table is displaying the data as expected, we can format it and make it ready for the dashboard :

  • Remove Latest Order Date & Customer ID
  • Set the Row Banding
  • Set the Row Dividers to Level 0, so only row lines appear at top and bottom
  • Remove Column Dividers
  • Format all text (row/column headings & text data) to be 8pt.
  • Alias Sales to be Total Sales (right click on the Sales title in the columns and Edit Alias)
  • Remove Region & State from the Filter shelf. These will get re-added later.

Building the Map

On a new sheet, double click State to load a map (you may need to set your location to United States : Map menu -> Edit Locations).

Change mark type to a Filled Map, add Region to Colour and adjust colours.

Remove all map layers (Map menu -> Map Layers and uncheck all options listed on left hand side). Change border on Colour shelf to white.

Remove row & column dividers, and remove all map options (Map menu -> Map Options and uncheck all options).

Drag a new instance of State onto the canvas and Add a Marks Layer. This will create a 2nd marks card on the left. Change the colour of the circles to black. Add Sales to the Size shelf and adjust. Adjust the tooltip of both marks card (you’ll need to add Sales to the Tooltip on the map marks card).

Building the Legend

On a new sheet add Region to Columns and type in MIN(1) into the Columns shelf too. Add Region to the Label shelf, and the Colour shelf. Adjust the height of the rows, so you can see the text.

Edit the axis so it is fixed from 0 to 1. Then format the Label so the font is larger and centred.

Hide the axis and the Region (uncheck show header). Remove row & column dividers and any gridlines. Adjust the border on the Colour shelf to be white. Set the tooltip not to show.

Building the Bar Chart

On a new sheet, add Sub-Category to Rows and Sales to Columns. Order by Sales desc. Add Sub-Category to the Filter shelf and set to show Top 10 by Sales. Reduce the Size of the bars, and align the row labels to be left aligned, and the font to be 8.

Format the axis, so the values are displayed in $K.

Hide the row label, and adjust Tooltip. You may need to create an additional calculated field based on Sales to add to the Tooltip which you can then format to $ with 0dp.

Adding the interactivity & context filters

Add the sheets onto a dashboard. You’ll need to use a mixture of vertical & horizontal layout containers, inner and outer padding and background colours to get the required layout.

Add a dashboard filter action which runs on Select when the Legend sheet is selected, which affects all other sheets and shows all values when unselected.

Add another filter dashboard action, which this time runs on Select when the Map sheet is selected, which affects all sheets except the Legend sheet, and also shows all values when unselected.

Now click on one of the regions in the legend, then click on a state in the map. This has the effect of adding filters to the shelves on the other sheets. Navigate to the Table sheet, and add the 2 ‘Action’ filters to context

Do the same for the Bar char sheet.

And you should now have a completed viz. My published version based on this solution is here.

Happy vizzin’!

Donna

Let’s switch those measures!

It was time for Lorna to add her iteration to the dashboard challenge that’s been set over the last few weeks. The challenge this week was to incorporate a measure swap control which allows the user to select which measures they want to compare.

Lorna gave the option to build out either your own or an already published solution, or to build a simpler brand new viz that just demonstrates the technique.

By incorporating this measure swap control into an existing solution, existing calculated fields and other objects will need to be adjusted/renamed. I started to build against my own solution, but that already had multiple instances of calculated fields where I’d chosen to change from a table calculation version in week 5 to an LoD based solution in week 6. I decided instead to use Kyle’s solution from week 7, which is published here.

However, for the purposes of this blog, I will build out the simple solution, so the focus is on what you need to do to build the control.

Building the Measure Selector

So to start, I simply copied the text for the secondary data source off the challenge page

I then opened Tableau Desktop, connected to the Orders sheet of the relevant Superstore Sales excel spreadsheet, then on the Data menu, selected Paste, which added a ‘clipboard’ data source and presented the data on a sheet.

To start with I chose to Alias the Number field (right click > Aliases) to map the numbers in the data source to the names of the measures we want, as below

The values selected are going to be captured within a parameter, so I then set these up

pMeasureX

Integer parameter defaulted to 1 (ie Sales)

I created a similar pMeasureY too, this one defaulted to 3 (ie Profit Margin).

For the selector control, we need to indicate which measure has been selected, so we need

X Selected?

[pMeasureX]=[Number]

and

Y Selected?

[pMeasureY]=[Number]

I then added Number to Rows and created 3 MIN(1) fields on the Columns shelf

On the first MIN(1) marks card, I changed the mark type to Shape, then added X Selected? to the Shape shelf. I adjusted the shape using options from the Ratings shape palette, and adjusted the size to suit.

On the 2nd MIN(1) marks card, I changed the mark type to Circle, reduced the size to as small as possible, and changed the colour to be completely transparent (Opacity = 0%). I then added Number to the Label shelf, changed the alignment to be Middle Centre, and adjusted the height of the rows, so all the text labels are visible.

On the 3rd MIN(1) marks card, I repeated the steps taken for the 1st marks card, but added Y Selected? to the Shape shelf instead.

I then hid the axes and the Number pill (uncheck Show Header), and removed all row/column borders and gridlines/zero lines.

When this sheet is added to the dashboard, we’re going to need to add parameter actions to pass information from this sheet into the pMeasureX and pMeasureY parameters. For this I found I needed to create copies of the Number field, as using the same field to control both selectors caused me some issues. So I created

Number (X)

[Number]

and added this to the Detail shelf of the 1st MIN(1) marks card.

And then

Number (Y)

[Number]

which was added to the Detail shelf of the 3rd MIN(1) marks card.

Another feature of this sheet when added to the dashboard, is we don’t want the circle selected to be highlighted/remain selected, so we’ll use a filter action to solve that. But again we need some additional fields on the sheet to help us with that. Create calculated fields

True

TRUE

False

FALSE

And add both of these to the Detail shelf of the All Marks card.

Finally adjust the title of the sheet to contain the text ‘X Select a Measure Y’. You’ll need to play around with the number of spaces to use. It’s best to wait until the sheet is on the dashboard to get it right.

Building the Scatter Plot

On a new sheet, the first thing needed for this challenge is to create

Profit Ratio

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

Now we need to define fields that will determine what measure to display based on the value stored in the relevant parameter

X Measure

CASE [pMeasureX]
WHEN 1 THEN SUM([Sales])
WHEN 2 THEN SUM([Profit])
WHEN 3 THEN [Profit Ratio]
WHEN 4 THEN SUM([Quantity])
END

Y Measure

CASE [pMeasureY]
WHEN 1 THEN SUM([Sales])
WHEN 2 THEN SUM([Profit])
WHEN 3 THEN [Profit Ratio]
WHEN 4 THEN SUM([Quantity])
END

Add X Measure to Columns and Y Measure to Rows, and add State to Detail.

We need to label the X & Y axis based on the name of the selected measure, so we need

X Dimension

CASE [pMeasureX]
WHEN 1 THEN ‘Sales’
WHEN 2 THEN ‘Profit’
WHEN 3 THEN ‘Profit Ratio’
WHEN 4 THEN ‘Quantity’
END

Add this to the Columns shelf

Y Dimension

CASE [pMeasureY]
WHEN 1 THEN ‘Sales’
WHEN 2 THEN ‘Profit’
WHEN 3 THEN ‘Profit Ratio’
WHEN 4 THEN ‘Quantity’
END

Add this to the Rows shelf

Edit both axes and remove the title from both. Right click on the word ‘Profit Ratio’ and Rotate Label. Right click on ‘X Dimension’ at the top and select hide field labels for columns. Right click on ‘Y Dimension’ on the left and select hide field labels for rows. Format to remove the row and column gridlines.

The State a user clicks on/selects needs to be captured within a parameter

pSelectedState

String parameter defaulted to New Jersey

We then need to determine if the State matches that selected

Is Selected State?

[State]= [pSelectedState]

Change the mark type to Circle, then add Is Selected State? to the Colour shelf and adjust to suit. Add a border to the mark too. Drag the ‘True’ value on the colour legend so its listed above ‘False’.

Add Is Selected State? to the Size shelf too, and adjust so the sizes are reversed, and alter the range.

Use the slider on the Size shelf too to make all the circles smaller too if need be.

Adjust the Tooltip so the text will be dynamic based on the measure selections too.

Then, modify the title of the sheet, so it too is dynamic.

Finally, this sheet too also needs to ensure the State circle clicked on doesn’t remain ‘selected’ so as above, create a True = TRUE and a False = FALSE calculated fields, and add these to the Detail shelf.

Adding the interactivity

Add both the sheets to a dashboard.

Create the following dashboard actions:

Set X Measure

A parameter action, that runs on Select of the Measure Selector sheet and passes Number (X) into the pMeasureX parameter.

Create a similar parameter action called Set Y Measure that passes Number (Y) into the pMeasureY parameter.

Create a dashboard filter action Selector Sheet – Unhighlight which on select of the Measure Selector sheet on the dashboard, targets the Measure Selector sheet directly, passing True = False into the filter. All values should show when the selection is cleared.

With these 3 actions, you should be able to test what happens when you click the different combinations.

Now we need 2 further dashboard actions

Set Selected State

A parameter action that on select of the Scatterplot passes the State field into the pSelectedState parameter.

Add finally, add another dashboard filter action, Scatter – Unhighlight that on select of the Scatter sheet on the dashboard, targets the scatter sheet directly, passing the fields True = False as the filter.

Hiding the Measure Selector

On the dashboard, select the Measure Selector sheet and select the Add Show/Hide Button from the context menu.

This will create a X button that can then be moved and positioned where required.

On the context menu of the X button, select Hide and the measure selector sheet will disappear, and the X will be replaced by a different image

Select Show from the context menu of this button, and the sheet will reappear. Test the functionality in presentation mode where simply clicking the button will invoke the show/hide behaviour.

And this should be the core functionality to demonstrate this measure swapping feature. My published viz based on this more basic solution is here.

If you’re aiming to build on the viz you’ve built over the last 3 weeks, then you need to replace any references you had to SUM([Sales]) and [Profit Ratio] in the various calculations with the X Measure and Y Measure fields accordingly. You’ll also need to adjust tooltips and titles of the various sheets using the X Dimension and Y Dimension fields as appropriate. My adapted solution built on from Kyle’s week 7 solution is here.

Happy vizzin’! Stay Safe!

Donna

Let’s analyse wildlife strikes

Sean Miller began the start of #WOW2022 with this challenge where the focus was on layout, interactivity and maps.

  • Building the map
  • Building the bar
  • Building the area chart
  • The Unknown indicator
  • Adding interactivity

Building the map

I’m starting with this as in order to build the map, I found after a bit of trial and error I needed to build a data model which related the wildlife strike data source provided by Sean with the spatial file data source provided via the link to the community post. I downloaded all the files, but found the link to the us_ak_hi_territories_shift_conformal_faux_WM.hyper.zip was the file I needed once unzipped (the middle file to download from Sarah’s post on 19 July 2020 (see below)

Once I unzipped the downloaded file I copied the us_ak_hi_territories_shift_conformal_faux_WM.hyper file to my usual data sources repository on my laptop.

I then connected to Tableau and built a data model by first connecting to the wildlife strikes csv file, then adding a relationship to us_ak_hi_territories_shift_conformal_faux_WM on State Name = Name

To make things clearer, I created a fields to store the number of incidents

Wildlife Incidents

COUNT([2022_01_05_WW01_FAA Wildlife strikes (1990-2021).csv])

This is simply referencing the ‘count’ field that is automatically generated that is related to the wildlife strike data source.

I then built the map by

  • add Geometry and Name to Detail
  • add Wildlife Incidents to Colour

This should have created the below

Remove all the background imagery via the Map > Map Layers menu – uncheck all the options from the left hand pane.

Add Name to the Filter shelf and exclude American Samoa, Guam, Northen Marianas, Puerto Rico and the Virgin Islands. This will remove the cluster of shapes to the right (I’m not sure if this is the expected method or not..).

Change the colour palette to use the red-gold colour range.

Finally amend the Tooltip accordingly and also remove the row and column dividers.

Building the bar

The bar chart displays the top 10 incidents by species type, with the rest all grouped under ‘other’, and displayed at the bottom. We need to create a set for this. Right click on Species Type and Create > Set. Create a set based on the top 10 of the count of the wildlife incidents data source.

Species Type Set

We then need a field to display the info in the bar

Species Type to Display

IF [Species Type Set] THEN [Species Type] ELSE ‘Other’ END

ie if the Species Type is in the Species Type Set then display the Species Type, otherwise display Other.

Add Species Type Set and Species Type To Display to Rows and Wildlife Incidents to Columns and sort descending (just click the sort descending button in the toolbar)

Add Species Type Set to the Colour shelf and adjust accordingly. Remove the column and row dividers and the row gridlines. Adjust the Tooltip.

The final step we need is to make the title dynamic and display a state name if filtered.

Firstly we will need a parameter to capture the selected state

pSelectedState

A string parameter defaulted to <empty string>

We will use a parameter action to populate this parameter later. We need an additional field to use in the chart title

Title: Selected State

IF [pSelectedState] <> ” THEN ‘in ‘ + [pSelectedState] ELSE ” END

Add this field onto the Detail shelf, then adjust the chart title

Building the area chart

Add Incident Year to Columns and Wildlife Incidents to Rows and change to mark type = Area. Adjust colour accordingly, and remove the column gridlines. Adjust the Tooltip.

Again the chart title needs to be dynamic based on state name and the species type selected, so we’ll need another parameter

pSelectedSpecies

string parameter defaulted to <empty string>

Add Title: Selected State to Detail and adjust the title as below

The Unknown indicator

On a new sheet double click in the space below the marks card to create a ‘type in’ pill

Enter the text ‘Unknown Location’ (including the single quotes) and the add this pill onto the Text shelf.

Change the mark type to square and adjust the Size to the maximum.

Add Wildlife Incidents to the Tooltip shelf. Then add Name to the Filter shelf and filter to only show the Null values. Adjust the colour and the Tooltip.

Adding interactivity

Create a dashboard and use layout containers to position the charts in the relevant places. The colour legend and the ‘unknown’ indicator will need to be ‘floated’ into position.

We’re going to need 4 dashboard actions; one to set the selected State, one to set the selected Species Type, one to filter the bar and area chart based on the the selected state, and one to filter the map and area chart based on the selected species.

Select Species

this is a parameter action to set the pSelectedSpecies paramater on selection of the bar chart, using the value in the Species Type To Display. The parameter should be reset to <empty string> when unclicked.

Select State

similar to above, but runs on selection of the map chart, and passes the Name field into the pSelectedState parameter.

Filter by State

This is a filter action that runs on selection of a state in the map chart. It affects all other charts on the dashboard except the unknown sheet. It should only filter on the Name field and not All fields.

Filter by Species

Another filter action, that runs on selection of the bar chart. This one does impact all the other charts on the dashboard, but again only filters based on the Species Type to Display field rather than all fields.

Hopefully, with all this, you should have a working solution. My published viz is here.

Happy vizzin’!

Donna

Tableau Coaches’ Favourite Challenges Advent Calendar

Candra set this seasonal fun challenge this week. It may not deliver data visualisation best practice, but as a ‘seasoned’ #WOW participant, this provided a chance to use a technique I rarely use (background images) and learn a brand new technique (colouring a bar chart with a gradient).

Getting set up

To start you’ll need to download all the files Candra provided, which will consist of the data (across 2 sheets) and 3 images.

The bauble and star image will need to be saved into your Tableau Shapes repository – I copied them into a new folder I called Xmas in this directory ..\My Tableau Repository\Shapes

The tree image just needs to be saved somewhere you’ll remember.

Building the Tree Chart

Connect to Sheet1 of the downloaded WOW Advent Calendar excel file.

Drag X to Rows and Y to Columns and disaggregate the marks (Analysis -> uncheck Aggregrate Measures)

You should get the basic layout of where the baubles are positioned. You may also notice that the X & Y values in the dataset were labelled wrong. The x-axis is your horizontal axis, but these have been stored in the Y field, while the vertical y-axis values have been stored in the X field. You may wish to rename these to save you getting confused.

Add Day to the Label shelf, and align middle centre.

We need to distinguish the bauble marks from the star mark. The star mark has a NULL value for the Day field, so we can utilise that

Day Is NULL

ISNULL([Day])

Add this onto the Shape shelf, and adjust the shape images to source the new images you saved into your shapes repository (if you can’t find them try clicking ‘reload shapes’). Adjust the initial size of the images via the slider on the Size shelf.

Now add Day Is Null to the Size shelf too. Edit the size and adjust the mark size range to suit, so the star is larger than the baubles.

Adjust the Label of the bauble so the text is larger and white.

To make the tooltip slightly more readable than that in the provided solution, I created my own custom version

Tooltip

IF [Day is Null] THEN “It’s Christmas Day!”
ELSEIF [Day]=1 THEN STR([Day]) + ” day until Christmas”
ELSE STR([Day]) + ” days until Christmas”
END

Add this to the Tooltip shelf, and adjust so it’s just referencing this field.

Add Link to the Detail shelf – this will be needed for the interactivity later.

Now we’ll add the background image via the Maps -> Add Background Image -> <datasource> menu option. Click to add image, then browse to where you’ve saved the Xmas tree image.

We need to define the min/max coordinates for where the tree image should be positioned, and based on this, we want the horizontal X axis (the Y field) to range from 0-4 and the vertical Y axis (the X field) to range from 0 to 6. Press the Apply button and you see whether things look to be placed correctly before you close all the dialog windows.

Next we need to fix the axis, so that when interacting later, we keep the whole tree visible. The vertical axis should be fixed from 0-7, and the horizontal axis should be fixed from 0 to 4.

Then we need to remove all the axis from displaying (uncheck show header), and all row/column borders and gridlines and axis rulers.

The final step is to remove the map options from displaying when you hover over the chart – Map ->Map Options and uncheck all the options displayed in the dialog

Building the Gradient Coloured Bar Chart

Candra provided a hint in her instructions pointing to the Flerlage Twins blog. A quick search on the site for the keyword ‘gradient’, and I landed on this post from 3 years ago https://www.flerlagetwins.com/2019/02/gradient-colors.html.

Since the process is already documented, I don’t have to write all the steps out myself 🙂 I’ll just point out the fields I used/created, since we’re working with a different data set.

Firstly I created a new data source for this chart, which combined Sheet1 with the Range sheet, and used a relationship calculation of 1 = 1 to combine the data (Relationships didn’t exist when the blog post was written).

The equivalent of the Sales Adjusted field is the existing pre-computed Sheet1(Count) field, so we don’t need any calculated field for this.

Max Segments in my solution is

Max Range

WINDOW_MAX(MAX([Range (Range)]))

Note – Range (Range) is referencing the Range field from the Range table. If you just drag the field into the calculation window, it will automatically present in this way.

Total in my solution is

Total Count

TOTAL(COUNT([Sheet1]))

Size in my solution is

Size

[Total Count]/[Max Range]

and finally Color in my solution is

Colour

([Max Range]-[Index]) * [Size]

Add Challenge Year to Rows (rather than Category), change the mark type to bar if it doesn’t change automatically. Reverse the axis so 2018 is listed at the top, then change the Challenge Year pill to be discrete (blue) rather than green.

If you follow the other blog post through, you should hopefully end up with

You then need to show the labels and adjust the tooltip against the CNT(Sheet1) marks card only.

As before, remove all gridlines borders etc.

Adding the interactivity

Add the 2 sheets to a dashboard. We need to allow the tree to filter on click of the bar chart. I used the ‘use as filter’ option on the context menu of the the bar chart object to quickly set this.

This adds a filter action to the dashboard, but is a quick way of getting it created rather than having to manually set it up via the dashboard -> actions menu.

I did use this menu though to add the URL action to the Tree sheet, which just needs to reference the Link to the Challenge field.

So a relatively short blog this week, since the most complicated section is already written up be Ken Flerlage (thanks Ken!). It was a great fun little challenge, which still allowed for plenty of learning opportunities. My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Filtering Table Calcs

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 Source tab 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 Minimum Order 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.

Happy vizzin’! Stay Safe!

Donna

Visualise Our Survey Data

This week, Ann Jackson set a table calculations based challenge, using the responses from a recent survey on #WorkoutWednesday, as the most requested topic was for table calcs!

There’s a lot of visuals going on in this challenge, and I’m shortly off on my holibobs (so will be playing catch up in a couple of weeks), so I’m going to try to pare down this write up and attempt just to focus on key points for each chart.

Donut Chart

By default when you connect, Respondent is likely to be listed in the ‘Measures’ part of the data pane – towards the bottom. This needs to be dragged into the top half to turn it into a dimension. You can then create

# of Respondents

COUNTD([Respondent])

which is the key field measures are based on throughout this dashboard.

When building donuts, we need to get a handle on the % of total respondents for each track, along with the inverse – the % of total non-respondents for each track. To do this I created fields

Total # of Respondents

TOTAL(COUNTD([Respondent]))

and then

Track – % of Total

[# of Respondents]/([Total # of Respondents])

along with the ‘inverse’ of

Non Track – % of Total

1-[Track – % of Total]

To then build the donut, we ultimately need to create a dual axis chart, with Which track do you participate in? on Columns and a MIN(1) field on Rows. Manually reorder the entries so the tracks are listed in the relevant order.

On the first MIN(1) axis/marks card, build a pie chart. Add Measure Names to the filter shelf and filter to the Track & Non Track % of Total fields. Set Mark Type to Pie Chart and add Measure Values to the angle shelf. Add both Which track do you participate in? and Measure Names to the Colour shelf. Set a white border on the Colour shelf. Reorder the entries in the colour legend, and set the colours appropriately.

The create another MIN(1) field next to the existing one on the Rows shelf

Set this marks type to circle, and remove all the fields from the colour & detail shelves. Set the colour to white. Add Which track do you participate in? and Track – % of Total to the Label shelf and format. Reduce the Size. Make dual axis, and synchronise. Further adjust sizes to suit.

Participation Bar Chart

Plot How often do you participate? against # of Respondents, and then add a Quick Table Calculation to the measure using Percent of Total. Manually re-sort the order of the entries, show mark labels and Colour the bars light grey. Apply relevant formatting.

Diverging Bar Chart

In this bar chart, the percentage of ‘agree’ responses are plotted to the right on the +ve scale and the percentage of the ‘disagree’ responses are plotted to the left on the -ve scale. The percentage of the ‘inbetweeners’ (neither agree nor disagree) is halved, and displayed on both sides. To address this, I created the following:

# of Respondents – Diverging +ve

CASE ATTR([Answer])
WHEN ‘Agree’ THEN [# of Respondents]
WHEN ‘Strongly Agree’ THEN [# of Respondents]
WHEN ‘Neither Agree nor Disagree’ THEN [# of Respondents]/2
END / [Total # of Respondents]

This is the % of total respondents for the ‘agree’ responses and half of the ‘inbetweeners’.

Similarly I then have

# of Respondents – Diverging -ve

(CASE ATTR([Answer])
WHEN ‘Disagree’ THEN -1*[# of Respondents]
WHEN ‘Strongly Disagree’ THEN -1 *[# of Respondents]
WHEN ‘Neither Agree nor Disagree’ THEN ([# of Respondents]/2) * -1
END) / [Total # of Respondents]

which is doing similar for the ‘disagree’ responses, except all results are multiple by -1 to make it negative.

The Question field is added to the Filter shelf and the relevant 5 questions are selected. Answer is also on the Filter shelf with the N/A answer excluded.

Add Question to Rows (and manually sort the entries), then add # of Respondents – Diverging -ve and # of Respondents – Diverging +ve to Columns and add Answer to the Colour shelf. Manually resort the entries in the colour legend and adjust the colours accordingly.

Make the chart dual axis, and synchronise the axis. Change the mark type back to bar and remove Measure Names from the colour shelf if it was added. Edit the bottom axis to fix the range from -0.99 to 0.99 and amend the title. Format the axis to display as percentage to 0 dp. Hide the top axis.

Additionally format both the measures to be percentage 0dp, but for the # of Respondents – Diverging -ve custom format, so the negative value is displayed as positive on the tooltip.

Adjust formatting to set row banding, remove gridlines etc and set tooltips.

Vertical bar chart

The best way to start building this chart is to duplicate the diverging one. Then remove both measures from the Columns shelf and add Answer to Columns. Manually re-sort the answers. Add # of Respondents to Rows and add a Quick Table Calculation of percent of total.

Show the marks label, and align bottom centre, and match mark colour. Hide the axis from displaying, and also hide the Question field (uncheck show header). Update the Tooltip.

Heatmap

Right click on the Question field > Aliases and set the alias for the relevant questions

Also add Question to Filter and select relevant values. Add Answer to Filter too and exclude NULL.

Add Question to Columns and add Answer to the Text shelf. Add # of Respondents to the Text shelf, and set to Percent of Total quick table calculation. Edit the table calculation to compute using the Answer field only

We need to get each of these columns ‘sorted’ from high to low – we want to rank them. To do this, add # of Respondents to Rows, then change it to be a blue discrete pill. Add a Rank quick table calculation and once again set to compute by Answer only. Also set the rank to be Unique

Now change the mark type to square, and then add the # of Respondents percent of total field onto Colour as well as Text (the easiest way to do this to retain all the table calc settings, is to hold down Ctrl then click and drag the pill from the Text shelf onto Colour. This should duplicate the pill.

Format the % of total displayed to be 0dp, and adjust the label. Change the Colour to use the purple range and set a white border too. Hide the ‘rank’ field from displaying and hide field labels for columns too.

The dashboard

I used a vertical container then added the objects as required, using nested horizontal containers to organise the side by side charts.

To make the diverging bar and vertical bar charts look like they are one chart, adjust the padding of diverging bar chart object to have 0 to the right, and similarly, adjust the padding of the vertical bar to have 0 padding to the left.

I found it a bit fiddly to get the charts to line up exactly. Both charts were set to fit entire view. The diverging bar chart displays it’s title. I also displayed a title on the vertical bar chart, but made the text white so it’s invisible.

Dashboard filter actions are set against the donut and the participation bar charts.

The filter uses selected fields, which for the donut chart references the Which track do you partcipate in? field. A similar dashboard action needs creating for the participation chart as the source and references the How often do you partcipate? field.

A highlight dashboard action is required for the diverging and vertical bar charts. They only impact each other and should be set up as below on hover.

Hopefully I’ve covered everything… my published version is here.

Happy vizzin’! Stay Safe!

Donna

Can you build a mobile calendar picker?

It was Natalia Miteva‘s turn to post the #WOW2020 challenge this week. It’s a parameter actions based challenge, inspired by a date range picker viz originally posted by Ludovic Tavernier.

Once again, I found this week tough! I understand the concept of parameter actions, and can use them for the ‘basics’, but the funky stuff people come up with to bend them to their will, is really beyond me.

I started the challenge with good intentions as always, ‘relating’ the Dates data to the Superstore data set matching the Date field to the Order Date field, built out the calendar view, but got stuck trying to figure out how to set the parameter action into an ‘End Date’ parameter, when I was already setting a ‘Start Date’ parameter. After staring at the screen for some time, I figured I’d try to work it all out from Ludovic’s viz instead.

There’s A LOT going on in this challenge, so I’ll do my best to explain.

  • Building the Calendar Picker
  • Building the Next / Previous control
  • Building the Year/Month control
  • Building the KPI & Trend Chart

Building the Calendar Picker

The calendar will only show 1 month, so let’s deal with that to start with.

A parameter will be used to store a date related to the month to show.

pMonthSelected

This is a date parameter that I chose to default to 01 June 2019.

Month Date

DATE(DATETRUNC(‘month’,[Date]))

This is truncating every Date in the dataset to the 1st of the relevant month

Month To Show

[pMonthSelected] = [Month Date]

Add this to the Filter shelf and set to True will limit the data to a single month, June 2019 by default.

To display the days of the month in the ‘grid’ layout, we need to know the day of the week each day falls on (so we know what column to position the day in), and we need to know what week in the year the day falls in, as this will dictate which row to position the day in.

Day of Week (Abbrev)

LEFT(DATENAME(‘weekday’, [Date]),3)

The basic calendar layout can then be built by

  • Month To Show = True on Filter
  • Day of Week (Abbrev) on Columns
  • WEEK(Date) on Rows
  • DAY(Date) on Text

The WEEK(Date) field is then hidden.

We’re going to need to represent each day by a ‘square’ block. I achieve this by adding MIN(1) to Columns, changing the mark type to Bar, fixing the axis from 0-1, then hiding the axis.

So now the basic calendar layout is achieved, we need to figure out how to select a start & end, and colour accordingly.

We’re going to use a single string parameter to store both the selected start & end dates, using a | to act as a delimiter allowing us to identify & so separate the 2 dates.

pSelectedDates

String parameter set to empty string by default

Show the parameter on the viz, and I will attempt to walk through the steps, showing how the values in the parameter influence other fields and what is displayed on the viz.

We need another field, that is going to be used in conjunction with the parameter. I’m going to build up what this field needs to store as we go through, so hoping it will all make sense.

Date Control

IF [pSelectedDates]=”” THEN STR([Date]) END

If the parameter is empty, then set Date Control to be the date.

Let’s add this to the Text so you can see what happens to this field.

So the first action we expect the user to take is to click on a single date displayed. This value stored in the Date Control field will be passed via the parameter action into the pSelectedDates field (I’ll show you how this works later). For now let’s assume the user has clicked on the 5th June, so type 2019-06-05 into the pSelectedDates field.

This is now the start date, and we need the Date Control field to change to reflect this

Date Control

IF [pSelectedDates]=”” THEN STR([Date])
ELSEIF [Date]<=DATE([pSelectedDates]) THEN STR([Date])
ELSE [pSelectedDates]+”|”+STR([Date]) END // else store start & end date

If the Date displayed is before that stored in the parameter, then Date Control will continue just to store the date, otherwise it will append | followed by the date onto the existing parameter value.

So when a date is clicked again, the value of the Date Control will once again be passed to the pSelectedDates parameter.

If the date clicked is earlier than the one already stored, eg let’s say 04 June was clicked, the Date Control value of 04 June is passed to the parameter, and is now the ‘start date’. Type 2019-06-04 into the pSelectedDates field.

But if a later date is selected, say 18th June, the Date Control value of 2019-06-04|2019-06-18 is passed into the pSelectedDates parameter, and we now need another step with our Date Control field :

Date Control

IF [pSelectedDates]=”” THEN STR([Date])
ELSEIF CONTAINS([pSelectedDates],”|”) THEN STR([Date])
ELSEIF [Date]<=DATE([pSelectedDates]) THEN STR([Date])
ELSE [pSelectedDates]+”|”+STR([Date]) END

The 2nd statement is now looking for the | character and if it finds it, we know a start & end dates has been selected, so we need to ‘reset’ this control field to how it was originally – just it’s own date.

Type in 2019-06-04|2019-06-18 into the pSelectedDates field to see this.

Ok, so now we can see how the pSelectedDates field is working with the Date Control field, we now need to define some additional fields to help us identify the start & end of the dates selected, so we can work out what to colour.

Date Selection Start

DATE(LEFT([pSelectedDates],FIND([pSelectedDates],”|”)-1))

This is looking for the position of the | field and taking everything from the first character in the pSelectedDates string up to the character before the |.

Note I originally used DATE(SPLIT([pSelectedDates], “|”,1)), but after extracting the data set, this calculation caused errors. I don’t know why, but the above workaround sufficed.

Date Selected End

DATE(MID([pSelectedDates], FIND([pSelectedDates],”|”)+1,10))

Find the position of the | and take all the characters starting at the position after the | and ending at the character 10 characters later (the date format is 10 characters so this can be easily hardcoded)

COLOUR: Date

IF [Date]= [Date Selection Start] OR [Date] = [Date Selection End] THEN ‘Hot Pink’
ELSEIF [Date] > [Date Selection Start] AND [Date] < [Date Selection End] THEN ‘Pink’
ELSE ‘White’
END

Add this onto the Colour shelf, and adjust accordingly

We can remove the Date Control from Text now, but it needs to remain in the view, so it can be referenced when we build the parameter action. Add it onto Detail shelf instead.

Finally add this viz onto a dashboard, then add the parameter action, setting the Target Parameter to pSelectedDates and the Field to pass as Date Control.

As you play around selecting values on the dashboard, you’ll notice the dates appear ‘greyed out’, as the ‘on click’ action is essentially highlighting what you’ve selected. To stop this from happening create a calculated field called True that just stores the value of True and and a calculated field called False that stores the value False. Add both of these to the Detail shelf of the calendar viz.

Then on the dashboard, add a Filter action that goes from the dashboard to the sheet itself, passing the values Source: True = Target: False

Building the Next / Previous Control

We need to store the value of the next month related to the Date unless we’re already in the last month of the data set, in which case we’ll just keep the same month (ie if the month is 01 Nov 2019, next month will be 01 Dec 2019, but if the month is 01 Dec 2019 which is the last month in the data set, the next month will also be 01 Dec 2019. This stops the next control from moving forward when we reach the end.

Next Month

IF [Month Date] = {MAX([Month Date])} THEN [Month Date] ELSE
DATE(DATEADD(‘month’, 1, [Month Date]))
END

Change the mark type to Shape and select an appropriate arrow. If need be reduce the transparency of the shape colour (I chose 50% which changed a black arrow to look like a grey arrow).

On a new sheet, Add Month To Show = True to the Filter shelf, and Next Month to the Detail shelf.

If you recall, right at the start, we created a pMonthSelected parameter, and this will be used to change the month displayed.

Add the viz to the dashboard, and add a parameter action that on select, targets the pMonthSelected parameter, passing in the Next Month field

For the Previous Month control, we need apply similar steps, but using the field below instead.

Prev Month

IF [Month Date] = {MIN([Month Date])} THEN [Month Date] ELSE
DATE(DATEADD(‘month’, -1, [Month Date]))
End

Building the Year/Month control

When building the Year selector, we need to filter the data to just the month name already selected/in view, which is based on the pMonthSelected parameter. Ie if the month selected is 01 June 2019, we want the data related to June 2016, June 2017, June 2018, June 2019

Filter Month

DATENAME(‘month’,[pMonthSelected])=DATENAME(‘month’,[Date])

Add this to the Filter shelf, set to true, then build out the view as below, fixing the axes between 0-1 again, and then hiding. You can see the values of the Month(Date)) displayed, and it’s this field that will be passed via a parameter action. It can be moved to Detail.

COLOUR:Selected Year

YEAR([pMonthSelected])=YEAR([Date])

Add this to the Colour shelf and adjust accordingly.

When added to the dashboard, the associated parameter action required is :

When building the month selector, we need a similar filter to ensure the months shown are all associated to a single year

Filter Year

YEAR([pMonthSelected]) = YEAR([Date])

Add this to the Filter shelf set to True.

The layout of the Months, requires some additional fields to determine which row and column the month should be positioned in.

Month Cols

IF (DATEPART(‘month’,[Date]) %4) =0 THEN 4
ELSE (DATEPART(‘month’,[Date]) %4)
END

DATEPART returns the integer value of the month, ie a number between 1-12, so this calculation is using the modulo (%) function to position based on the remainder when dividing the month number by 4.

Month Row

IF DATEPART(‘month’,[Date]) <=4 THEN 1
ELSEIF DATEPART(‘month’,[Date]) <= 8 THEN 2
ELSE 3 END

Adding these to the view as follows gives us a grid

Note the blue Month(Date) on the Text shelf is showing just the month name ie ‘January’, and has been formatted to it’s abbreviation. The green Month(Date) on the Detail shelf is storing the full month year ie January 2019.

COLOUR:Selected Month

[pMonthSelected]=[Month Date]

Add to the colour shelf and adjust accordingly.

You can then hide the Month Col & Month Row fields from displaying.

When added to the dashboard, the associated parameter action required is

Note You will need to apply the same ‘true=false’ trick described earlier to both these views using filter actions to prevent them from highlighting on selection.

Hiding the year/month selector

On the dashboard add a vertical container, then place the Year Selector sheet and the Month Selector inside.

Remove the chart titles, set the background of the container to white, then set the container to be floating and select the container option to Add Show/Hide Button.

You’ll need to spend some time adjusting the size and the position of the floating container once you have the layout of all the other objects finalised.

Building the KPI & Trend Chart

I’m going to try to be brief with this section. The above is A LOT to take in and write!

Selected Period

This is simply using the Date Selection Start & Date Selection End fields to display in a text field

KPI BANs

We need to be able to filter the data being displayed based on the selection

Dates to Show

[Date]>= [Date Selection Start] AND [Date]<= [Date Selection End]

This is added to the Filter shelf and set to True.

After that we’re just building a simple Text based viz utilising Measure Names & Measure Values as shown below

And finally the trend chart, again needs to be filtered by Dates To Show = True, and is a simple line chart plotting Date against the measures required as below

When added to the dashboard, these 2 sheets should be placed side by side in a horizontal container, both set to fit entire view. The right padding should be set to 0 for the KPI chart and the left padding set to 0 for the trend chart. This will have the effect of the horizontal lines for each chart joining together to make it look like a single chart.

Phew! I’m beat! My published viz is here. Keeping fingers crossed for a challenge that is easier to blog about next week 🙂

Happy vizzin’! Stay Safe!

Donna