Lorna provided this week’s challenge, drawing on some tips she’d picked up from Nhung Lee at #TC25. We’re building 2 charts, but for the second we need some supplementary data, and for this we need to start with some data modelling.
Modelling the data
I downloaded the ChatGPT Android reviews data from Kaggle into a csv file called chatgpt_reviews.csv. I also then copied the additional data Lorna provided in the challenge into another csv called WOW2025_19_Additional Data.csv.
I chose to model the data using a physical model rather than relationships, as I needed to ensure all the review data would always exist in the view. To do this I started by connecting to the chatgpt_reviews.csv. From the context menu of the sheet added to the datasource canvas, I selected Open to ‘open’ the physical model ‘window’
I then added a new connection to the WOW2025_19_Additional Data.csv file And added a Left Join to it, joining from the chatgpt_reviews.csv using a join calculation of
DATE(DATETRUNC(‘day’,[At]))
and joining this to the Release Date field from the WOW2025_19_Additioanal Data.csv file
Now with this set up, we can start to build.
Creating the bar chart
Add Score as a discrete dimension (blue disaggregated pill) to Rows and chatgtp_reviews.csv (Count) to Columns.
Add Score as a discrete continuous (green disaggregated pill) to Colour and adjust to use a grey colour palette. Make each row a bit wider, and show labels
Hide the Score column (uncheck show header), increase the size of the font for the Star Rating field, hide the Count of chatgpt_reviews axis (uncheck show header), hide all gridlines/axis rulers/ zero lines and row and column dividers. Adjust the font style of the label, hide the ‘Star Rating’ column label (right click > hide field labels for rows). AAjust the Tooltip as desired and update the sheet title. Name the sheet star Rating Bar or similar.
Building the line chart
On a new sheet, add At as a continuous pill at the ‘1st May 2025‘ day level (green pill) to Columns and add chatgpt_reviews.csv (Count) to Rows. Change the Colour of the line to dark grey.
For the ‘reference lines’ we’re actually adding bars all at the same height. The height we’re going to use is the maximum number of reviews, so create
Max Review Count
IF NOT ISNULL(MIN([Release Date (WOW2025 19 Additional Data.csv1)])) THEN WINDOW_MAX(COUNT([chatgpt_reviews.csv])) END
Add this to Rows and hide the null indicator (right click > hide indicator). Change the Mark type to bar.
Add Product to the Label of the Max Review Date marks card, and align as below
Adjust the table calculation of the Max Review Count field so it is computing by both the At and Product fields.
Make the chart dual axis and synchronise the axis. Adjust the Label to explicitly add a couple of carriage returns after the Product text. This has the effect of shifting the text to the left of the bars.
Fix the Count of chatgpt_reviews axis from -100 to 10,800
Ensure mark labels are set to allow labels to overlapother marks.
Tidy up by
hiding the right hand axis (uncheck show header)
Updating the title of both axis (right click > edit axis)
remove all gridlines, zero lines, row & columns dividers
show axis rulers
update tooltips as required
update the sheet title
name the sheets Trend or similar.
Then add both charts to a dashboard. My published viz is here.
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.
This week it’s the Tableau Conference edition – and as I was at #data23, I’m a bit delayed in getting the challenge and solution posted – too much going on and too much sleep to catch up on over the weekend 🙂
It was great to catch up with the #WOW crew in person and to meet so many participants at the social nights out and at the hands on live session too. And thank you for all those who complimented me on this blog – it really does make the time and effort I put into it worthwhile, when I get to hear how much it has helped you all!
Here’s a few pics from the Tableau Conference, and a massive shout out to Chris McClellan, who made me a custom WOW t-shirt!
Ok, enough of the pre-amble, onto the challenge and solution. Lorna set this challenge to recreate what she referred to as a ‘jitterfly’ chart.
Modelling the data
The data needs to be downloaded from data world. This contains multiple files and it was expected to connect to the central_trend_2017_base.xslx file. This file contains multiple sheets. The Population – Females and Population – Males sheets need to be unioned together in the data source canvas.
Drag Population – Females onto the canvas, then drag Population- Males on too, and drop it when the Union option appears beneath the Population – Females
You’ll know you’ve done it right, if you only have a single object in the canvas and the union symbol is displayed
The data displays all years in separate columns – we need to transpose this, so we have a column for Year and a column for the population value.
Click on the first year column (2011), then scroll across to the last year column (2050). Hold down shift and click the last column, and all the columns in between should be highlighted/selected. Right-click on any of the selected columns and select Pivot.
Rename the Pivot Field Names column to Year and rename Pivot Field Values to Population.
To tidy things up a bit, hide the following fields (right click on column and hide) : Sheet, Table Name, Gss Code, Component. Also, change the data type of the Year field to be a number rather than a string. This should leave you with 5 columns
Navigate to Sheet 1 and in the left hand data pane, drag Age from the lower ‘measures’ section to above the line and into the dimensions section. Age isn’t going to be something we aggregate (sum/avg) etc – it is simply a categorical property of the record. This step isn’t critical, but I just like things to be neat :-).
Finally, though it isn’t stated in the requirements, the data relating to District = London needs to be excluded, as it is a summarised total of the other rows. To handle this I added this exclusion as a Data Source Filter, so once applied, I didn’t have to worry about it when I built the chart. Right click on the data source listed on the top left, and select Edit Data Source Filters, then add a condition to exclude the London District.
Creating the calculations
The chart requires a user to select two years to compare – I’ll refer to these as the Primary and Secondary year. We’ll create parameters to enable the selections.
pPrimaryYear
Integer parameter, defaulted to 2033 and displayed in ‘2033’ format (ie no commas). It should allow all other years to be selected (easiest way to get this populated is to right-click on the Year field in the data pane, and Create > Parameter). If you don’t do this, then use the Add values from button to select the Year field to populate the list.
pSecondaryYear
as above, but default to 2023 (the easiest way to create this parameter is to duplicate the first and just amend the name and default value).
With these parameters, we can then create the calculated fields needed to present the relevant values for each year and gender (as hinted by Lorna).
Population – Primary Male
IF [Year] = [pPrimaryYear] AND [Sex] = ‘male’ THEN [Population] END
format this to K with 1 dp
Population – Primary Female
IF [Year] = [pPrimaryYear] AND [Sex] = ‘female’ THEN [Population] END
format this to K with 1 dp
Population – Secondary Male
IF [Year] = [pSecondaryYear] AND [Sex] = ‘male’ THEN [Population] END
format this to K with 1 dp
Population – Secondary Female
IF [Year] = [pSecondaryYear] AND [Sex] = ‘female’ THEN [Population] END
format this to K with 1 dp
popping these into a table as below, you can see the results
The final calculation we need is for the age banding. We can’t use the in-built ‘bins’ function as the final ‘bin’ contains the ‘rest’ and not just 10 values. Also if we used ‘bins’ the ‘labels’ would be based on the data values and not a custom display as we have here.
Age Bracket
IF [Age] <= 10 THEN ‘<=10’ ELSEIF [Age] <=20 THEN ’11-20′ ELSEIF [Age] <=30 THEN ’21-30′ ELSEIF [Age] <=40 THEN ’31-40′ ELSEIF [Age]<=50 THEN ’41-50′ ELSEIF [Age]<=60 THEN ’51-60′ ELSEIF [Age]<=70 THEN ’61-70′ ELSEIF [Age]<=80 THEN ’71-80′ ELSE ’80+’ END
Note – I altered the calculation and label to be <=10 rather than just <10 as is shown in the solution
Building the Viz
On a new sheet, add Age Bracket to Rows, Population Primary Male to Columns and District to Detail. Change the mark type to circle. Manually re-sort the Age Bracket values so <=10 is listed at the top (just drag the value from the bottom to the top).
Drag Population Primary Female onto the canvas and drop it on the Population Primary Male axis when the double green column symbol appears
This will make the values for both male & female display on the same axis, and Measure Names and Measure Values automatically gets added to the viz. But the values are all displaying in the same direction (the positive axis).
To resolve this, double click into the Population Primary Female pill that is in the Measure Values box underneath the marks card and type in *-1 to the end of the pill and press return
The female values will then be displayed in the opposite direction. Adjust the colours of the Measure Names legend to suit.
To make the dots ‘jitter’, that is appear in a random vertical position, we need a measure (green pill) on the Rows so we generate a y-axis.
Now typically when I am creating jitter plots I use the undocumented RANDOM() function which generates a random number between 0 and 1. The function is undocumented, as it only works for some data sources (excel being one). Using RANDOM() was something I mentioned to several attendees of the Live WOW session at Tableau Conference.
However, due to a later requirement, you’ll need to use a different function instead – in this case INDEX(). For clarity I created an explicit calculated field for this
Jitter
INDEX()
INDEX()is a table calculation that creates a unique sequence number from 1 to n for each record in the table partition. In this case the partition is each Age Bracket. Add Jitter to Rows and adjust the table calculation setting so it is computing by District only. Set the fit of the chart to Fit Width to ensure you can see the display better.
Adjust the colour of the marks to 50% opacity.
To handle the detail displayed on the Tooltip we need to create some additional fields for the population values, these ones not split based on gender.
Population – Primary Year
IF [Year] = [pPrimaryYear] THEN [Population] END
format to K with 1 dp
Population – Secondary Year
IF [Year] = [pSecondaryYear] THEN [Population] END
Add Sex, Population – Secondary Year, Population – Primary Year and Population Change to the Tooltip and adjust accordingly.
The coloured bands are based on the average for the ‘primary’ year and sex. Add Population Primary Female and Population Primary Male to the Detail shelf. Double click on Population Primary Female and type in *-1 to ensure you get the relevant negative value.
Right click on the ‘value’ x-axis and Add Reference Line.
Change the option to be a reference band per pane, and set a band to go from Population Primary Female *-1 : Average to a Constant of 0. Ensure no labels/tooltips display, and set the fill colour of the band accordingly.
Add another reference band for the which goes from constant 0 to the Average of Population Primary Male. Adjust fill colour to suit.
Add Population Secondary Male and Population Secondary Female to the Detail shelf. Double click into the Population Secondary Female pill and add *-1 to the end.
Right click on the ‘value’ x-axis and Add Reference Line.
Add a reference line that is the Average of the Population Secondary Female * -1 field. Adjust colour and thickness of line to suit (I used the middle thickness and line coloured at 80% transparency).
Repeat the same to add an average reference line for the Population Secondary Male field.
The final step is to add the age banding label into the centre of the viz.
Double click into the Columns shelf and type MIN(0). This will create a secondary axis with a new marks card. Remove all the pills except District from the MIN(0) marks card. Add Age Bracket to the Label shelf. Adjust the label properties as below – to label the Min/Max per pane; at the District field level, and label the maximum value only.
This positions the label in the same place on each age banding. This works because we have used INDEX() to control the jittering which means the maximum value is always the same for each bracket. If we had used RANDOM() to define the jittering, there would be no guarantee the same maximum value would have existed for every banding.
Reduce the opacity to 0% and size of the circle to be as small as possible on the MIN(0) marks card, and then make the chart dual axis and synchronise the axis.
Finally format the chart by
Hide the Age Bracket column (uncheck show header)
Hide the Jitter axis (uncheck show header)
Hide the MIN(0) axis (again uncheck show header)
Format the Value axis so the title is Population, and the scale is in 0K format and positive in both directions (custom format ,##0,”K”;#,##0,”K”) NOTE wrapping the K in “” ensures the display is retained when publishing to Tableau Public – thank you Deborah for the tip!)
Hide the nulls indicator (right click – hide indicator)
Remove all gridlines, zero line, axis ticks etc
Remove column dividers
Set Row dividers to white with the widest thickness
Set the chart to fit entire view
Add the sheet to a dashboard. Use a text object to display the title and sub-tile which should reference the pPrimaryYear and pSecondaryYear parameters. Float the parameter controls and resize to give the appearance of just the drop down option – this will take a bit of tweaking to get just right, and you may need to edit via Tableau Public to get the positioning right.
This week’s #WOW2022 challenge was focused on dashboard layout, specifically the use of containers and padding to give your charts room ‘to breathe’ and be aligned beautifully.
This has come at a great time for me, as now I’m spending more time in my role developing business dashboards, I’m trying to find a ‘go to’ style that works for me. I know things will often need to be adapted on a client by client basis, but having a consistent reference point for anything I develop personally, or just want to conceptualize will make my life easier (less decisions to be made). As a result I’m currently spending a lot of time figuring out what padding/background colours etc I want to use for my dashboards.
A note on the data
You may notice that my solution looks a bit different from Luke’s in terms of content. The requirements state to use Superstore v2021.4, but the excel file that ships with Tableau doesn’t actually contain a Manufacturer field which Luke uses. I thought I’d try to use the file linked in the requirements, but (at the time of writing), that actually linked to a 2019.4 version, so the numbers didn’t match up. I therefore chose to use the Superstore v2021.4 excel file I already had on my laptop, and built the table using the Product Name field instead. I think the Superstore.tds file that ships may contain Manufacturer, but I couldn’t see to find that either at the time.
I also chose to use a count distinct function when counting the Order IDs rather than a count, as this seemed more appropriate to me, which is why that KPI differs.
As the focus on the challenge was on the layout, I didn’t think it was necessary to get these points resolved before building my solution.
Building the KPIs
I managed to build the visuals required for the solution using 5 sheets – 1 sheet per KPI ‘card’ and 1 for the table. After I completed my solution, the requirements were updated and suggested 9 sheets were required, essentially 2 per KPI card – the measure and the bar chart. Personally I think having 1 sheet for each KPI is ‘cleaner’ so I left it as is.
In order to build each KPI in a single sheet, I need additional calculations to provide the ‘total’ measure value.
Total Sales
{FIXED: SUM([Sales])}
Count of Orders
COUNTD([Order ID])
Total Orders
{FIXED: [Count of Orders]}
Profit Ratio
SUM([Profit]) / SUM([Sales])
formatted to % with 1 dp
Total Profit Ratio
{FIXED: [Profit Ratio]}
formatted to % with 1 dp
Total Profit
{FIXED : SUM([Profit])}
Then create a bar chart by adding Order Date to Columns and setting to the Month (May) date part (blue discrete pill), and adding Sales to Rows. Add Total Sales to the Detail shelf. Change the mark type to Bar.
Change the Colour to #afaaf3 and reduce the Size. Edit the title of the chart to reference the Total Sales field and format as below with the SALES in 9pt and Total Sales in 15pt.
Modify the Tooltip then remove all gridlines/axis rulers etc, and hide the axis and the month headers.
Rename this sheet as Sales, then duplicate, and create one for Orders by replacing Sales with Count of Orders and Total Sales with Total Orders. You should be able to drop the replacement pill directly on top of the pill being replaced, and everything will update. Update the sheet title to change the word SALES to ORDERS. Rename this sheet Orders, then duplicate again and repeat the process to create the Profit Ratio and Profit sheets.
Building the table
On a new sheet add Product Name (or Manufacturer if you have the right data set) to Rows and add Sales, Profit, Count of Orders and Profit Ratio into the table so you end up with Measure Names on Columns and Measure Values on Text.
Remove all row banding, but add row dividers across each row. Widen each row and the header. Sort by Sales descending.
Change the mark type to Square then add another instance of Measure Values to the Colour shelf. Right click on the Measure Values pill and select Use separate legends.
This will display 4 colour legends, which you need to amend as follows :
Sales Legend
Edit the colour and select Purple sequential. The click on the coloured square at the right end of the range, and change this colour to #675CF3.
Then select the Stepped Colour checkbox and change the value to 8.
Profit Legend
Edit the colour and choose a Diverging colour palette. It doesn’t matter which one. Click on the coloured squares at each end and change them both to white. Set the number of steps to 2.
Count of Orders Legend
Do exactly as described above for the Profit Legend.
Profit Ratio Legend.
Edit the colours and choose a diverging colour palette. Change the colour on the left to #9A1500. Change the colour on the right to #777777. Set the number of steps to 8.
Building the dashboard
Now we have the charts, we need to start building the dashboard, and I’ll see if I can step through my approach to doing it, and if I’ll end up with the right result….
Firstly create a dashboard and set the size to 1100 by 850.
Now a habit I’ve got into when I want to be really precise about the formatting I’m applying (ie I’m building a business dashboard), is I start by adding a floating container to the dashboard, which is set to the exact dimensions of the dashboard. This was a tip I picked up from Curtis Harris here. The main reason for doing this, is that I feel I have more control as I don’t then use any Tiled container type that automatically gets added. If any do appear, I find the objects I want out of them and move them elsewhere, then delete the whole tiled section. In this instance I’m going to start by adding a Vertical floating container, and position it at 0,0 and 1100 wide and 850 high. Use the fields on the Layout pane to set these.
I also get into the habit of renaming the containers, to help me keep track of what is where and whether the objects are in the right place.
The next thing I always do when working with containers, is add a blank object. As we build out, this object will eventually be removed, but it’s a recommended step to help position other objects you add into the containers.
So on the Dashboard tab, change the selection to Tiled and then drag a blank object into the main canvas.
This ‘blank’ is now in the container and the ’tiled’ option means its anchored to that container, which means if you move the container, the objects within will move with it (unlike if the object was floating).
In the item hierarchy, you can see the blank nested in the Base container.
In the item hierarchy, click the Base container, so it is selected on the dashboard (it will be surrounded by a blue frame). Adjust the settings so the background is pale grey, the outer padding is 20px all round, and inner padding is 30px all round.
Add a Horizontal container above the blank object. This is going to be a header section for the title and the export options. Name the container Header in the item hierarchy. Add a blank object into the header container.
Add a Text object into the Header container to the left of the blank object, and enter the text for the title and strapline. I used Tableau Book 16pt bold for the main title and 9pt for the subtitle (there was no instruction for this). Set the outer padding of this text box to 0 all round.
Download the image files Luke provided.
Add a Download object to the right of the blank in the Header container. Edit the button and set to export to an image, use an image button style and select the relevant image.
The image will probably look incredibly large, but don’t worry. First, set the outer padding of this object to be 10 all round.
Then select the Header container and edit the height to be 55px (use the down arrow on the selected object to open the context menu and select Edit Height).
Add further download objects to the right of the existing one (making sure you’re in the Header container) – one to export to PDF and one to PowerPoint. Set the outer padding of the PDF one to 10 all round, and for the PowerPoint one, set the outer padding to 10 for left, top & bottom, but 0 for the right.
Now add another Horizontal container below the Header container and above the blank. Rename this to Main. Set the outer padding of the Main container so that it has 25px at the top. This padding (25) + height of header container (55) + inner padding of Base container (30) gives us the requirement that the height of the grey header to the components needs to be 110px. Add a blank object into the Main container.
You can now remove the blank object that is at the bottom of the Base container – the one underneath the Main container.
NoteIt’s worth saying at this point, that when I built my solution most of this was all bit trial and error / tweaking this & that to meet all the specific layout requirements Luke states. As I’m rebuilding my original solution as I write, I know what order I want to add objects to the dashboard and what all the settings need to be as I add each object.
The Main container is horizontal as it it will consist of 2 columns; 1 with the KPIs and 1 with the table. However both columns need to contain a Vertical container each. For the KPIs columns, this is to stack each KPI card on top of each other. For the table, this is because we have to add the purple bar on top of the table.
So, add a Vertical container into the Main container, to the right of the blank. Rename this container Table Column. Then add a blank into this container, and underneath that add the Table sheet.
If you look in the item hierarchy, you should see that a Tiled section has been automatically added to the dashboard – boo!! This contains all the colour legends associated to the table, and you can just make out where they are (top right) as they’re behind the floating Base container.
In fact, the Tiled container added actually contains lots of other containers.
We don’t need any of these legends to display or any of the containers that have been added, so remove (right click on the first Tiled container and Remove from Dashboard). You’ll get a warning message but just hit Delete to continue. The section will be removed and your item hierarchy looks nice and clean again 🙂
Now that’s sorted, we can focus back on the Table Column container and the objects within. Select the Blank object in the container. Change the background colour to #675cf3, set the outer padding to 0 all round, then edit the height to be 10 px. Rename the blank object in the item hierarchy to ‘purple bar’.
Now select the table sheet itself. Hide the sheet title. Set the outer padding to 0 all round and set the inner padding to 10 all round. Set the background colour to white. Set the sheet to Fit Width. Don’t worry at this point about the table looking cramped.
Now add another Vertical container into the Main container, this time to the left of the blank. Rename this container KPI Column and add a blank into it. You can now remove the blank that is sitting between the KPI Column container and the Table Column container. This will cause the table to expand.
Edit the width of the KPI Column container to be 300 px.
Now we’re ready to deal with the KPIs.
Each KPI ‘card’ consists of a purple vertical line and the KPI sheet itself. This means we need a horizontal container per card to manage this. WHAT! More containers……. Fun isn’t it 🙂
So add a Horizontal container into the KPI Column container, above the blank object. Name this container Sales KPI. Add a blank object into this container. Then add the Sales sheet into the Sales KPI container to the right of the blank. Hopefully you have something like below…
Select the blank object in the Sales KPI container and set the background colour to #675cf3, and the outer padding to 0 all round and edit the width to be 10 px. Rename this object to ‘purple bar’.
Then select the Sales sheet. Set the background to white, the outer padding to 0 all round and the inner padding to 10 all round.
Now select the Sales KPI container, and set the bottom and right outer padding to be 5px. The top and left should be 0.
Add another horizontal container to the KPI Column container directly below the Sales KPI container. Name this container Orders KPI. Set the outer padding on this container to be left 0, right, top & bottom all 5.
Add a blank object into the Orders KPI container, then add the Orders sheet to the right of this. As before, select the blank object and set the background colour to #675cf3, and the outer padding to 0 all round and edit the width to be 10 px. Rename this object to ‘purple bar’.
Now select the Orders sheet. Set the background to white, the outer padding to 0 all round and the inner padding to 10 all round.
Repeat this process to create the Profit Ratio and Profit sections. When you add the Profit KPI container, set the outer padding for the top and right to 5, and the bottom and left to 0.
Remove the blank object that should now be at the bottom of the KPI Column container. The click on the KPI Column container in the item hierarchy to select it, and use the context menu from the drop down at the top right of the object and Distribute Contents Evenly.
The final tweak to make is to add some left padding to the Table Column container. Select the container and set the left padding to 5 and the rest to 0.
And that should hopefully be it, and meet all the requirements. My full item hierarchy is visible below.
Granted there are more containers than Luke suggests, but hopefully you can see how it’s organised and much more maintainable.
Containers can be tricky beasts, but if you work methodically with them, they become easier to use. In summary, the steps I try to follow are
Start with a floating container sized accordingly, and add ’tiled’ (ie not floating) objects into that
Always add a blank object into any container you add. Add required objects, then remove the blank if no longer required.
Rename containers as you go.
Deal with any Tiled containers as they appear – if you need a legend/filter that gets added in it, select that object via the item hierarchy and move it to where you want it to be, then delete the complete tiled container.
It’s Tableau Conference next week, and I’m actually going to be attending in person for the very first time (I’m super super excited!). I’m not sure when next week’s challenge will actually land or when I’ll actually get a chance to complete, let alone blog. I might have to end up playing catch, so I apologise in advance for those who can’t attend and may be waiting for my guide to help them out. It will get published… I just don’t know when!
The challenge this week from Ann Jackson was all about map layers, a feature introduced in v 2020.4. As an extra twist Ann specified that you couldn’t use table calculations or LoDs, and that the viz should all be on a single sheet.
The key features of this challenge are
display the map segmented by region
colour the borders of each region with different colours
display the cities sized by sales
display sales per region
on hover of a city, display city sales, city name and % of regional sales
Display the map segmented by region
Maps work with geographic data types, so to split map by Region, the field needs to be converted to this type. To do this, right click on Region > Geographic Role > Create From > State
Double clicking on Region should automatically create a map visual. Change the mark type to filled map, and you’ll see the region boundaries.
Strip off all the background & labels etc via the Map > Map Layers menu. Uncheck all the options on the right hand side.
Set the colour of the map to grey (via the Colour shelf), then set the background of the worksheet to a darker grey/black. Remove row & column dividers.
Colour the borders of each region with different colours
On the colour shelf, you can set a border colour, but this is a fixed single colour, not one that can be associated to a field. This is where the map layers start to come in.
We need to create a layer per region.
To start we need a calculated field
West Region
IF [Region]=’West’ THEN [Region] END
This should still be a geographic role (if it hasn’t got a globe symbol next to it, then make it geographic as you did above).
Drag this field onto the map, and drop it on the Add a Marks Layer symbol that displays. A new West Region marks card will display. Change the mark type to filled map.
Now on the Colour shelf, reduce the opacity to 0. Change the border colour to #7ec886, and the halo to None.
Finally, and this is the really sneaky bit that took me some time to figure out, right click on the Null on the legend on the right hand side, and Hide. Only the West region now has the coloured border.
Repeat these same steps for the Central (#82d7d6), East (#b6b6dd) and South (#fd8b59) regions.
A final step at this point is to turn off tooltips across all 5 layers. Also, we don’t want the regions ‘highlighting’ as you hover the mouse over the map, so on each layer, disable selection
Display the cities sized by sales
Drag the City field onto the map, and drop it to create another layer. Drag State onto the Detail shelf of the City marks card (as there are some cities that are associated to different states).
Add Sales to Size and adjust accordingly.
Add Region to Colour, and reduce the Opacity to suit. Remove tooltips from displaying.
Display sales per region
Add another layer by dragging Region onto the map again. Change the mark type to Text. Add Sales to the Text shelf. Format appropriately. Set Disable Selection on the layer.
Good practice, rename the layer to Sales Text or similar.
On hover of a city, display city sales, city name and % of regional sales
For this we need to store a selected city, and we’ll use sets to do this. I did this by right-clicking on one of the cities in the viz, and choosing Create Set. Rename the set Selected City.
We can now work out the value of the Sales for this city, and the % of sales.
Selected City Sales
IF [Selected City] THEN [Sales] END
format to $ 0dp
% Total Sales
SUM([Selected City Sales])/SUM([Sales])
format to % 1dp.
Add these onto the Text shelf of the Sales Text marks card and format.
Now we want to add the City Name, some other additional text.
Selected City Name
IF [Selected City] THEN [City] END
However, if you add this field to the Text shelf, the marks move and things go a bit crazy.
I had to ponder about this for a long time, and I can’t explain why it does it. However it didn’t happen when I added measures (green pills) to the Text shelf. So I converted the field to a measure by editing the field and changing it to
Selected City Name
MIN(IF [Selected City] THEN [City] END)
Adding this to the Text shelf and the text stayed where it was.
A further calculated field is required, which is simply
Selected City Sub Text
MIN(IF [Selected City] THEN ‘OF REGION TOTAL’ END)
Add this and adjust the text.
So now we have the text showing, we need to make it change based on hover.
Add the map to a dashboard, then add a set action (Dashboard > Action > Add Action > Change Set Values)
Set the properties as below, and then test the interactivity by hovering over different cities.
Hopefully that’s everything you need to complete this challenge. The borders and the ‘moving marks’ certainly caused me sometime of puzzlement, but it is a very satisfying feeling when you get that ‘lightbulb’ moment. My published viz is here.
Week 19 of #WOW2020 saw Ann provide this challenge, which she declared would be easier than Luke’s from the previous week.
The aim was to start with a chart by week, but on selection drill in to show a by day view, with the slight twist that all days between the first and last selected dates should display, even if the specific week in between wasn’t explicitly selected.
I knew this was going to involve set actions (although there was a clue on the ‘latest challenges‘ page – I tend to pick up the challenges through Twitter, so often bypass this page, but it’s worth a look for extra clues :-))
So on the face of it, the challenge seemed as if it should be ok, but it’s usually only when I start building that things can start to unravel.
One area that Ann hadn’t been explicit about in the requirements, was the behaviour in certain scenarios. I had to revise some of my initial attempts/calculations in order to match Ann’s workbook. This week I’m not going to go into all the wrong turns, but this is the behaviour I observed in Ann’s workbook, and so ultimately tried to replicate.
When at the week level, if multiple points are selected, the days then displayed should start from the first day of the first week selected (which will be a Sunday as we’re working with how the US standardises a week start), and finish on the last day of the last week selected (which will be a Saturday).
When at the week level, if a single point is selected then the 7 days in that week only should be displayed.
When at the day level, if multiple points are selected, the days then displayed start from the first day of the week the first day is in and end on the last day of the week the last selected day is in (so you may get more days than actually selected). eg if the 1st day selected is a Tuesday and the last day selected is a Friday, the ‘drill in’, will start on the previous Sunday, and end on the following Saturday. This isn’t necessarily what you may expect to happen.
Based on the above, when at the day level, a single day is selected, the ‘drill in’ will show the 7 days in the same week. Once you’re down to displaying the 7 days in the same week, clicking on any single day or selecting a couple of days, won’t appear to do anything as the same results are displayed.
Being aware of the above, will explain why some the calculations I end up with look the way they do.
Ok, let’s get on with the build…..
Ann stated that she’d hardcoded to the ‘last 52 weeks’, but essentially it looked like she was displaying data for all of 2019. This wasn’t really something the challenge was testing, so I simply started by adding a data source filter for the year 2019 (right click on data source -> Edit data source filters). I also had to set the date properties of my data source to set a week to start on a Sunday as I’m UK based, so my week’s are defaulted to start on Mondays (again right click on data source -> Date Properties). As a result of my year filter, I didn’t seem to have the same starting/ending dates as Ann, but as already stated, this wasn’t the main aim of the challenge, so I didn’t stress about it.
I decided quite early on that I was going to also use a Parameter Action to decide the level I was at (ie had I drilled in or not). I wasn’t entirely sure when I started how many ‘levels’ I might need, so chose to use an integer parameter for this. I ended up only needing 2 levels, so a boolean could have worked equally as well, or, as I type, I think I could have used this parameter to store the ‘date level’ (day or week) I want to display my dates at, which would have made some of my calculations easier to read. I’m not going to do this though.
Drill Down
Integer parameter set to 0 by default.
Now I want to define a date field that I’m going to use on the axis, that will vary depending on the ‘level’ we’re at.
Date to Plot
IF [Drill Down]>0 THEN DATETRUNC(‘day’, [Order Date]) ELSE DATETRUNC(‘week’, [Order Date]) END
Note – I used >0 as I wasn’t sure if I’d have levels 0, 1 & 2… as it turns out I just used 0 & 1 in the end.
Add Date to Plot to Columns as a Continuous, Exact Date and Sales to Rows and we’ve got our starting point
Change the Drill Down parameter to 1 and the chart will change to display at the day level
Drilling Down on selection
On selection, we want to add the selected dates into a set, so first up, we need to define that set.
Right click on Date to Plot and Create -> Set
Selected Dates
Select some random dates so we can test with. These will get set properly later based on the Set Action we define on the dashboard.
Based on the dates in the set, we need to determine a min and a max date we can then use to restrict the dates being plotted on the chart.
Min Date
{FIXED:MIN(IF [Selected Dates] OR [Drill Down]=0 THEN DATETRUNC(‘week’,[Date to Plot]) END) }
This either gets the 1st day of the week based on the earliest date in the set, or the 1st day of the week of the whole data set (when we’re at the ‘starting’ level with Drill Down =0).
We also need
Max Date
IF [Drill Down]=0 THEN {FIXED:MAX(DATEADD(‘day’,-1,[Date to Plot]))} ELSE DATEADD(‘week’,1,{FIXED:MAX(IF [Selected Dates] THEN DATETRUNC(‘week’,[Date to Plot]) END)})-1 END
If we’re at the starting level (Drill Down = 0) then we want the last day within the latest week in the data set (this field is used in the title display, so necessary to get this to display right), otherwise we need to get the last day of the week associated to the maximum date in the set.
Now we need to be able to restrict the dates displayed in the chart based on these
Dates to Include
[Order Date]>= [Min Date] AND [Order Date]<= [Max Date]
Add this to the Filter shelf and set to True.
Your display shouldn’t change, as we’re still at ‘level 0’, even though our set has random dates selected. If you now manually change Drill Down to 1, you should see a change
Setting up the dashboard actions
Ok, now we’ve got the basic idea, let’s get it all working properly with dashboard actions before we sort out all the other bits n bobs.
First up, let’s manually reset everything by setting the Drill Down parameter to 0 and emptying all the values selected in the set.
Add the sheet onto the dashboard, and create the set action, which is set to target the Selected Dates set and to Keep set values if you click on a blank area of the chart after selection.
However, this on it’s own won’t change the display. We need to set the Drill Down parameter to 1 too.
For this we need another field
Set Drill DownLevel
1
Add this to the Detail shelf of the chart.
Then back on the dashboard, add a Parameter Action that targets the Drill Down parameter using the value stored in the Set Drill Down Level field
Now if you select the dates in the dashboard, you should get the desired behaviour, and if you select again, you should filter the days selected further (as per the behaviour described at the top of the page).
Resetting the display
The Reset button is actually another sheet.
I created a ‘fake’ bar chart by adding MIN(1) to Rows, and double clicking in the space below the Detail and Tooltip shelves on the Marks card, and typing the text ‘CLEAR SELECTION’. This creates a ‘pill’ without having it defined explicitly as a calculated field, and I added this to the Text shelf, and centred/formatted appropriately. I then set the axis to be fixed from 0 to 1 and hid it.
I set the colour to #a26dc2 and set the text to ‘match mark colour.
We only want this sheet to display, if we’ve ‘drilled down’, so I need
Show Reset
[Drill Down]=1
which is added to the Filter shelf and set to True.
I also need a parameter action off this sheet, to reset the Drill Down to 0 on selection. For this I need another field
Reset
0
which is added to the Detail shelf.
Depending on what you’ve already been playing around with, there’s a chance this sheet may already be empty. Get the dashboard into a state where you’ve drilled down to the day level, then add this sheet, and add another Parameter Action.
Reset is set to run off the ‘button’ sheet only, to target the Drill Down parameter by using the value in the Reset field.
You should now be able to test all this out and get the desired behaviour.
Adding Animations
To get the chart to transition between selections, we need to use the Animations functionality.
On the Format menu, select Animations and adjust the settings as you choose.
Play around and you should have the main features of this challenge now working
Dynamic Title
The title needs to change based on whether you’re at the weekly or daily level. It also needs to show total and average sales. So for all this I need
LABEL: Level
IF [Drill Down]>0 THEN ‘Day’ ELSE ‘Week’ END
LABEL: Subtitle Level
IF [Drill Down]>0 THEN ‘Daily’ ELSE ‘Weekly’ END
LABEL: Instruction
IF [Drill Down] = 0 THEN ‘SELECT WEEKS TO DRILL DOWN TO DAILY VIEW’ ELSE ‘CLEAR SELECTION USING BUTTON’ END
Total Sales
WINDOW_SUM(SUM([Sales]))
Avg Sales
WINDOW_AVG(SUM([Sales]))
All these fields are added to the Detail shelf of the main chart along with Min Date and Max Date, and then the title is edited and formatted accordingly to reference these.
Tooltips
In a similar manner, the tooltips also need adjusting, they just need to refer to the LABEL: Level field
Colour of the Line
Although not explicitly mentioned, the line colour seemed to change from a lighter shade at the weekly level to more intense at the daily level. To achieve this I created
COLOUR
[Drill Down]
and added to the Colour shelf. This gives a colour legend displaying either 1 or 0 depending at what state you are in the interaction. Set colour values accordingly.
Average Line
Add this to the chart simply by selecting Average Line from the Analytics pane and dragging then dropping onto the Table shelf that appears. Format appropriately.
And apart from other basic formatting to remove gridlines/ axis titles etc, that should be it.
Donna + DataViz 