Yoshi & Yusuke presented this week’s #WOW2025 challenge live at the Agentforce World Tour in Tokyo. The key feature of this challenge is utilising the Pages shelf to step through how the shipment rankings for a set of products changes on a month-by-month basis.
Gathering the required files & modelling the data
I downloaded the Sample Super factory (subset).xlsx file from the provided google drive and connected directly to it. Relate Production_Plan_Output_Shipment to Product Master via the Product ID fields. I then added a Data Source Filter to restrict the data to Date: Year = 2025
I also downloaded all the images from the images folder and saved them in a new WOW2025_47 directory I created within my …\My Tableau Repository\Shapes\ directory.
Building the core rank calculation
On a new sheet, add Date to Columns and set to the Month level as a discrete (blue) pill. Add Product Name to Rows and Shipment Qty to Text.
Create a new field
Shipment Rank
RANK_UNIQUE(SUM([Shipment Qty]))
and add to the table. Adjust the table calculation setting, so the field is being computed explicitly by Product Name only.
Create another new field
Rank Display
IF [Shipment Rank] = 1 THEN ‘1st’ ELSEIF [Shipment Rank] =2 THEN ‘2nd’ ELSEIF [Shipment Rank] =3 THEN ‘3rd’ ELSE STR([Shipment Rank]) + ‘th’ END
Build the bar chart
On a new sheet, add Shipment Rank as a discrete (blue) pill to Rows and Shipment Qty to Columns and Product Name to Text.
Add Spiciness Level to Colour and change the field to be an Attribute so the table calc doesn’t break. Then add another instance of Product Name to the Detail shelf, and then click the icon to the left of the pill to add it to the Colour shelf, so there are 2 dimensions on the colour shelf. Adjust the colours to suit.
Tip – the quickest way to get the colours to be in a range is to edit colours > ctrl-click to multi select a set of colours, choose a diverging colour palette and click assign palette.
Edit the axis and reverse the axis. Adjust the size of the bars to make them a bit smaller. Adjust the label and match mark colour. Hide both the axis and the Shipment Rank header and all gridlines, and row/column dividers. Add Date at the Month level to the Pages shelf and update the sheet title.
Creating the Viz in Tooltip
On a new sheet ad Product ID to Rows. Change the mark type to Shape and add Product ID to shape. Edit the shapes to use the WOW2025_47 shapes added. As they are stored in alphabetical ordered, just use Assign Palette to set the right images to the right products. Hide the Product ID header row and remove any row/column dividers. Set the sheet to Entire View.
Back on the bar chart sheet, add Product ID to Detail and set as an Attribute so the table calc doesn’t break. Update the Tooltip to reference the relevant pieces of text and then add a reference to the VIT worksheet
The image should now display on hover of a bar.
Creating the bump chart
On a new sheet, add Date at the month level as a discrete (blue) pill to Columns and Rank Display to Rows. Add Product Name to Detail. Change the mark type to circle.
Add Spiciness Level to Colour and change the field to be an Attribute. Change the icon next to Product Name from Detail to Colour. The colours should automatically change and match the settings made against the bar chart.
Add Date at the month level as a discrete (blue) pill to the Pages shelf. The table will be filtered to January.
On the pages control, check the show history checkbox, then click on the ‘down arrow’ to display the additional history options. Set them as follows : show All marks, show both marks, allow fade and adjust the range, then format the trails to a narrower line at a lower opacity.
As you now press ‘play’ on the pages, the trails will display. HOWEVER (and this took some time to realise), in Desktop, the display of a circle on previous months does not work. Publish to cloud and it does. I did go down some dual axis related route until I discover this 😦
Add Product ID as an attributeto the Detail shelf and Shipment Qty to the Tooltip shelf and update the Tooltip and reference the Viz in Tooltip images sheet as before.
Then tidy up by
Removing row dividers
Adding column dividers
Adding column banding
Hide the Date label heading (right click > hide field labels for columns)
Hide the Rank Display label heading (right click > hide field labels for rows)
Update the title
Then add the sheets onto a dashboard and you should be good to go:-) My published viz is here.
To build anything Tableau, you need to connect to some data. I used Superstore as suggested by Kyle, and within my solution did actually utilise one of the data fields to help build each row.
The challenge relies on the use of some custom shapes that Kyle provided. Refer to this KB to understand how to store the shapes for use in Tableau Desktop.
Building the Star
Create calculated field
Dummy
‘dummy’
Add to the Detail shelf and change the mark type to shape. Select the star shape you have saved. Set the view to Entire View and the format the sheet and set the worksheet background colour to green. Remove the Tooltip.
Name the sheet Star or similar.
Building the Bauble sheets
Duplicate the star sheet. Change the shape to the bauble image. Add Customer ID to the Filter shelf and select 2 records only (doesn’t matter what IDs are selected). Then add Customer ID to Columns.
Uncheck Show Header from the Customer ID pill to hide the column header, then remove all row and column dividers. Name the sheet Bauble 2 or similar.
Then to create the other Bauble sheets, just duplicate the sheet again, and select another entry in the Customer ID filter, and rename the sheet.
Repeat this 6 more times, so you end up with 8 bauble sheets and 1 star sheet.
Building the Christmas Tree
On a dashboard sized to 1000 x 1200, add a vertical layout container, and add all the sheets in the correct order
Remove the title from each sheet and set the outer padding of each object to 0, so everything ‘butts up’ against each other
Add a blank object to the bottom (within the same layout container). Set the background colour to brown and remove all padding.
Select the vertical layout container, and then select the option to Distribute Contents Evenly which will adjust every row within the container to be the same height.
To make the sheet on each row narrower, we use padding for the left and right, with different values on each row. I did some calculations based on how wide the dashboard was (1000) and how wide each ‘image section’ should be – 9 baubles at the widest point, so each section was 1000/9 = 111 px wide.
So for the top row, the padding on each side I calculated to be (1000-111)/2 = 444px each. For the second row, the padding on each side I calculated to be (1000 – (2×111))/2 = 389px and so on.
I then added a title and footer, and my Christmas Tree was complete. My published viz is here.
For this week’s #WOW2024 challenge, I asked the community to rebuild this unit chart depicting the medals won each day by country. I built this out while the Olympics was on, curating the data myself, so there is a chance it may not match ‘official’ records (some events got delayed, some medallists may have since been disqualified or reinstated).
Creating custom shapes
The challenge requires a set of custom shapes representing the sports. Download all the image files from the Olympic Sports directory here and save them into a new folder in your …\My Tableau Repository\Shapes directory (as discussed here).
Building the viz
Add Day as a discrete (blue) pill to Columns. Change the mark type to Shape and add Event to Shape. Choose the Olympic Sports shape palette you created above (click reload shapes if it isn’t visible), then click Assign Palette. As the images are named exactly as the events are, they should all match without the need to manually assign each shape to the event. Also note, doing this step first, ensures all events are listed and assigned.
Add Country to Filter and select Great Britain. Show the filter and change to a single select drop-down and customise so you can’t select the ‘All’ option.
You can immediately see the basic layout we’re after. However, as we ned to display shapes and circles to represent the medal types, we need to use a dual axis chart. But at this point there is no axis.
Change the Day field on Columns to be continuous (green). This gives us a axis, but the marks for each event are on top of each other.
Create a new field
Index
INDEX()
Add to Rows and adjust the table calculation to compute by Event.
Edit the Index axis and set it to be reversed. Then add Medal, Country and ID to Detail (to ensure distinct marks are displayed), and readjust the table calculation of the Index field to also compute by these fields as well.
Add Event Detail, Athlete and Notes to the Tooltip shelf, and adjust accordingly.
If you change the Country filter to another entry, eg Albania, the display won’t show every day as they only won medals on days 15 & 16. They only won 1 medal on those days too, so the y-axis has also altered. We don’t want this to happen – the ‘frame’ of the display should remain regardless of the country selected. To resolve this, change the data type of the Day field to Number (decimal) (right click field > change data type). Then edit the Day axis and fix from 0.5 to 16.5 – changing the data type means we can fix using decimal numbers which means we don’t get 0 and 17 displayed on the axis.
To fix the height of the chart, we could ‘hardcode’ it as well, but while the number of days in an Olympic Games cycle is always static for cycle, the maximum medals won per day could change – so if I wanted to reuse this chart on a set of data for a different Olympic Games, I’d have to find out what the max was to hardcode. So instead, we’ll make this dynamic using a nested LoD calc.
{FIXED Day, [Country]: COUNT([Daily Medal Winners])} returns the count of medals per day per country
{FIXED Country: MAX(<code above>)} then returns the max number of the above per country
then the outer (FIXED: MAX()} statement gets the maximum of all of these
Add this to the Detail shelf, then add a Reference Line to the Index axis that shows the average of this field, and doesn’t display any line or values, or tooltips. The axis should extend.
If you select other countries, the axis should remain the same.. until you choose USA and it moves to show 20, as the maximum number of medals in one day has been hit.
Again we don’t want this causing any shift in the ‘frame’. So to resolve, double click into the Max Medal Count field and type + 1 at the end, so the reference line is actually 1 higher. The 20 is still visible, but now it’s visible for all countries. This axis won’t be displayed anyway, but now it won’t shift at all either. Chang the
Now the main framework is in place, we can add the ‘medals’. Add another instance of Day to Columns. On the Day(2) marks card, change the mark type to circle then add Medal to Colour and adjust accordingly. I used bronze: #ce8451, silver: #b3b7b8, gold: #edc948 and the reduced the opacity to 50% and added a dark grey border. Re-order the values in the colour legend and then edit the table calculation on the Index pill again, and ensure Medal is listed first. This should make any bronze medals won on a day be listed at the top, followed by silver and then gold
NOTE – I noticed at this point, that adjusting the Index meant I lost the reference line, so I had to reapply.
Make the chart dual axis and synchronise the axis. You may need to make adjustments to the size of each of the marks cards so the event shapes are within the circles, but this is probably best done after you’ve added to the dashboard.
Hide all gridlines, zero lines, axis rulers and row & column dividers. Hide the Index axis (uncheck show header). Edit the bottom Day axis, delete the title and set the tick marks to None for both major & minor tick marks. Edit the top axis and hide the title. Increase the font of the top axis labels.
Finally we need to show the count of the medals each day. Create field
Count Medals by Country Per Day
{FIXED Day, [Country]: COUNT([Daily Medal Winners])}
then
Label: Medal per Day
IF INDEX()=SIZE() THEN SUM([Count Medals By Country Per Day]) END
Add this to the Label shelf of the ‘circles’ marks card, and adjust the table calculation so it’s computing by all fields except Day and that Medal is listed at the top. The label should display underneath the last circle.
Format the font to be a bigger/bolder style and explicitly align bottom centre, then add to a dashboard and that should be it.
It was Sean’s turn to set the challenge this week which included a lots of little details to provide a wealth of different ‘views’ on the same data within a single chart, specifically the change of measure and the change of date granularity.
Building a viz showing the timeline at different levels of granularity based on user selection, is something I’ve done several times in the past, both for other WOW challenges and also in real work situations. So I immediately headed down my ‘usual route’, creating a parameter to identify the level of the date to report at, and a calculated field to display the appropriate level based on the parameter. However, then I noticed that in Sean’s viz, if I changed the date level, the date axis display also changed, sometimes showing <Month Year> format, sometimes <Year Q> etc. Having the axis change like this isn’t something that would work with my ‘usual’ solution. I did ponder this for some time, and the only thought I had was multiple sheets – one for each date part. Sean hadn’t stated how many sheets were needed for the challenge, and it made me wonder if the omission was perhaps deliberate…
Building the Month chart
To start we need to define a parameter to identify the level of date to select
pDateGranularity
string parameter defaulted to ‘month’ with a list of options, which have an alternative display name
We also need to identify the measure we want to show. Before we can define this, we need
Profit Ratio
SUM([Profit])/SUM([Sales])
and
#Orders
COUNTD([Order ID])
We also need to be able to capture the selected measure in a parameter
pMeasure
string parameter, defaulted to PROFIT (note case). A list isn’t needed as the value will be set via an action on the dashboard
so we can then build
Measure to Display
CASE [pMeasure] WHEN ‘SALES’ THEN SUM([Sales]) WHEN ‘PROFIT’ THEN SUM([Profit]) WHEN ‘PROFIT RATIO’ THEN [Profit Ratio] WHEN ‘ORDERS’ THEN [#Orders] END
On a new sheet, add Order Date to Filters and set as a relative date filter to the last 4 years. Add Order Date to Columns as a continuous (green) pill set at the month level, and add Measure to Display to Rows.
Add the Order Date filter to ‘context’ (right click > Add to context) and set it to apply to worksheets > all using this data source. The context is necessary as when it comes to the option to select a date to highlight, only the dates within the timeframe selected should be visible. Show the pMeasure and pDateGranularity parameters.
Edit the Measure to Display axis to change the title to reference the pMeasure parameter instead (right click and edit axis).
Test the values change as expected by changing the text in the pMeasure parameter to SALES or ORDERS or PROFIT RATIO.
To identify a month to highlight, I created
Order Date – month
DATE(DATETRUNC(‘month’,[Order Date]))
and formatted this to a custom format of yyyy-mm
I then created a set off of this field (right click > create > set) and selected a single date 2021-11
Order Date – month Set
In order to make use of the set and its values, the set needs to be on the view. Add Order Date – month Set to the filter shelf. If it doesn’t happen by default, change the option on the pill to Show In/Out of set and then select both In and Out as the filter options (ie all possible values so nothing is actually being filtered).
Then click on the Order Date – month Set pill on the filter shelf and select show set. The list of values in the set should display.
Change the input type of the Set control to be a single value drop down, where the (All) option doesn’t show (via customise) and the option All values in Context is selected
We need to capture the value associated with the date selected
Month Measure to highlight
IF ATTR([Order Date – month Set]) THEN [Measure to Display] END
Add this to the Rows shelf, then make the chart dual axis and synchronise the axis. Adjust the colours of the Measure Names and then make the marktype of the Month Measure to Highlight marks card, a circle, and increase the size.
Hide the right hand axis, remove the title of the bottom axis and remove all row & column dividers. Right click on the nulls indicator and hide indicator.
Call this sheet month chart or similar.
Building the Quarter chart
Go through similar steps to build a chart to display the information at a quarterly level. The field on the Columns shelf should be Order Date set at the continuous (green) quarter level.
You will need a field
Order Date – quarter
DATE(DATETRUNC(‘quarter’,[Order Date]))
and format this to a custom format of yyyy-“Q”q
Use this to create a set off of this field (right click > create > set) and Order Date – quarter Set
You’ll also need
Quarter Measure to highlight
IF ATTR([Order Date – quarter Set]) THEN [Measure to Display] END
Building the week chart
Repeat again.
The field on the Columns shelf should be Order Date set at the continuous (green) week level.
You will need a field
Order Date – week
DATE(DATETRUNC(‘week’,[Order Date]))
and format this to a custom format of yyyy-“W”ww
Use this to create a set off of this field (right click > create > set) and Order Date – week Set
You’ll also need
Week Measure to highlight
IF ATTR([Order Date – week Set]) THEN [Measure to Display] END
Creating the BANs
To start with, we need to create some calculated fields to store the total values for each measure and the ‘highlighted’ vallues
Total Sales
{FIXED: SUM([Sales])}
format to $ with 0 dp
Total Profit
{FIXED: SUM([Profit])}
format to $ with 0dp
Total Profit Ratio
{FIXED: [Profit Ratio]}
format to % with 0 dp
Total Orders
{FIXED:COUNTD([Order ID])}
format to number with 0 dp
and we’ll also need values for the highlighted date
Highlighted Sales
IF ([pDateGranularity]=’quarter’ AND ([Order Date – quarter Set])) OR ([pDateGranularity] = ‘week’ AND ([Order Date – week Set])) OR ([pDateGranularity] = ‘month’ AND ([Order Date – month Set])) THEN ([Sales]) END
format to $ with 0dp
Highlighted Profit
IF ([pDateGranularity]=’quarter’ AND ([Order Date – quarter Set])) OR ([pDateGranularity] = ‘week’ AND ([Order Date – week Set])) OR ([pDateGranularity] = ‘month’ AND ([Order Date – month Set])) THEN ([Profit]) END
COUNTD(IF ([pDateGranularity]=’quarter’ AND ([Order Date – quarter Set])) OR ([pDateGranularity] = ‘week’ AND ([Order Date – week Set])) OR ([pDateGranularity] = ‘month’ AND ([Order Date – month Set])) THEN [Order ID] END)
format to number with 0 dp.
On a new sheet, double click into columns and manually type MIN(0.0). Repeat this 3 more times, so there are 4 instances of MIN(0.0) on the Columns shelf.
On the All marks card, add Measure Names to the Label shelf.
Now right click on Measure Names in the left hand data pane and select Aliases. Alias each of the MIN(0.0) measures to SALES, PROFIT, PROFIT RATIO, ORDERS
The labels on the viz should change
Create a new field
Sales Selected
[pMeasure] = ‘SALES’
On the 1st MIN(0.0) marks card, change the mark type to shape and add Sales Selected to the shape shelf. Use a transparent shape (see here for details) for the False value, and use the provided sparkline image (which you need to add to your shape palette) for the True value (change the pMeasure parameter to SALES for the True option to show). Increase the Size of the shape to around the 3/4 mark.
Add Total Sales and Highlighted Sales to the Label shelf, and then adjust the font position, size and colour accordingly. This is what my label dialog looked like – Measure Names is centred, while the other two fields are right aligned, and the overall alignment is right too.
Adjust the MIN(0.0) axis to be fixed from -0.2 to 1 to give the mark and the text enough space to breathe.
Now repeat the exercise for the other 3 marks cards. You will need the fields
Profit Selected
[pMeasure] = ‘PROFIT’
Profit Ratio Selected
[pMeasure] = ‘PROFIT RATIO’
Orders Selected
[pMeasure] = ‘ORDERS’
and you’ll need to change the value in the pMeasure parameter to get the relevant shape to show or not for each measure
Finally, create fields
True
TRUE
False
FALSE
and add these both to the Detail shelf of the All marks card. We’ll use these to stop the BANs from being ‘highlighted’ on click on the dashboard.
Then remove all row/column dividers and gridlines & zero lines
Building the dashboard
Create the dashboard, and arrange all the objects so the controls/filters are listed in a row at the top, and the 3 charts are arranged side by side underneath.
If you’ve got dates selected for your quarter & week, then in the data pane, right click on the relevant set, edit set and uncheck any option. This should then give you (None) in the dropdowns.
We will use dynamic zone visibility to control which charts & highlight date controls to display. For this we need fields
Show Monthly
[pDateGranularity] = ‘month’
Show Quarterly
[pDateGranularity] = ‘quarter’
Show Weekly
[pDateGranularity] = ‘week’
On the dashboard, select the monthly chart, and then from the Layout tab, select Control visibility using value and choose the Show Monthly field. Set the control visibility for the highlight a month object to also use the Show Monthly field.
Set the control visibility for the Quarterly chart and highlight a quarter object to use the Show Quarterly field, and set the control visibility for the Weekly chart and highlight a week object to use the Show Weekly field.
Create a dashboard parameter action to set the pMeasure parameter on click of the BANs sheet, passing in the Measure Names to the parameter
Set measure
Create a dashboard filter action to stop the BANs from being highlighted ‘on click’
Regular #WOW participant, Caroline Swiger, set the guest challenge for community month this week, to recreate a visual table in a single sheet. She was heavily inspired by this Super Advanced Tables viz built by my colleague Sam Parsons which he discusses in this YouTube video. This was a concept I’d been meaning to try for a while, so having this set was ideal, as I now get to try it out and blog about it 🙂
I think the easiest way to approach this blog is simply column by column. When I tackled this initially, I ticked off as much as I could remember to do initially, and then referenced Sam’s video when it came to building the bars. As a consequence of that, I did then have to add a field that meant I had to adjust all the existing columns I’d made. If you follow this blog from start to finish, you shouldn’t need to do that.
This table revolves around utilising what I refer to as ‘fake axis’ to allow you to use different mark types other than text within each cell of the table. All of the columns in this table make use of a MIN(0) measure to act as the ‘fake axis’. Whilst we can just type MIN(0) into the Columns shelf each time, we’ll give these measures a specific name relating to the data being presented in each column, so we can easily find them on the Marks card if ever we need to make an adjustment.
Initial Set Up
Create a new field
Y-Axis Position
MIN(0.5)
Add Sub-Category to Rows and add Y-Axis Position to Rows as well. Having a measure on the Rows shelf is necessary for when we come to build the bar chart columns, and I’ll explain why in that section. Edit the Y-Axis Position axis to be fixed from -1 to 2.
Sales Rank Column
The main focus of the data is for the latest year values. So we need to identify various measures relating to just this year.
Latest Year
{MAX(YEAR([Order Date]))}
For the data I’m working with, this returns the year 2023, and from this we can then determine
Current Sales
IF YEAR([Order Date]) = [Latest Year] THEN [Sales] END
Format this to $ with 0 dp.
Apply a Sort to the Sub-Category pill on Rows based on this field.
Create a new field
Sales Rank
RANK(SUM([Current Sales]))
and a field for the ‘axis’
Sales Rank Axis
MIN(0)
Add Sales Rank Axis to Columns. Change the mark type to circle, increase the size a bit. Change the Colour to dark grey.
Add Sales Rank to the Label shelf and edit the table calculation so that it is computing by Sub-Category. Align the text middle centre and bold the text.
Current Sales Bar
Create a new field
Current Sales Axis
MIN(0)
and add to the Column shelf. Change the mark type of this to a bar. Remove Sales Rank from the Label shelf, and replace with Current Sales. Change the alignment of the label to be top left, and ‘un-bold’ the font. Add Current Sales to the Size shelf, then click on the Size shelf button, and change the size from Manual to Fixed, aligned left. This action will make the bars look like proper horizontal bars, with the same starting position, and a length proportionate to the value of Current Sales.
The manual vs fixed sizing option only becomes available when there is a measure (green pill) on both the Rows and the Columns, which is why we needed to create the Y-Axis Position field. Without this, we would only have had a slider size option which wouldn’t have achieved the desired result. The ‘height’ or depth of the bar is based around the position on the Y-Axis (ie 0.5) and the scale of the axis. Fixing the axis from -1 to 2 as mentioned at the start, positions the bar roughly central to where we want it and with a relatively narrow height. If you adjust the axes, you will see how this impacts the bar chart.
YoY Sales Column
For this column, we need to know what the previous year’s sales were, the % YoY difference and whether that difference was positve, negative or didn’t change
PY Sales
IF YEAR([Order Date]) = [Latest Year]-1 THEN [Sales] END
The SIGN() function is a more efficient way than saying IF value is >0 THEN… ELSEIF value < 0 THEN…. ELSE … END. SIGN() returns +1, 0 , -1 depending whether difference is positive, negative or the same.
We also need
YoY Sales Axis
MIN(0)
Add this field to the Columns shelf. Change the mark type to shape and add YoY Sales as a blue discrete pill to the Shape shelf. Use the arrow shapes and assign to the 1, 0 -1 values accordingly. The arrow shapes are available on the challenge page if they’re not already available for you (you might find them in the Arrows shape palette if that exists). Refer to this blog to understand how to add custom shapes.
Add YoY Sales as a discrete blue pill to the Colour shelf and adjust the colours using the blue, red and light grey options referenced in the requirements.
Add YoY Sales Diff to the Label shelf and align middle right. Unbold the text.
Profit Rank Column
Create new fields
Profit Rank Axis
MIN(0)
and
Current Profit
IF YEAR([Order Date]) = [Latest Year] THEN [Profit] END
formatted to $ with 0dp, and then create
Profit Rank
RANK(SUM([Current Profit]))
Add Profit Rank Axis to Columns and then add Profit Rank to the Label shelf. The mark type should already be set to a circle and coloured correctly to dark grey. Align the label middle centre (if it’s not already), and adjust the table calculation so it is computing by Sub-Category.
Current Profit Column
Create
Current Profit Axis
MIN(0)
Add to Columns and change the mark type to square. Add Current Profit to the Label shelf – align middle centre and un-bold. Add Current Profit to Colour. Edit the diverging colour legend. Click on the dark orange coloured square at the left side of the colour scale and change the colour to match the red hex code provided. Similarly click on the blue colour square at the right side and change the colour to match the blue provided. Tick the Use Full Colour Range option.
Create a new field
Current Profit – Size
MIN(1)
and add to the Size shelf, then increase the Size slider to as large as possible.
Change in Profit Columns
The positive and negatives values indicating the change in profit from the previous year is built as two separate columns. So we need
Change in Profit Neg Axis
MIN(0)
Change in Profit Pos Axis
MIN(0)
We need to determine the profit for the previous year
PY Profit
IF YEAR([Order Date]) = [Latest Year]-1 THEN [Profit] END
and with this we can work out the change
Change in Profit
SUM([Current Profit]) – SUM([PY Profit])
We then also need to explicit fields to plot on each axis.
Profit Change -ve
IF SIGN([Change in Profit]) = -1 THEN [Change in Profit] END
This field will only contain a value if the Change in Profit field is negative. Format this to $ with 0dp
Profit Change +ve
IF SIGN([Change in Profit]) = 1 THEN [Change in Profit] END
This field will only contain a value if the Change in Profit field is positive. Format this to $ with 0dp
Add Change in Profit Neg Axis to Columns and change the mark type to bar. Add Profit Change -ve to the Size shelf, and adjust the Size so it is Fixed and left aligned. Add Profit Change -ve to the Label shelf and align top right and un-bold. Add Change in Profit to the Colour shelf, and adjust the diverging colour legend exactly as described above.
Now repeat. Add Change in Profit Pos Axis to Columns and change the mark type to bar. Add Profit Change +ve to the Size shelf, and adjust the Size so it is Fixed and left aligned. Add Profit Change +ve to the Label shelf and align top right and un-bold. Add Change in Profit to the Colour shelf, and adjust the diverging colour legend exactly as described above.
Then right click on the Change in Profit Pos Axis and Add Reference Line. Create a Constant reference line of 0 value. This line won’t be that visible at this point, but once we remove all the other formatting, it will display.
Profit Ratio Column
Create the field
Profit Ratio Axis
MIN(0)
and
Current Profit Ratio
SUM([Current Profit])/SUM([Current Sales])
format this to % with 1 dp.
and
Profit Ratio – Colour
SIGN([Current Profit Ratio])
Add Profit Ratio Axis to Columns and change the mark type to Shape. Select the rounded shape from the shape palette (again this is provided on the requirements page and needs to be added as a custom shape).
Add Profit Ratio – Colour to the Colour shelf as a blue discrete pill and adjust accordingly to use the red and light grey options provided. Increase the Size of the shape as necessary, and then add Current Profit Ratio to the Label shelf.
Tidying up
So we’ve built the table, just need to clean it up by
Right click the y-axis and uncheck show header
Right click one of the x-axis and uncheck show header
Right click on the Sub-Category label at the top of the column and select hide field labels for rows
Format the chart and remove all column dividers
Remove all grid lines, zero line, axis rulers and axis ticks.
Select the All Marks card and click on the Tooltip button and uncheck show tooltips
The sheet can now be added to a dashboard. Use a horizonal container positioned above the chart and add text objects to create the column labels. My published viz is here.
It’s community month still for #WOW2022, and this week saw Samuel Epley set this challenge to visualise the home run trajectories of Aaron Judge.
I had a little mini-break to Rome this week, so was hoping I was going to be able to get this week’s challenge done and dusted on the Tuesday evening if it landed early enough, as I wasn’t going to be around.
It did land on the Tuesday for me, but wow! it was not going to be easy! I managed to build the KPIs & the scatter plots on the Tuesday evening, and knowing I didn’t have much time, just chose to use the Home Runs stats data set only. I knew these charts weren’t going to need any data densification, so found this approach simpler.
I’m afraid I’m still constrained by time at the moment, so this post isn’t going to be the detailed walkthrough you might usually expect – sorry! I’m just going to try to pull out key points from each chart.
KPIs
I built this on a single sheet, using Measure Names and Measure Values.
I used aliases on the Measure Names (right click -> Aliases) to change the label you can see displayed ie the Distance pill is aliased to ‘Average Distance’
I also custom formatted the various numbers and applied suffixes to display the unit of measure
Note – to To get the degree symbol, I typed Alt+ 0176
Scatter Plots
I built the Exit Velocity by Distance scatter plot first, and completed all the formatting & tooltips. Then I duplicated the sheet to form the basis of the other scatter plots, and just swapped the relevant pills as needed.
For the ball shape, I loaded the provided images as custom shapes into my shapes repository. I then just created the following calculated field to use as a discrete dimension I could add to the Shape shelf
Ball Shape
[HR Number]%9
It’s not as completely randomised as perhaps it should be, but it looks random enough on the display.
The Pitcher in the data is in the format <Surname>, <Forename>, but on the tooltip it needs to display as <Forename> <Surname>, so I just used a transformation on the Pitcher field to split the field based on the comma (right click Pitcher -> Transform -> Split). This automatically created 2 fields I could use on the Tooltip.
I also noticed a very subtle wording change in the tooltip based on whether the match was Home or Away. If Home, the tooltip read ‘New York Yankees vs. <Opposition>’ otherwise it read ‘New York Yankees at <Opposition>’. I used a calculated field for this logic
TOOLTIP: vs or at
IIF([Location]=’Home’,’vs.’, ‘at’)
The Trajectory Plot
OK, so this was the hardest part of this challenge, and mainly due to getting your head round the physics involved, as so many of the calculations are dependent on each other.
I’m generally pretty confident with my maths, but this was complex, especially with the force calculations for the y-axis. Samuel stated that both gravity and drag impacted the Y-axis calcs, but it wasn’t clear to me how both these forces should be applied (a bit of trial and error and I ended up adding them within the formula).
By the time I came to tackle this challenge, Samuel had already posted a video walkthrough, which can be viewed here and is another reason why I’m not going down to the nth degree in this post.
My suggestion is to watch Samuel’s video and/or feel free to download my workbook. I built my workbook independent of Samuel’s video, so there may be steps/calculations that differ.
However, I have tried to number my calculations in the order in which I created them, so you can hopefully follow the thought process. I have also left a CHK:Data sheet in the workbook, which I used to sense check what I was doing.
All the table calculations in the CHK:Data sheet are just set to the default ‘table down’ as I have filtered the sheet to a specific Home Run (HR Number = 1) only (ie I didn’t change any of the table calc settings as I added the pills to the sheet).
However, when you build the main trajectory chart, you have multiple HR Numbers in the view, so all the table calculations must be set so that calculations are only working for each HR Number. This means that any table calc (and any nested calculations) need to have all the fields except HR Number checked
When using the Pages shelf, which isn’t something I’ve ever really had to do before, you need to Show History and adjust the various settings to get the trail lines to show
To rotate the ball (the bonus option), you need another field to use on the Shape shelf. I had lost the will to live a bit by this point, so used the formula from my friend Rosario Gauna’s solution.
Rotation Shape
STR(IIF([14-Start Position Y m] <= 0, 0, (MIN([Time Interval]) * 1000 / 25) % 9))
Note – when you add this to the Shape shelf, and select your baseball palette, just then use the Assign Palette button to automatically assign a ball to a number – this will get them into the correct order, without you having to do it one by one.
Finally, when adding the reference average lines, be sure to set the scope to per pane rather than table, otherwise you’ll end up with the wrong figures.
I think I’ve pretty much covered all the ‘little’ points that I came across that may trip you up, aside from all the tricky calcs of course!
My published workbook is here. I hope what I’ve written is enough for you to build it yourself. I think I’d still be here next year if I tried to do anything more fully! I’m off for a lie down now!
Sean Miller was back this week to set this challenge to recreate a ‘rat sighting’ map using hexbins. I’ve only used hexbins in other #WOW challenges, so needed a bit of a refresher (the previous challenges pre-dated my own blog, so I couldn’t use myself as a reference). A quick google for ‘tableau hexbins’ and I found a variety of articles that provided the refresher needed.
Sean also used the opportunity to apply some other crucial skills – adding custom shapes and custom colour palettes, which I recommend is the first step you do in completing this challenge.
Adding custom shapes
Download the hexagon shape provided by Sean, and then save it into a folder in your …My Tableau Repository\Shapes directory. I have a folder called ‘Custom’ where I place random shapes I need. This post will help you out if you’re having difficulty with any of this.
Adding custom colour palette
Open a text editor such as Notepad, then open the preferences.tps file that is located in your .. My Tableau Repository directory. Copy & paste the block of code provided by Sean between the opening and closing <preferences> tag. Save the file and close the text editor. This post will help if you’re having trouble.
Building the map
The provided rat sighting data set contains a Longitude and Latitude value for every rat sighting since 2010.
Hexbins provide a way to group (bin) these Lat & Long values together. The size of the bin is typically determined by a parameter, so lets first set this up
pRatio
integer or float parameter which I set to a default value of 250 (Sean didn’t specify the value he’d used, but trial and error suggested to me this looked ‘about right’).
Edit the geographic role of this field to be mapped to Latitude.
Add HexbinX to Columns and HexbinY to Rows. Modify each field so that it is a continuous dimension.
Change the mark type to Shape and select the hexagon shape you saved earlier.
Add the auto generated ‘count of dataset’ (Count of Rows) field to Colour, and adjust the colour to use the OrRd-5 palette you added earlier. Ensure to set the palette to Reversed.
Set the Tooltip so it doesn’t display anything on hover, and add Borough to the Detail shelf (this is needed for the interactivity later). Hide the nulls indicator that displays in the bottom left (right click -> hide).
On the Map menu, select Map Layers, and set the background style to dark.
Using the map controls, zoom in and pan to the left slightly, so the coloured area is mainly central and there’s less ‘sea’ at the bottom. It’s likely that you may need to adjust further once you’ve placed the map on the dashboard. But before publishing, we want to turn the map controls off, to prevent a user from shifting the display (Map -> Map Options -> uncheck all options).
Building the Bar Chart
On a new sheet, add Borough to Rows and Count of Rows to Columns. Add Borough to Filter and exclude Null and Unspecified. Sort the rows descending. Adjust the colour to suit.
Show mark labels, but only display the max & min values. Format the Count of Rows pill so the labels are displayed in K to 2 dp.
Hide the axes, right align the row label headings, adjust the tooltip. Hide the column heading. Remove zero lines and axis rulers. Set the background colour of the worksheet to ‘None’ (ie transparent).
Further formatting is required, but this is best done after the sheet is added to the dashboard, as you’ll lose visibility of the text at this point.
Creating the dashboard
Add the map to a dashboard, and remove all the additional containers/legends etc that are added, and the sheet title. Then add the bar as a floating object and position bottom left. Fit to entire view. Edit the title so it contains the ‘on hover’ instruction and format in light grey font.
Now you can format the row labels, the row headings and the gridlines to be appropriate colours – light grey rather than white.
Add a dashboard highlight action that on hover of the bar chart, highlights data in the Map
Then add floating text boxes and add the title and description. I ended up adding a text box just containing the OH, and then another position just below containing RATS! and then the description, as otherwise the carriage return between OH & RATS! made the spacing too wide. I used the controls on the layout tab to ensure both text boxes were positioned at the same x-coordinate.
Hopefully, you should now have a beautiful looking viz. My published version is here.
Side note – When I first started building this I tried from memory, and didn’t quite get things right. I then adjusted various fields as described above, but when I then tried to add the Count of Rows to Colour, I was only ever getting a value of 1 against each bin. I double & triple checked all the calcs and couldn’t see any issues. It was very weird. I simply ended up closing down my workbook and starting again from scratch and all was fine. I’m just letting you know this in case you too come across any oddities during your build, and things don’t behave as expected. It meant, what was ultimately quite a straight forward build (once I got the calcs right), ended up involving more time and head-scratching than really required 😦
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
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 Segmentsin 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]))
Sizein 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.
It’s getting near #data19, Tableau’s annual customer conference, which once again is being held in Las Vegas. So for #WorkoutWednesday this week, Lorna decided to incorporate this into her challenge, which was also intended to showcase some of the new features introduced into v2019.3 (so you’ll need this version at least to complete the challenge).
For the challenge, Lorna provided a dataset in a .hyper format that simply consisted of the (casino) name, address and it’s associated latitude and longitude values.
The core requirement was to show all the casinos within a user defied radius from another casino, which was selected by user by clicking on the map.
So we need to
identify the selected casino
determine the lat and long of the selected casino (the start)
determine the distance (in miles) between the selected casino and all the other casinos
identify the radius
restrict the display to just those within the specified radius
Identify the selected casino
This is simply a parameter Selected Casino which is initially hardcoded to have the default of “Mandalay Bay Resort & Casino” (it’s important you get this spelt right using the exact case too).
I then have a boolean calculated field
Is Selected Casino?
name = [Selected Casino]
which is true if the name of the casino in the dataset matches the value stored in the parameter
Lat & Long of selected casino
My aim is to get the latitude and longitude of the selected casino to be stored against each row in the dataset, so I need to use an LoD to do this
The first MAKEPOINT function above determines the start point using the information about the selected casino, and the second MAKEPOINT determines the end point, using the casino location information stored on each row.
Identify the radius
The viz requires the data to be restricted just to those within a defined radius, so we need another parameter, How many miles? to capture this value.
In Lorna’s viz, the parameter has a fixed range, which is specified as part of the parameter properties
In order to then restrict the casinos shown to just those within range, we need
Within specified miles?
[Distance (miles)]<= [How many Miles?]
The final thing we also need at this point, is a Distance (metres) field as this is used to colour the marks on the viz, and is also referenced on the tooltip.
Building the map
So now we’re at the point we can start building the map.
My initial observations upon hovering over Lorna’s published solution, was this would need to be a dual axis map. The text on the tooltip differed dependent on whether the mark was the selected one or not. The colour of the marks also differed; for the selected mark, the colour is black, while for the non selected marks the colour varied based on the distance.
So with this in mind, I decided to build another calculated field
IsSelected Lat
IF [Is Selected Casino?] THEN [latitude] END
which would just store a latitude value against the row of the selected casino only, as demonstrated below (I set it to have a geographic role of latitude)
I figured I’d then plot this as a dual axis on the map, but as soon as I added, second latitude pill, my display changed from having a map background, to a standard scatter plot chart 😦
So I started to go down the route of just making it a single axis chart, but the tooltips started to get too complicated, and the colouring just wasn’t working….
So I reverted back to the original plan, but this time, used the same latitude pill for my dual axis, which retained the map background
I now needed to differentiate on each map between the selected casino, and the others, and did this initially by Size
Size (selected)
IF [Is Selected Casino?] THEN 2 ELSE 1 END
Size (non selected)
IF [Is Selected Casino?] THEN 2 ELSE 1 END
These are independent fields which can be adjusted differently, and one is placed on the Size shelf of each marks card and adjusted so on one chart the selected mark is the biggest, and all the other marks are as small as they can be. On the other chart, the selected mark is made as small as possible.
I then started adding the various other features
added Distance (metres) to the colour shelf on the non- selected marks card, changing the colour palette to purple, and reversing the range so the lower the distance, the darker the colour
changed the mark type to Shape on the selected marks card and added Is Selected Casino? to the shape shelf, changing the shapes accordingly
The location marker image I found on flaticon.com (remember to add an attribution on your viz if need be to the icon creator)
See this blog for how to get the shape into Tableau
Set the tooltip appropriately for each marks card
Filtered the view to restrict to the casinos in range
The map background needs to be changed to street view, which is set via the Map -> Map Layers setting in the menu
We can now make the map properly dual axis, and can put on a dashboard.
Changing the selected casino
Now it’s on the dashboard, we can use a parameter action to change the value of the selected casino ‘on click’
Displaying count of casinos within radius
The dashboard shows a summary on selection, which I added as the title of the map sheet
I created a calculated field
Count Casinos
TOTAL(SUM([Number of Records])) – 1
which needs to exclude the selected casino, hence the subtraction. I added this to the Detail shelf, so I could reference it in the sheet title.
Ensuring all the casinos remain ‘visible’ on selection
By default, on selection of a different casino, all the other casinos within range are faded out, which we don’t want to happen
So I used the same ‘dummy’ trick with highlight action that I documented in a previous blog to prevent this from happening
The dashboard just needed a few more tweaks – adding the title (which references the parameters), adding the colour legend and the distance parameter, and I was done. I published my viz.
Except I wasn’t completely happy…
When I increased the number of miles, the location shape became ‘hidden’ beneath the other marks, but if I changed the order of the axis, tiny marks were visible on each casino, which I couldn’t get the colour to match
This stumped me, so I had to check out Lorna’s solution, but I stared for some time and just couldn’t figure out what she’d done. It was Rosario Gauna’s solution where I immediately saw what I had to do, as Rosario had made a comment in the worksheet caption as a reminder.
Frustratingly, this is exactly what I do to remind me of this sneaky trick… the small marks on the ‘selected casino’ mark shelf, were ‘hidden’ by simply right clicking on the Is Selected Casino? shape legend and ‘hiding’ the false values
Doh! I wish there was a way you could see more easily on the sheet in Desktop where someone has done this.
Week 39 of #WorkoutWednesday was set by Lorna this week. Details of the challenge and a description of what a BCG Growth Share Matrix is here.
The main complexity of this challenge is the calculations, and more so with Lorna’s stipulation ‘no table calcs allowed’.
I started using Tableau before LoDs were introduced, so I tend to be much more comfortable with Table Calcs than I do with LoDs; I know there are many people who are the other way round.
So, let’s get started. In the interest of time (it’s now Sunday afternoon, and I’ve dipped into this challenge on & off since Wednesday), I’m going to focus on the calcs involved in achieving my solution, and not necessarily going into the detail of putting it all together on the Viz.
Sales Scatter
To build the Sales Scatter Plot, we need the following information to help get the %growth and %market share values :
The total sales across all the specified years per subcategory
The sales for the latest year per subcategory
The total sales for the previous years per subcategory
The overall total sales across all the specified years
The number of years to compare against is set by a parameter to be 2,3,or 4 years (No.of Years).
To limit the data just to these years, I created a Dates To Include as
YEAR([Order Date]) >= [Max Year in Dataset] – ([No of Years]-1)
Max Year in Dataset is another field that I could have simply hardcoded to 2019, but I decided to be more dynamic, and derive it using an LoD
YEAR({FIXED: MAX([Order Date])})
which gets the latest Order Date in the data and then retrieves the Year part of it, which happens to be 2019 in this instance. Dates To Include is then True if the year of the order date is greater than, or equal to, the maximum year less one less than the No of Years param.
Eg if No of Years = 2, then Dates to Include is True if the year of the order date >= 2019 – (2-1) = 2019-1 = 2018.
if No of Years = 4, then Dates to Include is True if the year of the order date >= 2019 – (4-1) = 2019-3= 2016.
To sense check the numbers I need, I built a tabular view by Sub Category adding Dates to Include = True to the filters shelf.
Total sales across all the specified years per subcategory
is simply achieved by adding SUM([Sales]) to the view.
Sales Latest Year
The sales for the latest year per subcategory is
IF YEAR([Order Date]) = [Max Year in Dataset] THEN [Sales] END
Sales Previous Years
The sales for the previous years per subcategory is then just the opposite
IF YEAR([Order Date]) <> [Max Year in Dataset] THEN [Sales] END
The value of this field ultimately changes as the No of Years parameter changes. If No of Years = 2, then this will contain the 2018 sales, if its 3, then it will contain the sum of the sales in 2017 & 2018 etc.
The Growth % is stated as being the change for the latest year in comparison to the average sales of the previous years. So next I need
Average Sales Previous Years
SUM([Sales Previous Years])/([No of Years]-1)
Note the [No of Years]-1, as when comparing across 2 years, I’m really only being asked to compare 2019 with the previous year, ie 2018.
The difference of the latest year compared to the average, as a proportion of the average.
Now we need the Market Share of Sales. This would usually be a pretty simple Percent of Total quick table calculation on SUM([Sales]), but I’ve been challenged to do this without table calcs. So an LoD is needed instead. I created
Sales All Years
{FIXED [Dates to Include]:SUM([Sales])}
which gives me my total sales across the specified years, at the ‘overall’ level.
From this I can calculate
Market Share Sales
SUM([Sales])/SUM([Sales All Years])
The table below gives the breakdown of all these fields at a sub-category level for 2 years
Next step is to categorise each row into Cash Cows, Dogs, Question Marks or Stars based on their % Growth & % Market Share values. Lorna stated that 7% should be considered the mid-point for % Market Share, but that the Growth mid point should be half of the maximum growth value.
From the table above, 64.8% (against appliances) is the maximum value in the set, so I need a new field against each row that outputs 32.4%. Once again, with Table Calcs I would just have used WINDOW_MAX to find the max and halve it. But instead I needed another LOD, this one a bit more complex…
I find INCLUDE and EXCLUDE LoDs a bit of a black art, and there may well have been another way to do this, but it was a bit of trial and error in the end, and so once it gave the value I was after, I stuck with it 🙂 I’ve tried to write some sentences to explain it better, but can’t 😦
So with the mid point defined, the categorisation is then
Sales Category (Sub Cat)
IF [Growth Sales]<=MIN([Mid Sales Growth Constant (Sub Cat)]) AND [Market Share Sales]<=MIN([Mid Market Share Constant]) THEN ‘Dogs’ ELSEIF [Market Share Sales]<=MIN([Mid Market Share Constant]) AND [Growth Sales]>MIN([Mid Sales Growth Constant (Sub Cat)]) THEN ‘Question Marks’ ELSEIF [Growth Sales]>MIN([Mid Sales Growth Constant (Sub Cat)]) AND [Market Share Sales]>MIN([Mid Market Share Constant]) THEN ‘Stars’ ELSE ‘Cash Cows’ END
This is all the information now needed to plot the Sales Scatter chart. Custom shapes were sourced from flaticon.com and I used Paint to colour and resize them appropriately, before saving them into a folder in the Shapes folder of the My Tableau Repository directory on my laptop.
Order Scatter
The steps for creating the Orders scatter are the same in principle. I need to find
The total orders across all the specified years per subcategory
The number of orders for the latest year per subcategory
The number of orders for the previous years per subcategory
The overall number of orders across all the specified years
However in doing this I got quite different numbers from Lorna, and its due to how you choose to count orders. Unlike a sales value, where the value is just attributed to a single line in the data set, an Order ID can span multiple lines, which means they can span sub-categories too. Eg imagine you have
Order A – contains 2 line of appliances, and 1 line of phones
Order B – contains 1 line of appliances.
How many orders are there? Answer = 2
How many order lines are there? Answer = 4
How many orders contain appliances? Answer = 2 (100%)
How many order lines contain appliances? Answer = 3 (75%)
How many orders contain phones? Answer 1 (50%)
How many order lines contain phones? Answer 1 (25%)
So when it comes to the ‘market share %’, should I be considering order lines (which means it’ll sum to 100%) or distinct orders which means it’ll sum to more than 100%.
I wrestled with this, and ultimately concluded, this challenge is purely for illustrative purposes, and to stick with my original assumption based on distinct orders (which I’d already calculated before finding the discrepancy). So my Market Share % won’t sum to 100%, and probably isn’t really a definition of Market Share, but I made peace with myself and moved on 🙂
# Orders
COUNTD([Order ID])
Orders Latest Year
COUNTD(IF YEAR([Order Date]) = [Max Year in Dataset] THEN [Order ID] END)
Orders Previous Years
COUNTD(IF YEAR([Order Date]) <> [Max Year in Dataset] THEN [Order ID] END)
Once again the Market share mid point was defined to be a 7% constant, while the growth % mid point needed to be half the max value, which in this instance based on the above, was 1/2 of 51.2%. The same type of LOD was required which gave me
IF [Growth Orders]<=MIN([Mid Orders Growth Constant (Sub Cat)]) AND [Market Share Orders]<=MIN([Mid Market Share Constant]) THEN ‘Dogs’ ELSEIF [Market Share Orders]<=MIN([Mid Market Share Constant]) AND [Growth Orders]>MIN([Mid Orders Growth Constant (Sub Cat)]) THEN ‘Question Marks’ ELSEIF [Growth Orders]>MIN([Mid Orders Growth Constant (Sub Cat)]) AND [Market Share Orders]>MIN([Mid Market Share Constant]) THEN ‘Stars’ ELSE ‘Cash Cows’ END
Top 20 Manufacturers Bar Chart
To create the bar chart, I created a new field Manufacturer-SubCat as
[Manufacturer] + ‘ (‘ + [Sub-Category] + ‘)’
I then set about creating the same data tables as I’ve included above, one for Sales and one for Orders.
The majority of the measures used were the same, but when it came to determining the growth mid-point, I found creating a separate field was simpler than trying to use a single field that worked across both Sub-Category and Manufacturer-SubCat. I followed the same methodology though:
I also found though that the 7% market share constant didn’t really give me the split, so I decided a 3% constant would be better to give me categorisations at this level into each bracket. As I had different comparison values, I needed new categorisation fields too.
Sales Category (Manu Sub Cat)
IF [Growth Sales]<=MIN([Mid Sales Growth Constant (Manu Sub Cat)]) AND [Market Share Sales]<=MIN([Mid Market Share Constant (Manu Sub Cat)]) THEN ‘Dogs’ ELSEIF [Market Share Sales]<=MIN([Mid Market Share Constant (Manu Sub Cat)]) AND [Growth Sales]>MIN([Mid Sales Growth Constant (Manu Sub Cat)]) THEN ‘Question Marks’ ELSEIF [Growth Sales]>MIN([Mid Sales Growth Constant (Manu Sub Cat)]) AND [Market Share Sales]>MIN([Mid Market Share Constant (Manu Sub Cat)]) THEN ‘Stars’ ELSE ‘Cash Cows’ END
Orders Category (Manu Sub Cat)
IF [Growth Orders]<=MIN([Mid Orders Growth Constant (Manu Sub)]) AND [Market Share Orders]<=MIN([Mid Market Share Constant (Manu Sub Cat)]) THEN ‘Dogs’ ELSEIF [Market Share Orders]<=MIN([Mid Market Share Constant (Manu Sub Cat)]) AND [Growth Orders]>MIN([Mid Orders Growth Constant (Manu Sub)]) THEN ‘Question Marks’ ELSEIF [Growth Orders]>MIN([Mid Orders Growth Constant (Manu Sub)]) AND [Market Share Orders]>MIN([Mid Market Share Constant (Manu Sub Cat)]) THEN ‘Stars’ ELSE ‘Cash Cows’ END
To Sort the Manufacturer-SubCategory data by Sales or Orders, I created a SORT BY parameter with values of Sales and Orders.
I then created
Sort Value
IF [Sort] = ‘Order’ THEN [# Orders] ELSE SUM([Sales]) END
On the Manufacturer-SubCat field, I then set the Sort by property to be
Manufacturer-SubCat was then also added to the Filters shelf, with the filter set to
And that covers the main calcs and complexities that went into my version/interpretation of this challenge. I hate it when I can’t get the numbers to match 😦 but ultimately now feel comfortable with what I did and I hope it might explain why some of you may have got differences too…
My version of the viz is here, and I’ve included additional detail on the tooltips too which should show the numbers that went into the % calcs for each mark.
Donna + DataViz 