Kyle set the challenge this week to recreate a drill-down, but with the stipulation that no parameters were to be used. I immediately figured this would be a challenge requiring set actions, and indeed the hint on the splash page of the #WOW site, confirmed this
Building the Viz
After connecting to the data source, create a Set off of the Category field (right click > create > Set). Select a single option eg Technology
Category Set
Create fields
Display Value
IF [Category Set] THEN [Sub-Category] ELSE [Category] END
and
Expand Indicator
IF [Category Set] THEN [Category] ELSE ‘+’ END
Add Category, Expand Indicator and Display Value to Rows and Sales to Columns and press the sort desc button in the toolbar, to sort all the bars . The click the Category pill to add another sort by sales descending.
Hide the Category pill (uncheck show header).
Format the Expand Indicator column, so the text is aligned vertically
Right click on the Expand Indicator text heading displayed in the view and hide field labels for rows. Widen each row a bit, remove all gridlines, remove the Sales axis title (right click axis > edit axis). Add a title to the sheet. Adjust the Tooltip.
Adding the interactivity
Add the sheet to a dashboard, then add a dashboard set action
Select Cat
On select of the viz on the dashboard, target the Category Set, adding values to the set when the viz is clicked (selected), and remove all values when the selection is cleared. Only allow 1 selection at a time to be made.
For this week’s challenge, Lorna set the relatively straightforward task of creating a Sankey using Tableau’s Viz Extension. The challenge may not have the complexity/nuances you might sometimes find, but at #WOW HQ, giving you the opportunity to try out new features is one of the key benefits of running this community project.
This blog will be brief:-)
Connect to the data source, and and drag Number from the Dimensions section of the data pane (above the line) to the Measures section (below the line).
On the Markscard, select to AddExtension
in the Add an Extension dialog window, select the Sankey (by Tableau) option which is likely to be the first listed (if not, search for it).
then click Open on the following screen and the marks card will change and give different buttons/shelves
Add Age group to Level. Then add Mode of former study to Level, followed by Level of qualification obtained and then Domicile.
The Levels listed from top to bottom on the marks card is represented from left to right in the Sankey on the canvas.
Add Number to Link to adjust the size of the flows between each Level.
Then press Format Extension and adjust settings as required. I set the Level Padding and the Edge Padding to 10 to increase the spacing between each ‘row’ and ‘column’. I set the colour palette to Purple-Pink-Grayand set the Level Labels font to bold.
I then adjusted the Tooltip to suit and added the sheet to a dashboard. Simples 🙂
Tableau’s documentation on Viz extensions here and my published viz is here.
The requirement involved both the use of the Superstore data set and a custom Target data set, so these need to be combined. In the data pane, relate the two data sets together applying the fields below in the screen shots as the relationship fields (not you’ll need to create a relationship calculation to build the 3rd relationship.
Building the table
The table displays Category & Sub-Category along with a 3rd dimension which will differ depending on user selection. So we need to enable this choice.
pBreakdown
string parameter containing a list of options, defaulted to Ship Mode
Then create a calculated field to determine the actual field to show based on the parameter selection
Breakdown Dimension
CASE [pBreakdown] WHEN ‘Segment’ THEN [Segment] WHEN ‘Region’ THEN [Region] WHEN ‘Ship Mode’ THEN [Ship Mode] END
The user will also need to select a month. I chose to use a calculated field and parameter to drive this.
Month
DATE(DATETRUNC(‘month’, [Order Date]))
and this then feeds the parameter
pMonth
date parameter, formatted to custom date format of mmmm yyyy (to display September 2023). The list is populated when the workbook opens from the Month calculated field created above. Default date is 01 Sept 2023.
The report will need to be filtered based on the date selected in the parameter, so create
Filter Date
[Month] = [pMonth]
On a new sheet, add Category & Sub-Category (from the Orders data set) and Breakdown Dimension to Rows. Show the parameters. Add Filter Date to the Filter shelf and set to True.
Now, the table shows some conditional formatting, which is a clue that we’re not going to building this table in the basic way. Instead we’ll be using what I refer to as ‘fake axis’ to define placeholder columns, which we can then use to have more flexibility on how the data is displayed.
Double click into the Columns shelf and type MIN(0.0).
Change the mark type to Shape and use a transparent shape (see this blog for more information on doing this). Add Sales to the Label shelf and align left. Add all subtotals (Analysis > Totals > Add All Subtotals) and then set the all to display at the top (Analysis > Totals > Column Totals to Top).
Adjust the formatting, so the row dividers are at the highest level
and the row banding is also at the total pane/header level
Type in another instance of MIN(0.0). This will create a second marks card. Adjust the Sales pill and apply a quick table calculation of percent of total. Set to compute using the Breakdown Dimension field. Format the field to % with 0dp.
To stop the % displaying on the Total rows, click the table calc field and select Total using > Hide
Add another instance of MIN(0.0) and this time add Sales Target value from the Sheet1 data set to the Label shelf.
You can see the target values fill down against the Breakdown Display rows, but we don’t want this.
So to work out whether we’re on a Total row or not, we’re going to make use of the SIZE() table calculation.
We want to count the number of Breakdown Dimension rows being shown.
Count Breakdown Dimension Rows
SIZE()
change this to be discrete, add this to Rows and adjust the table calculation so it is compute using the Breakdown Dimension field. You can see the value against each row represents the number of (in this case)different Ship Mode values.
What isn’t visible is the fact that the SIZE() values against the Total rows are actually 1. Move the Count Breakdown Dimension Rows to the Tooltip shelf on the All marks card. If you now hover over the rows of data, the tooltip should display 1 against this field when it’s a total row and the count otherwise. We’ll use this to identify the Total rows.
This is a common technique that is used. Note though, that if the ‘variable’ you’re counting over only has 1 value, then any logic based on this will apply to that row too (see the Copiers row for Ship Mode & Sept 2023).
So to display the Target Sales as we want, we need
Sales Target to Display
IF [Count Breakdown Dimension Rows] = 1 THEN SUM([Sales Target]) END
ie only show the value of the target on the total rows (or when there’s only 1 value in the dimension being counted).
Replace the Sales Target pill on the 3rd MIN(0.0) marks card with this Sales Target to Display field. Make sure the table calculation is set to compute using Breakdown Dimension
Now we need the variance
Variance
(SUM(Sales)-[Sales Target to Display])/SUM([Sales])
Format this to ▲0.0%;▼0.0%
and we need to identify if it’s +ve or -ve
Variance is +ve
[Variance] >=0
Create another instance of MIN(0.0). Add the Variance field to the Label shelf and verify it is computing by Breakdown Dimension
Add Variance is +ve to the Colour shelf. Adjust the colours, then on the Label shelf, set the font to match mark colour.
To change the ‘Total’ labels to ‘All’, right click on one of the labels, and change the label in the left hand pane.
Finally, adjust the formatting – I set the font throughout to be a darker colour, set the All labels to be bold, removed all gridlines, zero lines and column dividers, hid the MIN(0) axes and also hide field labels for rows. Turn off tooltips for the whole table.
Building the dashboard
I then added the sheet to a dashboard, and placed it within a vertical container. Above the vertical container, I used a horizontal container in which I added text boxes for the column headers. The text box for the variable dimension, referenced the pBreakdown parameter value. I also then used a floating blank set to a height of 2px with a background colour of grey to give the appearance of a line above the column headings.
In the dashboard heading, refer to the pMonth parameter to display the date.
Once published, I did have to tweak the width of the heading text boxes and the position of the floating line within the web edit view.
I essentially ‘faked’ the table header row but I still only used 1 sheet to build the table and I was able to dynamically change the title of the dimension column, so hey I hit the brief 🙂 If you want to understand how to build the header row into the actual table itself, then check out Rosario Gauna’s blog.
In this week’s #WOW2022 instalment, Luke set this map-based challenge to visualise COVID-19 cases across the continental US states on 30 December 2021.
Each ‘pyramid’ on the map represents a county within a state, with the height and colour of the pyramid indicating the volume of cases recorded.
Luke provided a workbook with some pre-modelled data as a starting point, so I used that.
To familiarise myself with the data I built out a basic table, focusing on the State of Idaho (ID) only.
Clat10 and Clon10 are the latitude and longitude positions of the County and metrics.caseDensity is the key measure used to indicate the volume of cases.
To draw the pyramid, we need to plot 3 marks for each County, 1 for each point in the triangle. This means we need to densify the data so we have multiple rows for each County. If we add Point as a discrete dimension (blue pill) to the table, we can see we can generate up to 20 rows per County.
This is obviously excessive – we only need Points 1-3, so for simplicity, we can add Point to Filter and limit to just 1, 2 and 3. I added the filter by duplicating the Point pill from Rows (hold ctrl and then click and drag the pill from Rows to Filter – this added the filter as a discrete dimension and I could just select options 1, 2, 3).
With only 3 points, we can now build the lat and long coordinates for each point. I am assuming point 1 is the bottom left point, 2 is the top point, 3 is the bottom right.
Triangle Long
CASE [Point] WHEN 1 THEN [Clon10] – 0.1 WHEN 2 THEN [Clon10] WHEN 3 THEN [Clon10] + 0.1 END
As the requirements stated the base of pyramid was 0.2 longitude points wide, then the bottom left and bottom right points need to be 0.1 points to the left or right of the County longitude, while the peak is aligned centrally.
Triangle Lat
CASE [Point] WHEN 1 THEN [Clat10] WHEN 2 THEN [Clat10] + ([metrics.caseDensity]/60) WHEN 3 THEN [Clat10] END
The latitude of the left and right bottom points are aligned with the County latitude, while the height is an additional 1/60 of the metrics.caseDensity value above the County longitude.
Change both the Triangle Lat and Triangle Long fields to be of the Geographic role type of Latitude or Longitude accordingly (right click field -> Geographic Role -> Latitude/Longitude).
Adding these into the table, we can see how the coordinates are forming
So with this, we can now build the map
Add State to Filter and exclude AK, HI, PR (Alaska, Hawaii, Puerto Rico).
Double click Triangle Lat then double click Triangle Long to add them to the sheet – a map should automatically be presented.
Add County to Detail, and a mark should appear for every County.
Add Point to Detail and change to a dimension – there should now be 3 marks per County forming triangles. Zoom in to really see this.
Change the mark type to line and the points all join up to form the pyramid.
Add metrics.caseDensity to Colour, then edit the colour. Choose the red-gold colour palette, then click the Advanced button, and first change the centre to 75, then set the start & end value to 0 and 200.
Modify the Tooltip and reduce the Size of the line. Zoom back out
Finally amend the map background ( Map menu -> Background Layers). Uncheck base and land cover, check terrain and coastline. Uncheck Country/Region Names and State/Province Names.
Hide the null indicator and then place on a dashboard. Done!
Once again, I found this week tough! I understand the concept of parameter actions, and can use them for the ‘basics’, but the funky stuff people come up with to bend them to their will, is really beyond me.
I started the challenge with good intentions as always, ‘relating’ the Dates data to the Superstore data set matching the Date field to the Order Date field, built out the calendar view, but got stuck trying to figure out how to set the parameter action into an ‘End Date’ parameter, when I was already setting a ‘Start Date’ parameter. After staring at the screen for some time, I figured I’d try to work it all out from Ludovic’s viz instead.
There’s A LOT going on in this challenge, so I’ll do my best to explain.
Building the Calendar Picker
Building the Next / Previous control
Building the Year/Month control
Building the KPI & Trend Chart
Building the Calendar Picker
The calendar will only show 1 month, so let’s deal with that to start with.
A parameter will be used to store a date related to the month to show.
pMonthSelected
This is a date parameter that I chose to default to 01 June 2019.
Month Date
DATE(DATETRUNC(‘month’,[Date]))
This is truncating every Date in the dataset to the 1st of the relevant month
Month To Show
[pMonthSelected] = [Month Date]
Add this to the Filter shelf and set to True will limit the data to a single month, June 2019 by default.
To display the days of the month in the ‘grid’ layout, we need to know the day of the week each day falls on (so we know what column to position the day in), and we need to know what week in the year the day falls in, as this will dictate which row to position the day in.
Day of Week (Abbrev)
LEFT(DATENAME(‘weekday’, [Date]),3)
The basic calendar layout can then be built by
Month To Show = True on Filter
Day of Week (Abbrev) on Columns
WEEK(Date) on Rows
DAY(Date) on Text
The WEEK(Date) field is then hidden.
We’re going to need to represent each day by a ‘square’ block. I achieve this by adding MIN(1) to Columns, changing the mark type to Bar, fixing the axis from 0-1, then hiding the axis.
So now the basic calendar layout is achieved, we need to figure out how to select a start & end, and colour accordingly.
We’re going to use a single string parameter to store both the selected start & end dates, using a | to act as a delimiter allowing us to identify & so separate the 2 dates.
pSelectedDates
String parameter set to empty string by default
Show the parameter on the viz, and I will attempt to walk through the steps, showing how the values in the parameter influence other fields and what is displayed on the viz.
We need another field, that is going to be used in conjunction with the parameter. I’m going to build up what this field needs to store as we go through, so hoping it will all make sense.
Date Control
IF [pSelectedDates]=”” THEN STR([Date]) END
If the parameter is empty, then set Date Control to be the date.
Let’s add this to the Text so you can see what happens to this field.
So the first action we expect the user to take is to click on a single date displayed. This value stored in the Date Control field will be passed via the parameter action into the pSelectedDates field (I’ll show you how this works later). For now let’s assume the user has clicked on the 5th June, so type 2019-06-05 into the pSelectedDates field.
This is now the start date, and we need the Date Control field to change to reflect this
Date Control
IF [pSelectedDates]=”” THEN STR([Date]) ELSEIF [Date]<=DATE([pSelectedDates]) THEN STR([Date]) ELSE [pSelectedDates]+”|”+STR([Date]) END // else store start & end date
If the Date displayed is before that stored in the parameter, then Date Control will continue just to store the date, otherwise it will append | followed by the date onto the existing parameter value.
So when a date is clicked again, the value of the Date Control will once again be passed to the pSelectedDates parameter.
If the date clicked is earlier than the one already stored, eg let’s say 04 June was clicked, the Date Control value of 04 June is passed to the parameter, and is now the ‘start date’. Type 2019-06-04 into the pSelectedDates field.
But if a later date is selected, say 18th June, the Date Control value of 2019-06-04|2019-06-18 is passed into the pSelectedDates parameter, and we now need another step with our Date Control field :
Date Control
IF [pSelectedDates]=”” THEN STR([Date]) ELSEIF CONTAINS([pSelectedDates],”|”) THEN STR([Date]) ELSEIF [Date]<=DATE([pSelectedDates]) THEN STR([Date]) ELSE [pSelectedDates]+”|”+STR([Date]) END
The 2nd statement is now looking for the | character and if it finds it, we know a start & end dates has been selected, so we need to ‘reset’ this control field to how it was originally – just it’s own date.
Type in 2019-06-04|2019-06-18 into the pSelectedDates field to see this.
Ok, so now we can see how the pSelectedDates field is working with the Date Control field, we now need to define some additional fields to help us identify the start & end of the dates selected, so we can work out what to colour.
This is looking for the position of the | field and taking everything from the first character in the pSelectedDates string up to the character before the |.
NoteI originally used DATE(SPLIT([pSelectedDates], “|”,1)), but after extracting the data set, this calculation caused errors. I don’t know why, but the above workaround sufficed.
Find the position of the | and take all the characters starting at the position after the | and ending at the character 10 characters later (the date format is 10 characters so this can be easily hardcoded)
COLOUR: Date
IF [Date]= [Date Selection Start] OR [Date] = [Date Selection End] THEN ‘Hot Pink’ ELSEIF [Date] > [Date Selection Start] AND [Date] < [Date Selection End] THEN ‘Pink’ ELSE ‘White’ END
Add this onto the Colour shelf, and adjust accordingly
We can remove the Date Control from Text now, but it needs to remain in the view, so it can be referenced when we build the parameter action. Add it onto Detail shelf instead.
Finally add this viz onto a dashboard, then add the parameter action, setting the Target Parameter to pSelectedDates and the Field to pass as Date Control.
As you play around selecting values on the dashboard, you’ll notice the dates appear ‘greyed out’, as the ‘on click’ action is essentially highlighting what you’ve selected. To stop this from happening create a calculated field called True that just stores the value of True and and a calculated field called False that stores the value False. Add both of these to the Detail shelf of the calendar viz.
Then on the dashboard, add a Filter action that goes from the dashboard to the sheet itself, passing the values Source: True = Target: False
Building the Next / Previous Control
We need to store the value of the next month related to the Date unless we’re already in the last month of the data set, in which case we’ll just keep the same month (ie if the month is 01 Nov 2019, next month will be 01 Dec 2019, but if the month is 01 Dec 2019 which is the last month in the data set, the next month will also be 01 Dec 2019. This stops the next control from moving forward when we reach the end.
Next Month
IF [Month Date] = {MAX([Month Date])} THEN [Month Date] ELSE DATE(DATEADD(‘month’, 1, [Month Date])) END
Change the mark type to Shape and select an appropriate arrow. If need be reduce the transparency of the shape colour (I chose 50% which changed a black arrow to look like a grey arrow).
On a new sheet, Add Month To Show = True to the Filter shelf, and Next Month to the Detail shelf.
If you recall, right at the start, we created a pMonthSelected parameter, and this will be used to change the month displayed.
Add the viz to the dashboard, and add a parameter action that on select, targets the pMonthSelected parameter, passing in the Next Month field
For the Previous Month control, we need apply similar steps, but using the field below instead.
Prev Month
IF [Month Date] = {MIN([Month Date])} THEN [Month Date] ELSE DATE(DATEADD(‘month’, -1, [Month Date])) End
Building the Year/Month control
When building the Year selector, we need to filter the data to just the month name already selected/in view, which is based on the pMonthSelected parameter. Ie if the month selected is 01 June 2019, we want the data related to June 2016, June 2017, June 2018, June 2019
Add this to the Filter shelf, set to true, then build out the view as below, fixing the axes between 0-1 again, and then hiding. You can see the values of the Month(Date)) displayed, and it’s this field that will be passed via a parameter action. It can be moved to Detail.
COLOUR:Selected Year
YEAR([pMonthSelected])=YEAR([Date])
Add this to the Colour shelf and adjust accordingly.
When added to the dashboard, the associated parameter action required is :
When building the month selector, we need a similar filter to ensure the months shown are all associated to a single year
Filter Year
YEAR([pMonthSelected]) = YEAR([Date])
Add this to the Filter shelf set to True.
The layout of the Months, requires some additional fields to determine which row and column the month should be positioned in.
Month Cols
IF (DATEPART(‘month’,[Date]) %4) =0 THEN 4 ELSE (DATEPART(‘month’,[Date]) %4) END
DATEPART returns the integer value of the month, ie a number between 1-12, so this calculation is using the modulo (%) function to position based on the remainder when dividing the month number by 4.
Month Row
IF DATEPART(‘month’,[Date]) <=4 THEN 1 ELSEIF DATEPART(‘month’,[Date]) <= 8 THEN 2 ELSE 3 END
Adding these to the view as follows gives us a grid
Note the blue Month(Date) on the Text shelf is showing just the month name ie ‘January’, and has been formatted to it’s abbreviation. The green Month(Date) on the Detail shelf is storing the full month year ie January 2019.
COLOUR:Selected Month
[pMonthSelected]=[Month Date]
Add to the colour shelf and adjust accordingly.
You can then hide the Month Col & Month Row fields from displaying.
When added to the dashboard, the associated parameter action required is
Note You will need to apply the same ‘true=false’ trick described earlier to both these views using filter actions to prevent them from highlighting on selection.
Hiding the year/month selector
On the dashboard add a vertical container, then place the Year Selector sheet and the Month Selector inside.
Remove the chart titles, set the background of the container to white, then set the container to be floating and select the container option to Add Show/Hide Button.
You’ll need to spend some time adjusting the size and the position of the floating container once you have the layout of all the other objects finalised.
Building the KPI & Trend Chart
I’m going to try to be brief with this section. The above is A LOT to take in and write!
Selected Period
This is simply using the Date Selection Start & Date Selection End fields to display in a text field
KPI BANs
We need to be able to filter the data being displayed based on the selection
Dates to Show
[Date]>= [Date Selection Start] AND [Date]<= [Date Selection End]
This is added to the Filter shelf and set to True.
After that we’re just building a simple Text based viz utilising Measure Names & Measure Values as shown below
And finally the trend chart, again needs to be filtered by Dates To Show = True, and is a simple line chart plotting Date against the measures required as below
When added to the dashboard, these 2 sheets should be placed side by side in a horizontal container, both set to fit entire view. The right padding should be set to 0 for the KPI chart and the left padding set to 0 for the trend chart. This will have the effect of the horizontal lines for each chart joining together to make it look like a single chart.
Phew! I’m beat! My published viz is here. Keeping fingers crossed for a challenge that is easier to blog about next week 🙂
Donna + DataViz 