Candra McRae was back to set the challenge this week. I found it relatively straightforward, so if you’re relatively new to Tableau, this is quite a good challenge to start with. In this we’ll cover
Grouping the states
Applying the sort
Adding the total value
Adding the interactivity
Grouping the states
The states need to be grouped based on the initial letter. Candra stated she wasn’t expecting a large IF STARTWITH… type formula. I did it by making use of the fact characters can be converted into ASCII which provides a numerical representation of a letter, which we can then utilise. So we need
State Initial ASCII
ASCII(UPPER(LEFT([State],1)))
This takes the 1st letter of the State, ensures it is uppercase, and converts to ASCII. You can see below what this looks does
With this knowledge, we can then create
State Group
IF [State Initial ASCII] <=77 THEN ‘A-M’ ELSE ‘N-Z’ END
and you can now easily create the stacked bar chart (note, I’ve already removed the various gridlines etc)
Applying the Sort
The ultimate intention is to capture the Category a user clicks on into a parameter, so we need to define that parameter
pSelectedCategory
A string parameter defaulted to Office Supplies
Show this parameter on your sheet, so you can manually test how the sort will work by manually changing the values.
Create a new calculated field to define the sort
Sort
IF [pSelectedCategory]=[Category] THEN 0 ELSE 1 END
Then edit the sort property of the Category field that’s on the Colour shelf as below
Now change the value in the parameter box to Technology and see how the chart changes.
Adding the total value
Create a new field to store the total values
Total Sales by State Group
{FIXED [State Group]: SUM([Sales])}
Add this onto the Detail shelf, then add a reference line per line as below
Adding the interactivity
Once you’ve added the chart onto a dashboard, you need to add a parameter action which will set the pSelectedCategory parameter with the value from the Category field on select.
To prevent the selected category from ‘remaining selected’ on click, I applied the ‘true=false’ trick I use a lot.
Create a field True = true and a field False = false, then add both to the Detail shelf of the chart viz. On the dashboard add a new filter action which on select passes selected fields only setting true = false. As this condition can never be true, the filter doesn’t apply and this clears the highlight action.
And that is it for this week – short & sweet, but covers a handful of bite-size concepts. My published viz is here.
Continuing with ‘Community Challenge’ month, it was the turn of Will Perkins to set the challenge for this week; a challenge inspired by Google’s stock tracker.
By interacting with the published solution and reading the requirements, I deduced that I was likely to need 3 sheets – 1 for the Region headings & KPIs, 1 for the trend line chart, and 1 to drive the timeframe selections. The trend line chart looked like it was going to involve a dual axis combining a line and and area chart, along with ‘filled’ reference bands, although exactly how it would work I wasn’t entirely sure initially. Finally, there was going to be some ‘parameter actions’ action along with the ‘true = false’ trick to ensure selected marks didn’t remain highlighted.
But before we can tackle the actual chart build, we need to nail some of the calculations involved.
Identifying the date range to highlight
The intention of the chart is that on initial load, it has highlighted the timeframe for the last 14 days up to ‘today’. As this chart is being built with a static data set, which only has data up to the end of 2021, I chose to ‘hardcode’ my ‘today’ value into a parameter. This is so that in a year’s time when I might look at this again, I won’t be presented with a broken looking viz.
pToday
Date parameter defaulted to 20 Sept 2021
The user also has the ability to highlight/select dates on the chart itself, which will define a start and end date range. So we also need some additional parameters to capture this information.
pStartRange
Date parameter defaulted to 01 Jan 1900
Similarly you’ll need a pEndRange parameter too, also defaulted to 01 Jan 1900.
Later on we’ll define parameter actions which will ‘set’ these values based on user interaction.
With these fields, we can then define calculated fields to store the start and end dates depending on whether we’re using the defaults due to initial load (ie 14 days to today), or a user selected range.
Selected Range Start Date
IF [pStartRange] = #1900-01-01# THEN DATE(DATEADD(‘day’,-14,[pToday])) ELSE DATE([pStartRange]) END
Selected Range End Date
IF [pEndRange] = #1900-01-01# THEN DATE([pToday]) ELSE DATE([pEndRange]) END
We’re going to be plotting Order Date on our axis at the day level, and so to simplify things IMO, I created
Order Date Day
DATE(DATETRUNC(‘day’,[Order Date]))
which I then reference in the following calculated field, which is just to capture all the days within the range selected
Selected Dates To Plot
IF [Order Date Day]>= [Selected Range Start Date] AND [Order Date Day]<=[Selected Range End Date] THEN [Order Date Day] END
We can now start to build out the basic chart
Plotting Order Date Day and Selected Dates to Plot side by side you can see the date axis differ, with only the dates from 06 Sep – 20 Sep 21 displaying on the right hand side. The marks type for Selected Date to Plot is set to Area, and to get the marks to join up, you need to turn Stack Marks Off (Analysis -> Stack Marks -> Off menu).
Defining the Timeframe to Display
We’re going to use another parameter to store the timeframe value
pTimeframe
String parameter defaulted to 6 MONTHS (note the case – it’s simpler to match it to the display format that’s going to be used)
We then need a calculated field to tell us what to do with this value
Timeframe to Display
CASE [pTimeframe] WHEN ‘1 MONTH’ THEN [Order Date Day]>=DATEADD(‘month’,-1,[pToday]) AND [Order Date Day]<= [pToday]
WHEN ‘6 MONTHS’ THEN [Order Date Day]>=DATEADD(‘month’, -6, [pToday]) AND [Order Date Day]<= [pToday]
WHEN ‘YTD’ THEN [Order Date Day]>=DATETRUNC(‘year’,[pToday]) AND [Order Date Day]<= [pToday] WHEN ‘1 YEAR’ THEN [Order Date Day]>= DATEADD(‘year’,-1,[pToday]) AND [Order Date Day]<= [pToday] ELSE [Order Date Day] <= [pToday] END
This field will return true for all the dates that fall within each statement and false otherwise.
Add this field to the Filter shelf and select True.
You can test how the left hand side of the chart is affected by manually typing the different values into the parameter
Colouring the chart
The line and area charts are coloured based on whether dates fall in the selected range and whether the difference between the sales values at the start and end of the selected range is positive or not. We need several more calculated fields to work this out.
We firstly need to capture the min and max dates of the selected area for each region. Now, you initially might think that the Selected Range Start Date and Selected Range End Date fields already have these values. However there isn’t always a sale in every region for these dates. You could argue, that in that case, the sales value for that date should be 0 (ie there were no sales on that day), but to match the solution (and it was easier), we just get the min and max dates within the selected range that have a sales value for each region.
Min Selected Date Per Region
{FIXED [Region]: MIN([Selected Dates to Plot])}
Max Selected Date Per Region
{FIXED [Region]: MAX([Selected Dates to Plot])}
Pop these out into a quick view, and you can see how the dates differ per region compared to the default start & end date values
Now we want to work out the sales value on these dates
Min Date Sales
{FIXED [Region]: SUM(IF [Order Date Day]=[Min Selected Date Per Region] THEN [Sales] END)}
Max Date Sales
{FIXED [Region]: SUM(IF [Order Date Day]=[Max Selected Date Per Region] THEN [Sales] END)}
and then we can work out the difference and the % difference
Range Sales Diff
SUM([Max Date Sales])-SUM([Min Date Sales])
custom formatted to +”$”#,##0.00;-“$”#,##0.00 to show a ‘+’ prefix for positive values
Range Sales % Diff
[Range Sales Diff]/SUM([Min Date Sales])
custom formatted to ▲0.0%;▼0.0%
Now we can compute a field to use to colour the line/area chart
Colour – Trend
IF MIN([Order Date Day]) >= [Selected Range Start Date] AND MIN([Order Date Day])<= [Selected Range End Date] THEN
IF [Range Sales Diff]>= 0 THEN 1 ELSE -1 END ELSE 0 END
If we’re within the selected date range, then test to see if the value is positive (set to 1) or negative (set to -1), otherwise we’re outside the selected date range, so set to 0
Go back to the trend chart and add this field to the Colour shelf of the All Marks card (so it gets added to both sets of marks). Change it to be a discrete (blue) pill and the adjust the colours accordingly. At this point you may want to change the background colour if you’re using a white line. I’m just setting it to a light grey at this point, but eventually it’ll get set to black.
Adding the highlight band
This took a lot of thinking. I knew I’d need a reference band, but it took some time to figure out how to get the backgrounds coloured differently, since you only have the option to fill between the band with one colour.
The trick is to make use of the two date axes we have and to apply a band per pane.
But we need some more fields to make this happen.
Ref Line Start Date -ve
IF [Range Sales Diff]<0 THEN [Selected Range Start Date] END
Ref Line End Date -ve
IF [Range Sales Diff]<0 THEN [Selected Range End Date] END
Add these fields to the Detail shelf of the Order Date Day card and set to be continuous (green). Then add a reference band to this axis, applying the settings as below (note, the Line is a white dotted line, so isn’t showing up in the field setting, though you can see it on the viz).
Because the reference band has been set at the pane level, and the reference line dates are only relevant if the difference is negative, then the band is just showing on one row.
We then do something very similar, but this time we get some dates only if the difference is positive.
Ref Line Start Date +ve
IF [Range Sales Diff]>=0 THEN [Selected Range Start Date] END
Ref Line End Date +ve
IF [Range Sales Diff]>=0 THEN [Selected Range End Date] END
Add these as continuous pills on the Detail shelf of the Selected Dates to Plot card, and add another reference band to this axis instead.
Now you can set the chart to be a dual axis, synchronising the axes, and removing the Measure Names field from the All Marks card which will have automatically been added
This is the core viz, that will need further formatting before its ready to put on the dashboard – remove gridlines, borders etc, set background, remove headers. NOTE– You’ll need to manually re-sort the Regions before the field is hidden.
The KPI table
We need to build a ‘fake table’ for this, by putting Region on Rows and typing MIN(0) on Columns, then adding the Range Sales Diff and Range Sales % Diff fields to the Text shelf. We need an additional field to colour the text though.
Colour – KPI
[Range Sales Diff]>=0
Finally, I capitalised the Region values by using Aliases. This is a quick method when there aren’t many values, but otherwise I would usually create a field with UPPER([Region}).
Once again don’t forget to sort the Regions, and the apply relevant formatting.
The Timeframe Selector
Will states you can use a separate data source for this, so create the list in Excel
and then copy and paste (via the Data > Paste) menu into your workbook
On a new sheet add Date Range to Columns and Date Range to Text. The size and colour of the text differs based on which one has been selected. So create a field
Timeframe Selected
[Date Range] = [pTimeframe]
and add this field to both the Size and Colour shelves. You’ll need to adjust the settings, and hide headers, remove gridlines etc. Try to avoid touching the Text formatting directly, as you might find the Size then doesn’t adjust.
Adding the interactivity
You’ll need to use layout containers to organise all the objects on the dashboard. Then you can add the various dashboard parameter actions needed
Selecting the timeframe
Add a parameter action that on select passes the Date Range field into the pTimeframe parameter
Selecting the date range to highlight
You’ll need 2 parameter actions for this, one that passes the minimum Order Date Day selected into the pStartRange parameter, and the other that passes the maximum Order Date Day selected into the pEndRange parameter.
Deselecting the highlighted marks
By default when you click on a mark/select marks in Tableau, they are highlighted/selected and the other marks are faded, until you ‘click’ again. To stop this from happening I use a ‘true = false’ trick, that has become very common in #WOW challenges, and I’ve blogged many times before.
Create calculated fields
True
True
and
False
False
and add these to the Detail shelf of the All Marks card on the trend line chart.
Then on the dashboard add a Filter action that on select targets the sheet directly, mapping the true field to the false field. As this can never be ‘true’ the filter doesn’t apply, and the marks become unselected.
Repeat the same on the Timeframe Selector sheet.
Hopefully that’s covered all the core points. My published viz is here.
Ann Jackson’s husband Josh (@VizJosh) set the challenge this week, to build an ‘application’ to help visualise the scale of wildfires; that is when a fire is said to be 5000 acres, you can use the app to view how that compares to an area of the world you may know, so you can really appreciate just how large (or small) the fire is.
I have to hold my hand up here, and say that after reading the requirements several times, I was absolutely stumped as to where to start. We were provided with some ‘data’ to copy which consisted of 5 rows, which I duly copied and pasted into Desktop, but I then like ‘what now….?’ I knew I needed something geographic to build a map, but couldn’t understand the relevance of the 5 rows… I’ve said before I don’t use maps that often, so was unsure whether there was something I needed to do with this data. After staring at the screen for what seemed like an age, I ended up looking at the solution.
The data is just ‘dummy’ data and is just something to allow you to ‘connect’ Tableau to. You can’t build anything in Tableau without a data source. It could just have been 1 row with a column headed ‘Dummy’ and a value of 0. If it had been that, it might have been more obvious to me 🙂
Defining the parameters
Building the map
Apply button
Dashboard Actions
Defining the parameters
Ultimately the ‘data’ being used to build the viz is driven by parameters – the Location selector and the Latitude & Longitude inputs.
pLocation
An integer list parameter that stores values, but displays worded locations – wherever you choose. I opted for my hometown of Didcot in the UK alongside locations Josh had used, mainly so I could validate how the rest of the ‘app’ would work when I came to build it.
pLongitude
Float input, defaulted to the longitude of location 1 (ie Didcot) above.
I just googled Didcot Latitude and Longitude to find the relevant values
Note – Longitude W means an input of -1 * value. Similarly for Latitude S needs to be a negative input.
Then I created
pLatitude
Since we’re talking about parameters, there’s a couple more required, so lets create them now
Acres
Integer parameter defaulted to 5000
pZoom
Integer, list parameter with the values below, defaulted to 2.
Building the map
Now we have some lat & long values (in the pLatitude and pLongitude parameters), we can create some geographic data needed to build a map.
Location
MAKEPOINT([pLatitude], [pLongtitude])
This gives us the centre point which we want to build the ‘fire size’ buffer around. For this we need the calculation JOsh kindly provided :
Acres to Feet
SQRT(([Acres]*43560)/PI())
and then we can create the buffer
Fire Size
BUFFER([Location],[Acres to Feet],’ft’)
Double click on this field and it should automatically create you a ‘map’
Adjust the map ‘format’ via the Map > Map Layers menu option. I chose to set it to the dark style at 20% washout, then ticked various selections to give the details I needed (I added and removed options as I was testing against Josh’s version). I also set the colour of the mark via the Colour shelf to be pale red.
Also, as per the requirement, turn off the map options via Map > Map Options menu, and unchecking all the selections.
So this is the basic map, and you can input different lats & longs into the parameters to test things out.
The zoom had to be x times the size of the circle on the map, so achieved by
Zoom
BUFFER([Location],[pZoom] * [Acres to Feet],’ft’)
Add this a map layer (drag field onto the map and drop onto the Add a Marks Layer section that displays)
This has generated a 2nd circle and consequently caused the background map to zoom out. We don’t want this circle to show, nor to be selected, so on the Colour shelf, set the Opacity to 0%, and the Border and Halo to None. To prevent the circle from showing when you hover your mouse on the map, you need to Disable Selection of the Zoom marks card
Apply Button
On a separate sheet, double click into the space below the Marks card, and type ‘Apply’ into the resulting ‘text pill’ that displays, and then press return.
This will create a blue pill, which you can then add to the Label/Text shelf. Align the text to be middle centre
This view is essentially going to act as your ‘Apply’ button on the dashboard. When it is clicked on, we want it to take the Lat & Long values associated to the place listed in the pLocation parameter, and update the pLatitude & pLongitude parameter values.
For this, we need a couple of extra calculated fields
Location Lat
CASE [pLocation] WHEN 1 THEN 51.6080 WHEN 2 THEN 40.7812 WHEN 3 THEN 51.5007 WHEN 4 THEN 48.8584 END
Note – as before, all these values were worked out via Google as shown above.
Location Long
CASE [pLocation] WHEN 1 THEN -1.2448 WHEN 2 THEN -73.9665 WHEN 3 THEN 0.1246 WHEN 4 THEN 2.2945 END
Add both these fields to the Detail shelf of the Apply sheet.
Dashboard Actions
When you add the 2 sheets to the dashboard, you then need to add parameter actions to set the values of the pLongitude & pLatitude parameters on click of the Apply button
Set Lat
A parameter action that runs on Select of the Apply sheet, setting the pLatitude parameter with the value from the Location Lat field.
You need another action Set Long which does a similar thing by passing the Location Long field into the pLongitude variable.
Finally, you don’t want the ‘Apply’ button to look ‘selected’ (ie highlighted pale blue) once clicked. Create calculated fields True = True and False = False and add both of these to the Detail shelf on your Apply button sheet.
Then add a dashboard filter action that uses Selected Fields and maps True to False
Hopefully, this should provide you with all the core features to get the functionality working as required. Ultimately once I got out of the starting blocks, it wasn’t too bad…
Luke Stanke returned for week 43 of #WOW2020 with a challenge focussed on building KPIs for mobile consumption.
In general this looked to be (and was) less taxing than some from previous weeks, but Luke did throw in some very specific requirements which did prove to be a bit tricksy.
To deliver this solution I built 8 sheets, 1 for each KPI heading and 1 for each bar chart. The dashboard then uses a vertical layout container to arrange the 8 objects in. A filter control on each bar chart determines whether the bar chart should ‘show’ or not. When a particular bar chart is displayed it fills up the space, which makes the display look to ‘expand’. Parameter actions are used to drive the ‘expand/collapse’ functionality.
The areas of focus for this blog are
Building the KPI chart
Formatting the Bar chart
Expand / Collapse function
Ensuring the KPI isn’t highlighted on selection
Making the display work for mobile
Building the KPI Chart
Because we have text on the left and the right, then I built this as a dual axis chart.
I’m going to build the Sales KPI.
I used MIN(1) on Columns, with Mark Type of bar, and fixed the axis to range from 0 to 1. SUM(Sales) is then added to Label, right aligned and formatted appropriately.
For the 2nd axis, we’re going to use MIN(0) positioned alongside MIN(1) on Columns, and this time, set the Mark Type to Gantt. I type the word ‘SALES’ into the Label field.
We also need to display a + or – icon on this label too. This will rely on a parameter that is going to be set.
Create a string parameter Selected Measure which is just empty.
For the icon, then create
Sales – Icon
IF [Selected Measure] = ‘SALES’ THEN ‘▬’ ELSE ‘✚’ END
I use this site to get the characters I use for these types of things.
Add Sales – Icon to the Label shelf of the MIN(0) axis, and position in front of the ‘SALES’ text.
If you show the Selected Measure parameter on the screen, and enter the word SALES, you’ll see the shape change to ‘-‘. Make this a dual axis chart and synchronise axis, and you should have the basis of the KPI (once all the axis have been hidden of course).
We also need to add an additional field onto the chart that we’ll need to use with the parameter action later.
Sales – String to pass
IF [Selected Measure] <> ‘SALES’ THEN ‘SALES’ ELSE ” END
When the Selected Measure parameter is empty (or contains another value), then this field will contain ‘SALES’ otherwise it’ll be blank.
Add this onto the Detail shelf of the ‘All’ Marks card (ie it needs to exist on both axes).
You essentially need to duplicate this sheet and build instances of the calculated fields for for Profit, Margin (SUM(Profit)/SUM(Sales)), and Customers (COUNTD(Customer ID)).
Formatting the Bar chart
Luke is very specific in his requirements that the axis tick for the bars displayed needs to be centred. This means there needs to be a continuous (green) pill on the date axis.
This very tiny requirement gave me a lot of grief 😦
I initially used MONTH(Order Date) (which will return numbers 1-12), but when I formatted the axis to First Letter, I get a D (for 0) and J (for 13) at each end.
Fixing the axis to start from 1-12 doesn’t work, as this chops off part of the first and last bars.
I tried a variety of mechanisms but to no avail. A chat with my fellow #WOW participant Rosario Gauna, who was also mulling over the issue eventually provided a solution, courtesy of Annabelle Rincon.
We need to use an ‘undocumented’ formatting option of mmmmm, which is the formatting for first letter. However, this doesn’t work with the above. I need to change the axis to use an actual date field (which was one of the mechanisms I’d already tried).
This is taking every date in the data set, and ‘baselining’ it to all be on the same year (I chose 2019, but this could be any year you choose). The DATETRUNC is then setting all dates within the same month to be reported as being the 1st of the month.
Adding this to Columns instead as an exact date, and then formatting the axis and changing the Dates option to mmmmm will display the 1st letter
There is also a requirement to just show a label and tick mark for every other month, starting in Feb. You need to edit the axis, and on the Tick Marks tab
Set Major Ticked Marks to Fixed
Set the Tick Origin to 01 Dec 2018, and the interval to every 2 months (2 months after 1st Dec 2018 is 1st Feb 2019, so the F displays while the J for Jan doesn’t)
Set Minor Tick Marks to None
An additional ‘formatting’ setting I used on the bar charts was to fix the vertical axis to a number high enough to ensure the label always displayed after the bar and not in it. What this needs to be set to will vary and is only really noticeable once added to the dashboard, so may require some tweaking to get it correct.
Once again, you’ll need an instance of this bar chart for each of the measures Sales, Profit, Margin & Customers.
Expand / Collapse Function
On the dashboard, you need to use a vertical layout container, and place all the sheets in order underneath each other, so you have KPI, bar, KPI, bar etc.
Each bar chart needs a filter which will determine whether it shows any data or not. Create a calculated field
FILTER : Selected Measure
[Selected Measure]
On the Sales bar chart, make sure the Selected Measure parameter contains the value ‘SALES’, then add the Filter: Selected Measure to the Filter shelf and select the value SALES (it’ll be the only option available)
Then go to the Profit bar chart, and change the Selected Measure parameter to PROFIT. Now add the FILTER : Selected Measure to the Filter shelf and select the value PROFIT. If you switch back to the Sales bar, you’ll find the display is empty. Repeat this for the MARGIN and CUSTOMERS bar charts.
If you return to the dashboard you should find you probably have the 4 KPIs displayed and 1 bar chart showing. The final step is to add parameter actions.
For each KPI sheet, create a parameter action that targets the Selected Measure parameter by passing the relevant <Measure> – String to Pass field.
Ensuring the KPI isn’t highlighted on selection
I’ve used the True = False concept which I’ve applied multiple times to previous challenges.
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 teach KPI viz.
Then on the dashboard, add a Filter action for each KPI sheet that goes from the KPI object on the dashboard to the sheet itself, passing the values Source: True = Target: False
Making the display work for mobile
This frustrated me no end. When I click on the Phone layout option (right image below), the layout container just doesn’t behave as it does on the Default view – the space for the bar charts is retained even when there’s no data.
I tried all sorts of combinations of containers to try to resolve this, and just couldn’t get it, and I struggled to find anything online that would help. I published to Tableau Public to test what the result would actually look like on my mobile, in the vain hope it might ‘just work’ but it didn’t 😦
When Sam Epley posted his solution, and I found it worked on mobile, I had to look. and found that you could just ‘delete’ the phone layout.
A couple of gems in this one that I need to store away for future! My published viz is here.
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 🙂
Lorna set a fun ‘create your own pizza’ challenge this week to demonstrate the ability to both add and remove items in a set via the use of Set Actions, a feature introduced in v2020.2 (so you’re going to need this to complete the task).
There’s essentially 5 components to this dashboard, which I’ll guide you through
The central bar chart
The graphical product type selector on the left
The list of selected products to the right
The actual vs budget bar at the top
An indicator of how much you’re over/under budget
Central Bar Chart
The essence of this is a simple Type, Product by Price bar chart, coloured by Type. Manually move the Type field so the ‘Size’ option is at the top.
To indicate if the product is vegetarian or not, we create
Veg Indicator
IF [Vegetarian] = ‘Yes’ THEN ‘●’ ELSE ” END
which we can add to the Row shelf, and format to be coloured green (I use this site to get the shapes).
The tick is used to indicate if the product has been added to the pizza or not. Selected items will be identified by the use of a set, so we need to create one. Right-click Product and Create->Set. Tick a few options.
And similar to the Veg Indicator we can build
Selected Product Indicator
IF [Selected Products] THEN ‘✔’ ELSE ” END
Add this to the Row shelf between Type and Product.
The order of the Products listed will change. I want it to be orderedalphabetically by Product within each Type. The quickest way to resolve this, was to duplicate the Product field, add it to the Rows between the Type and Selected Product Indicator pills, and then hide it.
The final requirement for this chart, is to highlight the selected products in a different shade of the ‘base’ colour.
Add Selected Products to the Detail shelf, then change the … detail icon to the left of the pill to the colour icon. This will mean there are 2 pills on the Colour shelf, and the colour legend will change. Adjust to suit
The chart just then needs formatting to add the Price to the Label of the bar, remove the row/column lines, hide the Type column, hide the field labels, hide the axis, and adjust the width of the columns.
Product Type Selector
Lorna provided the images needed for this, which need to be saved to a new folder in the Shapes directory of your My Tableau Repository. I called my new folder simply Custom.
Then on a new sheet, add Type to Rows, set mark type to Shape and add Type to shape too.
Hide the Type heading and remove the row lines.
Selected Products List
Add Type & Product to Rows and enter MIN(1) onto the Columns. Add Selected Products to the Filter shelf to restrict the list just to those in the set.
Add Type to Colour and Product to Label, changing the font to white and centring. Fix the axis from 0-1 and hide it. Hide the Type & Product columns.
Manually reorder the Type field to be listed Size -> Crust -> Sauce -> Toppings
Actual vs Budget Bar
The budget field can be changed by the user, so we need a parameter, Budget, for this, which is an integer parameter where the display is formatted to £ with 0dp, and defaulted to 15.
Create the bar by adding Price to Columns, Selected Products to Filters and Type to Colour. Reorder the items in the colour legend to get the colours displaying in the correct order with ‘Size’ on the left and ‘Toppings’ on the right.
Add the Budget parameter to the Detail shelf, then add a Reference Line which refers to the Budget. Adjust the label displayed to be custom as shown below, and format the thickness and colour of the line to suit
Format the reference line so the text is displayed at the top right, and edit the font & size of the label displayed.
Then make the row height of the chart taller, but adjust the size of the bar to be narrower. The reference line spans the whole height of the row, so reducing the height of the bar itself provides space for the label to display without overlapping the bar.
Hide the axis and remove gridlines etc.
Finally we need to display the total price of the products selected. We can’t just add Price to Label as there are multiple segments on the bar, and this will display a Price per Type.
Instead we need a new field
Total Price Selected Products
{FIXED [Selected Products]:SUM([Price])}
Since this chart is filtered by Selected Products = True, this returns the total price of all the items in the set (selected). Add this to the Columns shelf and make dual axis & synchronise axis.
Readjust the mark types, so the Price marks card is back to a bar, and the Total Price Selected Products is Text. Remove the Measure Names pill from the Colour shelf of both cards (the dual axis will have automatically added it), and also remove the Type pill from the Colour shelf of the Total Price Selected Products card. Instead, add Total Price Selected Products to the Text shelf of this card.
The text will overlap the bar whether you align left , middle or right. The trick is to add some spaces in front of the text and then align right.
Hide the axis.
Over / Under Budget Indicator
This is just a text display, but a few calculated fields are needed so the text can be coloured differently depending on whether it’s over or under.
First we need to know the difference from budget
Diff from Budget
[Budget]-[Total Price Selected Products]
Text Budget Diff Over
IF [Diff From Budget] < 0 THEN ABS([Diff From Budget]) END
This will just display the difference if the value is negative, but the ABS function will return the number as a +ve. This field should be formatted to £ with 2 dp.
Text Budget Diff Under
IF [Diff From Budget] >= 0 THEN [Diff From Budget] END
This will just display the difference if the value is positive. Format to £, 2 dp.
Only one of these fields will ever hold a value at any time.
Similarly we need fields to show the text to display.
Over Budget Text
IF [Diff From Budget] < 0 THEN ‘Over Budget’ END
Under Budget Text
IF [Diff From Budget] >= 0 THEN ‘Under Budget’ END
On a new sheet, add Selected Products to the Filter shelf, then add all four of the above fields to the Text shelf.
Adjust the order and colour of the fields in the Label editor, setting the fields related to being ‘over budget’ to red and the ‘under budget’ fields to green.
Now we’ve got all the building blocks, we can put it all on a dashboard.
Building the dashboard interactivity
Add all the sheets in their various locations. Most were tiled, except for the Budget parameter and the Over/Under Budget Indicator sheet, which I floated.
Filter the bar chart
Add a dashboard Filter Action to filter from the Type Selector sheet to the Product Bar Chart on select, filtering on the Type field only.
Add / Remove from Set
Add a dashboard Set Action to the Chart sheet that runs from the Menu, and adds the selected item to the Selected Products set.
Create another Set Action on the same chart, which removes values instead.
And that’s the main crux of the challenge. The only addition is ‘nice’ feature to avoid the item selected from being shown as selected (ie fading out all the other items in the chart).
I reverted to the trusty true = false dashboard action which I applied to the Selector chart and the Selected Items chart.
This involves creating 2 new fields True = True and False = False and adding these to the Detail shelf of the relevant chart.
A dashboard Filter action is then added which targets itself using the fields True = False
However, when I tried to add this same feature to the main bar chart itself, the bar chart stopped working as expected. I think there was conflict between there being 2 filter actions on the same chart. I have to admit, this is where I did check Lorna’s solution, as this was a small feature that really bothered me, and I didn’t want to publish without it.
It turns out she simply used a highlight action to resolve this
I vaguely recall using this sometime ago, but the ‘true=false’ concept has become so ingrained as my ‘go to’ method, that I struggled to think of this.
And that should be it. You now have the tools to customise your own pizza 🙂
By Week 20 of #WOW2020, Tableau v2020.2 was released, so guest challenger, Sean Miller, returned with a challenge to demonstrate one of the new bits of functionality that had been released – the ability to remove values from a set via dashboard actions.
Selected states on the map are highlighted and added to the list displayed on the right; clicking a state on the list then removed it from being highlighted on the map.
This felt like it should be straightforward, which was quite a relief after the last couple of weeks, and I’m hoping this blog doesn’t take too long to write 🙂
Spoiler alert! It was straight forward – hurrah! I do really value these ‘simpler’ challenges, when the main purpose is to introduce new features and functionality. In a business environment, it’s not always possible to work with the latest release, so having these challenges as a useful working example of a new feature to reference in future, is of great benefit.
So, if you hadn’t already realised, you’re going to need v2020.2 to complete this challenge, which you can get here , or you may need to use Tableau Public instead.
Creating the State set
Double-click State, double-click Sales and change mark type to Filled Map, and you’ve got your basic starting point 🙂
We want to be able to have different colours for the states depending on whether they’re in or out of a set of selected states.
Create a Selected States set by right clicking on State and selecting Create -> Set. Choose a random set of states.
Note – in v2020.2, the Selected States set is now just listed in the top left hand ‘dimensions’ pane (which also is now no longer labelled dimensions) with a ‘set’ icon to indicate its ‘data type’, rather than being listed in a separate ‘Sets’ section towards the bottom.
Drag this set onto the Colour shelf of the map. The states selected in the set will be coloured differently from the other states. Adjust the In/Out colour legend to suit.
Remove the map features
The displayed map in the solution is very ‘clean’; all labels,non US countries etc are hidden. This is set by selecting Map -> Map Layers from the top menu, and unchecking all the pre-selected Map Layer options.
Map Tooltip
The tooltip displays more measures than just the Sales, so we need some calculated fields
# Orders
COUNTD([Order ID])
# Customers
COUNTD([Customer ID])
Add these to the Tooltip shelf and adjust the display accordingly.
Creating the State List
There are several ways to build a basic list, but to get the formatting nicer, I created it using a bar chart as follows
Type in ‘Min(1)’ to Columns
Add State to Rows
Add Selected States to Filter shelf
Add State to Text shelf
Adjust Text to read ‘x <State>’ and format to white text, and align left middle
Adjust the colour of the bar to suit, then
Uncheck Show Header from the State pill in the Rows
Adjust the axis to be fixed from 0 to 1
Hide the axis.
Change the Tooltip text to ‘CLICK TO REMOVE’
Change the title to include the instruction
Invoke the Set Actions
Add both sheets to a dashboard.
Add a Change Set Values Dashboard Action to the Map sheet as below
As you click a state or select a group of states, it/they will be added to the list.
Add another Change Set Values dashboard action, this time based off the state list as follows
As you click on a state in the list now, it will disappear from the list and the state will no longer be highlighted on the map.
You will notice though, that as you click states on the map, your selections remain ‘selected’ until you click again. To fix this we need to use a little ‘true = false’ trick to ‘automatically deselect’ the states.
Automatically deselect states
Create new calculated fields called
True
True
False
False
and add both of these to the Detail shelf on the Map sheet.
Then back on the dashboard create a Filter action as follows
Building the Bar Charts
The above has completed the main part of the challenge and demonstrates the new feature, the option to Remove values from set in the Set Action dialog.
However Sean added some extra charts to the display.
Sales Chart
Add Sales to Columns and Selected Sales to Colour. Change Sales to be a Quick Table Calculation and set it to Percent of Total.
The tooltip and the chart title have additional information so we need
Total Sales
{FIXED:SUM([Sales])}
Sales of Selected States
{FIXED: SUM(IF [Selected States] THEN [Sales] END)}
% Sales of Selected States
[Sales of Selected States] / [Total Sales]
Format all these accordingly.
Add all these to the Detail/Tooltip shelf as required and amend Tooltip and Chart Title as necessary.
Orders Chart
This is pretty much the same as above….
Add # Orders to Columns and Selected Sales to Colour. Change # Orders to be a Quick Table Calculation and set it to Percent of Total.
The tooltip and the chart title have additional information so we need
Total Orders
{FIXED:COUNTD([Order ID])}
Orders of Selected States
{FIXED: COUNTD(IF [Selected States] THEN [Order ID] END)}
% Orders of Selected States
[Orders of Selected States] / [Total Orders]
Customers Chart
This doesn’t work quite the same (I only found out after the event), as a customer can order against more than 1 state it would seem. So while you’ll still need the following
Total Customers
{FIXED:COUNTD([Customer ID])}
Customers in Selected States
{FIXED: COUNTD(IF [Selected States] THEN [Customer ID] END)}
% Customers of Selected States
[Customers of Selected States] / [Total Customers]
we need to build it as a dual axis, with % Customers of Selected States on Columns alongside Min(1)
This should be all the building blocks needed. My published viz is here.
Donna + DataViz 