Erica Hughes set her first #WOW challenge this week, which is all about reference lines. The intention by the #WOW crew is to use this initial challenge as the basis for the next few weeks, so it’s going to be interesting to see how that works out.
But back to this week. We’re building a basic Sales vs Profit Ratio scatter plot by State, but then need various calculated fields to plot the reference lines and the reference box.
Building the basic chart
Adding the dotted reference lines
Adding the reference box
Making the y-axis symmetrical
Highlighting the selected state
Building the basic chart
First up we need to create our favourite calculated field
Profit Ratio
SUM([Profit]) / SUM( [Sales])
format to percentage with 0 dp.
Create the basic scatter plot as below
Remove all gridlines, zero lines and axis rulers and tick marks.
Adding the dotted reference lines
These lines are the median values for the Profit Ratio and Sales.
Sales – Median
WINDOW_MEDIAN(SUM([Sales]))
format to Custom Currency with 1 dp, $ prefix and display units to Thousands (K).
PR- Median
WINDOW_MEDIAN([Profit Ratio])
format to percentage with 0 dp.
Add both these fields to the Detail shelf, and adjust the table calculation settings of both fields so they are computing by State
Add a reference line to the Sales axis (I do this by right clicking on the axis > Add Reference Line, but you can also drag from the Analytics pane).
Set the reference line to be against the Entire Table, using the Sales – Median field, no label, and the tooltip set to custom as below. Set the line to be dotted.
Apply a similar reference line to the Profit Ratio axis, using the PR – Median value instead.
Adding the reference box
I have to admit, this bit took a bit of trial and error. I knew it was going to involve a combination of bands and lines and colouring above and below, but things didn’t always go to plan. It was this post by Jonathan Allenby that helped.
The block is basically bounded by the 25th & 75th percentile of the Sales and Profit Ratio values. So lets create them
Sales – 25th Percentile
WINDOW_PERCENTILE(SUM([Sales]),0.25)
Sales – 75th Percentile
WINDOW_PERCENTILE(SUM([Sales]),0.75)
Add these fields to the Detail shelf and set the table calculation on both to compute by State.
Add a reference line again to the Sales axis, and this time select Band that goes from the Sales – 25th Percentile to the Sales – 75th Percentile and fill based on the colour stated in the requirements.
Now add a reference line to the Profit Ratio axis. This time select Distribution.
Select the computation to be Percentiles and enter 25 as the value. Set Label, Tooltip and Line to None. Now this is the odd bit… set the Fill to the colour white, then check the Fill Below box, which then seems to set the Fill option to ‘No Fill’. Doing this seems to have the desired effect. Trying this in any other order, or manually selecting the Fill option to be No Fill, just doesn’t seem to work…
Now repeat the process adding another reference line on the Profit Ratio axis, and this time create a distribution for the 75th percentile. This time, after setting the Fill to white, check the Fill Above checkbox. Weirdly this time, the fill on my laptop got set the Grey Light, although it was correctly displaying as white on the screen. I manually changed it to ‘No Fill’.
It feels like there’s something a bit ‘buggy’ with all this, which might explain while my initial attempts were failing. I’ll be interested in knowing if you see this behaviour too (I created on v 2021.4.2).
Making the y-axis symmetrical
This was a ‘bonus’ feature, but is again achieved with a reference line. What we’re looking for here is to understand what is the maximum absolute value so we can determine where to place a hidden reference line – ie if the maximum profit ratio is 20% and the minimum is -30%, the maximum absolute value is 30%, and we’d need a reference line +30%, so the axis extends from -30% to +30%. Conversely if the maximum profit ratio is 40% and the minimum is -25%, the maximum absolute value is 40% and we need a reference line at -40% for symmetry.
I’ve encapsulated all this logic within one field below
Ref Line – Profit Ratio Symmetry
IF MAX(WINDOW_MAX([Profit Ratio]), ABS(WINDOW_MIN([Profit Ratio]))) = WINDOW_MAX([Profit Ratio]) THEN -1*WINDOW_MAX([Profit Ratio]) ELSE -1*WINDOW_MIN([Profit Ratio]) END
The statement MAX(WINDOW_MAX([Profit Ratio]), ABS(WINDOW_MIN([Profit Ratio]))) is returning the maximum of the max and min values (ie MAX(20, ABS(-30)) = MAX(20,30) = 30, MAX(40, ABS(-25)) = MAX(40,25) = 40.
Add this onto the Detail shelf and add another reference line to the Profit Ratio axis, setting it to use the Minimum of the Ref Line – Profit Ratio Symmetry field
Test how the field works, by selecting a few of the State marks towards the top and exclude; the axis should change, but still be symmetrical.
Highlighting the selected state
For this we need a parameter
pSelectedState
string parameter defaulted to Virginia
Then we need a field to identify which state has been captured
Selected State
[State]=[pSelectedState]
which returns a boolean. Change the mark type to Circle and add Selected State to the Colour shelf, and adjust accordingly (add a border to the circle too via the Colour shelf). Also add Selected State to the Size shelf and again tweak.
Once the sheet has been added to the dashboard, create a dashboard parameter action that passes the State field into the pSelectedState parameter on Select of the chart.
The final step to add is to stop all the other marks from ‘fading’ out when a State mark is selected. This is achieved by creating the following calculated fields
True
True
False
False
Add both of these to the Detail shelf of the scatter chart.
On the dashboard, create a dashboard filter action as below, which passes selected fields setting True = False, which can never be true and prevents the mark from being highlighted.
And with that, you should have the desired dashboard. I’m interested to see if this matches Erica’s solution (it’s likely I’ll start with the provided workbook next week, rather than mine, just in case there are some discrepancies – eg I’ve used a lot of table calcs… LoDs may have been possible…)
Lorna Brown returned this week with her first challenge of 2022 which was aimed at practising tableau calculations, although when I completed the build, I realised I actually didn’t use any – oops! I’d ended up using an LoD based solution instead. As is often the case in Tableau, there can be multiple ways to solve a problem, so I created another solution involving table calcs. As I blog I will endeavour to provide both sets of calculations used and make it obvious when I do so. Hopefully, it’ll all make sense 🙂
Building the calculated fields
After connecting to the data, the first step I did was to create a parameter to store the ‘selected date’. I hard coded/defaulted this to 1st Nov 2020.
pSelectedDate
Date parameter defaulted to 01 Nov 2020
Show this on the worksheet, so you can test what happens when you manually change it later.
As I typically do, I’m going to work in a tabular format to sense check all the calculations we’re going to need before I start visualising the information graphically.
To start add Company then Date (set to exact date & discrete) to Rows then add Close to Text and change the aggregation to AVG.
I now want to work out what thevalue of the average Close is on the selected date (in this case 01 Nov 2020) for each company, and I want this value to be displayed against every row associated to that company.
Close on Selected Date per Company
LOD Option
{FIXED [Company]: AVG(IF [pSelectedDate]=DATETRUNC(‘month’,[Date]) THEN [Close] END)}
Table Calc Option
WINDOW_MAX(AVG(IF [pSelectedDate]=DATETRUNC(‘month’,[Date]) THEN [Close] END))
Drag this field onto the table. If you’re using the LoD option, change the aggregation to AVG (although as we’re working with the data at the lowest granularity, SUM gives the same value, it just makes ‘more sense’ to me). If you’re using the Table Calc option, change the table calculation so that it is computing by Date only. In the below screen shot, I have included both versions of the calculation which match each other. Scrolling down, you should be able to verify that the value displayed in the new field(s) for all rows for a specific company, matches the value in the first column for the selected date. Test by entering different dates in the parameter (though make sure you’re just entering 1st of month each time, as that is what our Date field contains).
Now that for each row we have an average Close value and the average Close value on a specific date, we can work out the percentage difference for each row.
% Diff from Selected Date
LOD option
(AVG([Close])-AVG([Close on Selected Date per Company]))/AVG([Close on Selected Date per Company])
Table Calc option
(AVG([Close])-[Close on Selected Date per Company])/[Close on Selected Date per Company]
There is a slight difference in the syntax as the table calc option is referring to a field that is a table calculation and which is already aggregated, so there’s no need to aggregate again (ie wrap in an AVG…. Tableau will error if you try to do this).
Format this to a Percentage with 0 dp.
Once again I am choosing to show both versions on the screenshot below. Again if you’re using the table calc version, you need to verify the calculation is set to compute by Date only.
So now we have the core fields we need to start building the viz.
I am now just going to refer to the calculated field names I’ve mentioned above and not differentiate between LoD or table calc version. The only thing to remember is when adding the table calc fields, set the compute by, and if you’re using the LoDs, set the aggregation to AVG (although SUM will work too due to the level of aggregation we’re at).
Building the viz
On a new sheet add Date to Columns and set to the continuous Month level or exact date (either will work in this instance, since we only have 1 date per month in the data set). Add % Diff from Selected Date to Rows. Add Company to Colour and adjust to suit (use the hue circle colour palette).
Add Company to the Label shelf and also add % Diff from Selected Date to the Label shelf.
Adjust the Label settings, so the labels only display at the end of the line.
To make the text match the mark colour, click Label >then expand the Font dialog by clicking the arrow to the right, and select Match Mark Colour.
To format the % value displayed, right-click on the % Diff from Selected Date pill on the Text shelf, and select format. In the formatting pane that appears on the left hand side, change the number formatting to custom â–²0.0%;â–¼0.0% ( I use this site to get the shapes I need – just copy and paste)
Now rearrange the text on the label so that company is displayed first.
To format the Date field within the tooltip and theaxis, right click on the Date pill in Columns and select format. On the Format Date pane on the left hand side, set the Dates value on the Pane tab, in the Default section, to custom : mmm yy. Then click on the Axis tab and set the Dates value in the Scale section to the same custom mmm yy value.
The Tooltip
We need quite a few fields on the Tooltip to get the information required.
Add Close and Close on Selected Date per Company to Tooltip. We also need a couple more calculated fields
TOOLTIP – Selected Date
[pSelectedDate]
format this field to custom mmm yy
TOOLTIP – % Diff
[% Diff from Selected Date]
Format this to % with 0 dp.
Add both these to the Tooltip too, then adjust the wording on the tooltip as required.
The Reference Line
Add pSelectedDate to the Detail shelf. Then right click the date axis and Add Reference Line. Adjust the settings so there is a Line for the Entire Table, which references the pSelectedDate value and has a custom Label of Reference Date: <Value>. Set the tooltip to None and the Line to be dotted
Format the pSelectedDate value displayed, by right clicking on the pill on the Detail shelf and set the Dates format on the Pane tab to mmm yy.
The right click on the Reference Date: Nov 20 label itself and select Format, and change the alignment to the top.
Finally, remove the axes titles, remove the gridlines and all axes rulers and change the worksheet background colour. Again, format the reference line label, so there is 0 transparency.
Test the visualisation by manually changing the date of the parameter to the 1st of another month and seeing how the viz updates.
Building the interactivity
Add the sheet to a dashboard, then create a parameter action, the runs on select of a mark on your sheet. It should change (target) the pSelectedDate parameter passing in the Date field
The only problem with this, is that when selecting a mark, the line relating to the mark is highlighted while the others ‘fade’ into the background. To prevent this from happening :
Create a new calculated field called True which contains the value True
Create a new calculated field called False which contains the value False
Add both these fields to the Detail shelf on the line chart.
Add a Dashboard Filter action, which runs on select, and has the sheet on the dashboard as it’s source, and the sheet in the workbook as it’s target. Set to show all values when selection is cleared, and filter by selected fields, setting True = False.
And with that, you should have a functioning solution – there just may be a few further formatting tweaks to apply.
Ann set this week’s #WOW2021 challenge during #TC21 and chose to live stream her build. I couldn’t watch it but I did then manage to catch a snippet of Kyle Yetter who bravely chose to share his attempt at recreating the challenge via a live stream too with Ann & Luke watching. I had already completed my build by the time I watched Kyle, and it was interesting to see where our approaches differed.
The main via is a single chart, so I started by building out all the data I needed in tabular form, so I could verify the sort.
Defining all the calculations for the main viz
First up, the data needs to be compared to ‘today’ where ‘today’ is set to 01 Jan 2022. I used a parameter to store this
pToday
Date constant set to 01 Jan 2022
We also need to understand the latest order date per customer, so need
Max Order Date By Customer
DATE({FIXED [Customer ID]: MAX([Order Date])})
and so with this we can calculate the days since last order, which is one of our key measures.
Days Since Last Order
DATEDIFF(‘day’, [Max Order Date By Customer], [pToday])
The other measures we need are
#Orders
COUNTD([Order ID])
Avg. Order Value
SUM([Sales])/[#Orders]
along with Sales and Quantity.
These are the 5 main measures displayed, but there are some additional calculations needed for the display labels and tooltips.
The label to indicate the ‘time since last purchase’ is displayed in days or years, so for this I created 2 specific fields
LABEL:Days Since Last Order
IF [Days Since Last Order] <365 THEN [Days Since Last Order] END
This will only store a value when the days is less than 365. I then formatted this field to have a ‘ days’ suffix
Similarly, I created
LABEL:Years Since Last Order
IF [Days Since Last Order]>=365 THEN [Days Since Last Order]/365 END
which stores a value for records >=365 only. This was formatted to have 1 dp and the ‘ years’ suffix instead.
The colour of the first measure is based on whether the customer is considered ‘active’ or not. The threshold for this is managed via a parameter
pActiveCustThreshold
An integer parameter defaulted to 90.
We can then create
Is Active Customer?
[Days Since Last Order]<=[pActiveCustThreshold]
This is a boolean field and will return true or false, but to get the tooltip value to display appropriately, I edited the alias of this field (right click > Aliases)
For the tooltip on the ‘total products’ (ie quantity measure), we need to display the number of distinct products orders, which we can capture in
#Products
{FIXED [Customer ID]: COUNTD([Product ID])}
Pop all these fields, along with the Customer ID and Customer Name into a table and you can see validate all the values are as you expect
Sorting the Sort
We need another parameter to manage the sort.
pSort
I used an integer parameter, but set the display to the required text strings. This is because writing logic on integers is more efficient than strings
I then created a calculated field to determine the measure to sort by
Sort By
CASE [pSort] WHEN 1 THEN SUM([Days Since Last Order]) * -1 WHEN 2 THEN [#Orders] WHEN 3 THEN SUM([Sales]) WHEN 4 THEN [Avg. Order Value] WHEN 5 THEN SUM([Quantity]) END
Notice the first measure is being multiplied by -1 since this measure is to be displayed in Ascending order rather than Descending.
In the table, amend the Customer ID pill to Sort by the Sort By field descending
Show the pSort parameter and test the functionality by switching the values.
Building the main viz
Having got all the calcs defined, this viz is relatively straight-forward. It is a multiple axis viz by Customer ID and Customer Name (note I use Customer ID as well, which I hide, just in case there are multiple customers with the same name).
Customer ID and Customer Name on Rows. Apply sort to Customer ID as described above. Hide Customer ID (uncheck Show Header). Align Customer Name to right, and adjust font.
Type in MIN(1) on Columns. Edit Axis to be fixed from 0-1. Add Is Active Customer? to Colour. and adjust. Add LABEL:Days Since Last Order and LABEL:Years Since Last Order to Label. Align Centre and match mark colour. Make sure the two fields are side by side on the label, and not underneath each other. Add Max Order DateBy Customer to the Tooltip shelf. Adjust Tooltip
Add #Orders to Columns. Strip off all the fields that have been automatically added to this marks shelf. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
Add Sales to Columns. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
Add Avg. Order Value to Columns. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
Type in MIN(0) on the Columns shelf. Change mark type to Circle. Set the Colour accordingly. Add Quantity to Label. Add #Products to Tooltip. Adjust Tooltip
Remove all row & column borders and gridlines, zero lines etc.
Add a 0 constant Reference Line to the #Orders, Sales and Avg. Order Value axes.
Hide the axes, hide field labels for rows.
Building the Viz in Tooltip
The Viz in Tooltip shows the Top 10 Products per Customer by Sales.
On a new sheet, add Customer ID, Customer Name and Product Name to Rows and Sales and Quantity (via Measure Values) to Text.
Add a Rank Quick Table Calculation to the Sales pill, then edit the table calc to be unique and to compute by Product Name
Click on the Sales rank pill , press Ctrl and drag it into the measures pane. Name the field Sales Rank. Format the field to have a # prefix. Add Sales back into the view.
Now drag the Sales Rank field from the Measure Values section and add to Rows, then change to be a discrete (blue) field, then move so it is to the left of the Product Name field. This should cause everything to re-sort into the required order automatically. It’s always worth double checking the table calc is still computing as expected.
Click on the Sales Rank pill and press Ctrl and this time drag to the Filter shelf and check values 1 to 10.
Now hide the Customer ID and Customer Name fields, and format the display.
Finally, set the display to Entire View. This will make the ‘view’ unreadable, but is necessary to ensure you don’t get a ‘viz is too large to display’ message when you get to add the Viz to the tooltip.
Adding the Viz in Tooltip
Back to the chart sheet, and edit the tooltip of the MIN(0) marks card. Insert the Top 10 sheet via Insert > Sheets. Adjust the width and height values and change the filter to just use <Customer ID>
Hovering on the circle mark should then display the Top 10 Products for that specific customer.
Adding the column headings
On the dashboard, use a horizontal layout container above the main viz. Add 6 text objects, enter the relevant text and centre. Adjust the width of each text object to line up with each column.
The help icon
Add a floating text object to the sheet – it’ll appear over the top of the chart. Position the text object to the top left, and set the background to white. Enter the text. From the context menu of the object, select the Add Show/Hide Button. Position the button in the appropriate location and resize. Edit the button options to have a grey background and show ? when hidden and X when shown.
I think that’s covered all the core points. My published viz is here.
Candra McRae was back to set the challenge this week. I found it relatively straightforward, so if you’re relatively new to Tableau, this is quite a good challenge to start with. In this we’ll cover
Grouping the states
Applying the sort
Adding the total value
Adding the interactivity
Grouping the states
The states need to be grouped based on the initial letter. Candra stated she wasn’t expecting a large IF STARTWITH… type formula. I did it by making use of the fact characters can be converted into ASCII which provides a numerical representation of a letter, which we can then utilise. So we need
State Initial ASCII
ASCII(UPPER(LEFT([State],1)))
This takes the 1st letter of the State, ensures it is uppercase, and converts to ASCII. You can see below what this looks does
With this knowledge, we can then create
State Group
IF [State Initial ASCII] <=77 THEN ‘A-M’ ELSE ‘N-Z’ END
and you can now easily create the stacked bar chart (note, I’ve already removed the various gridlines etc)
Applying the Sort
The ultimate intention is to capture the Category a user clicks on into a parameter, so we need to define that parameter
pSelectedCategory
A string parameter defaulted to Office Supplies
Show this parameter on your sheet, so you can manually test how the sort will work by manually changing the values.
Create a new calculated field to define the sort
Sort
IF [pSelectedCategory]=[Category] THEN 0 ELSE 1 END
Then edit the sort property of the Category field that’s on the Colour shelf as below
Now change the value in the parameter box to Technology and see how the chart changes.
Adding the total value
Create a new field to store the total values
Total Sales by State Group
{FIXED [State Group]: SUM([Sales])}
Add this onto the Detail shelf, then add a reference line per line as below
Adding the interactivity
Once you’ve added the chart onto a dashboard, you need to add a parameter action which will set the pSelectedCategory parameter with the value from the Category field on select.
To prevent the selected category from ‘remaining selected’ on click, I applied the ‘true=false’ trick I use a lot.
Create a field True = true and a field False = false, then add both to the Detail shelf of the chart viz. On the dashboard add a new filter action which on select passes selected fields only setting true = false. As this condition can never be true, the filter doesn’t apply and this clears the highlight action.
And that is it for this week – short & sweet, but covers a handful of bite-size concepts. My published viz is here.
Sean Miller provided the challenge for this week, resurrecting a challenge originally set by Emma Whyte in 2017. Revisiting these older challenges is great fun, as often newer product features provide a different way of solving. For me, I also like the fact I know I’ve already solved it once, and have my own work to reference if I get stuck – ha ha!
Sean hinted that this wasn’t a challenge to ‘overthink’ – no table calcs or LoDs required. You need to be able to display average responses per question per university alongside the overall average response for the question. Simply filtering by university isn’t going to cut it, as the quick filter will immediately eliminate all the data that isn’t associated to the selected university, which means you can’t compute an ‘overall average’ without using LoDs.
The key to this challenge is to use a parameter to drive the University selection. Create this by right clicking on the University field -> Create > Parameter. This will create the parameter dialog box, prepopulated with all the university values. Set the default to University of Liverpool.
pUniversity
With this, we can now create calculated fields to store the values associated to the selected university only.
Note 1, there is already field called Sample Size in the data set. The actual name of this field is <space>Sample Size<space> which Tableau sees as a different name. In hindsight I should have just renamed the original field, so I could then have ‘Sample Size‘. Be mindful of this when I refer to the field later; unless I call it out, I’m referring to my version.
Note 2, I chose to apply the AVG aggregation within the calc rather than changing the default aggregation on the pill when added to the view. There was a reason I did this, but I can’t recall what it was, and think it wasn’t necessary in the end….
University Avg
AVG(IF [University] = [pUniversity ] THEN [% Agree] END)
formatted to percentage, 1 dp
We can also then define the overall average for comparison
Overall Avg
AVG([% Agree])
formatted to percentage, 1 dp
and with that can calculate the variance between the two
Delta
([University Avg]) – ([Overall Avg])
This is custom formatted to 0.00%â–²;0.00%â–¼ (I use this site to get the arrow characters)
And then we need a field to define how the mark needs to be coloured
Colour
[Delta]>=0
We can put these all out in a view
Question Number (which I renamed to No) on Rows
Question Text on Rows
Sample Size on Rows (set to be a discrete blue pill)
University Avg on Rows (set to be discrete)
Overall Avg on Rows (set to be discrete)
Delta on Rows (set to be discrete)
University Avg on Columns (continuous green pill)
Change Mark Type to Circle
Add Colour to the Colour shelf and adjust
Now we need to work on adding the various lines and bands on the chart. This is all managed by adding reference lines (or bands).
Drag % Agree to the Detail shelf, and change to be AVG.
The drag the same field % Agree on Detail again, this time change to MIN. Repeat again, and change to MAX.
Right click on the University Avg axis > Add Reference Line. Create a line, per pane using the AVG(% Agree) field.
Add another reference line (by right clicking on the axis again). This time create a band that starts at MIN(% Agree) and ends at MAX(% Agree). Set the Fill colour to light grey.
We need to create some new fields for the quartile values.
Lower Quartile
PERCENTILE([% Agree],0.25)
Upper Quartile
PERCENTILE([% Agree],0.75)
Add both these fields to the Detail shelf again, then add another reference line (band) similar to that above, but referencing the quartile fields. Set the Fill colour to be a darker grey.
Adjust the formatting and set the tooltips and you’ve got the main chart…. well almost…
In the solution, the first column, the question no, is not labelled. I couldn’t figure out how to do this, which is why I relabelled to simply No. I tried various things, including using text boxes as column headings on the dashboard, but the layout just didn’t work.
BUT I’ve now found out how to do it… because I googled, and I didn’t yesterday when I was building 😦 Andy Kriebel explains it all here. He searches for a ‘zero width space’ character on this site , and then copies the resulting ‘character image’ displayed and pastes into the label of a calculated field. Watch the video to see it in action, but I’ve noted the steps here, just as much for my own benefit when I can’t remember what to do in future… I can see this type of feature cropping up often 🙂
The legend utilises a lot of the concepts above, but we don’t what the mark changing with each university selection. So let’s just hardcode
Legend Avg
AVG(IF [University] = ‘Middlesex University’ THEN [% Agree] END)
and we’ll need a dedicated field for the colour
Legend Colour
[Legend Avg] – [Overall Avg] >=0
The legend sheet can then be built just by plotting the Legend Avg pill on the Columns shelf, with a mark type of circle, the Legend Colour on the Colour shelf, and the same pills used in the reference lines above on the Detail shelf.
When adding the reference lines and bands this time, you will need to add labels and format their position.
The quartile band, also has dotted lines indicating the end of the band, which you can apply as part of the band properties. However the quartile band also has one label left aligned, while the other is right aligned. For a single reference band, the labels can either both be formatted left aligned or both right aligned. To resolve this, don’t add a label to the ‘band to’ section. Create another reference line for the Upper Quartile value, and you can then format the label of this independently.
My published via associated to this challenge is here.
My version based on the original challenge from 2017 is here. The requirement was a bit more complicated it would seem, and it looks like I utilised FIXED LODs quite heavily.
This week’s #WOW2021 challenge by Ann Jackson is focussed on dashboard design/layout, reference lines and formatting time, so that will be the focus of this blog too. I’m hoping it might be a fairly short post this week 🙂
Filtering the data
Creating the key measures, formatting time, adding average lines
Dashboard layout
Filtering the data
The data Ann provided is based on the Google Analytics data the team have harnessed related to the activity on the #WOW website. It’s a static data set, which contains data from 29 Dec 2019 up to 12 April 2021, but Ann states her solution only includes data up to 10 April 2021. I therefore added the Date field to the Filter shelf on the sheet and set it to end on 10 April 2021
Knowing I’d be building multiple sheets for this challenge, I set this filter to be a global filter by setting it to apply to worksheets – > all using this data source
Creating the key measures, formatting time, adding average lines
For this challenge, we’ll be creating a sheet for each BAN, and a sheet for each trend line depicting a measure by week, so 8 sheets in total.
For the Sessions measure, both the BAN and the trend chart are straightforward.
The BAN just shows SUM([Sessions]) on the Text shelf, appropriately formatted. The other BAN sheets are pretty much the same, but just show the appropriate measure in the appropriate colour.
The trend line displays Week([Date]) by SUM([Sessions]), with the average line added by dragging Average Line from the Analytics pane onto the chart and then formatting.
If the numbers don’t quite match up, it may be because your week is set to start on a different day. By default as I’m UK based, dates are set to start their week on a Monday. For this challenge to match Ann’s solution, your week needs to start on a Sunday. You can set this by right clicking on the data source itself and changing via the Date Properties option
To determine the average session duration, we need to build a calculated field, that then needs to be formatted to show minutes and seconds.
Average Session Duration
(SUM([Session Duration]) / SUM([Sessions]))/86400
Session Duration / Sessions will return a value in seconds. To be able to format this in the way required, we need to get the number of seconds as a proportion of a day. There are 86400 seconds in a day (60 * 60 * 24), so we divide by this.
We can then use a custom format on this field and use the nn:ss notation. NOTE not mm:ss. If you needed to format this as hours:minutes:seconds, the format would be hh:mm:ss, but mm:ss does not provide you with the right values. This video demonstrates all this, if you’re interested.
When it comes to building the trend line for this measure, the average line, can’t be added as simply in the way it could for the Sessions trend chart (at least I couldn’t get it to work that way…).
We need to build a calculated field that will show the same overall average value alongside the weekly averages. This value needs to match what’s displayed in the BAN chart.
Overall Session Duration mm:ss
{FIXED : [Avg Session Duration]}
This is returning the Avg Session Duration based on all the rows in the data. As the Date field has been added as a global filter, it is acting like a data source filter, so the dates we don’t want have been excluded from the data set that the FIXED LoD is being applied against. If the Date filter was a simple ‘quick filter’, this calculation wouldn’t work, as the data for the 11th & 12th April would also be included in the calculation.
Format this to nn:ss as well. Add this field to the Detail shelf of the trend chart, then right click on the Average Session Duration axis and Add Reference Line, and reference the Overall Session Duration mm:ss field.
For the bounce rate, we simply need
Bounce Rate
SUM([Bounces])/SUM([Sessions])
which is formatted to a percentage of 1 dp.
When building the trend line, I added the average line from the analytics pane, but that gave me a different value from my BAN. So I built
Overall Bounce Rate
{FIXED : [Bounce Rate]}
This was formatted to % 1dp, and added as a reference line as described above.
Finally the last measure, we need
Avg Time on Page
(SUM([Time on Page])/(SUM([Pageviews])- SUM([Exits])))/86400
formatted to nn:ss
and
Overall Time on Page mm:ss
{FIXED : [Avg Time on Page]}
and again formatted to nn:ss.
The charts for these are built exactly like the Average Session Duration.
Dashboard Layout
The easiest way to describe the layout I built is to show it 🙂 Note the Item Hierarchy on the left hand side of the image below.
I have a vertical container as the Main page.
The first row in this container is the title in a Text object.
The second row is a Blank object and is the yellow line. The background of the blank object is set to the relevant yellow, the outer padding is set to 0 all round, and then the height is set to 4. This gives the appearance of a thick coloured line.
The third row is another vertical container, and I’ve done this, so I can ultimately use the option to Distribute Contents Evenly on the container to ensure the horizontal container ‘rows’, which I’ll be adding into this container, are evenly spaced.
So ‘within’ the 3rd row, the 4th-7th rows are managed using a horizontal container, which in turn contains a blank object (the coloured vertical line), the BAN sheet and the trend sheet. Around each horizontal container I set the outer padding to 10 all round to give some spacing. The blank object in each ‘row’ is given the relevant background colour and set to a width of 15.
Finally, I finished off with an additional horizontal container at the bottom which is where I added my standard #WOW footer. Note this horizontal container is essentially the 4th row of the original Main vertical container though.
Hopefully I’ve provided enough for you to build the challenge yourself / resolve any issues you might have. If there’s anything I’ve missed, do please comment to let me know.
My published viz is here. Please note, that for some reason (and I don’t know why), Tableau Public does not seem to display my time axis properly (just shows 0). As the workbook renders on Public, the values on the axis show, but once fully loaded, they change. If you download the workbook, the values are fine. I published from Tableau Desktop 2021.1.0. I’m putting this down to an issue with Tableau Public.
Ann Jackson returned this week with a challenge primarily focussed on formatting.
The core requirement this week was to be able to present different measures on a chart, based on a user selection, but where the values displayed were of differing numerical formats
Sales per order in $ to 0 decimal places, formatted to show a ‘,’ every 1,000.
Profit Ratio as a % to 1 decimal place
Items per order as a numerical value to 2 decimal places
My focus points this week are
Measure swapping
Adding the line labels
Labelling the y-axis
Adding the reference lines
Building the blocks
Measure Swapping
This is technique that should be in everyone’s arsenal, as it’s a great way to present multiple views of the data without the need for multiple instances of the chart – it saves space and clutter but continues to allow flexibility.
The 3 measures required needed to be defined through calculated fields
Profit Ratio
SUM([Profit])/SUM([Sales])
Sales per Order
SUM([Sales])/COUNTD([Order ID])
Items per Order
SUM([Quantity])/COUNTD([Order ID])
A parameter is also required to allow the user selection. I chose to use a string parameter with the various measures displayed as below
SELECT A MEASURE
Then to pull this altogether, I needed to build a calculated field to store the relevant value based on the parameter value selected
Display Measure
CASE [SELECT A MEASURE] WHEN ‘Profit Ratio’ THEN ROUND([Profit Ratio]*100,1) WHEN ‘Sales Per Order’ THEN ROUND([Sales Per Order],0) WHEN ‘Items Per Order’ THEN ROUND([Items Per Order],2) END
It’s within this field I chose to define the number formatting I wanted to display, and by then setting the number format of the field to Number Standard, it seemed to show what I intended on hover. Add Display Measure to Rows and plot against QUARTER(Order Date) coloured by Category to get the display below.
Adding the line labels
However while the numeric format is what’s required, I haven’t got the $ or % symbol, and I can’t apply that as part of the default formatting.
Instead I created explicit prefix & suffix fields
$ Label Prefix
IF [SELECT A MEASURE] = ‘Sales Per Order’ THEN ‘$’ END
% Label Suffix
IF [SELECT A MEASURE] = ‘Profit Ratio’ THEN ‘%’ END
Adding these 2 fields to the Detail shelf, they can then be referenced in both the Tooltip and the Label as follows
Based on the logic, either both fields will be NULL/blank else, only one will be populated, so you’ll never get $1,000% displayed!
Labelling the y-axis
Amend the y-axis to delete the Display Measure title, then add the SELECT A MEASURE parameter to the Rows shelf. Rotate and format accordingly.
Adding the Reference Lines
Adding a reference line – simples, surely! But why would Ann be making a challenge if it was that easy….hmmmmm! So what were the challenges posed here
If you add an ‘Average’ reference line, you don’t get a value per line (even if you select the ‘per cell’ option) – you just get one average line. If the chart was split so there was a row per category, you’d be able to get this.
2.The lines displayed can’t be created via a ‘dual’ axis chart where the 2nd axis is showing the average, because the line format is a finely dotted line, and we can’t format a line mark this way. Proper reference lines can be formatted though, so I concluded the lines had to be true reference lines.
3. However, the labelling of a reference line is quite limited, and while I can show the value, I can’t use other calculated fields (ie the Prefix/Suffix fields) on the reference line label…
I came up with the following solution : create separate fields to store the AVG values for each Category, so that I could add 3 separate reference lines to the main chart; then create a dual axis line chart which also showed the average per category, label the line accordingly, and reduce the opacity of the line to 0%.
Ref Line Per Category
WINDOW_AVG([Display Measure])
Stores the average of the data displayed, and can be varied based on the table calc settings.
Ref Line – Tech
IF MIN([Category]) = ‘Technology’ THEN [Ref Line per Category] END
Only stores the average for the Technology data. I created equivalent ones of these for Ref Line – Office and Ref Line – Furniture
All 3 fields were added to the Detail shelf, then added as 3 different reference lines, coloured and formatted as a dotted line accordingly
To make the labels, I added Ref Line per Category to the Rows shelf to create a secondary axis. The table calculation was set as below
This produces a straight line for each category on a second chart, which I duly labelled by choosing to label the start of line, and aligning top left
I then set the opacity of the line colour to 0%, which makes the line disappear
I then set the chart to be dual axis, and synchronised the axis.
Building the blocks
For the block chart, I started by building a Tree Map (using Show Me) based on Category and the Ref Line Per Category fields.
I created a Rank field as
RANK([Ref Line per Category])
which I added to the Tooltip. I also then filtered the chart to Rank=1 to give me the main block. I then duplicated this sheet, and changed the rank filter to Rank=2, and repeated again for Rank=3. This gave me 3 sheets I could then organise onto the dashboard, as just using the tree map view directly, I couldn’t control how the different sections would display.
This was a fun challenge this week, slightly less taking than the previous weeks! My published viz is here.
For #WOW2020 Week 16, Lorna set a slightly different challenge that involved data blending. Blending is a technique in Tableau used to combine data from different data sources. You can read more about it here.
Lorna’s scenario is quite a common one – you have a data source which stores some ‘actual’ data (that in a typical scenario is likely to change as you move through the year), along with a more static data source, storing plan/budget/target data for each month. This is typically created at the start of the year and rarely changes. Comparing actuals to target is a very common business requirement.
Once again, I’m going to tackle this challenge but working out all the numbers I need for each month in a tabular format, before I go onto build the viz.
Building out the data
For this challenge we have 2 data sources, the pipeline data containing multiple years and the target data just containing data for 2020. So the first this we need to do is add a filter for Closed Date from the PipelineData source to be the Year 2020.
The data has been specially crafted as if it’s at a particular point in time in April, in my case at the point of building it was 15 April 2020. If this was being built for a real life scenario, we’d want to be reporting based off the Today() function. To simulate this, I created a calculated field to hardcode my ‘today’ date, but if I was doing this ‘for real’, I’d have set it to TODAY().
Today
#2020-04-15#
I need to be able to report the Pipeline Data that is at Stage=Closed Won separately from data that is still in the pipeline (hasn’t been closed as won or lost). I’ll use some calculated fields for this
Closed Won
ZN(IF Stage = ‘Closed Won’ THEN [Sales] END)
Note– the ZN will display as 0 if there is no Sales.
Pipeline
IF [Stage]= ‘Negotiating’ OR [Stage] = ‘Proposing’ THEN [Sales] END
Let’s start to build the table out:
Month of Closed Date on Rows
Closed Won and Pipeline on Cols (as Measure Values)
Year of Closed Date = 2020 on Filter Shelf
Let’s now add in the target from the Target Data source. This will be a blend. When we blend we need to define how to ‘join’ the data sources together. I prefer to make it obvious what fields I am blending on, so although I can use existing fields and define a rule, I prefer created explicit calculated fields so it’s clear.
The Target Data contains a record for each month, dated as per the 1st of each month. In the Target Data, create a new field
BLEND – Month
[Date]
The in the Pipeline Data, create a field named exactly the same
BLEND – Month
DATE(DATETRUNC(‘month’,[Closed Date]))
but in this case we’re truncating the Closed Date to the 1st of each month, and ensuring it too is a Date rather than Datetime data type, so the fields can match.
Now add Target from the Target Data onto the table. If you get a warning message, click ok, then click the ‘link’ symbol that is currently greyed out against the Blend – Month field in the Target Data.
The ‘link’ symbol will go red and indicate that the data is being ‘joined’ on this field. The Target values in the table will now match the values if you check the data source excel file directly, and the Target pill in the Measure Values will show a ‘database’ symbol with an ‘orange tick’ which indicates it’s from a secondary data source. The data sources listed in the Data pane (top left) will also be coloured blue (primary) and orange (secondary), which indicates data blending is being used.
We now need to start working out how much off the YTD target we are so far, so we then work out how much pipeline is potentially missing from each future month.
So first up, how much has been closed won so far this year (only considering complete months). Ie how much has been won in Jan, Feb & March?
YTDClosed
WINDOW_SUM(SUM(IF [Closed Date] < DATETRUNC(‘month’, [Today]) THEN [Closed Won] END))
If the Closed Date is before the 1st of the current month (ie April in this example), get the Closed Won value already computed, but SUM all the values we have for all the months.
Add this onto the table, and you can see the total of the Closed Won values for Jan, Feb & Mar is listed against every month.
The table calc has automatically computed ‘table down’, but I’m, going to explicitly set it, as I know I’m going to move the fields around later, and I don’t want that value to change based on where it gets moved to,
Right click on the YTD Closed pill -> Edit Table Calculation and check Month of Closed Date
We need to work out how much we should have closed in the first 3 months of the year too. So in the Target Data, create a similar calculated field
YTD Target
WINDOW_SUM(SUM(IF [Date] < DATETRUNC(‘month’, [Today]) THEN Target END))
(Note – a Today calculated field also hardcoded to 15th April 2020 needs to be added to this data source too).
Add this field into the table too, and again, set the table calculation to be explicitly set against Month of Closed Date.
In order to work out how much missing pipeline to add to each month, we need to figure out how far we’re currently ‘off’, and then distribute this value across the remaining months.
I’m doing all this in steps, so I can sense check the calcs as I go. We can work out how much we’re off by creating a new field in the Pipeline Data
Basically this is YTD Target – YTD Closed, but when you refer to a field from the secondary data source, the field will be prefixed by the data source name.
Add this to the table, and again verify the table calc is set explicitly.
As this is a field based on other table calcs, you will see them listed as Nested Calculations, and you need to verify each one listed is set appropriately.
To work out how many months in the year are remaining that we need to distribute the above value over, we need
Remaining Months
12 – (DATEPART(‘month’, [Today]) -1)
As Today is in April, which is month 4, then the remaining months is 12 – (4-1) = 9.
An now we can work out how much needs to be added per month
Distributed Missed Sales Value
[Missed Sales Value] / [Remaining Months]
Pop this onto the table, and verify the table calc again.
Now we have this, we can work out what the Target needs to be adjusted to for each of the remaining months to make up the shortfall, which is basically adding the monthly shortfall above to the existing Target for the month (but only for the current and future months).
Adjusted Target
IF MIN([Closed Date]) >= DATETRUNC(‘month’, [Today]) THEN SUM([Monthly Target (2020_04_15_WW16_Sales Pipeline)].[Target]) + [Distributed Missed Sales Value] END
Note – we wrap Closed Date in a MIN function as we’re working with aggregated fields, so the Date needs to be aggregated too. MAX would work just the same.
Finally we need to work out what the shortfall is in the existing Pipeline to meet the Adjusted Target (if there is any).
For the months beyond the current month, this is simply the difference between the Pipeline value and the Adjusted Target (but only if the Pipeline is less than the Adjusted Target). For the current month though, it’s the difference between the Pipeline + Closed Won values and the Adjusted Target.
Missing Pipeline
IF ZN(SUM([Pipeline])) = 0 THEN NULL
ELSEIF DATETRUNC(‘month’, MIN([Closed Date])) = DATETRUNC(‘month’,[Today]) THEN //it’s current month, so need to consider what’s closed & what’s remaining IF (SUM([Closed Won]) + SUM([Pipeline])) < [Adjusted Target] THEN ZN([Adjusted Target] – (SUM([Closed Won]) + SUM([Pipeline]))) END
ELSEIF SUM([Pipeline]) < [Adjusted Target] THEN ZN([Adjusted Target] – SUM([Pipeline]))
ELSE 0 END
Add this onto the table
And we’ve now got all the pieces we need to start to build the viz. Name this sheet Check Data or similar. We want this as our reference sheet to make sure our figures remain correct.
Building the Bar Chart
Firstly, duplicate the table viz, and remove the fields we don’t need in the final display (YTD Closed, YTD Target, Missed Sales Value, Distributed Missed Sales Value).
Now move the pills as follows :
Closed Date from Rows to Columns
Measure Values from Text to Rows
Measure Names from Rows to Colour shelf
Change Mark Type to Bar
Now move Adjusted Target and Target to the Detail shelf.
Adjust the colours of the remaining measures to suit, and reorder, so that the bars a stacked with Closed Won on the bottom and Missing Pipeline on the top.
Before we deal with the target lines, we’re going to sort the Tooltip out. It’s quite tricky… it might be there’s a better way, but I had to create a few custom calculated fields to get the display required.
Creating the Tooltip
For the first 3 months, the tooltip just needs to display the Closed Won value, but from April onwards, we need to display values for Closed Won, Pipeline & Missing Pipeline, even if the values are 0. Also the first 3 months just show the Target, but the remaining months need the Adjusted Target too. These values are displayed with | symbols in between along with labels, which should only show if relevant.
Firstly, we need to make sure all the measure values displayed, are accessible regardless as to which bar we hover over. So all of the 3 measures (Closed Won, Pipeline & Missing Pipeline) need to be added to the Tooltip. This is done by holding down Ctrl as you drag each pill from the Measure Values area onto the Tooltip shelf. This has the effect of duplicating the pill, and retaining any table calc settings that have been applied.
We only want the text ‘| Adjusted Target :’ to display if there is an Adjusted Target value :
Tooltip : Adjusted Target
IF [Adjusted Target] > 0 THEN ‘ | Adjusted Target : ‘ END
Add this to the Tooltip shelf.
We only want the text ‘| Pipeline :’ to display if there is a Pipeline value
Tooltip : Pipeline
IF [Pipeline] > 0 THEN ‘ | Pipeline : ‘ END
Add this to the Tooltip shelf.
And we only want the text ‘| Missing Pipeline:’ to display if we’re in the current or future months.
Tooltip : Missing Pipeline
IF DATETRUNC(‘month’, [Closed Date]) >= DATETRUNC(‘month’,[Today]) THEN ‘ | Missing Pipeline : ‘ END
Add this to the Tooltip shelf.
Now modify the Tooltip so the various pills are referenced and formatted as required
Finally adjust the Month axis, to set the months to be displayed as abbreviated values.
Adding the Target lines
At first glance, you might think the two target lines are both reference lines. However, if you hover over the tooltip of the Target (the solid line), you’ll see you have the same tooltip as the bars. Whilst there is some ability to control the tooltip of a reference line now, you can’t reference all the pills this tooltip requires.
So the Target is actually a dual axis mark. The Adjust Target however, is a reference line.
To get the Target to display, hold ctrl & drag the Target pill from the Detail shelf to the Rows shelf (to duplicate the pill), next to Measure Values.
On the Target marks card,
Remove Measure Names from the Colour shelf
Change the Mark Type to Gantt
Change the Colour to black, and add a black border too (to make the mark thicker)
Make the chart Dual Axis and Synchronise Axis
Uncheck Show Header on the Target axis
If you hover over the Gantt mark/Target line, you should have the same tooltip as when you hover over the bar.
The Adjusted Target is a reference line. To add this, right click on the left hand axis and Add Reference Line. Adjust settings as follows :
Scope – per cell
Value – Adjusted Target
Label – None
Tooltip – Custom, set to ‘Adjusted Target (Dashed) :’ then add Value from the selector
Change the Line to be black and dashed
Both target lines should now be displayed. It’s just now a case of applying some formatting to remove gridlines, row & column lines, adjust font sizes and remove axis title and column titles.
Building the legend
The dashboard displays a custom colour legend. As always there are multiple ways to do this. I chose to ‘fake it’ using aliases and some values associated to a completely different and unused dimension in the data.
Duplicate the Opportunity Name dimension. I just left it as Opportunity Name (copy). On a new sheet, add Oppotunity Name (copy) to the Filter shelf, and select 5 values only.
Then right click on Opportunity Name (copy) and select Aliases. For each of the values you selected in the filter, set an alias based on the legend names to display
Then build the legend as follows
Add Opportunity Name (copy) to Columns
Type in MIN(1) to the Columns shelf to create a fake axis
Add Opportunity Name (copy) to the Text shelf
Add Opportunity Name (copy) to the Colour shelf
Fix the axis of Min(1) to start at 0 and end at 1
Reorder the displayed values to suit.
Format to remove all rows/column lines and hide the headers.
Format the Label to be centred and size font
Clear the tooltip.
Note – I chose to copy the Opportunity Name pill just to make sure I didn’t inadvertently break anything, and to easily revert if things didn’t go to plan :-).
Now the 2 sheets can be placed on the dashboard along with a suitable title.
One final tip – to prevent the user from inadvertently clicking on the legend viz when on the dashboard, add a floating blank image and position over the top of the legend.
If you attended / watched Ann & Laura’s Speed Tip Session at TC this year, then you’ll have seen how to complete the core part of this challenge. I watched the session after I’d completed it, and found I’d approached it slightly differently from Ann. I’ll walk through what I did.
So the core requirement of this challenge was to
Identify % of Sales per State
Group those with a value less than a threshold defined by the user, into an ‘Other’ pot, and colour the ‘Other’ pot differently.
Sort the display based on % Sales descending, except the ‘Other’ pot should always be at the bottom
Label each bar with State/’Other’ pot and % sales, positioning the label above the bar, and colour accordingly
Add tooltips for each bar, coloured accordingly
Incorporate a summary of the number of states in and out of the group within the chart title (since it all needed to be created on a single sheet).
As I often do with these challenges, I played around with Ann’s published solution; changing the parameter and seeing how the viz altered, and hovering my mouse over various points, to see if it would give me a clue as to how it had been built – in this case I was mainly interested in the labels.
What I found from doing this was there was another mark at the 0 point of the axis; it looked like a gantt bar mark. This suggested dual axis would be involved.
So armed with that information, let’s crack on…
Identify % Sales Per State
Total Sales
{FIXED:SUM([Sales])}
This simply stores the sum of all the sales against each row in the data source
% Sales Per State
{FIXED [State]:SUM([Sales]) / SUM([Total Sales])}
The sum of the sales per State is determined as a proportion of all sales
Group those with a value less than a threshold
A [Threshold Percent] parameter was created to store the user defined variable (defaulted to 3%), and then this was used to determine how the State should be grouped for display
State Grouping
IF [% Sales Per State] >= [Threshold Percent] THEN [State] ELSE ‘All Other States’ END
Sort the display based on % Sales descending
Whilst the States needed to be sorted with the largest % first, the ‘Other’ pot always had to display at the end. To do this I created a new calculated field
State Order
IF [State Grouping] = ‘All Other States’ THEN 1 ELSE 0 END
With the basic components now defined, we can start to build the chart
The [State Order] field placed at the front, meant that the States are listed first, and forces ‘All Other States’ to the bottom.
Setting the Sort on the [State Grouping] field to be sorted based on the [% Sales Per State] desc, gives the required result:
Colouring based on [State Order] gives the split required too.
Label the Bar
As stated above, a dual axis is needed for this, utilising our old friend, MIN(0) on the columns. The Mark Type of MIN(0) is set to Gantt Bar, with the [State Grouping] and [% Sales Per State] added to the Label, with the font adjusted to Match Mark Colour, and aligned top right. The Colour of the Gantt Bar mark is also set to 0 transparency so it looks invisible.
To make the label appear when the axis is ‘dualled’ and synchronised, requires a bit of ‘jiggery pokery’ with the row height and bar size. I found this to be very much trial and error, even publishing a couple of times, as while it looked overlapped on my laptop screen, the view did render ok on public.
Note – what I found with this solution is that changing the parameter could make the labels overlap again, which wasn’t ideal. Ann’s solution also suffered from this. I’ve been trying to figure out an alternative, but haven’t got there yet.
Tooltips
The tooltips on the bars look straightfoward, but the State ‘label’ is coloured based on the bars, so requires a couple of calculated fields
Label : State
IF [State Order]=0 THEN [State] END
Label : Other States
IF [State Order] = 1 THEN ‘All Other States’ END
Both these fields are added to the Tooltip side by side and coloured accordingly. As only one will ever be true per row, only one will display :
Count of States for chart title
To count the states based on which group they were in, required a couple of table calcs :
Count States Listed
WINDOW_SUM(IF ATTR([State Order]) = 0 THEN COUNTD([State]) END)
Count States Grouped
WINDOW_SUM(IF ATTR([State Order]) = 1 THEN COUNTD([State]) END)
These were added to the Detail shelf, so could then be referenced in the title of the Viz. The table calc needed to be applied per State Grouping
Then it was just a case of hiding various pills, and formatting to remove row lines and gridlines. A reference line for the selected parameter was also added.
My published version is here. The main difference to Ann’s solution is that she used Sets to group the States together.
One of the main reasons I like to partake in the weekly #WorkoutWednesday challenges, is to get the chance to try out the newer features of Tableau that I can’t use at work, due to the version we’re using (we’re never in a position to react to upgrades at the same speed as Tableau releases).
This week, Lorna provided a challenge to introduce Explain Data which is a feature Tableau released as part of v2019.3, so to complete this challenge, you’ll need this version of Tableau Desktop.
The challenge itself isn’t that taxing, so I’m going to attempt not to make this blog that lengthy (which is a challenge in itself… 🙂 ).
Having not used Explain Data before, then the first thing I did was google it, and I watched & read the info Tableau had published here. There’s absolutely nothing wrong in using online resources to help you complete these challenges :-).
So with that understood, I built the basic vizzes required:
Sales by Quarter
Straightforward line graph….
This Year vs Last Year
A few calculated fields needed for this one:
Latest Year
{FIXED : MAX(YEAR([Order Date]))}
Previous Year
[Latest Year]-1
Sales This Year
IF YEAR([Order Date]) = [Latest Year] THEN [Sales] END
Sales Last Year
IF YEAR([Order Date]) = [Previous Year] THEN [Sales] END
The [Sales This Year] is then plotted against [Sub-Category] with [Sales Last Year] added to the Detail shelf and displayed as a reference line against each cell.
The [Sub-Category] field is hidden, so the row labels aren’t duplicated when displayed next to each other on the dashboard.
Sales by Order ID
This is a dot plot that has then been ‘jittered’ to spread the marks out vertically in a random manner.
Without the jitter, you’d just get…
…where all the circle marks are arranged on the same horizontal line, and subsequently, the majority all appear on top of each other.
To make the jitter effect, I make use of a hidden function in Tableau called Random() which assigns a random no between 0 and 1 to each mark on the row.
Jitter
Random()
Then adding Jitter to the rows (and disaggregating), a new axis gets added and the marks get spread vertically, giving a much better sense of the volume of marks that are around the same values.
Hide the axis to get the desired display. The vertical dotted line is a reference line set to be the average sales for each row.
For further reading on creating jitter plots using Random() or Index() please check out :
So with the 3 charts built, these can be added side by side on a dashboard, and with all set to Fit Entire View, you can guarantee each row will line up. You can adjust the padding of each object to make it look like the row divider lines actually continue as a single line (I forgot to do this on my published version :-)).
Explain Data
So having read the information on Explain Data, I switched to my dot plot, selected a mark, and used the ‘lightbulb’ in the command toolbar to view the Explain Data information. I then saved each of the suggested explanation visuals as it’s own sheet, which I added side by side on another dashboard.
The text at the bottom of each worksheet, is simply the caption text that is automatically generated, and can be exposed on the dashboard by enabling the caption to show.
And that’s it for this week. A relatively straight-forward challenge, but providing some good learning opportunities. My published viz is here.
Donna + DataViz 