It was Kyle’s turn to set the challenge this week, and took inspiration from a work based scenario he had encountered. As a result 3 fictitious data sets were provided, and a viz was required to be built against each of them.
Building the Marketing Campaigns Gantt chart
All 3 vizzes are tied together by 2 parameters – the highlight date and number of days, so lets’ start with defining those
pHighlightDate
date parameter defaulted to 01 Dec 2023
and
pDays
integer parameter defaulted to 120
On a new sheet, show both of these parameters.
Using the Campaign data source, add Campaign to Rows and Start Date to Columns as a continuous exact date (green pill).
To define the width of each mark, we need
Duration
DATEDIFF(‘day’, [Start Date], [End Date])
Add this to the Size shelf, then add Category to the Colour shelf and adjust accordingly.
Add Start Date as a discrete exact date (blue pill) to Rows and position in front of the Campaign pill. This will order each row based on the Start Date ascending.
Hide the Start Date pill on Rows (uncheck Show Header). Add End Date as a continuous attribute to Tooltip, and amend the tooltip as required.
Add pHighlightDate to Detail, then right click on the Start Date axis and Add Reference Line. Add a dotted line for the entire table based on the pHighlightDate field.
Remove all row and column dividers; delete the axis title (right click > edit axis) and hide the Campaign column heading (right click > hide field labels for rows). Format the Campaign numbers so they are aligned right.
Update the title of the sheet, and include the details for the legend title within
Finally, we need to filter the rows shown based on whether the campaign was running within the window based on the number of days before/after the highlight date. We need
Add Campaigns to Include to the Filter shelf and set to True. Test the viz changes as the parameters are changed.
Building the Experiments Gantt Chart
This is built in very much the same way as the above using the Experiments data source instead. In this case, Start Time and Experiment ID will be on Rows, Start Time on Columns and Status on Colour,
A Duration field should be on Size, but the calculation needs to be slightly different to handle those records where there is a start but not an end. In this case, we assume the experiment is still ongoing, so set the end to ‘today’.
should be added to the Filter shelf, and set to True – at this point all the experiments that didn’t have a start date either will disappear. Add a title and the reference line and format as before
Building the Emails bar chart
On a new sheet, using the CRM data source, add Date as a continuous exact date (green pill) to Columns and Sent to Rows. Change the mark type to bar chart. Click on the Size button, and change to be Fixed.
Add Email Type to Colour and adjust. Update the Tooltip.
Create fields Window Start and Window End as before, then create
Emails to Include
[Date] > [Window Start] AND [Date] < [Window End]
and add to the Filter shelf, set to True.
Add pHighlightDate to the Detail shelf, and add a reference line as before.
Remove all row/column dividers, gridlines & axis lines; delete the axis titles; add a sheet title including the legends.
The add all 3 sheets to a dashboard – I placed them all in their own vertical container, so I could then distribute contents evenly.
As we continue global recognition month for #WOW2023, Flavio Matos introduced this challenge which displays a unit chart of wines by type.
An added twist was to provide the ability for the user to switch between English (UK) and Portuguese (Brazil) languages, and the excel data set provided a sheet with the data per language.
After completing my solution, I checked out Flavio, and found he built the chart for each language (and so data source) in 2 separate sheets, added to 2 different dashboards and used navigation button with a flag image to switch between 2 dashboards. I chose to go a different route, one that didn’t mean duplicating the viz. This means that if I had to alter the viz in future for some reason, I’m only doing it once.
Modelling the data
To build in a single sheet, I needed to have the data for two languages combined into one table. Connect to the excel workbook and add the Wine sheet to the canvas, then add a relation to the Vinho sheet and set the relationship to match on the Wine field from each sheet.
Building the Viz
We need a parameter to manage the language selection
pCountry
string parameter defaulted to ‘UK’
This parameter is then used to determine which of the fields we need to use on the viz, and these are determined through calculated fields.
Country to Display
IIF([pCountry]=’UK’, [Category],[Family])
Add this to Rows then add Abreviation to the Detail shelf. Sort the Country To Display field by the count of the Abbreviation field descending.
Change the Mark type to Square then add Abreviation to the Label shelf. Adjust the sheet to Entire View and then align the label to be middle centre.
Create a parameter to store the list of English options that can be selected
pOptions-UK
string parameter containing the list of options (taken from the requirements page), with All listed first. Default to All.
Then create a version to store the Portuguese options
pOptions-Portuguese
string parameter containing the list of options (taken from the requirements page), with Todos listed first. Default to Todos.
To flag the wines that are related to the options selected, we need
Wine has Tag
IF [pCountry] = ‘UK’ THEN IF [pOptions-UK] = ‘All’ THEN TRUE ELSEIF CONTAINS([Tags],[pOptions-UK]) THEN TRUE ELSE FALSE END ELSE IF [pOptions-Portuguese]=’Todos’ THEN TRUE ELSEIF CONTAINS([Tags (Vinho)],[pOptions-Portuguese]) THEN TRUE ELSE FALSE END END
This returns true or false based on what country has been selected, and in turn what country specific option has been selected. If the wine is tagged with that option, or all wines have been selected then true is returned, otherwise false.
And then using this field we can determine how to colour the squares.
Colour
IF [Wine has Tag] THEN IF [pCountry]=’UK’ THEN [Type] ELSE IF [Type] = ‘Red’ THEN ‘Tinto’ ELSEIF [Type] = ‘Sparkling’ THEN ‘Espumante’ ELSEIF [Type] = ‘White’ THEN ‘Branco’ ELSE [Type] END END ELSE IIF([pCountry]=’UK’, ‘No Highlight’,’Nao Harmoniza’) END
This is slightly more detailed as the Type field in each of the excel sheets are both stored in English, but the legend in the viz shows the types in Portuguese when that language is selected. Ideally the Type should have just come straight from the data source.
Add the 3 parameters to the view, and then add Colour to the Colour shelf. Set the pCountry parameter to UK, and choose Appetizers from the pOptions-UK parameter. Adjust the colours to suit. Manually sort the colour legend options, so the colours are listed Red > Rose > Sparkling > White > No Highlight.
Then clear the pCountry parameter (or set it to any value other than UK) and set the pOptions-Portuguese parameter to Aperitivos. Once again adjust colours as required and re-order.
The tooltips need to be language specific, and only display for the wines that match the options chosen. For these we need the following calculated fields
Tooltip – Wine
IF [Wine has Tag] THEN [Wine (Wine)] ELSE ” END
Tooltip – Food Pairing
IF [Wine has Tag] THEN IIF([pCountry]=’UK’,[Food pairing],[Food pairing (Vinho)]) ELSE ” END
Tooltip – Food Pairing Label
IF [Wine has Tag] THEN IIF([pCountry]=’UK’,’Food pairing:’, ‘Harmoniza com:’) ELSE ” END
Tooltip – Notes
IF [Wine has Tag] THEN IIF([pCountry]=’UK’,[Notes],[Notes (Vinho)]) ELSE ” END
Add all four of these fields to the Tooltip shelf and adjust accordingly
Finally tidy up the formatting by removing row dividers and hiding the Country to Display column heading (hide field labels for rows).
Building the Country Selector
For this I needed to add the UK and Brazilian flags a custom shapes to my Tableau repository. I just sourced some images via my favourite search engine and added them to my repository as per the instructions here.
On a new sheet I then added Abreviation to Columns and also to Filter and just filtered to the first 2 options Ab and Ag. I changed the mark type to shape. I then created the field
Country Selector
IIF([Abreviation]=’Ab’,’UK’,’Brazil’)
and added this to the Shape shelf, and selected the two flags.
Remove the row dividers and hide the header (uncheck show header on the Abreviation pill).
Building the dashboard
Add the sheets to your dashboard and display both the options parameter and the colour legend. I used layout containers for most of the arrangement, but floated the colour selector viz. I then added the title in English and Portuguese in two separate text boxes. I also hid the title of the colour legend, and add 2 different text boxes with the colour legend title in English and Portuguese. My layout looked like
To only show the sections based on the language selected, we need the following fields
Country is UK
[pCountry]=’UK’
Country is Brazil
[pCountry]<>’UK’
On the dashboard, select the text box which contains the English title, then on the Layout tab, select the Control visibility using value checkbox, and choose the Country is UK field.
Repeat this for all the other objects – the other title, the food selector parameters and the colour legend titles, choosing the Country is UK or the Country is Brazil option as appropriate.
To switch the language, we need to add a dashboard parameter action
Select Country
On selection of the Country Selector viz, set the pCountry parameter by passing in the value from the Country Selectior field.
The final step is to stop the country flags from being ”greyed out’ ‘highlighted’ when one is selected (ie the other flag ‘greying out’).
Create a fields
True
TRUE
False
FALSE
and add these 2 fields to the Detail shelf of the Country Selector worksheet.
The back on the dashboard, add a dashboard filter action
Country Selector – Unhighlight
on selection of the Country Selector sheet on the dashboard, target the Country selector sheet directly, passing the fields True = False. Show all values if the selection is cleared.
And with that, the viz should be complete. My published version is here.
Erica set this challenge this week, an extension of the classic actual vs target visual that is very common in business dashboards. She provided a customised data set based on Superstore Sales which included some target values.
Building the basic viz
Add Order Date to Filter shelf and restrict to the Year 2023 only. Then add Order Date to Columns and set to the discrete (blue) month level. Add Profit to Rows. Change the mark type to bar.
Add Target Profit to Rows. Change the mark type on the Target Profit marks card to Gantt Bar. Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card. Adjust the colour of the gantt bar to grey.
Note – it is possible to add the Target Profit as a reference line. However the width of the line will span the whole width of the ‘space’ allowed for a single month, and can’t be adjusted. Putting Target Profit on its own axis means the width of the bars can differ from the width of the gantt bar which in turn differs from the profit tolerance area we’ll add next.
Adding the tolerance bands
Create a new parameter
pTolerance
Float value defaulted to 0.05 that is displayed as % to 0 dp. Set the range from 0 to 1 with 0.01 increments.
Then create
Target Tolerance Min
SUM([Target Profit]) * (1-[pTolerance])
and
Target Tolerance Max
SUM([Target Profit]) * (1+[pTolerance])
Add both these fields to the Detail shelf on the All marks card. Right click on the Profit axis and Add Reference Line. Add a reference band per cell, which gors from the Target Tolerance Min to the Target Tolerance Max field. Set all Labels & Tooltips to None. Fill the band with a light grey.
Colouring the bars
Create a calculated field
Colour – Bar
IF SUM([Profit]) < [Target Tolerance Min] THEN ‘red’ ELSEIF SUM([Profit]) > [Target Tolerance Max] THEN ‘blue’ ELSE ‘grey’ END
Add this the Colour shelf of the Profit marks card, and adjust the colours to suit. Reduce the opacity of the colour to 85%.
Reduce the Size of the bars slightly, so they are narrower than the Gantt lines.
format to % with 0 dp, and add this field to the Detail shelf of the All marks card.
In addition, format Profit and Profit Target to be $ with 0 dp.
Add Profit, Profit Target, Order Date as a discrete (blue) pill set to the Year level, to Detail too.
Adjust the Tooltip accordingly.
The right click on the right hand Target Profit axis and uncheck show header to hide the axis. Remove all column and row dividers. Remove the title from the Profit axis, and hide the Order Date header label (right click > hide field labels for columns).
Show the pTolerance parameter and test the functionality.
Building the legend
This is a sneaky way to build a legend using the data source available. It relies on using a field that isn’t in use, that has at least 3 dimensions. In this case I chose Region.
Add Region to Columns and exclude West. Right click on the Region field in the data pane and select Aliases. Add an alias for each of the other values to marry up to the legend names.
Manually resort the Regions on the sheet so they are listed in the correct order.
Double click into the Rows shelf and type MIN(1). Edit the axis to fix it to run from 0-1. Add Region to colour and adjust colours accordingly. Reduce opacity to 85% to.
Adjust the height and width of the display and you can see how it starts to look like the required legend.
Hide the axis and remove all gridlines. Adjust the font of the header labels. Hide the Region label heading and stop tooltips from displaying.
The final step is just the then add the two sheets onto a dashboard with the pTolerance parameter displayed too.
For this week’s challenge, Kyle revisited a previous challenge from 2020, but remade it over utilising some of the newer features of Tableau, specifically Dynamic Zone Visibility. I blogged my solution to the original challenge here, so this blog will lift some of the techniques (and documentation) I employed directly.
Define the parameters
The first step in this challenge is to define all the parameters needed, these being :
pDateSelector
A string parameter just set to contain the value Last 30 Days
This parameter will be set via a Parameter Action, so there is no need to define this a list with all the options.
pDays
An integer parameter defaulted to 120
pStartDate
A date parameter defaulted to 01 Jan 2023
pEndDate
Another date parameter defaulted to 01 Aug 2023
The chart needs to adjust based on a measure selected, so we need to capture the measure option selected
pSelectedMeasure
string parameter defaulted to Sales
This parameter will be set by a parameter action, so again there is no need to actually list the possible values.
Finally, I also created a parameter
pToday
Date parameter defaulted to 02 May 2023.
The requirements indicate the information displayed should be restricted to ‘today’, using the TODAY() function. However given the data set is static, if I use TODAY() and look at this in a year’s time, nothing will show. So instead I have ‘hardcoded’ ‘today’ using this parameter.
Using this parameter I then created
Order Date < Today
[Order Date] <= [pToday]
and added this as a data source filter set to True. This ensures that all the sheets I build is then automatically ignoring and data where the Order Date is 3rd May 2023 onwards.
Building the time-series chart
We need to determine which measure to show based on the selection made by the user and stored in the pSelectedMeasure parameter. This will be Sales,Profit or a count of the number of orders. First we need to create
Count Orders
COUNTD([Order ID])
and then we can create
Measure to Display
CASE [pSelectedMeasure] WHEN ‘Sales’ THEN SUM([Sales]) WHEN ‘Profit’ THEN SUM([Profit]) WHEN ‘Orders’ THEN [Count Orders] END
Add Order Date set to the continuous day level (green pill) to Columns and Measure to Display to Rows. This gives us all data from the earliest day in the data set up to 2nd May 2023.
We need to restrict this based on the ‘date selector’, so create
In Timeframe
CASE [pDateSelector] WHEN ‘Last 30 Days’ THEN [Order Date]>DATEADD(‘day’,-30,[pToday]) WHEN ‘Last N Days’ THEN [Order Date]>DATEADD(‘day’,-1*[pDays],[pToday]) ELSE [Order Date]>=[pStartDate] AND [Order Date]<=[pEndDate] END
If ‘last 30 days’, get the data that is greater than 30 days ago; if ‘last n days’, get the data that is greater than the last ‘n’ days ago, otherwise get the data between the start & end dates specified.
Add this to the Filter shelf and set to True.
Add another instance of Measure to Display to Rows, make dual axis and synchronise axes. Change the mark type of the first marks card to be Area and set the other to explicitly be a line.
Add pSelectedMeasure to the Colour shelf of the All marks card. Adjust the colour of the ‘Sales’ value accordingly.
If you’re not already displaying it, show the pSelectedMeasure parameter input, and manually change the value by typing in ‘Profit’. The chart will change colour, so again adjust accordingly. Repeat the process by typing in ‘Orders’.
Set the parameter back to ‘Sales’.
The tooltip needs to display different formatted values, so we need a couple of fields to handle this
Tooltip- Sales or Profit
IF [pSelectedMeasure] <> ‘Orders’ THEN [Measure to Display] END
Format this to $ with 0 dp, and set to use ( ) when value is negative.
Tooltip – Orders
IF [pSelectedMeasure] = ‘Orders’ THEN [Measure to Display] END
Format this to a number with 0 dp.
Add both these fields to the Tooltip shelf of the All marks card, and adjust the tooltip accordingly, positioning the two ‘Tooltip’ fields directly adjacent to each other
Hide the right hand axis (right click, uncheck show header), and edit the left hand axis to set the axis title to be sourced from the pSelectedMeasure parameter
Remove all gridlines, zero line, axis ticks etc and row/column dividers, and edit the date axis to remove the axis title. Name the sheet ‘Chart’ or similar.
Building the BANs
Add Measure Names to Columns
Add Measure Names and Measure Values to the Text shelf
Add Measure Names to the Filter shelf and select just the 3 measures we’re interested in.
Reorder the columns to match the requirement
(Optional) Change the mark type to shape and set the shape to be a transparent shape (see this blog post for more details)
Change the formatting of the Measure Names and Measure Values on the Text shelf to set the size of the font to suit, and align middle, centre
Format the Sales and Profit measures to be $ with 0 dp and ( ) for neg values.
Add Measure Names to the Colour shelf and adjust colours to match the ones used on the line chart. (If you’ve used the transparent shape, edit the label to set the font to match mark colour).
Format the display to remove the row lines
Hide the Measure Names heading (right-click the Measure Names pill on the Columns shelf and unselect Show Header).
Add In Timeframe = True to the Filter shelf to restrict the data.
Uncheck the show tooltip option from the Tooltip shelf to stop the tooltip from displaying.
Name the sheet ‘BANs’ or similar
Building the Date Selector
On a new sheet, double click into the Columns shelf and type in MIN(0.0) Change the mark type to Shape.Create a new field
Label: Last 30 Days
‘Last 30 Days’
And add this to the Label shelf.
Create anew field
Is Selected Date Option – Last 30 Day
[pDateSelector] = [Label:Last 30 Days]
And add this to the Shape shelf. Adjust the shape for the ‘True’ value to be a filled circle.
Show the pDateSelector parameter and change the text in some way. This should change the ‘shape’ to ‘False’. Set this shape value to be an open circle.
Change the colour to be a dark grey, and increase the font size. Uncheck ‘Show Tooltip’.
Now type in another instance of MIN(0.0) to Columns. Create the following fields
Label:Last N Days
‘Last N Days’
and
Is Selected Date Option – Last N Days
[pDateSelector] = [Label:Last N Days]
ON the 2nd MIN(0.0) marks card, replace the fields on the shape and label shelves with these ones.
By default, the ‘last n days’ shape should be set to false, so make sure it’s an open circle. Change the value in the pDateSelector parameter to ‘Last N Days’ and the shape should now be ‘true’ – set to a closed circle.
Create a 3rd instance of MIN(0.0) on Columns and create
Label:Custom Dates
‘Custom Dates’
and
Is Selected Date Option – Custom Dates
[pDateSelector] = [Label:Custom Dates]
and repeat the process above
Edit the axis of each to be fixed from -0.2 to 2. This has the effect of ‘left aligning’ the marks.
Hide the axis, and remove the row & column dividers, all gridlines and zero lines and axis lines.
Add Measure Names to the Detail shelf of the All marks card. The right click on Measure Names in the left hand data pane and select Alias. In the dialog box that presents, alias the 3 MIN(0.0) fields as per the 3 date selector options.
This step is the key to enabling the parameter action that will be set up to pass the appropriate ‘value’ into the pDateSelector parameter
Finally format the background of the whole worksheet to be grey.
Controlling the visibility of the selections
On the dashboard, I used a vertical layout container to add my title, the BANs and the Chart. Between the title and BANs, I added a horizontal container. On the left side of that I added the Date Selector sheet. On the right I then had another vertical container which contained other containers to display the start & end data parameters. It took a bit of ‘twiddling’ to get everything where I wanted.
I added the pDays parameter as a floating object and positioned it below the Last N Days option. I used the control visibility using value option to set this to display based on the value of the Is Selected Date Option – Last N Days calculated field.
Similarly, for the date input fields, I had the section all within a single container, so I set the visibility at the container level, rather than the object level (although you could repeat the step against all the objects you need to hide individually). For the container, I used the control visibility using value option to set this to display based on the value of the Is Selected Date Option – Custom Dates calculated field.
Adding the interactivity
To change the timeframe displayed in the BANs and chart, create a dashboard parameter action
Set Date Selection
On select of the Date Selector sheet, target the pDateSelector parameter, passing though Measure Names. When the selection is cleared, reset to ‘Last 30 Days’.
To change the measure displayed in the chart, create a dashboard parameter action
Select Measure
On select of the BANs sheet, target the pSelectedMeasurew parameter, passing through Measure Names. When the selection is cleared, reset to ‘Sales’.
Prevent the BANs and Selected Date option from being selected/highlighted
By default, clicking on one of the BAN numbers, or selecting an option in the date selector, will leave the option chosen ‘highlighted’ or ‘selected’ while the other options are ‘faded out’. To
Create two calculated fields
True
TRUE
False
FALSE
Add both these fields to the Detail shelf of the BANs sheet and the All marks card on the Date Selector sheet.
Then on the dashboard, create a dashboard filter action
Deselect BANs
On select of the BANs sheet on the dashboard, target the BANs sheet directly, passing through selected fields where True = False. Show all Values when the selection is cleared.
Repeat the exact process for the Date Selector sheet, creating a dashboard filter action called Deselect Date Selector.
You should now have a complete dashboard. My published viz is here.
This week’s #WOW challenge was set by me and born out of a client requirement to compare store locations via selections from a map. I adapted it to use our favourite Superstore dataset (v2022.4).
The core requirement is to be able to make a selection on the map and see how sales compare to the average of all the other states. There is more to this challenge, but it was too much for one week, so I’ve broken it into 2 parts. This is part 1. In a few weeks time, I’ll be building on this solution for a part 2.
Building the basic hex map
The requirements provide a link to here to get the relevant files needed to complete this challenge and build the hex map – this includes the 2022.4 version of Superstore, the Hex map template, the hexagon shape file and a transparent shape file (more on that one later).
Using the hex map template sheet provided, relate the Orders Superstore data to the hex map sheet, relating State/Province to State.
Then on a new sheet add Column to Column, Row to Row and State to Detail. Edit the Row axis, and reverse the scale.
Change the shape of the mark to be a hexagon (use the provided shape if need be and add to a custom shape palette), and increase the size of the marks. Add Sales to Colour and change to use the Grey sequential colour palette, and adjust the opacity to 80%. Add Country/Region to the Filter shelf and select United States. This will remove Alaska and Hawaii that don’t have any sales and aren’t in the Superstore data set.
Add Abbreviation to the Label shelf and align centrally, Adjust the font size if need be.
Identifying the selected state
We need to be able to capture the state that has been selected ‘on click’. This will be driven by a dashboard action.
When I first built this concept for a client, the natural first step was to utilise sets and set actions; that is capture the selected state in a set, and then colour the map, build the other charts and logic based on the existence in the set. However this method does caused some issues when I tried to prevent the highlighting later, so I chose to use a parameter and parameter actions instead.
Firstly we need a parameter that will be used to capture the state selected ‘on click’
pSelectedState
string parameter defaulted to <empty string>
We can then create
Is Selected State
[State] = [pSelectedState]
As we want to retain the colour by sales on the existing map, we need to make a dual axis. Show the pSelectedState parameter and type in ‘Florida’.
Add another instance of Column to Columns. Remove the Abbreviation label from the second marks card, and replace the Sales field on the Colour shelf with the Is Selected State field. Increase the opacity on this mark to 100% Set the colours as follows :
True- teal : #66b3c2
False- pale grey : #d3d3d3
Additionally, add Is Selected State to the Shape shelf of the second marks card. Set the True value to use the same hexagon shape, but set the False option to use a transparent shape (a transparent shape file is provided in the g-drive, and needs to be added as a custom shape).
This should make all the other states look like they disappear.
Make the chart dual axis, and synchronise the axis.
Format the Sales to be $ with 0dp, then add to the Tooltip shelf of the second marks card and adjust the tooltip. Remove all gridlines/row & column dividers and hide the axes.
Adding the Interactivity
Create a dashboard sized 1000 x 600.
Add a vertical container and add a text field to create the title. Below the title, add a horizontal container. Add the hexmap sheet into the horizontal container, then add a blank object to the right of it. Remove any legends etc that automatically get added. Set the width of the hexmap object to be fixed to 600 px
and set the height of the horizontal container the hex map sits within to 445px
Show the colour legend and set it to floating, and position bottom left.
Add a parameter action to set the selected state on click
Set Selected State
On selection of the Hexmap sheet, pass the State field into the pSelectedState parameter. Set the value to <empty string> when the selection is cleared.
If we click around on the dashboard page, we can see the colours being set, but everything is fading out on selection. To prevent this, create 2 calculated fields
True
TRUE
False
FALSE
Add these to the Detail shelf of the All Marks card of the hex map sheet. Then back on the dashboard, add a filter dashboard action
Deselect Map Marks
On select of the hex map object on the dashboard, target the hex map sheet itself, setting selected fields such that True = False. Show all values where selection cleared.
Now if we click on the dashboard, the shapes shouldn’t fade into the background. However we can’t ‘unselect’ the state and get back to its original state. This is because the filter action we added to stop the marks from fading actually unselected the mark, so when we click again, we’re not undoing any selection.
To resolve this, we need another calculated field.
State for Param
IF [State] = [pSelectedState] THEN ” ELSE [State] END
If the state being clicked is the one already captured in the parameter, then set the field to <empty string>, else set the fields to the state being clicked.
Add this to the Detail shelf of the All marks card on the hexmap sheet, and then update the Set Selected State parameter action to pass the State For Param field into the parameter instead of the original State field..
Now when the map is first loaded, the State For Param field contains the name of the state, so that is passed into the parameter on click. As the parameter now has a value, the State For Param field changes for the selected state to be <empty string>, so if the same state is clicked, <empty string> is then passed into the parameter and the view resets.
Building the calculations for the other charts
On a new sheet, add State to Rows, Sales to Text and add Country/Region = United States to Filter.
When a state has been selected, we want to display the state against the selected row, otherwise we want to display the text ‘Other States (Avg)’ .
State Label
IF [Is Selected State] THEN [State] ELSE ‘Other States (Avg)’ END
Now we need to display a different value for the sales measure depending on whether its the selected state or not.
Sales To Display
IF ATTR([Is Selected State]) THEN SUM([Sales]) ELSE SUM([Sales])/COUNTD([State]) END
If the state is the selected one, then use the sales, otherwise average the total sales over the number of states that make up the sales. Format this to $ with 0dp.
Note – in hindsight, this could have just been SUM([Sales])/COUNTD([State]) even for the single state, as since the count of state will be 1, this would just equate to the SUM([Sales]) itself.
Add this to the table, and remove State from the display
Building the bar chart
Duplicate this sheet (as we want to retain the filters), then move Sales To Display to Columns, and add Category to Rows in front of State Label. Add Is Selected State to the Colour shelf.
To ensure the Selected State is always listed first, even if alphabetically it comes after ‘Other States (Avg)’, add Is Selected State to the Rows shelf between Category and State Label. Manually sort it so True is always listed before False, then hide the column (uncheck show header).
Reduce the Size of the bars, remove gridlines and column dividers. Lighten the row dividers. Adjust font sizes. Hide the column labels (hide field labels for rows) and hide the axis.
Remove Sales from the Text shelf, and check Show mark labels instead. Update the Tooltip.
Building the line chart
On a new sheet add Country/Region to Filter and set to United States. Add Order Date to Columns and set to the continuous month level (eg May 2021). Add Sales To Display to Rows, State Label to Detail and Is Selected State to Colour. Manually move the values in the colour legend so that True is listed first. Adjust the tooltip
Remove the axis titles, adjust the axis fonts. Remove row/column dividers and zero lines and axis rulers.
Putting it all together
On the dashboard, add a vertical container between the hexmap and the blank object. Add the line chart and bar chart on top of each other. Remove the title for the bar chart, and update the title of the line chart to reference the pSelectedState parameter.
Remove the blank object to the right of the bar/line charts.
We need to control when the bar and line charts display, so we’ll use dynamic zone visibility for this, and for this we need another boolean field
Show Viz
{FIXED: MAX(IF [Is Selected State] THEN TRUE ELSE FALSE END)}
Is Selected State is a boolean field which essentially is 1 for True and 0 for False. If there is a state selected, the maximum value across all the data records, will be 1, so the field returns true, otherwise its 0, so false.
Use this to control visibility using value for the line and bar chart objects.
Make any further adjustments to the layout required -the size of the hex shapes may need tweaking for example. Then interact with the viz to check all is working as expected.
Sean set the challenge this week to build a custom relative date filter. He uttered the words “this week is pretty straightforward” which always makes me a bit nervous…
The premise was to essentially replicate the out of the box relative date filter. I had a good play with Sean’s solution, hovering my mouse over various fields, to see if there were any clues to get me started. I deduced the filter box, was a floating container, which stored multiple other worksheets and some parameters used to drive the behaviour.
I worked out that I think I’d need to build at least 4 sheets – 1 for the main chart, 1 to manage the date part ‘tabbed’ selector at the top of the relative date control, 1 to manage the radio button selections within the relative date control, and 1 to display the date range.
So let’s crack on.
Building the initial chart
I used the Superstore data from v2022.1, so my dates went up to 31 Dec 2022.
We need to create a line chart that shows the dates at a user defined date level – we need a parameter to control this selection
pDateLevel
string parameter containing the relevant date parts in a list, and defaulted to ‘Week’. Notice the Value fields are all lower case, while the Display As as all proper case. The lower case values is important, as this parameter will be fed into date functions, which expect the string values to be lower case.
Then we need a new date field to show the dates at the level specified
Display Date
DATE(DATETRUNC([pDateLevel], [Order Date]))
Then add this field onto Columns as a continuous exact date (green pill) and add Sales to Rows. Show the pDateLevel parameter and test the chart changes as the parameter does.
Name this sheet Chart or similar. We’ll come back to it later, as we need to ensure this data gets filtered based on the selections made in the relative date control.
Setting up a scaffold data source
So there were no additional data source files provided, and also no ‘you must do it this way’ type instructions, so I decided to create my own ‘scaffold’ data source to help with building the relative date control. Doing this means the concept should be portable if I wanted to add the same control into any other dashboards.
I created an Excel file (RelativeDate_ControlSheet.xslx) which had the following sheets
DateParts
A single column headed Datepart containing the values Year, Quarter, Month, Week, Day
Relative Options
3 columns Option ID (containing values 1-6), Option Type (containing values date part, n periods, to date) and Option Text (containing values Last, This, Next, to date)
I added both of these sheets as new data sources to my workbook (so I have 3 data sources listed in my data pane in total).
Building the Date Part Selector
From the DateParts data source, add the Datepart field to both the Rows and Text fields. Manually re-sort by dragging the values to be in the required order.
Manually type in MIN(1) into the Rows shelf and edit the axis to be fixed from 0-1. Align the text centrally.
We’re going to need another parameter to capture the date part that will be clicked.
pDatePartSelected
string parameter defaulted to ‘quarter’ (note case)
We also need a new calculated field to store the value of the date parts in lower case
Datepart Lower
LOWER([Datepart])
and then we can use these fields to work out which date part has been selected on the viz
Selected Date Part
[pDatePartSelected] = [Datepart Lower]
Add this to the Colour shelf, and adjust colours accordingly (False is white).
Remove headers, column/row dividers, gridlines and axis, and don’t show tooltips.
Finally, we’re going to need to ensure that when a value is clicked on in the dashboard, it doesn’t remain ‘highlighted’ as we’re using the colouring already applied to define the selection made.
In the DateParts data source, create new calculated fields
True
TRUE
False
FALSE
and add both these fields to the Detail shelf, along with the Datepart Lower field. We’ll refer to all these later when we get to the dashboard.
Building the Relative Date Radio Button Selector
In the Relative Options data source, create the following fields
Row
IF ([Option ID] %3) = 0 THEN 3 ELSE ([Option ID] %3) END
Column
INT([Option ID]<=3)
These fields will be used to define where each entry in our scaffold data source will be positioned in the tabular display.
We then need to get all the text displayed as is expected, but firstly we need to define the parameter that will capture n number of days, which is incorporated into the label displayed
pSelectPeriods
integer parameter
We can now create
Option Label
IF [pDatePartSelected] = ‘day’ THEN IF [Option ID]=1 THEN ‘Yesterday’ ELSEIF [Option ID] =2 THEN ‘Today’ ELSEIF [Option ID] = 3 THEN ‘Tomorrow’ ELSEIF [Option Type] = ‘n periods’ THEN [Option Text] + ‘ ‘ + STR([pSelectPeriods]) + ‘ ‘ + [pDatePartSelected] +’s’ ELSE UPPER(LEFT([pDatePartSelected],1)) + MID([pDatePartSelected],2) + ‘ ‘ + [Option Text] END ELSE IF [Option Type] = ‘date part’ THEN [Option Text] + ‘ ‘ + [pDatePartSelected] ELSEIF [Option Type] = ‘n periods’ THEN [Option Text] + ‘ ‘ + STR([pSelectPeriods]) + ‘ ‘ + [pDatePartSelected] +’s’ ELSE UPPER(LEFT([pDatePartSelected],1)) + MID([pDatePartSelected],2) + ‘ ‘ + [Option Text] END END
This looks a bit convoluted and could possibly be simplified… it grew organically as I was building, and special cases had to be added for the ‘yesterday’/’today’/’tomorrow’ options. I could have added more detail to the the scaffold to help with all this, but ‘que sera’…
Add Column to Columns and Row to Rows. Add Option ID to Detail and Option Label to Text. Manually re-sort the columns so they are reversed
We’re going to need to identify which of the options has been selected. This will be captured in a parameter
pOptionSelected
integer parameter defaulted to 2
and then we need to know if the row of data matches that selected, so create a calculated field
Selected Relative Date Option
[Option ID] = [pOptionSelected]
Manually type in to columns Min(0.0), then change the mark type to shape and add Selected Relative Date Option to the Shape shelf, and amend the shapes to be an open and filled circle. Change the colour to grey.
As before, remove all headers, axis, gridlines, row/column dividers and don’t show the tooltip. Also we’ll need those True and False fields on this viz too – you’ll need to create new calculated fields in this data source, just like you did above, and then add them to the Detail shelf.
Restricting the dates displayed
Now we need to work on getting the line chart to change based on the selections being made that get captured into parameters. But first we need one more parameter
pAnchorDate
date parameter, defaulted to 31 Dec 2022
From playing with Sean’s solution, what date is actually used when filtering can vary based on the date part that is selected. Eg if you choose Month, next 2 months, anchor date of 15 Dec 2022 and choose to display data at the day level, you’ll see all the data for the whole of December rather than just up to 15 Dec (there is no Jan 23 data to display – so you actually only see 1 month’s worth of data). Based on these observations, I created another calculated field to run the filtering against.
CASE [pOptionSelected] WHEN 1 THEN ([Order Date] >= DATEADD([pDatePartSelected],-1,[Compare Date]) AND [Order Date]< [Compare Date])
WHEN 2 THEN ([Order Date]>= [Compare Date] AND [Order Date] < DATEADD([pDatePartSelected], 1, [Compare Date]))
WHEN 3 THEN ([Order Date]>= DATEADD([pDatePartSelected],1,[Compare Date]) AND [Order Date] < DATEADD([pDatePartSelected],2,[Compare Date]))
WHEN 4 THEN ([Order Date]> DATEADD([pDatePartSelected],-1*[pSelectPeriods],[Compare Date]) AND [Order Date]<= [Compare Date])
WHEN 5 THEN ([Order Date] >= [Compare Date] AND [Order Date]< DATEADD([pDatePartSelected],[pSelectPeriods],[Compare Date]))
WHEN 6 THEN ([Order Date] >= [Compare Date] AND [Order Date]<= [pAnchorDate]) ELSE FALSE END
This returns a boolean true or false. Add this field to the Filter shelf of the line chart and set to True.
If you show all the parameters on the view, you can play around manually altering the values to see how the line chart changes
Building the data range display
On a new sheet, add Dates To Show to the Filter shelf and set to True. Then add Order Date as a discrete exact date (blue pill) to Text and change the aggregation to Min. Repeat adding another instance of Order Date and change the aggregation to Max. Format the text accordingly.
Note at this point, I have reset everything back to be based on the original defaults I set in all the parameters.
Finally, there’s one additional sheet to build – something to display the ‘value’ of the option selected in the relative date filter control.
On a new sheet, using the Relative Options datasource, add Selected Relative Date Option to Filter and set to True. Then add Option Label to Text. Add row and column dividers, so it looks like a box.
Putting it all together
On a dashboard, add the Chart sheet (without title). Position the pDateLevel parameter above the chart, and add the input display box we built last, next to it (remove the title). Add a title to the whole dashboard at the top. Then remove the container that contains all the other parameters etc which don’t want to display.
You should have something similar to the below.
Change the title of the pDateLevel parameter and then adjust the input box so it looks to be in line – this can be fiddly, and there’s no guarantee that it’ll be aligned when it gets published to server. You may have to adjust again once published. You can try using padding – ultimately this is a bit of trial and error.
Now add a floating vertical container onto the sheet (click the Floating button at the bottom of the Dashboard panel on the left and drag a vertical object onto the canvas). Resize and position where you think it should be. Add a dark border to the container and set the background to white.
Now click the Tiled button at the bottom of the dashboard panel on the left, and add the Date Part Selector sheet into the container. Remove the title, and also remove any legend that automatically gets added.
Now add a line : add a blank object beneath the date part selector. Edit the outer padding of this object to be 10 px on the left and right and 0 px on the top and bottom. Change the background colour to grey, then edit the height of the object to 1px.
Now add the radio button chart beneath the line (fit entire view)., and again delete any additional legend, parameters added
Now add a Horizontal container beneath the radio button chart. Then click on the context menu of the radio button chart and select parameter > pAnchorDate to display the anchor date on the sheet. Hopefully this should land in the horizontal container, but if it doesn’t, you’ll have to move it. Add the pSelectPeriods parameter this way too.
Change the titles of these parameters as required, then add another line blank object using the technique described above, although delay changing the height for now.
Add the Date range sheet below, and fit to entire view, and now you can edit the height of the line blank object to size 1px.
Next select the container object (it will have a blue border when selected like below) and use the context menu to select Add Show/Hide Button
This will generate a little X show/hide floating object. Use the context menu of this object to Edit Button
I used powerpoint to create the up/down triangle images, which I then saved and referenced via the ‘choose an image’ option
I then added a border to this option and positioned it (through trial and error) where I wanted it to be.
Adding the interactivity
The final step is to now add all the required dashboard action controls.
At least, this is the final step based on how I’ve authored this post. In reality, when I built this originally, I added the objects to the dashboard quite early to test the functionality in the various calculated fields I’d written, so it’s possible you may have already done some of this already 🙂
We need to set which date part has been selected on the control. This is a parameter action
Set Date Part
On select of the Date Part Selector sheet, set the pDatePartSelected parameter, passing through the value of the associated Datepart Lower field.
Add another parameter to capture which radio button has been selected
Select Relative Date Option
On select of the Relative Date Selector sheet, set the pOptionSelected parameter, passing through the value of the associated Option ID field (ensuring aggregation is set to None).
Then we need to add dashboard filter actions to stop the selected options from being highlighted when clicked. Create a filter action
Deselect Dale Part Selecteor
on select of the Date Part Selector view on the dashboard, target the Date Part Selector sheet directly, passing the fields True = False, and show all values when the selection is cleared
Apply a second filter action using the same principals but with the Relative Date radio button selector sheet instead.
And with all that, the dashboard should now be built and functioning as required.
Donna + DataViz 