Erica kicked off the 1st year of #WOW2025 with a table calculation based challenge asking us not to just use quick tableau calculations, to avoid the use of the RUNNING_SUM table calculation and to just create 2 calculated fields.
Defining the calculations
On a new sheet, add Order Date to Rows as a discrete (blue) pill at the Month-Year format and add Sales to Text.
For the running total, we want
Running Total
SUM([Sales]) + PREVIOUS_VALUE(0)
which takes the Sales from the current row, and adds it to the value of the cumulative Sales (ie this calculation) in the previous row
For the moving average, we want
3 Month Moving Average
WINDOW_AVG(SUM([Sales]), -2, 0)
which averages the Sales for the current row plus the previous 2 rows (ie 3 rows in total)
Building the Viz
ON a new sheet add Order Date as a continuous (green) pill at the Month-Year format to Columns and Running Total to Rows.
Change the marktype to Area, and set the Colour to #a16eaf with a 25% Opacity.
Add Order Date to Tooltip, and set to the MIN aggregation. Format the pill on the Tooltip shelf to have the <month year> format when displayed on the pane.
Add another instance of Running Total to Rows. Set the Mark type to be Line and reset the opacity on the colour shelf to be 100%. Make the chart dual axis and synchronise the axis.
Add 3 Month Moving Average to Rows. Set the Colour to be #67c79c at 25% opacity.
The add another instance of 3 Month Moving Average to Rows. Set the colour to #67c79c and the mark type to line and increase opacity to 100%. Make dual axis and synchronise the axis.
Update the Tooltip on the All Marks card, referencing the MIN(Order Date) pill and adding $ before the values
Edit the Order Date axis, to start from 01 Jan 2021 and end on 31 Dec 2024. Remove the axis title.
Format the Order Date axis, and set the Dates on the scale to be formatted with the MMMMM notation to just show the first letter of the month
Add a reference line to the Order Date axis, set to be a constant value of 01 Jan 2021, with a custom label of 2021 and the line is formatted to be a white dashed line of 100%
Then format the reference line so the label is aligned top right
Repeat this step 3 more times, adding Reference Lines for 01 Jan 2022 (labelled 2022), 01 Jan 2023 (labelled 2023) and 01 Jan 2024 (labelled 2024).
Finally, hide the right hand axis (uncheck show header), remove column dividers, but show the axis rules on the rows.
And that should complete the challenge – just pop the viz onto a dashboard and publish. My published version is here.
For the first WOW challenge of 2024, I set the task of completing a viz related to HR salary data.
Creating the calculations
To start, we’ll build out all the necessary calculations required, and display them in tabular form. First create
Employee
[Forename] + ‘ ‘ + [Surname]
then add Employee ID and Employee to Rows, along with Department and Job Title. Add Pay Level as a discrete dimension (blue pill) to Rows too, then format Salary to £ with 0 dp, and add to Text.
We want to compare each employee’s salary with the median salary of all the employees in the same pay level. For this we need
Median Salary per Pay Level
{FIXED [Pay Level]: MEDIAN([Salary])}
Add this to the view too. You should see that the value is the same for those rows where the Pay Level is the same.
We need to compute the difference between these numbers (to draw a line from the median to the salary)
Difference From Median
SUM([Salary])- SUM([Median Salary per Pay Level])
and we also need to understand the salary as a proportion of the median
Salary / Median %
SUM([Salary])/SUM([Median Salary per Pay Level])
format this to % with 0 dp and add both these fields to the table
Each employee is given a scale value based on the proportion
Salary Scale
IF [Salary / Median %] > 1.2 THEN 6 ELSEIF [Salary / Median %] >1.1 AND [Salary / Median %] <=1.2 THEN 5
ELSEIF [Salary / Median %] >1 AND [Salary / Median %] <= 1.1 THEN 4
ELSEIF [Salary / Median %] > 0.9 AND [Salary / Median %] <= 1 THEN 3
ELSEIF [Salary / Median %] > 0.8 AND [Salary / Median %] <=0.9 THEN 2 ELSE 1 END
Set this to be discrete, then add to the view on Rows.
Finally we need the markers for the 80%, 90% etc of the median salary
80% Median
0.8 * [Median Salary per Pay Level]
90% Median
0.9 * [Median Salary per Pay Level]
110% Median
1.1 * [Median Salary per Pay Level]
120% Median
1.2 * [Median Salary per Pay Level]
Building the Viz
On a new sheet, add Employee ID, Employee, Department and Job Title to Rows. Add Salary to Columns and set the mark type to Circle. Set the Colour of the circle to suit (or use #64cdcc).
Add Median Salary per Pay Level, 80% Median, 90% Median, 100% Median, 120% Median to the Detail shelf. Add Pay Level, Salary Scale and Salary / Median % to Tooltip.
Format the axis (right click) and set the scale to be at £k with 0dp
This will also change the formatting of the salary on the tooltip, but we want it to be more detailed so create
Tooltip-Salary
[Salary]
and format this to £ with 0 dp. Add this to Tooltip too and adjust tooltip to match.
Add a reference line to the axis (right click axis > add reference line) for the Median Salary per Pay Level. Set it to be per cell and display a dashed line with no labels/tooltips displaying.
Add another reference line. This time set it to be a band at the cell level which ranges from the 80% Median to the 90% Median. Don’t display any labels or tooltips or lines and fill the band with the appropriate colour.
Repeat the above process, adding a reference band from 90% – 110% of the median, and a further band from 110% – 120% of the median.
Add another instance of Salary to Columns. Change the mark type of the 2nd Salary marks card to gantt and adjust the colour. Add Difference From Median to Size and decrease the size as small as possible. Double click into the Difference From Median pill on the size shelf and manually type in * -1 to the end.
Now make the chart dual axis and synchronise the axis. Right click on the top axis and move marks to back.
We want the user to be able to filter the chart, so add Department, Pay Level (select All values) and Salary Scale to the Filter shelf.
Finally, format the chart – make each row a bit wider; set the background colour of the worksheet to #f9f8f7; remove column dividers; set row dividers to a thick white line; remove all gridlines, zero lines and axis rulers. Reduce the size of the axis values and increase the width of the initial 3 columns so the text doesn’t wrap as much. Test your filters.
If all working, then add the sheet to a dashboard.
Building the Legend
To build the legend, I simply duplicated the core sheet, then filtered to a specific Employee ID and hid the Employee ID, Employee, Department and Job Title fields. I then edited the reference lines to add custom labels to label each line band, formatting the text to display to the top or bottom. I added an annotation to the Salary circle mark to label that as ‘Employee Salary’ which I then manually moved into position.
When I added this to the dashboard, I then floated a blank object over the top so the legend could not be interacted with.
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
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.
So after a couple of weeks off blogging due to Christmas, I’m back providing solutions for #WOW2021! New contributor Candra Mcrae started off the year with this gentle workout focussing on table calculations. For those who have followed my previous blogged solutions, you’ll probably recall that table calcs don’t phase me as much as they do some – I started using Tableau before LODs existed, so they were the only tool at my disposal in the past. That said, I’m still not such an expert that I get it right first time – there’s often plenty of trial and error as I choose which fields I want to compute by, although I’ve got much better at this since I read Andy Kriebel’s Table Calculations Overview a couple of years ago.
As with any challenge that involves table calcs, I tend to start by building out a tabular view of all the data I’m going to need to build the viz. This ensures I can validate the data much easier and set the table calculation settings to what I need.
So let’s start….
For the line chart itself, we’re simply going to be plotting Year against Food insecurity which has been formatted to 1 decimal place and displays the % as a suffix
We will also need to capture 2 other values which will represent the coloured circles. Parameters are needed to help identify what these circles will be.
pSelected Year is an integer parameter that can be built by right clicking on Year and selecting Create -> Parameter. This will populate the parameter with all the values of the Year field. Default the value to 2018 and adjust the formatting of the displayed value so that it is 0 decimal places and include thousand separators is unchecked.
With this parameter, we can capture the value associated which is represented by the pink dot in the viz
Selected Year %
IF [Year] = [pSelected Year] THEN [Food insecurity (includes low and very low food security) Percent of households] END
It’s a bit more work to identify the black dot, as this will vary based on another parameter
pComparison
I created this an integer parameter storing the values 0, 1 & 2 (defaulted to 2), but aliased for display as First Year, Most Recent Year, Previous Year. I’ll be using this is an case statement/if clause later and comparing integers to strings is much more efficient in Tableau.
I like to take things step by step, especially when there’s table calcs involved, to ensure all the values I’m referencing are correct, so rather than identifying the selected value in a single calculated field, I’m using multiple.
Firstly I want to identify the 1st year in the dataset (without hardcoding).
First Year
WINDOW_MIN(MIN([Year]))
This will store the value of the earliest year (1995) against every row in the data that is outputted in the view (a bit like you would get with {FIXED : MIN([Year])}, but this is a no LOD challenge).
From this I can work out
First Year %
IF MIN([Year]) = [First Year] THEN MIN([Food insecurity (includes low and very low food security) Percent of households]) END
Notice the MIN() functions used in this statement, as opposed to the Selected Year % above. This is because First Year is a table calc which is an aggregation, and subsequently other fields referenced in the calculation also need to be aggregated. In this case other aggregations such as AVG, MAX, ATTR would also suffice.
Similarly, I’m going to derive the Latest Year % with
Latest Year
WINDOW_MAX(MAX([Year]))
Latest Year %
IF MIN([Year]) = [Latest Year] THEN MIN([Food insecurity (includes low and very low food security) Percent of households]) END
Finally, I’m also going to work out the value for the previous year
Previous Year
[pSelected Year]-1
Previous Year %
IF [Year] = [Previous Year] THEN [Food insecurity (includes low and very low food security) Percent of households] END
With these 3 % fields, I can now create a fourth field which stores the value of the % I want to compare with
Selected Comparison %
CASE [pComparison] WHEN 0 THEN [First Year %] WHEN 1 THEN [Latest Year %] WHEN 2 THEN SUM([Previous Year %]) END
This ultimately just stores the 2nd value required for the black dot. This could all have been written within this single calculation, but I find it easier to troubleshoot if things are broken down a bit.
Putting these onto a table we can see how the values in each row change, as the parameters are changed.
Note, for all the table calculation fields (all denoted by the triangle symbol on the pill), I have explicitly set them to Compute Using the specific dimension of Year rather than the default of table down. While this will give the same result, I prefer to be explicit to ensure the values don’t change if pills get subsequently moved around the canvas (in the case of the Selected Comparison %field, all Nested Calculations within the Edit Table Calculation dialog box need to be set).
This is enough information to build the main viz itself by
adding Year to Columns (green continuous pill)
adding Food insecurity to Rows
adding Selected Year % to Rows next to Food insecurity
then drag Selected Comparison % to the Selected Year % axis which will automatically change the display to have Measure Values on Rows instead. Set the table calculation setting to compute by Year
This chart can then be set to be dual-axis and the axes synchronised. The Food insecurities should remain as as line mark, and the Measure Values should be a circle. The colours, formatting and tooltip then need to be applied.
Now we need to go back to our table of data to build out other calculations. The requirement is a single viz, so we need to provide the % value of the selected year within the title of the chart, along with the difference from the comparison value. For this to work, we need to store the relevant values against every row in the data set.
We already have the Selected Year % value identified, but this is only captured against the row of the selected year in the data output. To get it to display against every row we need
Window Max – Selected Year %
WINDOW_MAX(MIN([Selected Year %]))
This is formatted to 0 dp with the % sign as suffix, as this is the field that will be displayed in the title. Added to our data table, with the table calc set to compute by Year, you can see the value replicated across every row
Similarly, we already have the Selected Comparison % captured, but in order to work out the difference, we also need to get this value against every row too
Window Max – Selected Comparison %
CASE [pComparison] WHEN 0 THEN WINDOW_MAX([First Year %]) WHEN 1 THEN WINDOW_MAX([Latest Year %]) WHEN 2 THEN WINDOW_MAX(MAX([Previous Year %])) END
Adding this in to the table (and remembering to set the table calc settings), you can also see the relevant value perpetuated against every row. Change the pComparison value and you’ll see the values change accordingly, but still the same on every row.
So for the difference…. at this point I chose to deviate from the solution published. We’re already dealing with a measure that is quantified as a % (as opposed to a measure that is quantified as £ say). So to get the % difference between two percentage measures, I simply chose to show the difference between the two values (ie selected – comparison). Some would refer to this a the point difference between the values. This makes most sense to me for this particular scenario. The alternative is to calculate the % difference in the more traditional way of (selected – comparison) / comparison as you may do if you were presenting the % difference between the value of sales in different years. But I personally found the result could be confusing to the reader .
Difference
[Win Max – Selected Year %]-[Win Max – Selected Comparison %]
BUT, the value displayed within the title is the absolute value (ie no -ve sign) and actually doesn’t display if the value is 0 (which you get if you select the year to be 2019 and comparison to most recent year). So I resolved this with
DISPLAY : Difference
IF [Difference] <> 0 THEN ABS([Difference]) END
which is formatted to 1 dp with % suffix
Finally, we have a symbol that is used to indicate if the change is +ve, -ve or remains the same.
Difference Indicator
IF [Difference] > 0 THEN ‘▲’ ELSEIF [Difference] < 0 THEN ‘▼’ ELSE ‘N/C’ END
Add this into the table too, once again remembering to set the table calc properties for all nested calculations.
So now you have these fields, you can add Window Max – Selected Year % and DISPLAY : Difference and Difference Indicator to the Detail shelf of the All marks card on the chart viz, and these will then be available in title dialog to add. Once again, ensure you set the table calcs to compute by Year.
You’ll also need to add some spacing in the title, to allow the parameter controls to be ‘floated’ into place on the dashboard. Getting the position right is really tricky. I positioned it very carefully on Desktop, but when I published, the controls were in completely different places. The easiest way I found to resolve this, was to use the Edit feature in Tableau public to edit the dashboard online and move the objects that way.
Donna + DataViz 