It’s Tableau Conference week, so this week’s challenge, set by Yoshi, was presented as part of the live session at #TC26. Yoshi set 3 levels of the challenge, to represent session data from TC25. I managed to get through the Basic and Bonus challenges
Building the Basic KPI sheet
After connecting to the data set, create a new field to determine if the session is AI related or not
AI-related
CONTAINS([Title], “AI”) OR CONTAINS([Title],”Agent”) OR CONTAINS(IFNULL([Topic],”),”Agentic Analytics”) OR CONTAINS(IFNULL([Topic],”),”Artificial Intelligence”)
Add this to the Text field on a new sheet, then adjust the text to include the rest of the words, adjusting the font style and colour as required.
Building the basic calendar sheet
Format Start Time and End Time to custom date format of hh:nn, and format Date as mmm dd
On a new sheet add Date as a discrete exact date (blue pill) to Columns and Start Time as a continuous exact date (green pill)to Rows, and edit the Start Time axis so it is reversed.
Add ID to Detail and change the mark type to circle. Create a new field
Index
INDEX()
and add this to Columns
Add AI-related to Colour and adjust accordingly. The edit the Index table calculation so it is computing by Id and AI-related only and sorted by the min value of AI-related descending, so the AI related sessions are listed first
Format the Start Time axis, so the Scale is formatted as h AM/PM and change the font style to larger, blue and bold
Format row and column dividers in the pane only to be thicker blue lines, with no dividers on the headers
Add thin blue gridlines to the Rows and remove column gridlines and zero lines
Edit the Start Time axis again, and set the Tick Marks to start at 08:00 and display every 2 hours
Remove the axis title. Hide the Index axis (right click, uncheck show header). Format the Date header fields (large, blue, bold) and remove the Date label (right click – hide field labels for columns).
Add Title, Description, Start Time and End Time to Tooltip and adjust accordingly.
Add both sheets onto a dashboard, and add an additional Text object to contain the additional explanation.
As before, add this to the Text field on a new sheet and adjust text to contain the additional wording and formatting.
Building the Bonus Calendar Sheet
Start by duplicating the basic calendar sheet. Add Session Duration to Size.
The sessions need to be sorted by the longest durations first, while still be grouped with AI -related sessions listed before non AI session. Create a new field
Sort
[Session Duration] + (INT([AI-related])*100)
I’m basically creating a numeric value to sort by, based on the duration. adding 100 if the session is AI related, ensuring the Sort value for even the shortest AI session (20 mins) will be larger than the longest non AI session (90 mins).
Adjust the Sort property on the Index field table calculation, to now sort by the minimum of Sort descending
Adjust the tooltip to include the Session Duration, and you should be done.
Duplicate your basic dashboard, then use the ‘object swap’ feature to change the 2 vizzes
Erica collaborated with Giulio D’Errico for this week’s #WOW2025 challenge, which contained lots of features, though the main challenge was to display filter on Region, which along with the Region name also displayed a KPI indicator that could change based on selection from other parameters.
Defining the parameters
We need 3 parameters for this challenge
pMeasure
strig parameter that lists the two options Profit and Sales; defaulted to Profit.
pProfitThreshold
integer parameter that lists the specified values, defaulted to 2,000
pSalesThreshold
integer parameter that lists the specified values, defaulted to 30,000
Building the core scatter plot
Add Sales to Columns and Profit to Rows and Sub-Category to Detail. Show the 3 parameters.
When pMeasure = Profit, we need to display horizontal reference lines against the Profit axis, and when Sales is selected we need to display vertical reference lines against the Sales axis. We need the following fields to return the user defined thresholds:
Ref – Profit Threshold
IF [pMeasure] = ‘Profit’ THEN [pProfitThreshold] END
Ref – Sales Threshold
IF [pMeasure] = ‘Sales’ THEN [pSalesThreshold] END
We also need to define the average per measure for each region:
IF [pMeasure] = ‘Profit’ THEN [Profit Avg Per Region] END
Ref – Sales Avg
IF [pMeasure] = ‘Sales’ THEN [Sales Avg Per Region] END
Add the 4 Ref – XXX fields to the Detail shelf. Then add 2 reference lines to the Sales axis; one referencing Ref – Sales Avg (coloured as a purple dashed line at 100% opacity) and one referencing Ref – Sales Threshold (coloured as a black dashed line at 50% opacity). Display a custom label and then format the label to be aligned vertically and coloured based on the relevant line, and with a 0% shading. Set the pMeasure to Sales to make these display.
Then repeat, adding 2 reference lines to the Profit axis instead. Change pMeasure to Profit for these to appear.
Change the mark type to square. Each mark needs to be coloured whether it is above or below the average for the measure selected.
Colour
IF [pMeasure] = ‘Profit’ THEN IIF(SUM([Profit]) >= SUM([Profit Avg Per Region]),1,0) ELSE IIF(SUM([Sales]) >= SUM([Sales Avg Per Region]),1,0) END
Set this to be discrete and then add to the Colour shelf and adjust accordingly.
Creating the colour-coded filter
The viz needs to be filtered by Region, but the filter displayed needs to show the region name along with an indicator based on whether the average for the region is above the threshold or not. This took a bit of an effort, but I finally managed it.
We need a field to capture the ‘KPI indicator’ based on which measure was selected. It also needs to be computed per region. We need a FIXED LoD for this, and I made use of coloured unicode characters from this site.: https://unicode-explorer.com/ (large yellow circle and large green circle which you can just ‘copy and paste’ into the calculation)
Region Indicator
{FIXED [Region]: (
IF [pMeasure] = ‘Profit’ THEN IIF(SUM([Profit Avg Per Region])>=[pProfitThreshold],’🟢’,’🟡’) ELSE IIF(SUM([Sales Avg Per Region])>=[pSalesThreshold],’🟢’,’🟡’) END )}
I then created
Filter – Region
[Region Indicator] + ‘ ‘ + [Region]
Add this to the Filter shelf, select all values and show the filter. If you change the pProfitMeasure to Profit and pProfitThreshold to 6,000, you’ll see the KPI indicators change.
Format the Tooltip
The tooltip requires several calculated fields to be created. Certain fields only need a value based on the pMeasure and others have conditional formatting (different colours) applied. For the tooltip, I created all these fields:
Add all these fields to the Tooltip. Update the Tooltip to reference the fields and the pMeasure parameter, colouring the text as required. Some of the fields will only display based on the filters selected, so they get places side by side with no spacing.
Finalise the viz by updating the title to reference the pMeasure and the Label – Region field. Remove all gridlines, axis rulers, zero lines etc. Colour the background of the sheet to pale blue.
Building the Overall Indicator
For the bonus challenge, we need to display an indicator that is green if all the regions are green and yellow if at least 1 region is yellow.
On a new sheet, add Region and Region Indicator to Rows and show the 3 parameters.
We’re going to create a field that will return 1 if the Region Indicator is yellow and 0 otherwise.
Region Indicator – Is Below
IIF( [Region Indicator] = ‘🟡’,1,0)
Set to be discrete and add to Rows. Change the pProfitThreshold to 6000 to see the flag change.
Create
Overall Region Indicator
IIF(WINDOW_MAX(MAX([Region Indicator – Is Below]))=1,’🟡’, ‘🟢’)
This says, if the maximum value of the rows ‘in the window’ is 1, then display a yellow indicator, else green. Add to Rows, and adjust the pProfitThreshold to see the behaviour.
Create a field
Filter – Index = 1
INDEX() = 1
Add to Filter shelf and set to True
Move Region to Detail. Remove Region Indicator and Region Indicator – Is Below. Adjust the table calculation setting of Overall Region Indicator to compute using Region only, and do the same for the tableau calculation on Filter – Index = 1 (re-edit the filter after changing, so only True is selected).
Note – originally I used a transparent shape mark type and displayed the indicator as a label, but after publishing to Tableau Public, the indicator became distorted, so I adjusted how this was built.
Set the mark type to circle and add Overall Region Indicator to Colour. Adjust to be green or yellow, depending on the colour of the KPI indicator (you’ll need to change the pProfitThreshold value to ensure you set the colour for both the yellow and green options).
Finally create
Tooltip – Overall Indicator
IIF([Overall Region Indicator]=’🟢’,’All regions meet the ‘ + [pMeasure] + ‘ target’, ‘At least one region does NOT meet the ‘ + [pMeasure] + ‘ target’)
Add this to Tooltip and then set the background colour to pale blue.
Building the dashboard & adding dynamic zone visibility
Using layout containers, add the viz to a dashboard. Use a horizontal layout container to arrange the parameters, the Region filter and the Overall Indicator sheet. I used a floating text object to display the ‘legend key’ again copying come unicode characters from the website referenced earlier.
Then create 2 new boolean calculated fields
Is Profit
[pMeasure] = ‘Profit’
Is Sales
[pMeasure] = ‘Sales’
Select the Sales Threshold parameter object, then update the visibility so it only displays if Is Sales is true (via the Layout > Control visibility using value option)
Repeat the same for the Profit Threshold object, but reference the Is Profit field instead.
Now as you switch the measure between Sales and Profit, the relevant threshold parameter will display. My published viz is here.
Yoshi set this week’s challenge which focused on 2 areas : the use of tools such as CHAT GPT to help us create Tableau RegEx functions (which are always tricksy), and the build of the chart itself.
Creating the REGEX calculated fields
I chose to use CHATGPT and simply entered a prompt as
“give me a regex expression to use in Tableau which extracts a string from another string in a set format. The string to interrogate is”
and then I pasted in the string I’d copied from one of the Session Html fields. I got the following response
along with many more code snippet examples. I used these to create
returns the day of the session as a proper date field, all hardcoded to 2023, since this is when the data is from.
Start Date
DATETIME(STR([Date]) + ” ” + [Start Time])
returns the actual day & start time as a proper datetime field.
End Date
DATETIME(STR([Date]) + ” ” + [End Time])
Duration
DATEDIFF(‘second’, [Start Date], [End Date])
Add fields to a table as below
Firstly, when we build the viz, we’ll need to sort it based on the Start Date, the Session Level (where Advanced is listed first, and All Levels last) and the Duration (where longer sessions take higher precedence) . For this we need a new field which is a combination of information related to all these values.
Sort
STR([Start Date]) + ‘ – ‘ + STR( CASE [Session Level] WHEN ‘advanced’ THEN 4 WHEN ‘intermediate’ THEN 3 WHEN ‘beginner’ THEN 2 ELSE 1 END ) + STR([Duration]/100000)
(this just took some trial and error)
Add this to the table after the Session ID pill and then apply a Sort to the Session ID pill so it is sorting by the Minimum value of the field SortAscending. You should see that when the start time and session level match, the sessions are sorted with the higher duration first.
The viz also lists the AM & PM sessions as separate instances, where the 1st session of the PM session is displayed on the same ‘row’ as the 1st session of the AM session. To handle this we need
Session Index
INDEX()
Make this discrete and add to the table after the AM|PM pill. Change the table calculation so it is computing by all fields except AM|PM and Session Date. The numbering should restart at each am/pm timeslot
Finally, the last field we need to build this viz, is another date field, so we can position the gantt chart bars in the right places. As the viz already segments the chart by Session Date and AM|PM, we can’t reuse the existing Start Date field as this will display the information as if staggered across the 3 days. Instead we want to create a date field, where the day is the same for al the sessions, but just the time differs
Baseline Date
DATETIME(STR(#2023-01-01#) + ” ” + [Start Time])
the date 01 Jan 2023 is arbitrary and could be set to any day. Add this into the table, so you can see what it’s doing. You will probably need to adjust the table calculation on Session Index again to include Baseline Date in the list of checked fields.
Building the viz
On a new sheet, add Session Date to Columns and manually re-order so Tuesday listed first. Then add AM|PM to Columns . Add Baseline Date as a continuous exact date (green pill) to Columns . Add Session Id to Detail then add Session Index to Rows and adjust the table calculation so it is computing by all fields except Session Date and AM|PM.
Create a new field
Size
SUM([Duration])/86400
and add this to the Size shelf. 86400 is the number of seconds in 24hrs, so adjusts the size of the gantt bars to fit the timeframe we’re displaying.
Add Session Level to Colour and adjust accordingly. You will need to readjust the Session Index table calc to include Session Level in the checked fields. Add a border to the bars. Edit the Baseline Date axis so the axis ranges are independent
Add Description and Session Title to Detail and again adjust the Session Index table calc to include these fields too. Apply a Sort to the Session Id field so the data is sorted by the Sort field descending
Add Location, Start Time and End Time to Tooltip, and update accordingly. Then apply formatting to
change font of the Session Date and AM|PM header values
remove the Session Date/ AM|PM column heading label (right click > hide field labels for columns)
Hide the Session Index field
Hide the Baseline Date axis
Add column banding so the Wednesday pane is coloured differently
Add the ability to highlight the Session Title and Description by selecting Show Highlighter from the context menu of each pill
Then add the sheet to a dashboard, using containers to display the colour legend and the highlighter fields.
Lorna set this week’s #WOW2025 challenge asking us to recreate what looked to be a simple line and area chart combination. I have to admit this was indeed a bit tricksy. I built out the data relatively quickly, but when trying to create the area chart I just wasn’t getting any joy.
After lots of trial and error, attempting various options, I eventually resorted to checking out Lorna’s solution. I found there were two specific areas that were causing the creation of the area chart to fail..why this happens is still a mystery….
Before we get on with the solution, I figured it was worth detailing at the start what I tried that failed, just in case you’ve been banging your head against a brick wall too 🙂
What didn’t work
The solution requires table calculations; for one calculation we need a percent of sales per quarter which means we also need to work out the total sales for the quarter. I originally used a FIXED LOD for this but found I could only get the area chart to work if I used a WINDOW_SUM() table calculation instead.
This challenge also requires all the customers for each quarter to be in the view to identify which are in the top X. I used a sorted INDEX() table calculation to identify the top X, but this too failed to work when it came to actually creating the area chart. Using the RANK() table calculation instead worked.
Like I said before why? I don’t know… but putting down as one of ‘just those things’ and hoping that maybe I’ll remember this post in future if it happens again!
Now back to the solution guide…
Creating the calculations
We’re dealing with table calculations, so I’m going to build out the required data in a tabular format to start with.
Create a parameter
pTop
Integer parameter ranging from 5 to 20 with the step size of 5 defaulted to 20
Show this parameter on the sheet, and then create a calculated field
Order Date (Quarters)
DATE(DATETRUNC(‘quarter’, [Order Date]))
Format to the YYYY QX style. Add Order Date (Quarters) to Rows as a discrete exact date (blue pill). Add Customer Name to Rows and add Sales to Text.
We need to identify customers who are in the top x for each quarter. Create a new calculated field
Is Top X Customer?
RANK(SUM([Sales]))<=[pTop]
Add to Rows and adjust the table calculation so it is computing by Customer Name only.
We now want the Sales just for those customers who are in the top x, so create
Top X Sales
IF [Is Top X Customer?] THEN SUM([Sales]) END
Add this field to Rows and we should see that Sales are only displayed for those customers that where Is Top X Customer? = True.
We now need the total of these sales per quarter so create
Total Top X Sales
WINDOW_SUM([Top X Sales])
Add this to the table and adjust the table calculation so both nested calculations are computing by just Customer Name. You should see that the total value is the same for every row for each quarter
We also need the value of the total sales in the quarter regardless of whether the customer is in the top X or not so create
Total Sales per Quarter
WINDOW_SUM(SUM([Sales]))
Add this to the table and again adjust the table calculation to compute by Customer Name only.
Now we have these two figures we can calculate the percentage. Create a new calculated field
Sales % per Quarter
[Total Top X Sales]/[Total Sales per Quarter]
Format this as a % to 1dp. Add to the table.
Duplicate this sheet. I like to do this as I make further changes so I don’t want to lose what I had.
Remove all the surplus field so that only Order Date (Quarters), Customer Name and the Sales % per Quarter fields remain
Create a new field
Customer Index
INDEX()
Convert this field to discrete (right click on the field).
Add this field to the Filter shelf and select 1. Adjust the table calculation so that it is computing using Customer Name only and sorted by SUM of Sales
Re-edit the filter and reselect 1 (making changes to the table calculation resets the filter values).
If everything has been set properly then you should end up with 1 row per quarter. Move the Customer Name pill from Rows to Detail.
We’ve now got the core fields we need to build the viz.
Building the Viz
Once again start by duplicating this sheet. Move Order Date (Quarters) to Columns and change to be continuous (green pill), then move Sales % per Quarter to Rows.
Add another instance of Order Date (Quarters) to the path shelf and change to be an attribute. You should now have a line chart. Increase the size of the line a little.
Add another instance of Sales % per Quarter to Rows, making sure the table calculation is set exactly as the existing version. Change the mark type of the 2nd marks card to Area.
Make the chart dual axes and synchronise axes .
Finally, tidy up the chart by
Adjusting the Tooltip
Removing gridlines, zero lines and row/column dividers
Hide the right hand axis
Fix the left hand axis to end at 1 (so the axis goes to 100%)
Edit the left hand axis title
Update the sheet title to reference the pTop parameter
Then add the viz to a dashboard. My published viz is here.
Sean set this table calculation based challenge this week. The key to this was for the reference line & bands to change if any of the non-date related filters were change, but for the values to remain constant regardless of the timeframe being reported. Based on this, I figured a table calculation filter would be necessary for the timeframe filter, as these get applied at the end of the ‘order of operations’.
I used the data set Sean provided in the challenge, to ensure I would get the same values to verify my solution. After connecting to the dataset, I did set the date properties on the data source so the week started on a Sunday, again to ensure the numbers aligned (in the UK, my preferences are set to Mondays).
Building out the calculations
Since we’re working with table calcs, I’m going to build the calcs into a table first, so we can verify the figures are behaving. Start by adding Order Date to Rows as a discrete (blue) pill at the Week level. Then add Sales.
Create a new field
Median
WINDOW_MEDIAN(SUM([Sales]))
and add this into the table. The value should the same for every row.
Create a new field
Std D
WINDOW_STDEV(SUM([Sales]))
and add this too – again this should be the same for every row.
But we want to to show the distribution based on +1 or -1 standard deviations, so create
Std Upper
WINDOW_AVG(SUM([Sales])) + [Std D]
and Std Lower
WINDOW_AVG(SUM([Sales])) – [Std D]
and add these to the table.
Each mark needs to be coloured based on whether it is greater than the upper band, so create
Sales above Std Upper
SUM([Sales]) > [Std Upper]
and add to the table on Rows
Add Category, Sub-Category and Segment to the Filter shelf, and show the filters. Adjust them to see that the table calculation fields change, although still remain the same across every row.
To restrict the timeline displayed, we need a parameter
pWeeks
integer parameter defaulted to 18
Show this parameter on the canvas. Then, to identify the weeks to keep, create a new field
Index
INDEX()
and convert it to discrete, then add to Rows to create a ‘row number’ for each row.
But, I want the index to start from 1 from the end of the table, so adjust the tableau calculation on the Index field so that is it computing explicitly by Week of Order Date and is sorted by Min Order Date descending
The first row in the table is now indexed with the greatest number, and if you scroll down, the last row should be indexed with 1.
With this we can now created
Filter – Date
[Index]<=[pWeeks]+1 AND [Index]<>1
I was expecting just to have [Index}<=pWeeks, but Sean’s solution excluded the data associated to the last week, I assume as it wasn’t a full week, so the above calculation resolved that.
Add this to the Filter shelf and set to True.
Changing the value in the pWeeks parameter only should adjust the number of rows displayed, but the values for the table calculated fields shouldn’t change.
Building the viz
On a new sheet, add Order Date as a continuous (green) pill at the week level to Columns and Sales to Rows.
Add another instance of Sales to Rows. Change the mark type of the Sales(2) marks card to circle. Make the chart dual axis and synchronise axis.
On the All marks card, add Median, Std Upper and Std Lower to the Detail shelf.
Add a reference line to the Sales axis, referencing the Median value. Set it to be a thicker darker line.
Then add another reference line but this time set it to be a band and reference the Std Lower and Std Upper fields, selecting a pale grey fill.
On the Sales(2) marks card, add Sales above Std Upper to the Colour shelf, and adjust to suit. Add a border to the circles. If required, adjust the colour and size of the line on the Sales marks card too.
Add the Category, Sub-Category and Segment fields to the Filter shelf, and show the filters. Show the pWeeks parameter. Then add Filter-Date to the Filter shelf. Adjust the table calculation as described above, then re-edit the filter to just show the True values
Finally tidy up by
remove row and column dividers
hide the right hand axis (uncheck show header)
edit the date axis and delete the title
Then add all the information to a dashboard and you’re good to go!
Erica set this week’s challenge and provided multiple levels specifically aimed a newer users of Tableau. My solution is for Level 3.
Setting up the calculations
First, create a parameter to capture the Sub-Category we care about
pSelectedSubCat
string parameter defaulted to Tables
Create a new field
Is Selected SubCat
[pSelectedSubCat]= [Sub-Category]
then create another field
Product to Display – Step 1
IIF([Is Selected SubCat], [Product Name], ”)
On a new sheet add Sub-Category and Product to Display – Step 1 to Rows. Show the pSelectedSubCat parameter. You will see that the Product rows only show for the Sub Category entered in the pSelectedSubCat parameter
We want to show the average of the product sales for each Sub-Category, so we can create
Add this to Text. By default it will aggregate this value to SUM, change it to AVG. For the rows associated to the selected Sub-Category the value of this field is the same whether its SUM or AVG, as it has been calculated at the level of detail being displayed on the row (Sub-Category and Product Name). For the other rows, by changing the aggregation to AVG we are getting the required value, which is essentially the sum of all sales associated to the Sub-Category divided by the number of distinct products. Sort the Sub-Category pill by the Average of the Sales by Sub Cat & Product field descending
Additionally, sort the Product to Display – Step 1 field the same way
We need to identify the top 5 records for the products associated to the selected Sub-Category. We will use a set for this. Right click on Product to Display – Step 1 > Create > Set
Product to Display Set
Select the Top tab and select the top 5 by formula
AVG(IF [Is Selected SubCat] THEN [Sales by Sub Cat & Product] END)
Add this to Rows and you should get In displayed against the product rows with the highest values
With this we can start to think about the ‘other’ text we need to display, but for this we need a handle on the number of products in each Sub-Category. Create
Count Products Per Sub-Category
{FIXED [Sub-Category]: COUNTD([Product Name])}
Add to Text so you can see the value, and then subsequently we can create
Product to Display – Step 2
IF NOT([Is Selected SubCat]) THEN ” ELSEIF [Product to Display Set] THEN [Product Name] ELSE ‘Other: ‘ + STR([Count Products Per Sub-Category] – 5) + ‘ Products’ END
Add to Rows to see the behaviour
The viz also needs to show an index value against the top 5 rows, so create
Index to Display
IF MIN([Is Selected SubCat]) AND MIN([Product to Display Set]) THEN STR(INDEX()) ELSE ” END
Add this to Rows as a blue discrete pill in front of the Product to Display – Step 2 field. Adjust the table calculation setting so the Sub-Category field is unchecked.
Next, we’re going to need to display a reference line that is the overall average product sales for the Sub-Category. This may sound like it’s what we already have, but that field is at the Sub-Category and Product Name level of detail, and we need to aggregate this back up to be at the Sub-Category level, so we create
Avg Sales by Sub Cat and Product
{FIXED [Sub-Category]: AVG([Sales by Sub Cat & Product])}
which is the average of the field we previous created but per Sub-Category. Pop this into the table to see what is happening. For the rows where the Products aren’t showing, the values match, but for the rows where the Products are displayed, you get the overall average, which is the same for all the rows.
If we now remove Product to Display – Step 1 from Rows (and amend the Index to Display table calc so it is not longer computing by this field too), we should have the data we expect. Format the Sales by Sub Cat & Product field to be $ with 0 dp.
Building the Viz
On a new sheet add Sub-Category, Product to Display Set, Index to Display and Product to Display – Step 2 to Rows and Sales by Sub Cat & Product to Columns and aggregate to AVG.
Sort the Sub-Category pill by the Average of the Sales by Sub Cat & Product field descending and apply the same sort to the Product to Display – Step 2 field. Edit the table calculation of the Index to Display field so it is not computing by Sub-Category.
Create a new field
Colour
IF [Is Selected SubCat] AND [Product to Display Set] THEN ‘Dark’ ELSEIF [Is Selected SubCat] THEN ‘Light’ ELSE ‘Grey’ END
Add this to Colour shelf and adjust accordingly.
Add Avg Sales by Sub Cat & Product to the Detail shelf, then add a reference line based on the Average of this field
Widen each row and from the Label shelf check Show mark labels. From the Tooltip shelf uncheck Show tooltips.
Hide the In/Out Product to Display Set field in Rows (uncheck show header). Format the font and style of the header columns, then hide the header field labels and hide the axis. Adjust the row banding and set all gridlines, zero lines, axis rulers and column dividers to none. Change the title of the sheet.
Test the behaviour by manually changing the value of the parameter.
Adding the interactivity
Add the sheet to a dashboard, then create a parameter dashboard action
Set SubCat
On select of the viz, update the pSelectedSubCat parameter passing in the value from the Sub-Category field.
Yusuke set this week’s challenge which is pretty tough-going. I got there eventually, but it wasn’t smooth sailing, and had a lot of false starts and changes to calculations throughout to get to the end. I will endeavour to explain where I had difficulties, but some of the calculations I came up with were more down to trial and error ( eg I wonder if this will work…?) rather than a known direction.
Modelling the data
We were provided with a version of Superstore and a State Abbreviations data set. I chose to use the Superstore Excel file I already had. I combined the two data sets in the data source pane using a relationship where State/Province in the Orders table of the Superstore excel file matched with the Full Name field in the State Abbreviations csv file.
As the data was just related to 2024, I added this as a data source filter (Year of Order Date = 2024)
Identifying the month
I decided I was going to use a parameter to select the month to filter. I first created
Order Date MY
DATE(DATETRUNC(‘month’,[Order Date]))
and then created a parameter
pOrderDate
date parameter defaulted to 01 Dec 2024, that I populated as a list using the values from the Order Date MY field. I set the display format to be a custom format of mmmm yyyy
Examining the data
On a new sheet, add State/Province and Abbreviation to Rows and Category to Columns to create a very simple ‘existence table’.
The presence of the Abc in the text table, shows that at least 1 record exists for the State/Category combination during 2024.
We can see some states, such as Arkansas, District of Colombia, Kansas etc don’t have any records at all for some Categories. However, when we look at the viz, we can see markers and labels associated to these Categories. In the image below, Kansas shows markers and labels against Furniture and Technology, when there are no records for these combinations at any point in 2024.
Handling this situation is the reason some of the calculations that follow are more complicated than you might expect.
Building the Trellis/Panel Chart calculations
Now when I built the viz, putting it into a trellis display was the last thing I did, but in rebuilding in order to write the blog, I’m finding that the table calcs needed start to help with the missing marks discussed above.
We need to count the number of States. For this create
State Count
SIZE()
Make this discrete and add to Rows. Adjust the table calculation to compute by State/Province and Abbreviation. 47 should be listed against every row in the column which is the number of rows displayed, and an Abc mark now exists for every State/Category combination.
We’ll also create
Index
INDEX()
make this discrete and add to Rows as well adjusting the table calculation to also compute by State/Province and Abbreviation
This has the effect of providing a counter for every row from 1-47.
We also need a parameter to define how many columns the display will be over
pCols
integer parameter defaulted to 8 which displays a range from 1 – 15 with a step of 1
With these fields, we can now define which row each record will sit on based on the counter for each State (the Index) and the number of columns to display (pCols).
Rows
INT(([Index]-1)/[pCols])
and we can also work out which column each record will sit in
Cols
([Index]-1)%[pCols]
Make both these fields discrete and add to Rows. Verify the table calc setting is as before. Show the pCols parameter, and test changing it and observe how the Rows and Cols values change
Remove State Count and Index from Rows. These won’t be needed in the final viz, as they’re just building blocks for other calculated fields.
Calculating the Profit Ratios
We need two Profit Ratio values – one for each State and Category per month and one ‘overall’ for each State by month
At the State/Category level, create
Monthly Sales
IF [Order Date MY] = [pOrderDate] THEN [Sales] ELSE 0 END
and
Monthly Profit
IF [Order Date MY] = [pOrderDate] THEN [Profit] ELSE 0 END
and from this create
Monthly PR
ZN(SUM([Monthly Profit])/SUM([Monthly Sales]))
custom format this to 0.0%;-0.0%;N/A which will then display the text N/A whenever the value is 0. Add this to the Text shelf.
Some additional State/Province records have appeared at this point with no Abbreviation (null). Filter these out.
To get the Overall profit ratio for the State, I created
State Sales for Month
{FIXED [State/Province]: SUM( IF [Order Date MY] = [pOrderDate] THEN [Sales] ELSE 0 END) }
and
State Profit for Month
{FIXED [State/Province]: SUM( IF [Order Date MY] = [pOrderDate] THEN [Profit] ELSE 0 END) }
and then created
PR for Month
SUM([State Profit for Month])/SUM([State Sales for Month])
and formatted this to a % with 1 dp.
Add PR for Month into the table and then apply a Sort to the State/Province field to sort by PR for Month descending
We’ve still got some gaps – we want to see the PR for Month value populated against every Category which have a profit ratio. For this create
State PR for Month
WINDOW_MAX([PR for Month])
Add this to the table and apply the table calculation to compute by Category only – we now have the overall profit ratio for each State plotted against every Category.
Note – we needed both PR for Month and State PR for Month as the latter is a table calculation and we can’t apply sorting on a table calc.
Building the panel chart
In building this I referred to my own blog post on a previous challenge, as the bar chart we need to build, isn’t a typical bar chart.
On a new sheet, add State/Province, Abbreviation and Category to Rows. Add Monthly PR to Text. Move the Abbreviation pill from Rows to Filters and exclude Null. Sort the State/Province field by PR for Month descending. Show the pOrderDate and pCols parameters.
Add Cols to Columns and Rows to Rows, so it’s the first pill listed. Move State/Province to Detail. Adjust the table calculations of both the Rows and Cols fields to be computing by State/Province and Category (listed in that order), and at the level of State/Province.
To build the bars, we’re using ‘fake axis’ on both the x & y axis to position the marks we want to display. We need
PR Bar Axis
ZN(LOOKUP(MIN(0.0),0))
and
Y-Axis
IFNULL(LOOKUP(MIN(0.5),0),0.5)
As alluded to above, these evolved as I played around to get the behaviour required.
Add Y-Axis to Rows and adjust the table calculation to compute by State/Province and Category (in that order) at the level of State/Province. Edit the axis to be fixed from -1 to 4.
Add PR Bar Axis to Columns. Again adjust the table calculation setting to be as the others. Change the mark type to Bar. Add another instance of Monthly PR to the Size shelf, and then adjust the Size to be Fixed and aligned Left.
Add Monthly PR to the Colour shelf, and adjust the colour legend to be fixed from -1.5 to 1.5 and centred at 0.
Edit the PR Bar Axis axis to be fixed from -1.25 to 1.25, to ensure the bars remain centred aligned regardless of the month selected.
Adjust the Tooltip as required.
Format the chart to remove all gridlines, remove the zero line for rows, and make the zero line for columns more prominent – dashed and darker. Remove all axis ticks. Add dark row and column dividers against the pane only and at the appropriate level so the information for a single State is contained within the boundaries.
Add row banding against the pane only at the appropriate band size and level
Reduce the width of each row, and widen each column to get a ‘squarer’ display.
Now we need to add the State/overall profit square. for this we need to ‘position’ a mark on the viz
Square Point
IF LAST()=0 THEN 0.9 ELSE NULL END
Add this to Columns. Edit the table calculation so it’s just computing by Category, then make the chart dual axis and synchronise axis.
This has created a second marks card, and essentially ‘duplicated’ the information for the ‘last’ Category only in each State.
Change the mark type to square. Remove Monthly PR from Colour and Size and Label . Add State PR for Month to Label and Colour instead. Adjust the table calculations to be computing by Category only. Align the label to be middle centre. Increase the Size of the mark so it’s roughly square.
Adjust the Colour legend of the State PR for Month so it ranges from -1.5 to 1.5 like the other one.
Adding Abbreviation to Label doesn’t work for every cell, so we need
Label – Abbreviation
WINDOW_MAX(MAX([Abbreviation]))
Add to Label and adjust the table calculation to be computing by Category only. Adjust the layout of the Label to match.
Update the Tooltip to reference State PR for Month instead. Hide the null indicator.
Finally hide the axis and the Cols and Rows pills (right click, uncheck show header). Hide the Category column title. Name the sheet.
And that should be the main viz. Add to a dashboard and only show one of the colour legends, renamed accordingly.
For this week’s challenge I expanded on my challenge from week 13. So for this solution guide, I’ll be starting with the workbook I built for week 13 and adjusting as required. You can either build on your own solution if you took part, start with my workbook, or rebuild from scratch (my solution guide to week 13 is here).
Based on the hint I provided in the challenge, I’m going to build this with 3 sheets; one sheet to display the bar charts per year for the specific Sub-Category, then one sheet to display the segmented bars up to the selected Sub-Category and one sheet to display the segmented bars below the selected Sub-Category.
For this we need to assign an index to each row of data.
Sub-Cat Index
INDEX()
Make this discrete and add it to Rows in front of Sub-Category and adjust the table calculation so that it computes using Sub-Category only. Each row should be numbered from 1 – 17.
Creating the top & bottom bar chart
We will need to be able to identify the name of the Sub-Category selected and the associated index value. We’ll use parameters to store this information
pSelectedSubCat
string parameter defaulted to ” <empty string>
and
pIndex
integer parameter defaulted to a very large number, in this case I chose 10,000 (basically a number higher than the number of dimensions listed.
Show the parameters, and manually type into pSelectedSubCat the word ‘Copiers’ and into the pIndex type 8 (the index associated with Copiers).
We want to display an arrow indicator based on whether a Sub-Category is selected or not. We need
Arrow Indicator
IF [pSelectedSubCat] = [Sub-Category] THEN ‘▼’ ELSE ‘►’ END
Add this onto the Rows after the Sub-Category pill. Readjust the table calculation of the Sub-Cat Index so it is computing by Arrow Indicator as well.
We only want to show up to the selected Sub-Category
Show Top
[Sub-Cat Index]<=[pIndex]
Add this to the Filter shelf and set to True. Verify the table calculation is also computing by Sub-Category and Arrow Indicator only. Hide the Sub-Cat Index column and format the arrows so they are coloured brown and aligned top centre. Name this sheet Top or similar
Duplicate the sheet.
To show the bottom half of the chart create
Show Bottom
[Sub-Cat Index]>[pIndex]
Remove Show Top from Filter and add Show Bottom instead. Set to True. Again verify the table calculation is computing by Sub-Category and Arrow Indicator only. Call this sheet Bottom or similar.
Building the year bar chart
On a new sheet add Order Date to Rows and Sales to Columns. Change mark type to bar. Adjust Colour and size
Create a new field
Is Selected Sub-Cat
[Sub-Category] = [pSelectedSubCat]
Add to Filter and set to True.
Add another copy of Sales to Columns, then double click into the pill, and manually wrap the text with WINDOW_MAX(….. )
On the Window_Max marsk card, reduce the size to as small as possible and add a white border (via the Colour shelf). Add Sales to the Label shelf and adjust the font to be smaller and coloured brown.
Make the chart dual axis and synchronise the axis. Remove measure Names from the All marks card, and right click on the top axis and move marks to back. Hide both axes, remove row/column dividers and gridlines/zero lines. Adjust the font colour and size of the row labels, and remove the row header.
Add Sales to the Tooltip shelf of the All marks card, and add a quick table calculation of percent of total. Format to 1 dp. Add Sub-Category to the Tooltip shelf too, and then adjust the Tooltip of the All marks card to suit. Name the sheet Years or similar.
Building the dashboard
On the existing dashboard, add a vertical container, and then add the Top, Years and Bottom charts within it.
Remove the chart titles.
You’ll notice that the bar for bookcases in the bottom chart is longer than the bar for copiers in the top chart. We don’t want this. To correct we need
Max Sales Axis
{MAX({FIXED [Sub-Category]:SUM([Sales])})}
Navigate to the Top sheet and add Max Sales Axis to the Detail shelf of the All marks card
Create a new parameter
pMaxAxis
float parameter defaulted to 500,000
On the Bottom sheet, show the Sales axis, then edit the axis and adjust so the axis range is Custom from 0 to pMaxAxis
Hide the Sales axis again
Adding the Interactivity
We will use parameter actions to set the various parameters on click, but we need to ensure the viz will ‘expand’ but also fully ‘collapse’. So we need
Sub-Cat to Pass
IF [Is Selected Sub-Cat] THEN ” ELSE [Sub-Category] END
Index to Pass
IF MIN([Is Selected Sub-Cat]) THEN 10000 ELSE [Sub-Cat Index] END
ie if we’re selecting the Sub-Category that’s already been selected then we need to ‘reset’ back to the start state.
Add both of these fields to the Detail shelf of the All marks card on both the Top and Bottom sheets.
Change Sub-Cat to Pass to be an Attribute so that it doesn’t impact the existing table calc settings, and verify Index to Pass is computing by Sub-Category and Arrow Indicator.
Back to the dashboard, and add the following dashboard action
Select SubCategory
On select of the Top or Bottom sheets, set the pSelectedSubCat parameter, passing in the value from the Sub-Cat To Pass field. When clearing the selection, reset to ” <empty string>
Set Index
On select of the Top or Bottom sheets, set the pIndex parameter, passing in the value from the Index To Pass field aggregated at the minimum level. When clearing the selection, reset to 10,000
Set Axis
On select of the Top sheet, set the pMaxAxis parameter passing in the value from the Max Sales for Axis field. When cleared reset to 50,000
You should now have a functioning chart. You may need to adjust padding of the objects and modify width of headers on the charts to get the alignment as required. Update the chart title as required as well.
Erica set the challenge this week, and I’m not gonna lie, I found this tough. On the face of it, it looks like something I felt I should be ok at, but nuances cropped up as I was building that meant I often had to change tact and try something different.
My intention was to build each version – Beginner, Intermediate then Advanced, adding to my solution each time, but decisions I made early on, then caused me grief later. For example, I did choose to utilise a lot of table calcs, but that meant when it came to applying the sorting mechanism that I wanted, I couldn’t reference the sort field I needed, as it contained a table calc. So I had to unpick the logic and build with LODs instead which took me a while to get right. Getting single lines to display in each ‘state’ cell also proved tricky at times, and that was even before I’d got to the requirement to pad out the ‘missing values’ with 0s. I also seemed to find that some things only seemed to work if I added pills and applied settings in a particular order. All in all, quite a challenge, and while I did get there in the end, I did have to peek at the solution at times to figure out if I was going nuts, but I found trying to scale back Erica’s solution to the beginner/intermediate version also suffered the same issues I was experiencing. I built with Desktop v2024.1 and there were times I was wondering if something had “broken” in that version, although having finally reached the end, I’ve yet to test that theory.
So, I’m blogging this guide with several caveats – Going from beginner to intermediate is ‘okay’, but when it gets to advanced I had to start again(ish). Some of the calcs I provide will just be ‘as is’. I will do my best to explain what’s going on, but there are times that I just don’t get it, and it’s just been trial and error that got me the results I needed – sorry!
So with all that in mind, let’s get building.
Initial steps
After connecting to the provided hyper file, I found I did have to create the modified sales value
Sales Modified
IF SUM([Sales])<5000 THEN SUM([Sales])*10 ELSE SUM([Sales]) END
I also decided to add a data source filter (right click data source > edit data source filters) to restrict the data to just Country/Region = USA.
The later versions of superstore have Canadian Provinces included too, and I don’t think these were listed in Erica’s solution. It just felt easier all round to exclude these records from source.
Beginner challenge
When building a trellis chart, we need to determine which Row and which Column our specific dimensions (in this case State/Province) will sit in. As the requirements already stated it was to be a 3 column grid, our calculations for this didn’t need to be so complex.
Cols
(INDEX()-1)%3
INDEX() returns an incremental number starting at 1 for whatever dimension or set of dimensions we’re counting over. In this case we’re counting the number of State/Provinces. %3 returns the remainder when the index is divided by 3, so we get values of 0, 1 and 2.
Change this field to be discrete (right click -> convert to discrete)
Add State/Province to Rows then add Cols to Rows. Edit the table calculation so the field is computing by State/Province. You can see that the first 3 rows will be positioned in columns 0, 1,2 respectively and so on.
Create a new field
Rows
INT((INDEX()-1) / 3)
This takes the index value (minus 1), divides by 3 and ’rounds’ to a whole number. Make this discrete too and add to Rows, setting the table calculation as described above. Now we can see the first 3 rows will all actually be in the same row (row 0), then next 3 rows in row 1 and so on.
Shift the pills around so Cols is on Columns, Rows is on Rows and State/Province is on Detail. Add Sales Modified to Rows.
Create a new field
Quarter Date
DATE(DATETRUNC(‘quarter’, [Order Date]))
and add this to Columns setting it as a continuous exact date (green pill). We’ve got a bit of unexpected ‘spaghetti’ going on…
To fix it, do the following ..
Add Quarter Date to Detail as a discrete exact date (blue pill). Change Quarter Date on Columns to be a continuous attribute (green pill – first change to attribute, then change to continuous). Edit the table calculation settings for both the Rows and the Cols fields to be computing by both State/Province and Quarter Date at the level of State/Province.
I had to reference previous challenges and blog posts I’d written to manage this… maybe there is something simpler, as this is pretty taxing for the ‘beginner’ part of the challenge.
Add another instance of ATTR(Quarter Date) to Columns and make dual axis and synchronise the axis. This will create an axis at the top of the chart as well as the bottom.
Format the ATTR(Quarter Date) pill so the axis format is custom formatted to “Q”q “‘”yy
Edit all the axis (top/bottom and left) and update the title. Adjust the Tooltip. Hide the Cols and Rows fields (right click and uncheck show header). Change the Colour of the line to grey.
This should be the Beginner solution. I have published this here.
Intermediate challenge
For this part of the challenge, we need to set up lots of new calculations, so let’s do this first. As usual, I’ll manage this in a tabular format. So on a new sheet, add State/Province and Quarter Date as a discrete exact date (blue pill) to Rows. Add Sales Modified to Text.
We need to get the threshold value for each State/Province, which is the average of the numbers all listed above, multiplied by 2. We’ll use LODs for this
working inside out… get the value of the Sales Modified value for each State/Province and Quarter Date (which is the same as the values you see listed above) and then average this at the State/Province level and multiple the final result by 2. Add this to the table. This is the field we’ll be using for the horizontal reference line.
Next we need to identify the rows where the Sales Modified value exceeds the threshold, and then return the Sales Modified values for only these rows. I’ll do this in 2 stages
Is Above Threshold?
INT([Sales Modified] > SUM([Threshold]))
This returns a 1 or 0 depending on whether the statement is True or False. Using actual numeric values rather than boolean helps later on. Set this field to be discrete and add to the table.
Above Threshold Sales
IF [Is Above Threshold?]=1 THEN [Sales Modified] END
Add this to the table too. For the rows where we have 1’s, a value is displayed. This is the field we’ll be using for the red circles.
Finally we need to determine some fields to help us define a reference band. These need to be dates as they’ll be applied to the date axis, but the band doesn’t stretch to the previous/next quarter, and is only present if the last value is over the threshold.
Again using LODs let’s get the final date in the quarter
Max Quarter Per State
{FIXED [State/Province]: MAX([Quarter Date])}
Add this to the table as a discrete exact date (blue pill).
Now we need to know if the value associated with the final quarter is above the threshold or not
Final Quarter Above Threshold
INT(MIN([Quarter Date]) = MIN([Max Quarter per State]) AND [Is Above Threshold?]=1)
Again this will return a 1 or 0. Change to discrete and pop that into the table too. We can see Colorado is the first state listed where this is true.
Now we want to ‘spread’ that value across every row associated to the state
For each State/Province and Quarter Date, get the Final Quarter Above Threshold value and then get the maximum value of this for each State/Province. This is where having the values as 1’s and 0s helps.
Make discrete and add this to the table. Every row for Colorado has this set to 1
Now we can work out some dates
Ref Band Min
DATE(IF [State Has Final Quarter Above Threshold] = 1 THEN DATEADD(‘month’, -1, [Max Quarter per State]) END)
If the State/Province is over the threshold for it’s final quarter, then get a date and set it to be 1 month less than the final quarter for that state.
Similarly
Ref Band Max
DATE(IF [State Has Final Quarter Above Threshold] = 1 THEN DATEADD(‘month’, 1, [Max Quarter per State]) END)
If the State/Province is over the threshold for it’s final quarter, then get a date and set it to be 1 month more than the final quarter for that state.
Add both of these of the table as discrete exact dates (blue pills).
Now we have all the building blocks to build the next bit of the challenge.
Start by duplicating the Beginner viz.
Add Above Threshold Sales to Rows and make Dual axis and synchronise the axis.. Remove the 2nd instance of the Quarter Date from Columns, so we now have marks cards relating to the 2 measures rather than the 2 dates. Remove Measure Names from the All marks card.
Change the mark type on the Above Threshold Sales marks card to circle and change the colour to red. Adjust size to suit.
Add Threshold to the Detail shelf of the All marks card. Right click on the Sales axis and add reference line. Set it to be per pane and use the Threshold field. Display the value as the Label. Don’t show a Tooltip. Display a dotted black line.
Update the Tooltip to now have a reference to the threshold value too.
Add Ref Band Max and Ref Band Min to the Detail shelf of the All marks card. Set them both to be continuous attributes (green pills).
Right click on the Quarter Date axis and add reference line. Set it to be a bandper pane that goes from the Ref Band Min to the Ref Band Max. Don’t display labels or tooltips or a line. Fill with a pale shade ot red/pink.
Add back in the additional Quarter Date dual axis as described in the Beginner section to get the dates listed the top again.
Hide the right hand axis (uncheck show header) and hide the NULL indicator.
This completes the Intermediate challenge. My version is published here.
Advanced challenge
Go back to the tabular sheet we were using to check the calculations needed for the Intermediate challenge, as we’ll build on this.
Firstly the sorting. We need to sort the states based on whether the final quarter was above the threshold or not, and then by the number of times the state was above the threshold. Let’s get the count to start with
For each State/Province and Quarter Date, get the Is Above Threshold? value and then sum these up for each State/Province. This is where again having the values as 1’s and 0s helps.
Make this discrete and add to the table
then create a field we’ll use for the sort. This is going to be a numeric field
Sort
IF [State Has Final Quarter Above Threshold] = 1 THEN 100000 + [Above Threshold Count Per State] ELSE [Above Threshold Count Per State] END
We’re just using a very large arbitrary number to force those states where the final quarter is over the threshold to be higher in the list. Make this discrete and add to the table too.
We can now apply a Sort to the State/Province field to sort by the Sort field descending
This results in Colorado moving to the top of the list followed by Minnesota, which also has 3 quarters above the threshold, including the last quarter, but alphabetically falls after Colorado so is listed 2nd.
To filter the data, I created another field, just for ease
Filter
IIF([State Has Final Quarter Above Threshold]=1,’Urgent’,’Non-Urgent’)
Add this to the Filter shelf and set to Urgent. The states should now be restricted to just those where the final quarter is above threshold.
For the final requirement of this challenge, we’ll build out another table on another sheet to demonstrate, as we need to work with a different instance of the quarter date.
On a new sheet, add State/Province to Rows and add Order Date at the Quarter (month year) level as a discrete field (blue pill) to Rows. Add Sales Modified to Text
For Alabama, we don’t have a 2020 Q3 or a 2023 Q3. Click on the Quarter(Order Date) pill and select Show Missing Values. These quarters appear but with no Sales Modified value.
We’ve had to use this different way to define the date quarter as if we tried to ‘show missing values’ against the Quarter Date field that is set to ‘exact date’, we send up getting every day that is missing, not just the dates relating to the quarter. Also, the ATTR(Quarter Date) field we’ve used on the previous vizzes, doesn’t allow the Show Missing Dates option.
Anyway, we need to get 0s in to these dates.
Show Modified with 0
ZN(LOOKUP([Sales Modified],0))
Apart from the Rows/Cols calcs needed for the trellis, this is the only table calculation I ended up using. It’s basically looking up it’s own row (LOOKUP([field],0)) and if it can’t find a value (as it’s missing) it’s returning 0 (the ZN() function). Add that to the table.
Ok so now we have the components needed, let’s build the viz. We can use the Intermediate version as a starting point, but will need to reapply some of the features.
Duplicate the Intermediate viz.
Remove the 2nd instance of the ATTR(Quarter Date) pill on Columns. Drag the Sales Modified with 0 pill and drop it directly over the Sales Modified pill so it replaces it. Hopefully the chart should still look the same.
Right click the Order Date field from the left hand data pane, and drag it directly onto the ATTR(Quarter Date) pill. Release the mouse and select the continuous quarter/year option from the dialog that displays.
Things will start to look a bit odd… you’ve lost your lines… Remove the Quarter Date pill from the Detail shelf on the All marks card. Fix the Rows and Cols table calc fields by just updating them to compute by State/Province only. Adjust the table calc of the Sales Modified With 0 field to compute by Quarter of Order Date and State/Province in that order.
Things still look crazy…. but just one more step… Show Missing Values on the Quarter(Order Date) pill.
Now every cell should be associated to a single State/Province with no broken lines, and Wyoming, right at the bottom, should show more than a single dot. This was A LOT of trial and error to fathom all this out.
Add back in the reference band following the instructions above (you should find the reference line for the threshold value will just then appear) and re-update the Tooltip.
Add another instance of Order Date at the quarter/year continuous level (green pill) to Columns and make dual axis and synchronise the axis. Edit the axis titles, and format them to the “Q”q “‘”yy custom format.
Apply the sort to the State/Province field on the Detail shelf so it is sorting by Sort Descending. Add Filter to the Filter shelf and select Urgent.
Add this to a dashboard, and that should be the completed Advanced challenge which I’ve published here.
There really was some black magic going on here at times. Tough one this week!
In 2024 the #WOW coaching crew has extended to include Yusuke Nakanishi (@YusukeNakanish3) and Yoshitaka Arakawa (@yoshi_dataviz) from the Japanese #datafam. This week Yusuke set this challenge asking us to build donut charts that represented when percentages were > 100%.
Building out the calculations
In the data set provided the Self-sufficiency ratio for food in calorie base [%] is a number that represents the actual % value ie 90 means 90%, 196 means 196%. To help me remember that, I formatted the field to be a number with 0 decimal places which had a % suffix.
But building the donut charts we need to ‘normalise’ the figures to represent a percentage out of 100. ie, if the value is 90%, we want 90%, but if the value is 196%, we want 96%. So I created
Self Sufficient %
([Self-sufficiency ratio for food in calorie base 【%】] %100) / 100
and then formatted this to a % with 0 dp.
but I can’t build a donut chart with just this value, I need to know the non self sufficient % too
Not Self Sufficient %
1-[Self Sufficient %]
and formatted this to be a % with 0 dp too.
Let’s put these into a tabular view. Add Prefecture to Rows and Measure Names to Columns. Add Measure Values to Text. Add Measure Names to Filter and restrict to the fields Self-sufficiency ratio for food in calorie base [%], Self Sufficient % and Not Self Sufficient %. Add Fiscal Year to Filters and restrict to FY2018. Sort the data by Self-sufficiency ratio for food in calorie base [%] descending.
You can see those rows where the Self-sufficiency ratio for food in calorie base [%], Self Sufficient % and Not Self Sufficient % is over 100% have a different value for the Self Sufficient %.
Showing the Fiscal Year as a filter the user can select, I can change to FY2019 and also see how these fields are behaving; ie when Self-sufficiency ratio for food in calorie base [%] is also over 200%, I’m getting the ‘remainder’ over 200 displayed ie 216% has a Self Sufficient % of 16%, which is what is needed for the ‘bonus’ challenge.
However, due to the way I’m building, I’m going to able to get the display working for the top & bottom 7 records regardless of year (making the build of the bonus challenge a bit easier).
So with this in mind, I now what to categorise the Self-sufficiency ratio for food in calorie base [%] based on what percentage range the values fall into.
% Bracket
FLOOR([Self-sufficiency ratio for food in calorie base 【%】] / 100)
This gives me values of 0, 1 and 2. By default this field will be created within the ‘measures’ section of the data pane (ie below the line). Drag it into the top section to convert it to a discrete dimension. Alias the values (right click -> Aliases) and set as below:
Then add to Rows.
and with the filter still set for FY2019, we can see the rows are categorised into 3 brackets.
Set the filter back to 2018. Now we want to restrict to the top and bottom 7 records only. For this I use Sets.
Create a set against Prefecture (right click the field > create > set).
Top 7
Top 7 records by Sum of Self-sufficiency ratio for food in calorie base [%]
Then create another set, this time for the bottom 7
Bottom 7
Bottom 7 records by Sum of Self-sufficiency ratio for food in calorie base [%]
Then create a Combined set (right click on one of the sets > create Combined set), that includes all values from both sets
Records to Include
Add this to the Filter shelf. By default it will show the records ‘in’ the set. However based on how the order of operations in Tableau works, this will apply the conditions based on the set first before it considers the year being filtered. Ie it will get the top and bottom 7 records based on the total sum of the Self-sufficiency ratio for food in calorie base [%] field for all the data in the data set, then filter to 2018. It means it could show some records that were in the top 7 overall, but not in the top 7 for 2018. To resolve this, and to ensure the data gets filtered by the Fiscal Year first, we need to add the Fiscal Year pill on the Filter shelf to context (right click pill and Add to Context). The pill will go grey.
Next we need a way to ‘categorise’ which rows are the top and which are the bottom. Add Top 7 to Rows which will split the rows into In or Out. Alias these values so In displays as Top 7 and Out displays as Bottom 7 (right click the text > Edit Alias).
Finally, when we build the viz, we need to ensure the 7 entries for each section align with each other. For this create
Top | Bottom Index
INDEX()
and convert the field to Discrete, then add to Rows before the Prefecture pill. Edit the table calculation so that it is computing based on the Prefecture and % Bracket pills only. This gives us an index from 1-7 for each set.
Building the Top & Bottom 7 Donut chart
On a new sheet, add Fiscal Year to Filter and set to 2018. Add the pill to context. Show the filter. Also add Records to Include to Filter.
Add Top 7 to Rows, then double click into rows and manually type MIN(0) to create a ‘fake axis’. Change the mark type to Pie.
Add Prefecture to Detail and Measure Values to Angle. Ensure only Self Sufficient% and Not Self Sufficient % are the only measures displayed (remove any others by dragging them out of the Measure Values box). Add Measure Names to Colour.
Add Top | Bottom Index to Columns and edit the table calculation so it is just computing by Prefecture. This should now give 7 columns of pie charts.
Add a Sort to the Prefecture pill on the Detail shelf, so it is sorting by the Self-sufficiency ratio for food in calorie base [%] field descending.
Add % Bracket to the Detail shelf, then click on the 3 dot icon to the left, and select the colour icon to add this pill to the Colour shelf as well as the Measure Names pill.
Re-edit the table calculation associated to the Top | Bottom Index pill so it is now also computing by the % Bracket field, so you get back to your correct top & bottom 7.
Adjust the order of the pills on the Colour shelf so that the % Bracket pill is listed before the Measure Names pill. Also adjust the order of the pills in the Measure Values box so Self Sufficient % is listed before Not Self Sufficient %.
Then adjust the colours on the colour legend as below and add a dark border to the marks (via the colour shelf).
Change the year filter to FY2019, and you can adjust the colours for the 200% entries too.
To make the ‘hole’ in the donut, double click into the Rows and add another instance of MIN(0). This will create a 2nd MIN(0) marks card.
On that marks card, move Prefecture from Detail to Label. Remove % Bracket, Measure Names and Measure Values. Add Self-sufficiency ratio for food in calorie base [%] to Label. Change the mark type to circle. Set the Colour of the circle to white. Align the label middle centre, and adjust the format/font to suit.
Set the chart to be dual axis and synchronise the axis. Adjust the Size of the pie chart mark independently from the size of the circle mark, so one is slightly larger than the other.
Finally tidy up the display by hiding the axis (uncheck show header), and the Top | Bottom Index field. Remove all gridlines, zero lines, axis lines/ticks and row/column dividers. Hide the In/Out Top 7 field label heading (right click label and hide field labels for rows. Update the title of the sheet so it references the Fiscal Year field. Change the filter back to 2018. Hide all tooltips.
Building the Legend
On a new sheet double click into Columns and type MIN(0.0). Add % Bracket to Rows. Change the mark type to square and add % Bracket to Colour and Label. Adjust colours to suit and add dark border to shape.
From the donut chart sheet, click on the Fiscal Year pill in the Filter shelf and set to apply to worksheets > selected worksheets and select the sheet you’re building the legend on. This will add the pill to this sheet too and changing the value on one sheet will impact the other.
Edit the axis so it is fixed to start at -0.1 and end at 1. This will shift the display to the left.
Hide the axis, and the % Bracket header column; remove all gridlines, zero lines, row/column dividers. Hide the tooltips.
Both the legend and the donut can now be added to a dashboard, and you’ve completed the main challenge. I chose to add the Fiscal Year filter to the display so the user could switch years if they wished.
The bonus challenge – Building the trellis chart
Because of how I’ve built the above, we’ve already done the hard work to handle the 200%+ data. The challenge here now is just to display all the 47 Prefectures for a given year in a 10 x 5 grid – a trellis.
So to build this, I started with the donut chart already built and duplicated the sheet.
When creating trellis charts, we need to create fields that represent which row and which column each Prefecture should sit in. There’s lots of blogs on creating trellis charts. As we know the number of rows and columns we need, I created
Cols
INT((INDEX()-1)%10)
Take the index of each Prefecture, decrement by 1 and find the remainder when divided by 10. This means the Prefecture with the highest % value at at position (rank) 1 will be positioned in column (1-0)%10 = 0. The Prefecture at position 11 will also be positioned in column (11-1)%10 = 0.
We also need
Rows
INT((INDEX()-1)/10)
Take the index of each Prefecture, decrement by 1 and divide by 10.
Convert both fields to be Discrete.
From the duplicated sheet, remove Top | Bottom Index from Columns and In/Out Top 7 from Rows. Remove Records to Include from Filter. Don’t panic if things look odd!
Add Cols to Columns. Adjust the table calculation to compute by both %Bracket and Prefecture. Specify the sort order to be a custom sort on the field Self-sufficiency ratio for food in calorie base [%] descending.
Now add Rows to Rows and apply the same settings on the table calculation. If all is well, you should have all 47 donuts displaying in the correct order.
Hide the Cols and Rows headings, and you can then add this to another dashboard.
Donna + DataViz 