Sean chose to revisit the first challenge he participated in as part of retro-month at WOW HQ. Since the original challenge in 2018, there have been a significant number of developments to the product which makes it simpler to fulfil the requirements. The latest challenge we’re building against is here.
Building the KPIs
This is a simple text display showing the values of the two measures, Sales and Profit. Both fields need to be formatted to $ with 0dp.
Add Measure Names to Columns
Add Measure Names to Filter and limit to just Sales and Profit
Add Measure Values and Measure Names to Text
Format the text so it is centrally aligned and styled apprpriately
Uncheck ‘show header’ to hide the column label headings
Remove row/column dividers
Uncheck ‘show tooltip’ so it doesn’t display
Building the map
The map needs to display a different measure depending on what is clicked on in the KPIs. We will capture this measure in a parameter
pMeasure
string parameter defaulted to Profit
Then we need to determine the actual measure to use based on this parameter
Measure to Display
If [pMeasure] = ‘Profit’ THEN SUM([Profit]) ELSE SUM([Sales]) END
format this to $ with 0 dp
Double click on State/Province to automatically generate a map with Longitude & Latitude fields. Add Measure to Display to Colour. Adjust Tooltips.
Remove the map background via the map ->background layers menu option, and setting the washout property to 0%. Hide the ‘unknown’ indicator.
Update the title of the sheet and reference the pMeasure parameter, so the title changes depending on what measure is selected.
Show the pMeasure parameter and test typing in Sales or Profit and see how the map changes
Building the bar chart
Add Sub-Category to Rows and Measure to Display to Columns. Sort descending. Adjust the tooltip.
Edit the axis so the title references the value from the pMeasure parameter, and also update the sheet title to be similar.
Building the dimension selector control
The simplest way of creating this type of control is to use a parameter containing the values ‘State’ and ‘Sub-Category’. But you are very limited as to how the parameter UI looks.
So instead, we need to be build something bespoke.
As we don’t have a field which contains values ‘State’ and ‘Sub-Category’, we’re going to use another field that is in the data set, but isn’t relevant to the rest of the dashboard, and alias some of it’s values. In this instance I’m using Region.
Right click on the Region field in the data pane and select Aliases. Alias Central -> State and East -> Sub-Category.
On a new sheet add Region to Rows and also to Filter and filter to State & Sub-Category. Manually type in MIN(0.0) into the Columns shelf. Add Region to the Label shelf and align right. Edit the axis to be fixed from -0.05 to 1, so the marks are shifted to the left of the display.
We will need to capture the ‘dimension’ selected, and we’ll store this in a parameter
pDimension
string parameter defaulted to Central
(note – although the fields are aliased, this is just for display – the values passed around are still the underlying core values).
To know capture which dimension has been set we need
State is Selected
[Region] = [pDimension]
Change the mark type to Shape and add State is Selected to the Shape shelf, adjusting so ‘true ‘ is represented by a filled circle, and ‘false’ by open circle. Set the colour to dark grey.
Change the background colour to grey, amend the text style, hide the Region column and the axis, remove all gridlines/row dividers.
Finally, we will need to stop the field from being ‘highlighted’ on selection. So create two fields
True
TRUE
False
FALSE
and add both of these to the Detail shelf. We’ll apply the required interactivity later.
Building the dashboard
You will need to make use of containers in order to build this dashboard. I use a vertical container as a ‘base’ which consists of the rows showing the title, then BANs, a horizontal container for the main body, and a footer horizontal container.
In the central horizontal container, the map and the bar chart should be displayed side by side. We need each to disappear depending on the dimension selected. For this we need
Show Map
[pDimension] = ‘Central’
and
Show Bar
[pDimension] = ‘East’
On the dashboard, select the Map object and then from the Layout tab, select the control visibility using value checkbox and select the Show Map field.
Do the same for the Bar chart but select the Show Bar field instead.
Select the colour legend that should be displayed and make it a floating object. Position where you want, and also use the Show Map field to select the control visibility using value checkbox.
Adding the interactivity
To select the different measure on click of the KPI, we need a parameter action
Set Measure
On select of the KPI chart, set the pMeasure parameter passing in the value from the Measure Names field.
And to select the dimension to allow the charts to be swapped, another parameter action
Set Dimension
On select of the Dimension Selector sheet, set the pDimension parameter, passing in the value from the Region field
Finally, to ensure the dimension selector sheet doesn’t stay ‘highlighted’, add a filter action
Unhighlight Dimension Selector
On select of the Dimension Selector sheet on the dashboard, target the Dimension Selector sheet directly, and pass values setting True = False
Hopefully this is everything you need to get the dashboard functioning. My published viz is here.
For my first #WOW2023 challenge, as an ‘official’ part of the #WOW crew, I challenged everyone to build this funnel chart. Inspiration came from a colleague who posed me the challenge of building something similar, ensuring the bars were a single entity when clicked on. In that example we’d just used Superstore data, but it wasn’t really reflective of how a funnel would typically work, so I sourced some dummy sales opportunity data and made the challenge more relevant to a real business scenario.
Building the Funnel Chart
I saw this as a table calculation exercise, though I have subsequently seen solutions that manage it all through LODs. I’ll obviously blog my solution.
Let’s start by getting the core data into a table, so we can see what we’re aiming for
The total value of all the Opportunities is $91.4M. Based on the sales pipeline process and the assumptions documented in the requirements, all of that value will have passed through the first stage – Prospecting.
There is $6.1M still sitting in Prospecting, so when we move onto the next stage, stage 2 -Qualification, the total amount that has passed through will be $91.4M – $6.1M = $85.3M
As there is $6.5M sitting in Qualification, as we move to stage 3, the total amount that has passed through will be $85.3M – $6.5M = $78.7M. A similar pattern repeats for Stage 4, but for Stage 5, Closed Won, the value that has ‘passed’ through that stage, is equivalent to itself.
If we amend the Value field to be a Running Total quick table calculation (and add Value back into the view too), we get
The Running Sum is computing down the table , with the value in each row being the sum of the preceding rows. But this is opposite to what we want. We want Stage 1 to have the biggest value, the total, and the values to get smaller as you look down the table.
To resolve this, edit the table calculation and change the Sort order to custom sort by Minimum Stage No Descending
This results in the running total to sum from the last row (Stage 6) up to the first row (Stage 1), and you can see the value against Stage 1 matches the total for the Value column.
However, this still isn’t quite what we need. While we need to consider the amount for Closed Lost in stages 1-4, we shouldn’t be including it in stage 5, Closed Won.
While excluding the Stage 6 from the view (Stage No on Filter shelf set to Exclude 6) , gives the correct value for Stage 5, we’ve now lost the amount for Closed Lost in the other rows
So, we need some additional calculations to help resolve this.
Amount Lost
{FIXED : SUM(IF [Stage No]=6 THEN [Value] END)}
This just captures the amount of Stage 6 and ‘spreads it across every row of data.
Let’s now get our running total field, baked into it’s own calculation, as this will help with other calcs
Cumulative Value Per Stage
RUNNING_SUM(SUM([Value]))
Replace the Value table calc field with this one, ensuring the table calculation settings are identical to those described above.
Now let’s add the Amount Lost onto the Cumulative Value Per Stage unless we’re at Stage 5, Closed Won
Total Amount Per Stage Inc Lost
IF MIN([Stage No]) = 5 THEN [Cumulative Value Per Stage] ELSE [Cumulative Value Per Stage] + SUM([Amount Lost]) END
Pop this into the table (checking the table calc settings are as expected) and the total amount now stored against Stage 1, is the total value of all the Opportunities.
Ok, so now we know the values, how do we plot this in such a way that it represents a funnel. I chose to do this by first working out the proportion of the total value that the ‘reverse’ cumulative value represented.
Total Value
{FIXED:SUM([Value])}
meant I could determine
Proportion of Total
[Total Amount Per Stage Inc Lost] / SUM([Total Value])
I formatted this to percentage with 0 dp.
Adding these into the table
If we plotted this information on a bar chart, we’d get this
but we need to ‘centre’ each bar in relation to the first bar to give us the funnel effect. This means we need to shift each ‘bar’ to the right, by half the amount that is the difference between 100% and the Proportion of Total.
Position to Plot
(1 – [Proportion of Total])/2
We can’t represent this with a actual bar chart, but instead we can use a Gannt chart.
Stage No to Rows
Stage No to Filter and exclude Stage 6
Stage to Detail
Position to Plot to Columns, adjusting the table calculation as previously described
Change mark type to Gantt bar
Add Proportion of Total to Size (and verify the table calc is set properly)
Hey presto! A funnel!
To finalise
add Stage to Label and align centrally. Make the font bold and match mark colour.
add Cumulative Value per Stage to Colour and reverse the colour range (via the Edit Colours dialog)
Widen each row a bit.
Fix the Position to Plot axis to start at 0 and end at 1 to ensure the funnel is centred exactly.
Add Value and Total Amount Per Stage Inc Lost to the Tooltip and adjust the tooltip text accordingly (don’t forget to sense check the table calc settings!).
Hide the Position to Plot axis, and the Stage No column. Remove all gridlines, zero lines, axis rulers /row/column banding etc.
Building the KPIs
We need a few calculated fields to store the required numbers
Won
{FIXED:SUM(IF [Stage No]=5 THEN [Value] END)} / [Total Value]
formatted to a percentage with 0 dp.
Lost
{FIXED:SUM(IF [Stage No]=6 THEN [Value] END)} / [Total Value]
formatted to a percentage with 0 dp.
Outstanding
1-([Lost] + [Won])
formatted to a percentage with 0dp.
On a new sheet add Measure Names to Columns and Measure Values to Text. Add Measure Names to Filter and select Total Value, Won, Lost, Outstanding. Manually re-order the columns.
Format Total Value to be a number displayed in millions with 1 decimal place & prefixed with $.
Add Measure Names to Text and adjust the text as required. Align the text to be centred.
Remove the row banding, and hide the column heading.
Then arrange the two sheets on the dashboard, ensuring both are set to fit entire view.
Kyle Yetter ended the #WOW2022 year with this challenge, to create a waffle chart. I haven’t built one for a while, so needed a quick google to refresh my memory.
You’ll need a ‘template’ data source which defines the grid pattern layout, and what each square represents. Every square represents a percentage point from 1% to 100%, and is positioned in a row numbered from 1 to 10 and a column numbered from 1 to 10. This information is stored in the ‘template’. You can build your own, or you can use the waffle_template.xlsx file I created that is stored here.
Connect to the Waffle_Template excel file and make Row and Column discrete dimensions (just drag them to above the line on the left hand data pane). Then add Row to Rows and Column to Columns and Percent to Text.
Building the waffle
Now connect to the Orders data from the SuperStoreSales data source. In that data source, create a new field
Consumer %
ROUND(SUM(IF [Segment]=’Consumer’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)
If the Segment is Consumer, get the sum of Sales as a proportion of the total Sales. Round the result to 2 decimal places.
Create similar fields for the other segments
Corporate %
ROUND(SUM(IF [Segment]=’Corporate’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)
Home Office %
ROUND(SUM(IF [Segment]=’Home Office’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)
Then in the Waffle_Template datasource, create the following field
Add Colour : Consumer to the Colour shelf, and adjust colours. If you get any warning messages about blending, just acknowledge.
To make the blocks, double click in the Columns shelf and type in MIN(1). Change the mark type to bar. Edit the MIN(1) axis, and fix it from 0 to 1. Remove the Percent field from the Text shelf.
Set the row and column dividers to be thick white lines, and hide the column and row headings and axis (uncheck Show Header). Uncheck Show Tooltip on the Tooltip shelf.
Name this sheet Consumer.
Then duplicate this sheet, and replace the Colour : Consumer pill on the Colour shelf with the Colour : Corporate pill. Adjust colours to suit. Name the sheet Corporate.
Then duplicate again, and repeat the process for the Colour : Home Office pill, naming the sheet Home Office.
Building the KPIs
On a new sheet, using the Orders – SuperStoreSales data source add Segment to Columns.
Create a new field
Percent of Total
ROUND(SUM([Sales]) / TOTAL(SUM([Sales])),2)
format this to percent with 0 dp and add this to the Text shelf.
Create a new field
Segment UPPER
UPPER([Segment])
and add this to the Text shelf.
Format the text and align centrally
Remove the row dividers, the Segment column labels, and uncheck Show Tooltips from the Tooltip shelf. Name the sheet BANs.
Creating the dashboard
Use a horizontal container to position the three waffle charts in. Ensure each chart is set to fit entire view. Hide the titles, and from the context menu on the container, distribute the contents evenly. Use padding on the objects to create more white space.
Add the BANs object underneath, remove the title and set to fit entire view. Adjust the heights and widths as required to ensure the waffles are spaced evenly and are square, and the BANs line up underneath.
Week 20 for #WOW2021, and Lorna Brown set the challenge based on comparing dates. The requirement was to avoid LoDs in preference of table calculations. Unintentionally, I managed to build a solution without table calcs either. This just happened based on the problem solving process I ended up taking. I figured I would encounter a need for a table calc as I progressed, but it didn’t happen.
Defining the key calculated fields
Building the BAN
Building the Line Chart
The Reset button
Adding the dashboard interactivity
Defining the key calculated fields
To drive this viz, you’ll need 2 parameters – one to store the selected date and one for the selected comparison timeframe.
pSelectedDate
pComparison
Set to be an integer (more efficient in calcs later), but display text on screen
We now need to work out what the comparison date will be for each of the selections.
We’ll start with the date last year. We’re looking for the ‘equivalent’ day of the year in the previous year. I didn’t read the hint provided, so came up with my own method based on the month and whether we’re in a leap year or not
Equivalent Day Last Year
DATE(IF ((DATEPART(‘month’,[pSelectedDate]) <=2) AND ((YEAR([pSelectedDate])-1)%4=0)) OR ((DATEPART(‘month’,[pSelectedDate])>=3) AND (YEAR([pSelectedDate])%4=0)) THEN DATEADD(‘day’,2,DATEADD(‘year’,-1,[pSelectedDate])) ELSE DATEADD(‘day’,1,DATEADD(‘year’,-1,[pSelectedDate])) END)
So what is this saying….
If the month of the pSelectedDate is Jan or Feb AND the previous year is a leap year (as it can be divided by 4 with no remainder), OR if the month of pSelectedDate is not Jan or Feb and this year is a leap year, then get the exact same date from last year, but add on 2 days, otherwise get the exact same date from last year, but add on just 1 day.
Now let’s consider the same day for last month. I played with Lorna’s solution, entering difference dates to see what the compare date came back as, and it wasn’t always clear to me what the logic was that was being used. So this is what I came up with. If the pSelectedDate was for example, the 3rd Tuesday in the month, then I wanted to get the 3rd Tuesday in the previous month. However, based on the length of months and when months start and end, some months can have nearly 5 weeks in a month, while the previous may only have 4. In my logic therefore, if there was no 5th Tuesday in the previous month, I would return the 4th Tuesday of the month. So in that instance if pSelectedDate is the 4th Tuesday in the month, I’d compare to the 4th Tuesday of the previous month. If pSelectedDate is moved to the 5th Tuesday in the same month, it would also compare to the 4th Tuesday of the previous month – PHEW! all a bit mind boggling perhaps, and it certainly took the most amount of time in this challenge.
I broke this down into multiple calculations.
DoW Selected Date
DATEPART(‘weekday’,[pSelectedDate],’Sunday’)
What is the day of the week number for the pSelectedDate? If pSelectedDate is 18th May 2021, this will be 3 (ie Tuesday).
Exact Date Prev Month
DATE(DATEADD(‘month’, -1, [pSelectedDate]))
as it says on the tin – exact same date last month, so if pSelectedDate is 18th May, this will be 18th April.
Prior DoW Prev Mth
DATE(CASE [DoW Selected Date] WHEN 1 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Sunday’) WHEN 2 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Monday’) WHEN 3 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Tuesday’) WHEN 4 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Wednesday’) WHEN 5 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Thursday’) WHEN 6 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Friday’) WHEN 7 THEN DATETRUNC(‘week’, [Exact Date Prev Month],’Saturday’) END)
If pSelectedDate is a Tuesday, then this returns the date of the Tuesday that is before the exact date last month. If pSelectedDate is Tuesday 18th May, Exact Date Prev Month is 18th April, which is a Sunday. Truncating this date to the start of the week, where the week starts on a Tuesday, returns Tues 13th April.
Next DoW Prev Mth
DATE(DATEADD(‘week’, 1, [Prior DoW Prev Mth]))
Just adds 1 week onto the above.
Equivalent Day Last Mth
IF MONTH([pSelectedDate]) = MONTH([Next DoW Prev Month]) THEN [Prior DoW Prev Mth] ELSE [Next DoW Prev Month] END
If the months of pSelectedDate and New DoW Prev Mth are the same, then use the ‘previous’ date, otherwise use the ‘next date’. This took some pen and paper to work out!
So now having worked out what the dates might be, I just plugged them into the below to get the comparison date we need.
Compare Date
DATE(IF [pComparison] = 1 THEN [Equivalent Day Last Year] ELSEIF [pComparison] = 2 THEN [Equivalent Day Last Month] ELSE DATEADD(‘week’, -1, [pSelectedDate]) END)
Further fields we then need are
Selected Date Sales
IF [Order Date] = [pSelectedDate] THEN [Sales] END
Compared Date Sales
IF [Order Date] = [Compare Date] THEN [Sales] END
% Difference
(SUM([Selected Date Sales]) – SUM([Compared Date Sales])) / SUM([Compared Date Sales])
custom formatted to ▲0.0%;▼0.0%
Building the BAN
I built this using a bar mark type with MIN(1) on Columns and the axis fixed from 0 to 1.
Then all the relevant fields were added to the Label field and organised accordingly. All date fields were formatted to the <weekday>, dd mmmm, yyyy format.
The % Difference field was added to Colour, and then the colour legend formatted as below
Building the Line Chart
The line chart only needs to show the information between the Compare Date and the pSelectedDate.
Dates to Show
[Order Date]>=[Compare Date] AND [Order Date]<=[pSelectedDate]
This needs to be on Filter shelf and set to True.
The main line chart is then just simply Order Date (exact date, continuous) plotted against Sales
For the circle markers, we can add Selected Date Sales and Compared Date Sales as ‘shared measures’ onto another axis
To get the ‘label’ points to show, we’re going to plot 2 more marks at an arbitrary point on the axis, but at the dates we care about. For this I created
Minus 500
IF [Order Date] = [pSelectedDate] OR [Order Date]= [Compare Date] THEN -500 END
Add this into the MeasureValues section, but aggregated to Avg rather than Sum
We want to label these marks, but not with the -500 value.
Selected | Compared Date Sales
IF [Order Date] = [pSelectedDate] THEN ([Selected Date Sales]) ELSEIF [Order Date] = [Compare Date] THEN ([Compared Date Sales]) END
Add this field onto the Label shelf of the Measure Values marks card and show mark labels
But we only want the labels to show against the lower marks. I have to admit I tried many things to try to make this work, but in the end had to peek at Lorna’s solution. The label setting needs to be – its the Measure Names field that is key here!
I then set the colour of the Minus 500 measure to white, so you can’t see it
This chart can then be set to dual axis (don’t forget to synchronise those axis). You may need to reset some of the mark types etc. Remove all headers/borders/gridlines.
The Reset Button
I created a Default Date field storing DATE(#2021-05-18#) and added to a sheet as below.
Adding the dashboard interactivity
On click of a point on the chart, it should set the pSelectedDate parameter. This is a parameter action.
We don’t what the chart to show the point as ‘selected’/highlighted though, so we need to use a dashboard filter action to supress this from happening.
Create fields True = True and False = False. Add these 2 fields to the Detail shelf on the line chart.
Then on the dashboard, add a dashboard filter action as below, setting the fields to map True to False.
Finally, the reset button needs to set the parameter too :
Hopefully this should be enough to help you complete this challenge. My published viz is here.
This week’s #WOW2021 challenge by Ann Jackson is focussed on dashboard design/layout, reference lines and formatting time, so that will be the focus of this blog too. I’m hoping it might be a fairly short post this week 🙂
Filtering the data
Creating the key measures, formatting time, adding average lines
Dashboard layout
Filtering the data
The data Ann provided is based on the Google Analytics data the team have harnessed related to the activity on the #WOW website. It’s a static data set, which contains data from 29 Dec 2019 up to 12 April 2021, but Ann states her solution only includes data up to 10 April 2021. I therefore added the Date field to the Filter shelf on the sheet and set it to end on 10 April 2021
Knowing I’d be building multiple sheets for this challenge, I set this filter to be a global filter by setting it to apply to worksheets – > all using this data source
Creating the key measures, formatting time, adding average lines
For this challenge, we’ll be creating a sheet for each BAN, and a sheet for each trend line depicting a measure by week, so 8 sheets in total.
For the Sessions measure, both the BAN and the trend chart are straightforward.
The BAN just shows SUM([Sessions]) on the Text shelf, appropriately formatted. The other BAN sheets are pretty much the same, but just show the appropriate measure in the appropriate colour.
The trend line displays Week([Date]) by SUM([Sessions]), with the average line added by dragging Average Line from the Analytics pane onto the chart and then formatting.
If the numbers don’t quite match up, it may be because your week is set to start on a different day. By default as I’m UK based, dates are set to start their week on a Monday. For this challenge to match Ann’s solution, your week needs to start on a Sunday. You can set this by right clicking on the data source itself and changing via the Date Properties option
To determine the average session duration, we need to build a calculated field, that then needs to be formatted to show minutes and seconds.
Average Session Duration
(SUM([Session Duration]) / SUM([Sessions]))/86400
Session Duration / Sessions will return a value in seconds. To be able to format this in the way required, we need to get the number of seconds as a proportion of a day. There are 86400 seconds in a day (60 * 60 * 24), so we divide by this.
We can then use a custom format on this field and use the nn:ss notation. NOTE not mm:ss. If you needed to format this as hours:minutes:seconds, the format would be hh:mm:ss, but mm:ss does not provide you with the right values. This video demonstrates all this, if you’re interested.
When it comes to building the trend line for this measure, the average line, can’t be added as simply in the way it could for the Sessions trend chart (at least I couldn’t get it to work that way…).
We need to build a calculated field that will show the same overall average value alongside the weekly averages. This value needs to match what’s displayed in the BAN chart.
Overall Session Duration mm:ss
{FIXED : [Avg Session Duration]}
This is returning the Avg Session Duration based on all the rows in the data. As the Date field has been added as a global filter, it is acting like a data source filter, so the dates we don’t want have been excluded from the data set that the FIXED LoD is being applied against. If the Date filter was a simple ‘quick filter’, this calculation wouldn’t work, as the data for the 11th & 12th April would also be included in the calculation.
Format this to nn:ss as well. Add this field to the Detail shelf of the trend chart, then right click on the Average Session Duration axis and Add Reference Line, and reference the Overall Session Duration mm:ss field.
For the bounce rate, we simply need
Bounce Rate
SUM([Bounces])/SUM([Sessions])
which is formatted to a percentage of 1 dp.
When building the trend line, I added the average line from the analytics pane, but that gave me a different value from my BAN. So I built
Overall Bounce Rate
{FIXED : [Bounce Rate]}
This was formatted to % 1dp, and added as a reference line as described above.
Finally the last measure, we need
Avg Time on Page
(SUM([Time on Page])/(SUM([Pageviews])- SUM([Exits])))/86400
formatted to nn:ss
and
Overall Time on Page mm:ss
{FIXED : [Avg Time on Page]}
and again formatted to nn:ss.
The charts for these are built exactly like the Average Session Duration.
Dashboard Layout
The easiest way to describe the layout I built is to show it 🙂 Note the Item Hierarchy on the left hand side of the image below.
I have a vertical container as the Main page.
The first row in this container is the title in a Text object.
The second row is a Blank object and is the yellow line. The background of the blank object is set to the relevant yellow, the outer padding is set to 0 all round, and then the height is set to 4. This gives the appearance of a thick coloured line.
The third row is another vertical container, and I’ve done this, so I can ultimately use the option to Distribute Contents Evenly on the container to ensure the horizontal container ‘rows’, which I’ll be adding into this container, are evenly spaced.
So ‘within’ the 3rd row, the 4th-7th rows are managed using a horizontal container, which in turn contains a blank object (the coloured vertical line), the BAN sheet and the trend sheet. Around each horizontal container I set the outer padding to 10 all round to give some spacing. The blank object in each ‘row’ is given the relevant background colour and set to a width of 15.
Finally, I finished off with an additional horizontal container at the bottom which is where I added my standard #WOW footer. Note this horizontal container is essentially the 4th row of the original Main vertical container though.
Hopefully I’ve provided enough for you to build the challenge yourself / resolve any issues you might have. If there’s anything I’ve missed, do please comment to let me know.
My published viz is here. Please note, that for some reason (and I don’t know why), Tableau Public does not seem to display my time axis properly (just shows 0). As the workbook renders on Public, the values on the axis show, but once fully loaded, they change. If you download the workbook, the values are fine. I published from Tableau Desktop 2021.1.0. I’m putting this down to an issue with Tableau Public.
For 2020 Week 42, the #WoW founder, Andy Kriebel, returned with a challenge to reproduce the Strava training calendar. Compared to some challenges recently, this looked to be quite straight forward; Andy threw in some specific requirements to test certain features – ie no data modelling and no LoDs.
I’ve been doing #WorkoutWednesday challenges since they first started, so I know that Andy is a stickler for formatting and layout – points not necessarily listed as a requirement, just expected as part of the challenge to reproduce. I kept my fingers tightly crossed when I published that I’d got all the finer details, but alas, Andy still found fault – my month summaries weren’t right aligned (my bad – missed that little nuance completely), and my bars had borders on them… Andy must have eyes like Superman to have seen that, as it wasn’t obvious. It also wasn’t a setting I’d intentionally added. I later found out that adding a particular type of pill to the Detail shelf caused borders to automagically be added… There’s always something to learn when Andy’s about!
So onto the challenge – as with previous weeks, I’m going to try to focus on the areas that may be a bit trickier / newer to some rather than detail the complete build step by step.
Using the data sets – blending
Building the calendar grid
Ensuring a 0 measure value is displayed for missing days
Adding the monthly hours summary
Building the BANs
Year Filter control
Remove highlighting
Setting the colour of the Calendar chart background
Using the data sets – blending
Andy was very specific that the 2 data sets provided should be used separately and not joined in any via the data pane.
This meant the data sources would need to be blended (further detail on this is here). Blending used to be one of the only ways within Tableau you could combine data together.
When blending, the number of rows in your output will never be more than the number of rows in your primary* data source. If there are multiple matching rows in the secondary data source, then the results will be aggregated in the display.
* whatever data source the first pill you add to your canvas comes from, will be the primary, and is denoted by a small blue icon by your data source. Secondary data sources are denoted by a small orange icon.
In the case of this challenge, we had a data set containing a list of dates (1 row per day from 01 Jan 2014 up to 31 Dec 2021), along with Andy’s Strava activity, containing a row for each activity recorded, which included the date time the activity occurred. This data could vary in that there could be multiple activities on the same day, and equally days when no activity occurred at all.
So the Calendar data set is our primary data source, as we need to show a bar on the calendar chart for every day of the year, regardless if there’s any activity. The Activity data set is our secondary data source. The number of hours, number of activities etc can all be aggregated from this data set.
When blending data sources, especially on dates, I prefer to create explicit calculated fields that define the fields I want to blend on. So in the Calendar data source I created
BLEND: Date
[Date]
essentially just a duplicate of the existing Date field, and in the Activity data source, I also created
BLEND: Date
DATE([Date Time])
Note the fields are spelled exactly the same, so Tableau automatically uses them as the linking fields when the view is built.
If you now do the following
Add the Calendar.BLEND: Date field to the Filter shelf, and select the Year = 2020,
Add Calendar.BLEND: Date as an exact date to Rows
Add Activity.BLEND: Date as an exact date to Rows
Add Activity.Seconds to Text
You can see that fields from the secondary data source have an orange icon by them; and that there are Null/missing values for the records from the secondary data source as these were the days when there was no activity recorded. You can also see a red link icon against the BLEND: Date field in the left hand data source pane, as this identifies how the two data sets are being matched.
Building the calendar grid
The calendar is essentially a ‘small multiple’ layout with each month being positioned in a particular row or column. To build out this layout we need to define the row number and the column number. There are many ways to build a dynamic small multiple grid which can flex based on the number of items you might be trying to organise, but for the purpose of this exercise, we can keep it simple. We’re working with 12 months that are to be displayed in a 4 x 3 grid layout. Create the following calculated fields in the Calendar data source.
Rows
IF MONTH([Date])<=4 THEN 0 ELSEIF MONTH([Date]) <=8 THEN 1 ELSE 2 END
Cols
(MONTH([Date])-1)%4
I make both of these to be dimensions rather than measures by dragging them above the line on the left hand data source pane. If you build out the view as below, you can see how these calcs are working
As we want to show a mark for every day in the month, we need to add the day of the month from the Calendar data source to Columns. Drag Calendar.BLEND: Date to Columns, then select the drop down to change to the Day date part
We need to show the amount of time in hours rather than seconds. In the Activity data source, create the field
Hours
([Seconds]/60)/60
and drag this onto the Rows, and change the mark type to bar. If need be re-add the YEAR(BLEND: Date) = 2020 to the Filter shelf. Now add Calendar.BLEND: Date as an exact date to the Detail shelf. You should now have
where you can see the gaps in the days where no activities took place, and if you hover vertically, you should find that the days of the month are vertically aligned – ie 30th Jan aligns with 30th May etc.
Ensuring a 0 measure value is displayed for missing days
With the above we displayed the Activity.Hours field, but if you hover over the day when there is no activity, nothing displays on the tooltip rather then 0.
To fix this, create a calculated field in the primary Calendar data source
Hours
ZN(SUM([Sheet1 (Activities Summary)].[Hours]))
This is basically just referencing the field in the secondary blended data source, but wrapping in a ZN() function means it will display 0 when no match can be found
Use this field from the primary data source instead on the calendar viz.
Adding the monthly hours summary
The requirements meant Andy expected the summary to be displayed within the same sheet as the daily calendar viz.
For this I used an old friend MIN(0) to create another axis, which is placed on the Rows in front of the Hours measure.
What I now plan to do is set this axis to be Text and plot the month, monthly hours, and the word ‘hours’ at a specific point to the right of the each cell – I’m choosing day 28 – you might want to experiment and choose a different day.
First up though, I need to build some fields to plot.
Month Name Abbrev
IF DAY([Date]) = 28 THEN UPPER(LEFT(DATENAME(‘month’,[Date]),3)) END
Hours in Month
IF MIN(DAY([Date])) = 28 THEN WINDOW_SUM([Hours]) END
LABEL: Hours
IF DAY([Date]) = 28 THEN ‘HOURS’ END
Month
DATE(DATETRUNC(‘month’,[Date]))
Add Month as an exact discrete date to Detail and the other 3 fields to the Text shelf of the Min(0) marks card (change the mark type to Text if you haven’t already done so). Alter the table calculation setting of the Hours in Month field to compute by all fields except Month
Building the BANs
These use a similar concept as above, by using 3 instances of MIN(0) placed side by side on the Columns shelf and set to the Text mark type. This creates 3 marks cards which you can then add the relevant measures and text on.
The measures are all coming from fields in the primary data source that reference measures in the seconday data source ie
# Activities (in Activity data source)
COUNT([Activity ID])
# Activities (in Calendar data source)
ZN([Sheet1 (Activities Summary)].[# Activities])
#Miles (in Calendar data source)
ZN(SUM([Sheet1 (Activities Summary)].[Miles]))
Year Filter Control
All the sheets you are building need to be filtered by the same BLEND: Date field from the Calendar data source (set the filter to Apply to all worksheets).
When this field is added to the dashboard, you can customise it so the All values does not show and the slider control also doesn’t display
Remove highlighting
To stop items on the dashboard from highlighting when they are clicked on, I use a trick that has been probably been the ‘most used trick of #WOW2020’ 🙂
In the primary data source, create a field called True which contains the value TRUE and a field False containing the value FALSE. Add both these fields to the Detail shelf of each sheet you don’t want highlighting on.
On the dashboard, create a Filter URL action for the each sheet that goes from the sheet on the dashboard to the sheet itself, and passes selected fields setting true = false. As this condition will never be true, then there is nothing to ‘filter’ so the marks don’t highlight. This needs to be repeated for each sheet on the dashboard, so I had 3 filter dashboard actions.
NOTE – a consequence of adding the True and False fields to the Detail shelf on the bar sheets, was that it caused a border to be added around the bars.
This wasn’t something I noticed, as it isn’t at all obvious, but Andy called it out!
Setting the colour of the Calendar chart background
You need to format the sheet and set the fill colour of the Pane rather than the whole sheet to grey.
There’s obviously a lot of other formatting settings to apply to get rid of all the row/column borders and gridlines etc, but this was a slight difference that I wanted to call out, as ended up with a ‘border’ on my dashboard that wasn’t required when I set the whole worksheet background.
Right, I think that’s about it for this week! Thanks for the fun challenge Andy – great to have you back!
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 🙂
Week 4 of #WOW2020 saw one of the new contributors, Sean Miller, provide his first challenge, based on providing a technique to improve the user experience when working with date inputs.
The date inputs allow a user to select from 2 ‘static’ options (Last 14 Days or Last 30 Days), a custom Last N Days option, where the user is then prompted to define how many days….
… along with a Custom Dates option, which when selected, prompts the user to define the start & end dates
Both the N Days parameter and the Start & End dates only appear when the appropriate selection is made. This makes the displayed interface much cleaner (as it’s less cluttered), and, more importantly, makes the action the user is then expected to make, much more obvious.
So let’s get started.
Define the parameters
The first step in this challenge is to define all the parameters needed, these being :
Date Selector
A string parameter just set to contain the value Last 14 Days
This parameter will be set via a Parameter Action, so there is no need to define this a list with all the options.
N Days
An integer parameter defaulted to 60
Start Date
A date parameter defaulted to 01 Jan 2019
End Date
Another date parameter defaulted to 31 Dec 2019
Build the Sales over time chart
The Sales over Time trend is simply Order Date as a continuous pill at the Day level, against SUM(Sales). Sales is duplicated as a dual axis, allowing one axis to be set to mark type Area, and the other to Line. The colours of each mark are slightly different, to give the ‘edged’ area look
The requirement stated the ‘Last…’ selections should be anchored to the latest date in the dataset, so we need to determine what this is:
Latest Date
{FIXED:MAX([Order Date])}
To restrict the data displayed, we need something we can filter on:
In Timeframe
CASE [Date Selector] WHEN ‘Last 14 Days’ THEN [Order Date]>DATEADD(‘day’,-14,[Latest Date]) WHEN ‘Last 30 Days’ THEN [Order Date]>DATEADD(‘day’,-30,[Latest Date]) WHEN ‘Last N Days’ THEN [Order Date]>DATEADD(‘day’,-1*[N Days],[Latest Date]) ELSE [Order Date]>=[Start Date] AND [Order Date]<=[End Date] END
In Timeframe can be added to the Filter shelf and set to True.
Based on the defaults selected, at this point, you should be displaying information for the last 14 days.
Expose the parameters onto the display, and test out the rest of the logic by changing the value of the Date Selector (type in one of the other options – eg Last 30 Days, Last N Days, Custom Days).
BANs
The 3 numbers displayed are the measures Sales, Profit and Profit Ratio, where Profit Ratio is a calculated field of
SUM([Profit]) / SUM([Sales])
formatted to be a percentage of 1 decimal place.
Then
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
Change the formatting of the Measure Names and Measure Values on the Text shelf to set the colour & size of the font to suit, and align middle, centre
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.
Format Sales and Profit accordingly.
Measure Selector
I built the measure selection view using the technique I’ve already blogged about related to #WOW2020 Week 1 : Can you sort dimensions with a single click? This is the 3rd week out of 4 so far that I’ve employed this technique, which is great, as it means it’s a concept that I’ve started to remember 🙂 The blog also describes
how to set the parameter action which is needed to alter the Date Selector parameter
how to invoke a filter action using a True and False calculated field to prevent the un-selected measures from ‘fading out’.
By this point, all the sheets should be on the dashboard with everything formatted and positioned where it needs. So now for the final task:
Make the Parameters Hide & Show
This part of the challenge was something totally new to me, so I did what I would usually do, and Googled it.
This is the primary post I followed to achieve the result for this challenge, so I’m not going to document it all here, when it’s already written down 🙂 It’s a bit fiddly to get the parameters positioned in exactly the right place. From other posts I found, this technique is also sometimes referred to as ‘parameter popping’.
Donna + DataViz 