Luke Stanke set the challenge this week, and posted a ‘sneak peak’ on Twitter before the challenge was formally released
Challenges from Luke can sometimes be on the harder end of the scale, so with the bit of extra time available and only the gif in the tweet as a clue , I had a play to see if I could get close at all. And surprisingly I could, so once it was formally issued, it was just a case of tidying up some of my calcs.
Calculations
First up we need to establish the basic calculations
Plotting these out onto a table with Segment we get
Note #Orders Per Customers needs to be set to be a discrete dimension.
So while this shows us a summary of the number of customers, we’ve only got 1 mark showing the summarised count. When plotting the chart we need something else in the view that will generate more marks. This is Customer ID.
At this point in order to help us build up our tabular view to ‘see’ what’s going on, I’ll filter the table to just show Segment = Consumer, and I’ll add Customer ID to Rows
As expected, our # Customers is now showing a count of 1 per row, but the data is now expanded as we have now got a row (ie a mark) per customer for each # Orders Per Customer ‘bucket’. But we still need a handle on the total customers in each ‘bucket’.
Customers Per Order Count
WINDOW_SUM([# Customers])
Adding this to the view and setting the table calc to compute based on Segment & Customer ID, we get the summarised value back again.
But we don’t want to actually show that number of marks; the number of circles to plot on the chart is dependent on a user parameter:
pMarkIndicator
Based on the requirements, if the number of customers is 15 and the user parameter is 5, then 3 circles should be drawn (15 / 5 =3 ), but if the number of customers was 14, only 2 circles should be drawn (14 / 5 = 2.8), ie the number of circles will always be set to the integer of the equal or lesser value (essentially the FLOOR() function). This can also be achieved by
Marks to Plot
INT([# Customers per Order Count]/[pMarkIndicator])
For some reason FLOOR can’t be used in the above as a table calculation is being used, but INT does the job just fine, and adding to the tabular view and adjust the table calculation accordingly we get
ie for the Consumer Segment, 6 customers have made 1 order in total, so based on batching the customers into groups of 5, this means 1 circle should be displayed. Whereas, 30 customers have made 3 orders in total, so 6 circles should be displayed.
But we can’t actually reduce the amount of rows (ie marks) displayed – we either have 1 row (by removing Customer ID) or a row per customer. But that’s fine, we don’t need to.
What we want is something in our data to group each row into the relevant batch size.
First up let’s generate an ID per row for each customer that restarts for each #Orders Per Customer.
Index
INDEX()
Add this as a discrete pill to Rows, and adjust the table calc
Now we have this, we calculate which ‘column’ each customer can sit in, a bit like what we would do if we were building a small multiple table, arranging objects in rows and columns (see here for an example of what I mean).
Cols
[Index]%([Marks to Plot])
This uses the modulo (%) notation which returns the remainder of the division sum. Lets put this on the view
For customers who have only ordered once, and where we’re only going to plot 1 mark, the Cols value is the same (0) for all rows.
Whereas for customers who have ordered twice, and where we want to plot 2 marks, the Cols value is either 0 or 1.
We’ve now got a value we can use to plot on an axis. We’re still going to plot a mark for each Customer ID, but some marks will be plotted in the same position, ie on top of each other, which therefore looks like just one mark.
Let’s show this more graphically, by duplicating the sheet, deleting some pills, moving some around, changing some to continuous, and setting the mark type to circle as below
Change the pMarksIndicator parameter and the number of circles will adjust as required.
So far, so good. We’ve got the right number of marks, it’s just not looking as nice and symmetrical as it should be.
We need to shift the marks to the left. But how far it shifts is dependent on whether we’ve plotted an odd or even number of marks.
If we have an odd number, the middle mark should be plotted at 0. If we have an even number the middle two marks should be plotted at -0.5 and +0.5 respectively. The calculation below will achieve this
Cols Shifted
IF [Marks to Plot]%2 = 0 //we’re even THEN [Cols] – ([Marks to Plot]/2) + 0.5 ELSE //we’re odd [Cols] – (([Marks to Plot]-1)/2) END
To demonstrate this, I’ve added Cols Shifted along side Cols on the viz (this time make sure all the table calculation settings (including the nested calcs) are applied to compute based on Customer ID only which is different from the calcs above)..
Now you can see how it all works, you can remove the Cols and the Segment from the Filter shelf.
And now its just a case of applying the various formatting to clean up the display, and adding to the dashboard.
Guest poster Ivett Kovacs was back to set the weekly #WOW2020 challenge this week, and delivered a very ‘relatable’ business challenge – Fiscal Year to Date reporting.
I’ve been quite used to doing this type of reporting within my job, so on the whole I found the core requirements pretty straightforward – there’s just a lot of calculated fields 🙂 The trickiest part I found was getting everything organised on the dashboard and making the budget parameters appear/disappear based on the filter selected – this took an extraordinarily looonnnng time even though I knew the technique – more on that later.
Setting up the Fiscal Year
The data source provided was one curated by Ivett. It contained financial ‘transactions’ against Account Codes from Oct 2018 to May 2020.
The date in the file was initially recognised by Tableau as a string, but simply changing it to a Date datatype in the Data Source pane easily resolved this.
The fiscal year starts on 1st October, so once on a sheet, set this by right-clicking on the Accounting Date field and choosing Default Properties -> Fiscal Year Start -> October
If you add Accounting Date to a sheet and expand from Year -> Month, you’ll see that the Year part is now labelled as FY 2019 and FY 2020, and Q1 starts in October
Just a couple of points to be aware of if you’re not familiar with working with Fiscal Years.
A relative date filter for ‘this year’ will recognise your fiscal date setting, so if you did this on this data set, you’d get data from Oct 2019 to May 2020.
Any date related functions such as YEAR([Date]) or DATETRUNC(‘year’, [Date]) does not recognise the fiscal year setting. So YEAR(#2019-11-01#) will return 2019 and YEAR(#2020-01-01) will return 2020 even though they are both in the same FY 2020 fiscal year. It does mean at times, depending on what you’re building, you may need to hard-code information that defines the start month of your fiscal year.
Building all the calculated fields
First up we need a couple of measures as stated in the requirements.
Sales
IF STARTSWITH([Account Number],’5′) THEN [Value] END
only counts for Account Number starting with a 5
OPP
IF STARTSWITH([Account Number],’5′) OR STARTSWITH([Account Number],’696′) THEN [Value] END
only counts for Account Numbers starting with a 5 or 696.
Then we need lots of fields to help us get all the various data needed. This is simply going to be a big list 🙂
finds the maximum date in the dataset and sets to the 1st of the month, in this instance it will be 01 May 2020. This is a more generic approach than harcoding.
FY Start Current Year
#2019-10-01#
I said there might need to be some hardcoding! Ideally, having set a fiscal year, you’d like a function along the lines of DATETRUNC(‘fiscal year’, [Date]) to give you this value. Apart from setting this value in a parameter, I’m not aware of any way you can determine this date without any sort of hardcoding 😦 This KB article tries to provide some suggestions, but you still need to hardcode a value to ‘shift’ to the appropriate month; in this case we’d have to hardcode 10 as October is the 10th month. Feel free to upvote this idea on the Tableau Forums which asks to address this 🙂
Current FYTD Sales
IF DATETRUNC(‘month’,[Accounting Date]) >= [FY Start Current Year] AND DATETRUNC(‘month’,[Accounting Date])<=[Current Month] THEN [Sales] END
only return sales values for dates from 01 Oct 2019 to 31 May 2020.
Current FTYD OPP
IF DATETRUNC(‘month’,[Accounting Date]) >= [FY Start Current Year] AND DATETRUNC(‘month’,[Accounting Date])<=[Current Month] THEN [OPP] END
as above but for the OPP measure.
Current Month Prev FY
DATE(DATEADD(‘year’,-1,[Current Month]))
go back 1 year to 1st May 2019.
Prev FYTD Sales
IF DATETRUNC(‘month’,[Accounting Date])<= [Current Month Prev FY] THEN [Sales] END
Only return sales values for dates up to 31 May 2019. Note the data only starts from 01 Oct 2018, which is the start of the previous FY, but if we couldn’t guarantee that, we’d have stored an ‘FY Start Prior Year’ and added an additional clause to the above calculation.
Prev FYTD OPP
IF DATETRUNC(‘month’,[Accounting Date])<=[Current Month Prev FY] THEN [OPP] END
To deal with the Budgets, we need to create a couple of parameters Budget Sales (M) and Budget OPP (M). Both are integer parameters defaulted to 2,300 and 3000 respectively.
But because these parameters aren’t actually in millions, we need further fields to translate them into the value we really need for comparisons.
We need the MIN() function as the same Budget Sales Ref Line value is stored on every row, so SUM() will multiply the value too much. AVG() or MAX() would have worked just as well.
Curr vs Budget Sales Diff %
[Curr v Budget Sales Diff] / MIN([Budget Sales Ref Line])
and again we need to duplicate these for the OPP measure
Curr vs Budget OPP Diff
SUM([Current FYTD OPP]) – MIN([Budget OPP Ref Line])
Curr vs Budget OPP Diff %
[Curr v Budget OPP Diff] / MIN([Budget OPP Ref Line])
Now we’ve got all the core variables we need, we need to determine which one to use as the comparison in the bar chart and the KPI indicator. This is based on a parameter for the user to decide if they’re comparing Actuals against Budget or against Previous Year Actuals.
Compare Filter
I chose to create an integer based parameter with the display altered to show the relevant text. We will be using this parameter in calculated fields and comparing integers rather than strings is much more efficient (and easier to read).
So with the parameter set up, we now need to create a few fields that we’ll use to show the values we want
Sales Diff
IF [Compare Filter] = 0 THEN [Curr v Budget Sales Diff] ELSE [Curr v Prev Sales Diff] END
This is custom formatted to display as $ in millions with an arrow to show positive or negative : ▲”$”#,##0,,M;▼”$”#,##0,,M
Sales Diff %
IF [Compare Filter] = 0 THEN [Curr v Budget Sales Diff %] ELSE [Curr v Prev Sales Diff %] END
This is formatted to 1 decimal place.
Opp Diff
IF [Compare Filter] = 0 THEN [Curr v Budget OPP Diff] ELSE [Curr v Prev OPP Diff] END
Same custom formatting as above.
Opp Diff %
IF [Compare Filter] = 0 THEN [Curr v Budget OPP Diff %] ELSE [Curr v Prev OPP Diff % ] END
Sales Ref Line
IF [Compare Filter] = 0 THEN MIN([Budget Sales Ref Line]) ELSE SUM([Prev FYTD Sales]) END
This is used for the line shown on the bar chart, and we’ll need the same for the Opp measure.
Opp Ref Line
IF [Compare Filter] = 0 THEN MIN([Budget OPP Ref Line]) ELSE SUM([Prev FYTD OPP]) END
Finally we need a couple of fields to use to work what colour the bars need to be.
Colour : Sales Diff
IF [Sales Diff %] <-0.05 THEN ‘Difference < -5%’ ELSEIF [Sales Diff %] > 0.05 THEN ‘Difference > 5%’ ELSE ‘-5% <= Difference <= 5%’ END
Colour : OPP Diff
IF [OPP Diff %] <-0.05 THEN ‘Difference < -5%’ ELSEIF [OPP Diff %] > 0.05 THEN ‘Difference > 5%’ ELSE ‘-5% <= Difference <= 5%’ END
Note – my published solution has something slightly longer winded as when I originally built the viz, I created the colour fields before I created the generic Diff/Diff% fields referenced above.
Right! That’s a LOT of calculated fields (I did warn you!). In some cases it may have been possible to combine, but I like creating building blocks to keep things simpler to read.
Sales YoY Trend Line
The basis of this type of chart is pretty much Desktop 101.
Month(Accounting Date) on Columns (blue pill)
Sales on Rows
Year(Accounting Date) on Colour with colours adjusted accordingly.
Add the average line and Label the most recent point
Format the gridlines/rows etc and axis
The most recent mark is a larger circle. We need another calculated field for this
Current Month Sales
IF DATETRUNC(‘month’,[Accounting Date]) = [Current Month] THEN [Sales] END
This just stores the sales value for the latest month.
Add this field to the Columns and make dual axis and synchronise axis. Adjust the marks back to be a line and a circle and adjust the Sizes to suit.
Duplicate all the above on another sheet for the OPP values instead.
Sales Bar Chart
Simple bar chart
Current FYTD Sales on Columns
Colour : Sales Diff on Colour
Sales Ref Line on Detail
Add Sales Ref Line as a reference line and label & format to suit.
Label bar and align middle right; format to suit.
The colour legend will only display a single option at a time. You’ll need to set the Compare Filter to compare against budget and show, then adjust, the value of the Budget Sales (M) parameter to values that go beyond the thresholds, to set the other colour options.
Repeat all this again on another sheet for the bar displaying the OPP measure.
Sales KPI
Simply add Sales Diff & Sales Diff % to the Text.Format row/column lines to suit.
Again, repeat for the equivalent OPP measures.
Year Legend
The standard legends aren’t used as these are only square icons, but the challenge shows circles. So a custom legend sheet is created as below
The hidden axis has been fixed from 0.45-1 to push the display to the left more.
% Diff Indicator Legend
Again the standard legend can’t be used this time, as only 1 option ever shows. So this is a custom ‘fake’ legend.
I simply used the Account Number field for this purpose. I filtered a sheet to just 3 different Account Numbers. I then built a similar viz to the above, but using Account Number throughout.
The sneaky trick I used here was to simply ‘alias’ the actual Account Numbers displayed to the text I wanted.
Building the dashboard
Getting all the objects in the right places can be a bit of trial and error. All the objects on my dashboard, apart from the Budget parameters, are tiled using a mix of horizontal and vertical containers, nested within.
Hide/Show the Budget Parameters
This probably took me the most time. It’s using a technique sometimes referred to as Parameter Popping. It uses containers and works similarly to sheet swapping.
I had to build a ‘blank’ sheet for this – a sheet that will show (a blank value) and hide based on the Compare Filter parameter.
I needed another field
Is Prev Value to Compare
[Compare Filter]=1
This returns true if the parameter is set to compare against the Previous Year rather than the Budget.
This is added as a Filter to the blank sheet and set to True, which means the sheet will ‘show’ when comparing to Previous Year, and hide when comparing to Budget.
A floating horizontal container is then added to the sheet, and all the objects – the Budget parameters, some Text boxes to label the parameters and the ‘blank’ sheet are ‘carefully’ added. The blank sheet should be the first object on the left. I say ‘carefully’ as it took a lot of trial and error to make it work. If it is done right, then changing the Compare Filter parameter should make the budget parameters move left & right as the blank sheet shows and then hides.
The floating horizontal container is made wider than the dashboard itself, so the showing of the ‘blank’ sheet, pushes the other objects so far to the right that they’re not on the dashboard at all.
The screen shots below show what it looks like in Desktop
As I said, Parameter Popping can be a bit tricky – I only used it twice, so haven’t yet found a sure fire way to get it right first time. If you google ‘Tableau Parameter Popping’ you’ll find a few links that might help, and/or check out Week 4 of #WOW2020 which also uses it (the other time I’ve used it).
And that’s about it. My published viz is here. Enjoy!
Lorna set a fun ‘create your own pizza’ challenge this week to demonstrate the ability to both add and remove items in a set via the use of Set Actions, a feature introduced in v2020.2 (so you’re going to need this to complete the task).
There’s essentially 5 components to this dashboard, which I’ll guide you through
The central bar chart
The graphical product type selector on the left
The list of selected products to the right
The actual vs budget bar at the top
An indicator of how much you’re over/under budget
Central Bar Chart
The essence of this is a simple Type, Product by Price bar chart, coloured by Type. Manually move the Type field so the ‘Size’ option is at the top.
To indicate if the product is vegetarian or not, we create
Veg Indicator
IF [Vegetarian] = ‘Yes’ THEN ‘●’ ELSE ” END
which we can add to the Row shelf, and format to be coloured green (I use this site to get the shapes).
The tick is used to indicate if the product has been added to the pizza or not. Selected items will be identified by the use of a set, so we need to create one. Right-click Product and Create->Set. Tick a few options.
And similar to the Veg Indicator we can build
Selected Product Indicator
IF [Selected Products] THEN ‘✔’ ELSE ” END
Add this to the Row shelf between Type and Product.
The order of the Products listed will change. I want it to be orderedalphabetically by Product within each Type. The quickest way to resolve this, was to duplicate the Product field, add it to the Rows between the Type and Selected Product Indicator pills, and then hide it.
The final requirement for this chart, is to highlight the selected products in a different shade of the ‘base’ colour.
Add Selected Products to the Detail shelf, then change the … detail icon to the left of the pill to the colour icon. This will mean there are 2 pills on the Colour shelf, and the colour legend will change. Adjust to suit
The chart just then needs formatting to add the Price to the Label of the bar, remove the row/column lines, hide the Type column, hide the field labels, hide the axis, and adjust the width of the columns.
Product Type Selector
Lorna provided the images needed for this, which need to be saved to a new folder in the Shapes directory of your My Tableau Repository. I called my new folder simply Custom.
Then on a new sheet, add Type to Rows, set mark type to Shape and add Type to shape too.
Hide the Type heading and remove the row lines.
Selected Products List
Add Type & Product to Rows and enter MIN(1) onto the Columns. Add Selected Products to the Filter shelf to restrict the list just to those in the set.
Add Type to Colour and Product to Label, changing the font to white and centring. Fix the axis from 0-1 and hide it. Hide the Type & Product columns.
Manually reorder the Type field to be listed Size -> Crust -> Sauce -> Toppings
Actual vs Budget Bar
The budget field can be changed by the user, so we need a parameter, Budget, for this, which is an integer parameter where the display is formatted to £ with 0dp, and defaulted to 15.
Create the bar by adding Price to Columns, Selected Products to Filters and Type to Colour. Reorder the items in the colour legend to get the colours displaying in the correct order with ‘Size’ on the left and ‘Toppings’ on the right.
Add the Budget parameter to the Detail shelf, then add a Reference Line which refers to the Budget. Adjust the label displayed to be custom as shown below, and format the thickness and colour of the line to suit
Format the reference line so the text is displayed at the top right, and edit the font & size of the label displayed.
Then make the row height of the chart taller, but adjust the size of the bar to be narrower. The reference line spans the whole height of the row, so reducing the height of the bar itself provides space for the label to display without overlapping the bar.
Hide the axis and remove gridlines etc.
Finally we need to display the total price of the products selected. We can’t just add Price to Label as there are multiple segments on the bar, and this will display a Price per Type.
Instead we need a new field
Total Price Selected Products
{FIXED [Selected Products]:SUM([Price])}
Since this chart is filtered by Selected Products = True, this returns the total price of all the items in the set (selected). Add this to the Columns shelf and make dual axis & synchronise axis.
Readjust the mark types, so the Price marks card is back to a bar, and the Total Price Selected Products is Text. Remove the Measure Names pill from the Colour shelf of both cards (the dual axis will have automatically added it), and also remove the Type pill from the Colour shelf of the Total Price Selected Products card. Instead, add Total Price Selected Products to the Text shelf of this card.
The text will overlap the bar whether you align left , middle or right. The trick is to add some spaces in front of the text and then align right.
Hide the axis.
Over / Under Budget Indicator
This is just a text display, but a few calculated fields are needed so the text can be coloured differently depending on whether it’s over or under.
First we need to know the difference from budget
Diff from Budget
[Budget]-[Total Price Selected Products]
Text Budget Diff Over
IF [Diff From Budget] < 0 THEN ABS([Diff From Budget]) END
This will just display the difference if the value is negative, but the ABS function will return the number as a +ve. This field should be formatted to £ with 2 dp.
Text Budget Diff Under
IF [Diff From Budget] >= 0 THEN [Diff From Budget] END
This will just display the difference if the value is positive. Format to £, 2 dp.
Only one of these fields will ever hold a value at any time.
Similarly we need fields to show the text to display.
Over Budget Text
IF [Diff From Budget] < 0 THEN ‘Over Budget’ END
Under Budget Text
IF [Diff From Budget] >= 0 THEN ‘Under Budget’ END
On a new sheet, add Selected Products to the Filter shelf, then add all four of the above fields to the Text shelf.
Adjust the order and colour of the fields in the Label editor, setting the fields related to being ‘over budget’ to red and the ‘under budget’ fields to green.
Now we’ve got all the building blocks, we can put it all on a dashboard.
Building the dashboard interactivity
Add all the sheets in their various locations. Most were tiled, except for the Budget parameter and the Over/Under Budget Indicator sheet, which I floated.
Filter the bar chart
Add a dashboard Filter Action to filter from the Type Selector sheet to the Product Bar Chart on select, filtering on the Type field only.
Add / Remove from Set
Add a dashboard Set Action to the Chart sheet that runs from the Menu, and adds the selected item to the Selected Products set.
Create another Set Action on the same chart, which removes values instead.
And that’s the main crux of the challenge. The only addition is ‘nice’ feature to avoid the item selected from being shown as selected (ie fading out all the other items in the chart).
I reverted to the trusty true = false dashboard action which I applied to the Selector chart and the Selected Items chart.
This involves creating 2 new fields True = True and False = False and adding these to the Detail shelf of the relevant chart.
A dashboard Filter action is then added which targets itself using the fields True = False
However, when I tried to add this same feature to the main bar chart itself, the bar chart stopped working as expected. I think there was conflict between there being 2 filter actions on the same chart. I have to admit, this is where I did check Lorna’s solution, as this was a small feature that really bothered me, and I didn’t want to publish without it.
It turns out she simply used a highlight action to resolve this
I vaguely recall using this sometime ago, but the ‘true=false’ concept has become so ingrained as my ‘go to’ method, that I struggled to think of this.
And that should be it. You now have the tools to customise your own pizza 🙂
This week for #WOW2020, Ann provided a table calculation feast of a challenge! This certainly is not for the faint-hearted! As well as cracking all the table calcs, the challenge features multiple views, measure swapping, parameters, BANs, filtering, sorting …. it’s got it all going on!
Ann hinted you’d probably want to start with the table, and even if there hadn’t been a table output in the display, this is what I would have done. If you’ve read enough of my blogs, you’ll know I often like to build up a ‘check data’ sheet, which just contains the data I need in tabular form as a quick reference. When working with table calculations this is an absolute must have!
So let’s build out that Check Data table to start with. I have a feeling this is going to be a lengthy blog 🙂
Initial Set up
First up, the requirements stated that the latest date would be 7 June, but I found records with a 8 June date. All the associated info for this date was null though, so I set a data source filter to exclude this. This means I wouldn’t get any issues if I needed to store the max date in a FIXED LoD calculation at any point.
I also found it easier to rename a couple of the measures provided to match the output, so rename PEOPLE_POSITIVE_NEW_CASES_COUNT to New Cases and PEOPLE_POSITIVE_CASES_COUNT to Reported Cases. I’ll refer to these renamed fields going forward.
Building all the Calculated Fields
To build out the table, we’re just going to focus on one State & County, as there’s a lot of data. So add Province State Name = Tennessee and County = Davidson to the Filter shelf.
Add Report Date (discrete exact date – blue pill) and New Cases & Reported to Rows. As you scroll down, you’ll see data starting to come in on 8 March.
We want to create our moving average calculations
3 Day Moving Avg
WINDOW_AVG(SUM([New Cases]), -2, 0)
14 Day Moving Avg
WINDOW_AVG(SUM([New Cases]), -13, 0)
Notice the number of rows to average over is 1 less than you might expect, as the current row is included, so the calculation is saying ‘current row’ and 2 | 13 previous rows.
Add these to the table, and adjust the table calculation so it is explicitly calculating by Report Date. This would have happened automatically, as the calculation would have been computing ‘down’ the table, but it’s best to fix the computation, so it doesn’t matter where the pill gets moved to in the view.
We now need to work out whether there is an increase or not between the 3-day and 14-day average.
Is Increase?
IF [3 Day Moving Avg] > [14 Day Moving Avg] THEN 1 ELSE 0 END
Is Decrease?
IF [3 Day Moving Avg] <= [14 Day Moving Avg] THEN 1 ELSE 0 END
I’m using 1s and 0s as it’s going to help with a later calculation.
NOTE – I’m assuming that if there is ‘no change’ it’ll be recorded as a decrease. This is how I interpreted the requirement, “ …whether it is an increase or a decrease (or no change)” and it wasn’t easy to find any matches anyway.
I also need some text to indicate the increase or decrease
Increase | Decrease
UPPER(IF [Is Increase?]=1 THEN ‘Increase’ ELSE ‘Decrease’ END)
The UPPER is used as that’s part of the tooltip formatting.
Let’s get these onto the view, always making sure the table calculations are set to Report Date.
We need to calculate the number of days that has been reported INCREASE in succession, and the number of days where successive DECREASE has been reported.
So first, let’s identify which rows match the previous row.
Match Prev Value?
LOOKUP([Is Increase?],-1) = [Is Increase?]
If the value of the Is Increase? field in the previous (-1) row is the same as the Is Increase? field in the current row, then this is true, else false.
Add to the view, and verify the table calculation for itself and all nested calculations being referenced, is set to Report Date.
We now have all the information we need to help us work out the number of days in the increase/decrease ‘trend’.
Days in Trend
IF (FIRST()=0) OR(NOT([Match Prev Value?])) THEN 1 ELSEIF [Increase | Decrease] = ‘INCREASE’ THEN ([Is Increase?]+PREVIOUS_VALUE([Is Increase?])) ELSEIF [Increase | Decrease] = ‘DECREASE’ THEN ([Is Decrease? ]+PREVIOUS_VALUE([Is Decrease? ])) END
If the row in the table is the very first entry (so there’s nothing previous to compare against), or the row in the table didn’t match it’s predecessor (ie there was a change), then we’re starting a new ‘trend run’, which obviously starts at 1.
Otherwise, if the current row we’re on indicates an increase, then we’ll add the value of the Is Increase? field (which is 1) to the previous value (which is also 1). PREVIOUS_VALUE works recursively though, so it essentially builds up a running sum, which gives our trend.
We ultimately do the same thing using the Is Decrease? column. This is why using 1 & 0s in the earlier calculation help.
Adding into the view, and setting the table calculation correctly, you should get something similar to this…
Finally, there’s one key field we need to add; something to help identify the latest row as we will need it for filtering in the table that’s displayed on the dashboard. Simply applying a standard ‘quick filter’ won’t work, as the table requires we show the 3-day & 14-day moving averages. A ‘quick filter’ to limit the data to the latest date (7th June), will show the wrong values, as the data related to the other days will be filtered out, so the table calc won’t have the information to correctly compute over.
We need to create another table calculation that we can use as a filter, and that due to Tableau’s ‘order of operations’ will apply later in the filtering process than a traditional quick filter.
Max Date
{FIXED : MAX([Report Date])}
The latest date in the whole data set.
Show Data for Latest Date
LOOKUP(MIN([Report Date]),0) = MIN([Max Date])
If the Report Date of the current row is the same as the maximum date in the whole data set, then return true.
We’ve now got all the core data components we need to create the various charts.
In the interest of time (my time in writing this out), I’m going to attempt not to describe the building of all the charts in too much detail, but just call out the useful bits you might need. If you’re attempting this challenge with the table calcs above, I’m assuming you know Tableau enough to not need everything defined to the lowest level.
The whole report is driven off a parameter which the user must enter a State – County combo.
You’ll need a calculated field to store the combo
State – County
[Province State Name] + ‘ – ‘ + [County]
and then create a parameter (State – County Parameter) off of this (right click, Create -> Parameter) which will create a string parameter with all the permutations.
When displaying on the dashboard, set this to be of type Type In
BAN
The BAN is a basic summary of the latest trend for the entered state county.
We need to filter the sheet to the value entered in the parameter
Is Selected State County?
[State – County Parameter] = [State – County]
Add this to the Filter shelf as true, along with the Show Data for Latest Date.
Add the relevant fields to the Text shelf to display the required text. The Report Date needs to be custom formatted to ddd, mmm d to get the Sun, Jun 7 display
Map
For the map, as well as filtering the latest date, we’re also going to need to filter just to the state only (not state & county) as above. So I created
Is Selected State?
LEFT([State – County Parameter], FIND([State – County Parameter],’-‘)-2) = [Province State Name]
This is unpicking the State – County combined string stored in the parameter, to just find the State part and compare to the Province State Name.
Build a filled map based on County and filter to the latest date and the selected state. I set the Map Layers to that below, which seems to match up
You’ll need to set both the Is Selected State County? and Increase|Decrease fields to the Colour shelf.
Bar & Line Chart
You’re going to need a few more calculated fields for this.
Moving Avg Selector
for the user to choose what the line should display. I’ve set it to an intger parameter that displays text
We then need a field to show on the display depending on what’s been selected in the parameter
Moving Avg to Display
If [Moving Avg Selector] = 3 THEN [3 Day Moving Avg] ELSE [14 Day Moving Avg] END
You’ll need a Dual Axis chart plotting New Cases and Moving Avg to Display against Report Date (continuous exact date)
The data only starts from 8th March, so I added Report Date to filter to start from 8th March. 8th March is also added as a constant reference line.
Table
Based on the State / County entered, the table is filtered to show the data for the latest date for all the counties in the state entered. Although not stated in the requirements, the first row is the county selected, with the rest ordered by Reported Cases.
You can get the selected county to the top, by adding Is Selected State County as a hidden field to the Rows, and moving ‘True’ to the top.
And that should be everything you need to build the dashboard, which is pretty much just stacking all the sheets one on top of each other in a single column.
When Luke’s #WOW2020 challenge landed this week, I did a little happy dance inside, as I was pretty sure I was going to be able to crack this fairly quickly with minimal head scratching effort. This isn’t because I create these types of charts often, but because this type of side-by-side bar formed the basis of a #WorkoutWednesday challenge way back in 2017, and is a technique I can still recall – I didn’t even have to check my published workbook for a reminder… there’s just some techniques that just ‘stick’.
There may well be other ways to solve this challenge, but the technique I know is based on ‘normalising’ and ‘jittering’ dates, and is one I recall I picked up from Zen Master Jonathan Drummey’s Bars & Lines Blog post and associated workbook which can be downloaded from the blog.
So let’s gets started & hopefully, all will become clear.
Building the chart
In the dataset we’re using we have 4 years worth of orders, and need to plot the value of sales per month, per year in a bar chart which ‘groups’ the yearly bars for the same month together.
We’re going to plot the dates on a continuous axis (green pill), which typically would give a bar chart that looks like this, where Year(Order Date) is also added to the Colour shelf (Note I’ve also adjusted the colours to match the requirements).
We have 1 bar per month per year, but the months are ‘grouped’ in their sequential years.
So the first thing we want to do, is ‘normalise’ the dates as if they all occurred in the same year – any year is fine, I’m going to baseline them all to 2019
As you can see if plotting Order Date alongside Date Normalised this is simply transposing 03 Jan 2016 to 03 Jan 2019, 03 Jan 2017 would also be transposed to 03 Jan 2019
Replacing Order Date with Date Normalised on our initial chart gives us
But we want the bars side by side.
When using the automatic date hierarchy to plot dates at the month level, what Tableau is doing ‘under the bonnet’, is ‘truncating’ each date to the 1st of the month; so 3rd Jan 2019 and 18th Jan 2019 etc, are both actually plotted at 1st Jan 2019, and so on.
And when a bar chart is used to plot on a date axis, the left hand side of the bar is plotted at the 1st of the month point.
So with all this in mind, what we’re going to is ‘jitter’ the dates for each year to be clustered before the 1st of the month (for 2016 & 2017) and on/after 1st of the month (for 2018 & 2019).
Date Jitter
CASE YEAR([Order Date]) WHEN 2016 THEN DATEADD(‘day’,-9, DATETRUNC(‘month’, [Date Normalised])) WHEN 2017 THEN DATEADD(‘day’,-4, DATETRUNC(‘month’, [Date Normalised])) WHEN 2018 THEN DATEADD(‘day’,1, DATETRUNC(‘month’, [Date Normalised])) WHEN 2019 THEN DATEADD(‘day’,6, DATETRUNC(‘month’, [Date Normalised])) END
If the year of the original Order Date is 2016, then truncate the normalised (2019) version of the date to the 1st of the month, but subtract 9 days. So if the OrderDate is 03 Jan 2016, then transpose it to 03 Jan 2019, then truncate to 1st of month, 01 Jan 2019, then subtract 9 days to 23 Dec 2018
03 Jan 2016 -> 03 Jan 2019 -> 01 Jan 2019 -> 23 Dec 2018
Depending on the year depends on whether dates are subtracted or added, and they have a suitable spacing between. The table below shows the relationship between the Order Date, the Normalised Date and the Date Jitter
Replacing Date Normalised with Date Jitter like for like, gives us the same view though, since it’s automatically rolled up to ‘month’
Change the Date Jitter to Exact Date
and ta-dah! you have your side-by-side chart. Each bar is being plotted at the exact date. If we just filter to look at May for example, we can see this clearer
The bars are obviously thinner than we want, so adjust the Size to be Fixed with a value of 4
To get the Tooltip to display the correct month and year, you need to add Order Date to the Detail shelf and change it to the discrete Month level (blue pill), then format the pill to display as an abbreviated month.
To get the axis to display an abbreviated month name, format that too and set to custom formatting of mmm.
Finally, to get the bottom axis and axis ticks to be darker, adjust the Columns Axis Ruler and Axis Ticks to be a solid dark line, then edit the axis, and delete the axis title
Your bar chart should be complete
Building the Legend
F0r the legend, I simply created a very simple existence chart using the circle mark type as below
And that’s it (once added to a dashboard of course!). The date jittering is a useful technique to be aware of – I’m pretty sure I’ve used it in other challenges too, but not necessarily for a side by side bar chart.
Note, my Date Normalised & Date Jitter fields in my published viz aren’t exactly as detailed above, because I decided to jitter then normalise originally, which meant I had to make an additional adjustment in my normalisation calculation. It’s only when typing this blog out and essentially rebuilding as I go, that I realised it was so much simpler the other way round!
At first glance it looked like it would be tricky, and it was! There were many head-scratching moments as I made my way through this, and I couldn’t complete it all without having to look at Ivett’s solution – more on that later.
Understanding the data
First up, a word on the data provided, as the requirements weren’t as clear as I’d have liked.
Ivett provided a small custom data set to build this challenge on
It contains 2 rows for each Sub-Category. The value to be plotted for Review is evident on the challenge – it’s an average. But the aggregation for Revenue & Budget isn’t that obvious, as there is no direct indication on a label or in the tooltip. Should the values be summed or averaged, or a combination of both?
The Profit value in the tooltip is the only clue you have. The Profit value for Tables is -$60, the value for Chairs is $50. From examining the values in the data, the assumption is Profit = AVG(Revenue) – AVG(Budget), so the values to plot for Budget and Revenue need to be averaged.
The assumption is this data set is a combination of a two datasets; one containing the Revenue & Budget per Sub-Category
the other containing the customer reviews
So when it comes to the viz, we’re looking to plot based on the following data
Understanding what numbers I need to be aiming for when doing these challenges is crucial to me to ensure I’m heading down the right path 🙂
Duplicating the Data
The key thing to realise about this chart is that the right hand line is a fake axis; ie not an axis at all, just a vertical line located so that it looks like it is an axis.
What we actually need to do is build a triangle for each Sub-Category, where each point of the triangle is a different (x,y) coordinate. This means we need 3 rows of data per Sub-Category, 1 row to represent each point being plotted.
So for this we need to Union the data twice, so there are 3 instances of the data – drag another instance of the table into the data pane and drop when the Union option appears. Do this twice.
The union duplicates the rows of data, and each set is identifiable by an automatically generated Table Name field containing values WW, WW1,WW2
Defining the points of the triangle
As I said above, we need to build a triangle for each Sub-Category, where each point of the triangle is a different (x,y) coordinate.
At point 1, we’re going to plot our Review value. At point 2, we’re going to plot our Revenue value, and Budget will be at point 3.
From the diagram above, you can see our x coordinates are one of two numbers, either 0 or x1 (ie the x coordinate for the Revenue & Budget points is the same).
The y coordinates vary per measure, and need to be ‘normalised’ to a common scale, as otherwise, the Revenue & Budget figures would be plotted significantly higher than the Review values (you might find this blog by Zen master Jonathan Drummey useful at this point).
When normalising, you’re typically looking to convert your values to a scale from 0-1; this means the values aren’t plotted at their absolute values, but are still plotted in the same relative order to each other ie lowest value at the bottom, highest at the top.
When normalising a set of values from 0 to 1, your lowest value would typically be at the 0 position , with your highest value at the 1 position. To calculate where your value would sit on this scale, you need to know 3 numbers; the value to convert, the maximum value in the range of values to plot and the minimum value in in the range of values to plot. The normalised value is then :
(Current value – min value) / (max value – min value)
ie the difference between your value and the lowest as a proportion of the difference between the whole range.
However, in this instance, I chose to simplify the normalisation, and my method only works due to the values in the example data provided.
Side Note I consider Revenue & Budget to be values that are directly related to each other ie if Budget = Revenue I’d expect them to be plotted at the same point. I therefore chose to normalise these values across the combined range. This gave me different results from the solution, where Budget and Revenue were normalised independently of each other.
The maximum average review value is 5, the maximum value for budget and revenue combined is 100. As 100 is exactly 20 times bigger than 5, I simply chose to normalise the revenue/budget values to be on a scale of 0-5 instead, rather than normalising all values (Review, Budget & Revenue) to be on a 0-1 scale.
First up, we want to identify a position for each point
Path
IF [Table Name] = ‘WW’ THEN 1 ELSEIF [Table Name] = ‘WW1’ THEN 2 ELSE 3 END
Then we’ll create the x coordinate for each point
x
IF [Path] = 1 THEN 0 ELSE 20 END
I’ve just chosen an arbitrary value to plot the 2nd & 3rd points at – could easily have been 1.
Then we’ll create the y coordinate for each point, which is where the normalisation comes in
y
IF MIN([Path]) = 1 THEN AVG([Review]) ELSEIF MIN([Path]) = 2 THEN AVG([Revenue])/20 ELSEIF MIN([Path]) = 3 THEN AVG([Budget])/20 END
Dividing by 20, normalises the values to a scale of 0-5 as discussed above.
Let’s put these values all out in a table, so we can see what’s going on
Building the Polygon Viz
You can see we have 3 points per Sub-Category, so we can plot the x & y measures on a sheet as follows :
Add Min(x) to Rows
Add y to Columns
Add Sub-Category to Detail
Add Table Name (or Path) to Detail
Change Mark Type to Polygon
Change Colour to #666666 with 30% opacity
Tooltips
We need 2 new calculated fields for the Tooltip
Profit
AVG([Revenue]) – AVG([Budget])
formatted to $ with 0 dp
Margin
[Profit]/AVG([Budget])
formatted to % with 1 dp.
Note this will differ from the solution, where I think Ivett inadvertently used SUM(Budget) rather than AVG.
Add these to Tooltip field and adjust text accordinly.
Colouring the Line
To make the line, I created a second instance of y
y2
IF MIN([Path]) = 1 THEN AVG([Review]) ELSEIF MIN([Path]) = 2 THEN AVG([Revenue])/20 END
which just plots 2 points rather than 3.
Add this to Columns next to y, make dual axis and synchronise axis. Things might have disappeared – you need to remove Measure Names from both marks cards. Change the mark type of the y2 card to Line.
You won’t see much difference at this point (or you shouldn’t). We need a field to define the colour
Colour : Line
IF [Profit] < 0 THEN ‘Non-Profitable’ ELSE ‘Profitable’ END
Add this to the Colour shelf of the y2 marks card and adjust to suit.
Labelling the line
On the y2 marks card, add Review (set to AVG) to Label shelf, and move Sub-Category to Label shelf. Set Label to only label Start of Line. Adjust format/layout of Label to suit.
Set the format of Review to Number Standard – this is a format little used, but will display a whole number or a decimal. I discovered this through an Andy Kriebel WoW from a long time ago and is a real gem!
Finalising the viz
To tidy up and get the viz looking like the solution
Format to remove all gridlines
Hide the y2 axis
Hide the ‘4 nulls’ indicator if you have it
Edit the y axis
Fix the axis from 0.5 to 5.25
Add a title
Set axis ticks to none
On the y2 marks card, edit the Colour shelf and change the markers to show to All (this gives the circles on each end of the line)
Change the Min(x) pill to be FLOAT(Min(x)) using the ‘type in pill’ function
Edit the x axis to be Fixed from -0.3 to 19.9
cutting off the end of the axis makes the end circle disappear. This is very sneaky, and I had to see the solution for this!
Hide the x axis
Remove the row divider lines
So we’ve now got the core viz – hooray! But we’re not quite done – boo!
Expand /Collapse
Ivett set the viz to expand to show a column by Sub-Category on click.
This is another requirement I couldn’t get however hard I tried. I duplicated my viz and created a version with Sub-Category on Columns and tried to use Parameter Actions to set a parameter that would control which viz would display using a sheet swap techinque. However when I added the necessary field to the Detail shelf, all the polygons disappeared and I don’t understand why…. I therefore published my first instance of this challenge with a parameter the user controlled to decide which view to show. This is here.
So I had to look at Ivett’s solution to see how she had achieved this, and it involved sets.
Right click on Sub-Category and Create – > Set
Selected Sub-Cats
Don’t select any values at this point.
Create a field
Expand
IF [Selected Sub-Cats] THEN [Sub-Category] ELSE ‘Click to Expand’ END
If there are values in the set, then the set values will be returned, otherwise the text ‘Click to Expand’ will display.
Add this to the Columns shelf, and ‘Hide field labels for columns‘ so the text ‘Expand’ doesn’t show.
A dashboard action will ultimately drive the behaviour, but we can test the display by editing the set and selecting all values.
The view should expand as expected, showing a column per Sub-Category
But the name of the Sub-Category is still displayed on the label, and in the example this isn’t the case. To fix this I created another calculated field
Label: SubCat
IF [Selected Sub-Cats] THEN ” ELSE [Sub-Category] END
which I added to the Label shelf, and adjusted the display to suit.
‘Reset’ the view to show the collapsed version by re-editing the set and removing all values.
How to Read this chart legend
I simply duplicated the main viz, and filtered by Sub-Category = Chairs.
I removed the label, and then used the Annotate Point functionality to add the relevant labels against the points.
Profitable Legend
The Profitable / Non-Profitable legend makes use of our trusted friend MIN(0), with pills arranged as below.
Applying the Set Action
Create a dashboard to the size specified, and just use floating objects to position the text and various sheets.
To drive the expand / collapse function, create a Set Action on the main viz as below, that targets the Selected Sub-Cats set.
Revenue / Budget Label
This is just managed using Text objects on the dashboard, carefully positioned in the right locations. You might need to add additional objects to get the layout required.
The background of the whole dashboard is set to light grey and the sheets and objects need to be set to the same grey or white to get the same presentation.
So fingers crossed, you should have a complete solution now.
My final version (after I peeked at Ivett’s) is here.
Designing for mobile isn’t something I inherently think about when building a viz, so, this week’s #WOW challenge from Lorna was a useful reminder on the inbuilt functionality Tableau has to make a display typically aimed at a laptop user, also render well on a mobile display with minimal effort.
Tableau allows you to choose a variety of device layouts and define specific instances of how the sheets should display on each, but this challenge was focused on the Automatic Phone Layout option, where the aim was to build a 1200×800 dashboard, which automatically rendered vertically when viewed on mobile. Lorna referenced this Tableau blog as a starting point, which is definitely worth a read.
YTD Summary by Sales
In the requirements, Lorna mentions ‘last 2 years YTD’, but given the dataset used contains years 2016-2019, and the challenge wasn’t really focused on this area, I just presented sales for the whole year as follows :
YEAR(Order Date) to Rows sorted by Order Date descending (so 2019 is listed first)
SUM(Sales) on Text with a Quick Table Calculation of Percent Difference. By default, this calculation will work ‘down’ the table, so we need to edit the table calc so it is relative to the Next record, rather than Previous.
Format this field using custom formatting of ▲0%;▼0% ( I use this site to copy & paste the shape images from)
Add another copy of SUM(Sales) to Text and then modify the text to format as required.
To only display Years 2018 & 2019 either
Filter the Order Date to exclude 2016, and additional filter the SUM(Sales) % difference table calculation to only show non-null values
or select year 2016 and 2017, right-click and choose Hide
Add YEAR(Order Date) to Colour and adjust to suit
Apply formatting to remove row/column/grid lines/ row banding. hide labels and rotate headings
Change the title of the sheet to YTD
Repeat all of this to create a version for Profit.
Sales Trend Line
This is pretty much Desktop 101 – one of the first charts you’re likely to build when training on Desktop 🙂 So here’s just the picture 🙂
Again repeat to create a version for Profit.
Top 10 Products by Sales Bar Chart
In the requirement Lorna stated (and displayed a title for) Top 10 Customers by Sales, but the chart displayed in her solution showed Products.
I chose therefore to show the same as Lorna (with a corrected title). The instruction in the requirements was also to use Sets. So to do this
Top 10 Products by Sales
Right click on Product Name, select Create -> Set and adjust as below
Then to build the chart
Add Sales to Rows
Product Name to Columns, sorted by Sales descending
Sales to Text
Order Date to Filter set to 2018 & 2019 only
Top 10 Products by Sales to Filter
At this point you might find you only have 9 rows displayed, and this is because the 10 largest product sales are considering all years, before then filtering by the years. We need toset the Order Date filter to be Added to Context (right-click on pill). This will force Tableau to filter the data by the relevant years. and then work out the Top 10. When added to context the pill will be grey rather than blue on the filter shelf.
Apply formatting as required
Top & Bottom 10 Products by Profit
Similar to above, I created a Top 10 Products by Profit set and also a Bottom 10 Products by Profit set ( just change the ‘Top’ drop down to select Bottom).
Top & Bottom Products by Profit
To the get a Top 10 & Bottom 10 set, you can create a combined set. I can’t recall if I’ve ever come across this before… I think it was probably part of some #WoW a long time ago… it certainly isn’t something I use regularly.
Right click on one of the Profit sets created and select Create Combined Set and choose your sets in the presented dialog. In this instance we want all members from both sets.
To build the chart
Add Profit to Rows
Product Name to Columns, sorted by Profit descending
Profit to Text
Order Date to Filter set to 2018 & 2019 only and added to context
Top & Bottom Products by Profit to Filter
We need a ‘header’ to label the Top & Bottom. I added Top 10 Products By Profit to Rows, and placed it in front of Product Name. This works since the sets are mutually exclusive – no product can be in both the top 10 and the bottom 10. This will display header values of In or Out, which I then aliased to display Top 10 or Bottom 10 (right clicked on ‘In’ and Edit Alias). Then just format to suit.
Building the Dashboard
We’ve got all the component parts , so now we want to add to the dashboard in such a way that the sheets will display vertically when viewed on mobile, even though the requirements stipulate a 1200 x 800 ‘desktop’ type layout.
The requirements also state to use the Automatic Phone Layout option only, which means you shouldn’t have a need to ‘fiddle’ with how the components are displayed when in mobile view.
Basically you’re building on the Default view
but when you click the Phone option, the padlock remains ‘locked’ ie the ‘automatic’ view, and all the objects display in the right order on top of each other.
The key to this, is knowing the fact that the automatic phone layout follows an A-Z approach – across the page from left to right, then down and across from left to right again.
All charts were set to Fit Entire View, and I didn’t spend any time creating specific layout containers – I just dropped the objects in the places I wanted.
This subsequently presented as below when I clicked the Phone option on the Dashboard tab
The part I found most tricky was getting the Top & Bottom 10 Products chart to display the detail I could see when viewing Lorna’s solution. I simply ended up needing to adjust the font sizes to be size 6 on the labels and the displayed product names, and moving the column sizer so I had enough of the bar to display.
By Week 20 of #WOW2020, Tableau v2020.2 was released, so guest challenger, Sean Miller, returned with a challenge to demonstrate one of the new bits of functionality that had been released – the ability to remove values from a set via dashboard actions.
Selected states on the map are highlighted and added to the list displayed on the right; clicking a state on the list then removed it from being highlighted on the map.
This felt like it should be straightforward, which was quite a relief after the last couple of weeks, and I’m hoping this blog doesn’t take too long to write 🙂
Spoiler alert! It was straight forward – hurrah! I do really value these ‘simpler’ challenges, when the main purpose is to introduce new features and functionality. In a business environment, it’s not always possible to work with the latest release, so having these challenges as a useful working example of a new feature to reference in future, is of great benefit.
So, if you hadn’t already realised, you’re going to need v2020.2 to complete this challenge, which you can get here , or you may need to use Tableau Public instead.
Creating the State set
Double-click State, double-click Sales and change mark type to Filled Map, and you’ve got your basic starting point 🙂
We want to be able to have different colours for the states depending on whether they’re in or out of a set of selected states.
Create a Selected States set by right clicking on State and selecting Create -> Set. Choose a random set of states.
Note – in v2020.2, the Selected States set is now just listed in the top left hand ‘dimensions’ pane (which also is now no longer labelled dimensions) with a ‘set’ icon to indicate its ‘data type’, rather than being listed in a separate ‘Sets’ section towards the bottom.
Drag this set onto the Colour shelf of the map. The states selected in the set will be coloured differently from the other states. Adjust the In/Out colour legend to suit.
Remove the map features
The displayed map in the solution is very ‘clean’; all labels,non US countries etc are hidden. This is set by selecting Map -> Map Layers from the top menu, and unchecking all the pre-selected Map Layer options.
Map Tooltip
The tooltip displays more measures than just the Sales, so we need some calculated fields
# Orders
COUNTD([Order ID])
# Customers
COUNTD([Customer ID])
Add these to the Tooltip shelf and adjust the display accordingly.
Creating the State List
There are several ways to build a basic list, but to get the formatting nicer, I created it using a bar chart as follows
Type in ‘Min(1)’ to Columns
Add State to Rows
Add Selected States to Filter shelf
Add State to Text shelf
Adjust Text to read ‘x <State>’ and format to white text, and align left middle
Adjust the colour of the bar to suit, then
Uncheck Show Header from the State pill in the Rows
Adjust the axis to be fixed from 0 to 1
Hide the axis.
Change the Tooltip text to ‘CLICK TO REMOVE’
Change the title to include the instruction
Invoke the Set Actions
Add both sheets to a dashboard.
Add a Change Set Values Dashboard Action to the Map sheet as below
As you click a state or select a group of states, it/they will be added to the list.
Add another Change Set Values dashboard action, this time based off the state list as follows
As you click on a state in the list now, it will disappear from the list and the state will no longer be highlighted on the map.
You will notice though, that as you click states on the map, your selections remain ‘selected’ until you click again. To fix this we need to use a little ‘true = false’ trick to ‘automatically deselect’ the states.
Automatically deselect states
Create new calculated fields called
True
True
False
False
and add both of these to the Detail shelf on the Map sheet.
Then back on the dashboard create a Filter action as follows
Building the Bar Charts
The above has completed the main part of the challenge and demonstrates the new feature, the option to Remove values from set in the Set Action dialog.
However Sean added some extra charts to the display.
Sales Chart
Add Sales to Columns and Selected Sales to Colour. Change Sales to be a Quick Table Calculation and set it to Percent of Total.
The tooltip and the chart title have additional information so we need
Total Sales
{FIXED:SUM([Sales])}
Sales of Selected States
{FIXED: SUM(IF [Selected States] THEN [Sales] END)}
% Sales of Selected States
[Sales of Selected States] / [Total Sales]
Format all these accordingly.
Add all these to the Detail/Tooltip shelf as required and amend Tooltip and Chart Title as necessary.
Orders Chart
This is pretty much the same as above….
Add # Orders to Columns and Selected Sales to Colour. Change # Orders to be a Quick Table Calculation and set it to Percent of Total.
The tooltip and the chart title have additional information so we need
Total Orders
{FIXED:COUNTD([Order ID])}
Orders of Selected States
{FIXED: COUNTD(IF [Selected States] THEN [Order ID] END)}
% Orders of Selected States
[Orders of Selected States] / [Total Orders]
Customers Chart
This doesn’t work quite the same (I only found out after the event), as a customer can order against more than 1 state it would seem. So while you’ll still need the following
Total Customers
{FIXED:COUNTD([Customer ID])}
Customers in Selected States
{FIXED: COUNTD(IF [Selected States] THEN [Customer ID] END)}
% Customers of Selected States
[Customers of Selected States] / [Total Customers]
we need to build it as a dual axis, with % Customers of Selected States on Columns alongside Min(1)
This should be all the building blocks needed. My published viz is here.
Week 19 of #WOW2020 saw Ann provide this challenge, which she declared would be easier than Luke’s from the previous week.
The aim was to start with a chart by week, but on selection drill in to show a by day view, with the slight twist that all days between the first and last selected dates should display, even if the specific week in between wasn’t explicitly selected.
I knew this was going to involve set actions (although there was a clue on the ‘latest challenges‘ page – I tend to pick up the challenges through Twitter, so often bypass this page, but it’s worth a look for extra clues :-))
So on the face of it, the challenge seemed as if it should be ok, but it’s usually only when I start building that things can start to unravel.
One area that Ann hadn’t been explicit about in the requirements, was the behaviour in certain scenarios. I had to revise some of my initial attempts/calculations in order to match Ann’s workbook. This week I’m not going to go into all the wrong turns, but this is the behaviour I observed in Ann’s workbook, and so ultimately tried to replicate.
When at the week level, if multiple points are selected, the days then displayed should start from the first day of the first week selected (which will be a Sunday as we’re working with how the US standardises a week start), and finish on the last day of the last week selected (which will be a Saturday).
When at the week level, if a single point is selected then the 7 days in that week only should be displayed.
When at the day level, if multiple points are selected, the days then displayed start from the first day of the week the first day is in and end on the last day of the week the last selected day is in (so you may get more days than actually selected). eg if the 1st day selected is a Tuesday and the last day selected is a Friday, the ‘drill in’, will start on the previous Sunday, and end on the following Saturday. This isn’t necessarily what you may expect to happen.
Based on the above, when at the day level, a single day is selected, the ‘drill in’ will show the 7 days in the same week. Once you’re down to displaying the 7 days in the same week, clicking on any single day or selecting a couple of days, won’t appear to do anything as the same results are displayed.
Being aware of the above, will explain why some the calculations I end up with look the way they do.
Ok, let’s get on with the build…..
Ann stated that she’d hardcoded to the ‘last 52 weeks’, but essentially it looked like she was displaying data for all of 2019. This wasn’t really something the challenge was testing, so I simply started by adding a data source filter for the year 2019 (right click on data source -> Edit data source filters). I also had to set the date properties of my data source to set a week to start on a Sunday as I’m UK based, so my week’s are defaulted to start on Mondays (again right click on data source -> Date Properties). As a result of my year filter, I didn’t seem to have the same starting/ending dates as Ann, but as already stated, this wasn’t the main aim of the challenge, so I didn’t stress about it.
I decided quite early on that I was going to also use a Parameter Action to decide the level I was at (ie had I drilled in or not). I wasn’t entirely sure when I started how many ‘levels’ I might need, so chose to use an integer parameter for this. I ended up only needing 2 levels, so a boolean could have worked equally as well, or, as I type, I think I could have used this parameter to store the ‘date level’ (day or week) I want to display my dates at, which would have made some of my calculations easier to read. I’m not going to do this though.
Drill Down
Integer parameter set to 0 by default.
Now I want to define a date field that I’m going to use on the axis, that will vary depending on the ‘level’ we’re at.
Date to Plot
IF [Drill Down]>0 THEN DATETRUNC(‘day’, [Order Date]) ELSE DATETRUNC(‘week’, [Order Date]) END
Note – I used >0 as I wasn’t sure if I’d have levels 0, 1 & 2… as it turns out I just used 0 & 1 in the end.
Add Date to Plot to Columns as a Continuous, Exact Date and Sales to Rows and we’ve got our starting point
Change the Drill Down parameter to 1 and the chart will change to display at the day level
Drilling Down on selection
On selection, we want to add the selected dates into a set, so first up, we need to define that set.
Right click on Date to Plot and Create -> Set
Selected Dates
Select some random dates so we can test with. These will get set properly later based on the Set Action we define on the dashboard.
Based on the dates in the set, we need to determine a min and a max date we can then use to restrict the dates being plotted on the chart.
Min Date
{FIXED:MIN(IF [Selected Dates] OR [Drill Down]=0 THEN DATETRUNC(‘week’,[Date to Plot]) END) }
This either gets the 1st day of the week based on the earliest date in the set, or the 1st day of the week of the whole data set (when we’re at the ‘starting’ level with Drill Down =0).
We also need
Max Date
IF [Drill Down]=0 THEN {FIXED:MAX(DATEADD(‘day’,-1,[Date to Plot]))} ELSE DATEADD(‘week’,1,{FIXED:MAX(IF [Selected Dates] THEN DATETRUNC(‘week’,[Date to Plot]) END)})-1 END
If we’re at the starting level (Drill Down = 0) then we want the last day within the latest week in the data set (this field is used in the title display, so necessary to get this to display right), otherwise we need to get the last day of the week associated to the maximum date in the set.
Now we need to be able to restrict the dates displayed in the chart based on these
Dates to Include
[Order Date]>= [Min Date] AND [Order Date]<= [Max Date]
Add this to the Filter shelf and set to True.
Your display shouldn’t change, as we’re still at ‘level 0’, even though our set has random dates selected. If you now manually change Drill Down to 1, you should see a change
Setting up the dashboard actions
Ok, now we’ve got the basic idea, let’s get it all working properly with dashboard actions before we sort out all the other bits n bobs.
First up, let’s manually reset everything by setting the Drill Down parameter to 0 and emptying all the values selected in the set.
Add the sheet onto the dashboard, and create the set action, which is set to target the Selected Dates set and to Keep set values if you click on a blank area of the chart after selection.
However, this on it’s own won’t change the display. We need to set the Drill Down parameter to 1 too.
For this we need another field
Set Drill DownLevel
1
Add this to the Detail shelf of the chart.
Then back on the dashboard, add a Parameter Action that targets the Drill Down parameter using the value stored in the Set Drill Down Level field
Now if you select the dates in the dashboard, you should get the desired behaviour, and if you select again, you should filter the days selected further (as per the behaviour described at the top of the page).
Resetting the display
The Reset button is actually another sheet.
I created a ‘fake’ bar chart by adding MIN(1) to Rows, and double clicking in the space below the Detail and Tooltip shelves on the Marks card, and typing the text ‘CLEAR SELECTION’. This creates a ‘pill’ without having it defined explicitly as a calculated field, and I added this to the Text shelf, and centred/formatted appropriately. I then set the axis to be fixed from 0 to 1 and hid it.
I set the colour to #a26dc2 and set the text to ‘match mark colour.
We only want this sheet to display, if we’ve ‘drilled down’, so I need
Show Reset
[Drill Down]=1
which is added to the Filter shelf and set to True.
I also need a parameter action off this sheet, to reset the Drill Down to 0 on selection. For this I need another field
Reset
0
which is added to the Detail shelf.
Depending on what you’ve already been playing around with, there’s a chance this sheet may already be empty. Get the dashboard into a state where you’ve drilled down to the day level, then add this sheet, and add another Parameter Action.
Reset is set to run off the ‘button’ sheet only, to target the Drill Down parameter by using the value in the Reset field.
You should now be able to test all this out and get the desired behaviour.
Adding Animations
To get the chart to transition between selections, we need to use the Animations functionality.
On the Format menu, select Animations and adjust the settings as you choose.
Play around and you should have the main features of this challenge now working
Dynamic Title
The title needs to change based on whether you’re at the weekly or daily level. It also needs to show total and average sales. So for all this I need
LABEL: Level
IF [Drill Down]>0 THEN ‘Day’ ELSE ‘Week’ END
LABEL: Subtitle Level
IF [Drill Down]>0 THEN ‘Daily’ ELSE ‘Weekly’ END
LABEL: Instruction
IF [Drill Down] = 0 THEN ‘SELECT WEEKS TO DRILL DOWN TO DAILY VIEW’ ELSE ‘CLEAR SELECTION USING BUTTON’ END
Total Sales
WINDOW_SUM(SUM([Sales]))
Avg Sales
WINDOW_AVG(SUM([Sales]))
All these fields are added to the Detail shelf of the main chart along with Min Date and Max Date, and then the title is edited and formatted accordingly to reference these.
Tooltips
In a similar manner, the tooltips also need adjusting, they just need to refer to the LABEL: Level field
Colour of the Line
Although not explicitly mentioned, the line colour seemed to change from a lighter shade at the weekly level to more intense at the daily level. To achieve this I created
COLOUR
[Drill Down]
and added to the Colour shelf. This gives a colour legend displaying either 1 or 0 depending at what state you are in the interaction. Set colour values accordingly.
Average Line
Add this to the chart simply by selecting Average Line from the Analytics pane and dragging then dropping onto the Table shelf that appears. Format appropriately.
And apart from other basic formatting to remove gridlines/ axis titles etc, that should be it.
I’m starting to write this blog with a bit of uncertainty today as I’m not ultimately sure where I’m going to end up….
I wasn’t even sure I was going to pen an entry this week…. I found Luke’s challenge tough, and the path I took full of multiple wrong turns, that meant trying to write out a comprehensible ‘how I did it’ quite tricky, as reviewing now I’m questioning ‘what did I do that for…’.
Whilst with perseverance and a bit of inspiration from Rob Saunders, I did manage to post a working solution, I knew I wasn’t overly happy with it, particularly because my expand and collapse functions didn’t behave as I saw others do… I had to click twice to collapse.
Before starting to write, I decided to check out Luke’s workbook which he’d finally published to see if I could understand where I was going wrong.
I’d used both a set action and parameter action in my attempt; Luke had just a parameter action. In trying to understand why I ended up with a set action and see if I could do without it, my solution gradually started unravelling, as various calculated fields needed changing.
So, I think the best way to approach this blog is to rebuild my solution from scratch, using only my existing workbook as a reference, and I will attempt to write and screen shot as I build. This could take some time, and I have no idea how successful I’ll be…. I may well get to a point where I’ve taken a wrong turn again, and everything I’ve written needs to be scrapped… at which point I may just have to say ‘sorry, I tried!’……
Ok, let’s get cracking.
Top N Sub-Categories
At the ‘first’ level of the table, we need to display the top n sub-categories ordered by Profit. Those not in the top n should be displayed under an ‘All Others’ grouping, and always displayed at the bottom. The top n can vary based on the user input.
We need a parameter to define the value of the top n.
SUB-CATEGORIES TO SHOW
This is an integer parameter ranging from 1-10, defaulted to 3. I’ve titled it exactly as displayed on the output, so it’s one less change to make later (hence the capitals).
The easiest way to group the sub-categories into those in the top n, is to use a set. Right click Sub-Category and Create -> Set.
Top N SubCats by Profit
Use the Top tab to define the rules for which Sub-Category to include, referencing the SUB-CATEGORIES TO SHOW parameter and the Profit field as shown below.
SubCat Group
IF [Top N SubCats by Profit] THEN [Sub-Category] ELSE ‘All Others’ END
If the Sub-Category is in the set then the name will display, otherwise it will be grouped under the ‘All Others’.
Add SubCat Group to Rows and change the sort on the pill to sort by Field = Profit desc
This will result in All Others being listed at the top, but to resolve that, drag the Top N SubCats by Profit set onto the Rows an place in front of the SubCat Group.
Let’s get some of the measures we need out onto the canvas.
Margin
SUM([Profit])/SUM([Sales])
format to percentage with 0dp.
Add Margin, Profit & Sales onto the sheet.
Top N Products
The next level in the table displays the Top N products per SubCat Group based on their margin. For those not in the Top N, the products should be grouped under ‘All Others’, and listed at the bottom. The Top N is once again defined by a user input via a parameter.
PRODUCTS TO SHOW
Once again an integer parameter ranging from 1-10 but defaulted at 5 this time.
Unfortunately, this time we can’t use a set to define our Top N grouping. This is because the set will only consider the Top N across all Products and will not consider the fact the Products are nested per Sub-Category grouping. So we need to come up with an alternative. I did do a bit of research to find ideas, and found this article by Emma Whyte at The Information Lab to get me started : Showing a Nested Top N with Other in Tableau.
The Top N needs to be based on the order of the Margin, or the rank.
Margin Rank
RANK_UNIQUE([Margin])
Change this to be a Discrete field.
This will give us a unique ‘number’ per row displayed based on the value of Margin. If records have the same Margin value, using RANK_UNIQUE will mean they get a different rank number (as opposed to how other ranking functions work). The table calculation of INDEX() could work just as well. NOTE – there is potential though that when the Margin values are the same for different products, what makes the Top N and what doesn’t may differ, so you might find you get a slightly different list from some of the solutions you see.
Add Margin Rank and Product Name onto the sheet, and edit the table calculation on Margin Rank, to compute by Product Name only
We now want to group the Product Name based on the Top N products.
Product Name Group
IF [Margin Rank] <= MIN([PRODUCTS TO SHOW]) THEN ATTR([Product Name]) ELSE ‘All Others’ END
We also only want to show (PRODUCTS TO SHOW + 1) rows per SubCat Group ie if PRODUCTS TO SHOW = 5, we want to display 6 rows per SubCat Group, where the 6th row displays ‘All Others’. The 6th row also needs to show the Profit, Sales and Margin values associated to all the Products in the ‘All Others’ Product Name Group.
So we need to calculate some new fields that will store a revised value for Proft, Sales and Margin, depending what row we’re working with.
Sales For Others
WINDOW_SUM(IF [Margin Rank] > [PRODUCTS TO SHOW] THEN SUM([Sales]) END)
If the Product Name isn’t in the Top N, then get it’s Sales value, and then sum all of those rows that meet the same condition.
If you put this onto the sheet, and set the table calculation for each of the nested calculations (Sales for Others & Margin Rank) to be by Product Name only, you’ll see that the value displayed in every row for each SubCat Group is the sum of the values associated to the rows in the ‘All Others’ group.
So now we need a field that’s either going to display the Sales for the 1 product or the sales for the group of products, depending on what row we’re on.
Grouped Sales
IF [Margin Rank]<= MIN([PRODUCTS TO SHOW]) THEN SUM([Sales]) ELSE [Sales For Others] END
Again the table calculation settings need to be set to compute by Product Name only. This is the field we ultimately want to display, so it needs to be formatted accordingly. The Sales and Sales For Others fields can be removed.
Along similar lines, we need
Profit For Others
WINDOW_SUM(IF [Margin Rank] > [PRODUCTS TO SHOW] THEN SUM([Profit]) END)
Grouped Profit
IF [Margin Rank]<= MIN([PRODUCTS TO SHOW]) THEN SUM([Profit]) ELSE [Profit For Others] END
Margin For Others
IF [Margin Rank] > [PRODUCTS TO SHOW] THEN [Profit For Others]/[Sales For Others] END
Grouped Margin
IF [Margin Rank] <= MIN([PRODUCTS TO SHOW]) THEN [Margin] ELSE [Margin For Others] END
When added to the sheet, once again make sure all the table calculation properties for all the nested calculations are set to compute by Product Name only.
As mentioned above, we only want to show PRODUCTS TO SHOW + 1 rows, so let’s create a field we can filter by :
Show?
[Margin Rank] <= MIN([PRODUCTS TO SHOW]) + 1
Add this to the Filter shelf and set to True. Once again the table calculation needs to be set to compute by Product Name only.
Add Totals
We want a grand total and subtotals only at the level of SubCat Group. Add the totals by Analysis -> Totals -> Show Column Grand Totals to get the overall total. Then on the SubCat Group pill, click and add SubTotals
We’ve now got the main components of the table. We now need to get the interactivity working to allow the expand / contract on arrow selection.
Expand / Contract All Others Product Group
First up, we’ll just tidy up our table display
Hide the In/Out Top N SubCats By Profit field
Hide the Margin Rank field
Hide the Product Name field.
Remove the Margin field
Alias the Grouped Margin, Grouped Profit, Grouped Sales fields
Right click and Edit Alias. Name the field ‘ Margin ‘ (note the trailing and leading spaces. You can’t alias just as Margin as a field already exists with that name, but the spaces make it think it’s a new name.
We need an additional field that will store our ‘arrow’ icon. We’re going to revisit this field. For starters
Product Group Header
IF [Margin Rank] <= [PRODUCTS TO SHOW] THEN ” ELSE ‘►’ END
I use this site to get my icon characters from. Add this field to the sheet after the SubCate Group pill, once again setting the table calculation tocompute by Product Name.
In the dashboard, the aim is to click on an arrow associated to a single SubCat Group, which will expand the Product Group Name field to display the actual Product Name (rather than ‘All Others’) with their associated Margin, Sales & Profit values, and also show a ▼ icon.
This will be achieved using Parameter Actions, for which we need a parameter :
Selected Sub Category Group
String parameter defaulted to ”
Display this parameter on the sheet, as we can start to test the interactivity ‘manually’ without the need for the dashboard. What the dashboard action will do is on ‘click’, it will be set to populate the value of this parameter with the associated SubCat Group value. We can then do some checks based off of this and set various fields accordingly. It means we need to revisit some of the fields.
First up let’s set the arrow….
Edit Product Group Header to be
IF [Selected SubCat Group] = MIN([SubCat Group]) THEN IF [Margin Rank] > [PRODUCTS TO SHOW] THEN ‘▼’ ELSE ” END ELSEIF [Margin Rank] <= [PRODUCTS TO SHOW] THEN ” ELSE ‘►’ END
Test this by entering the value of ‘Copiers’ into the Selected SubCat Group parameter. The arrow against ‘All Others’ should change.
We also need to change the value of the Product Name Group to show the actual Product Name on selection, so
edit the Product Name Group
IF [Margin Rank] <= MIN([PRODUCTS TO SHOW]) THEN ATTR([Product Name]) ELSE IF [Selected SubCat Group] = MIN([SubCat Group]) THEN ATTR([Product Name]) ELSE ‘All Others’ END END
Again test this out by changing the value in the parameter.
But we need to make more rows show too, so
edit Show?
([Margin Rank] <= MIN([PRODUCTS TO SHOW]) + 1) OR (MIN([SubCat Group]) =[Selected SubCat Group])
The values of our measures are still the totals though, so we need to edit these fields to
Grouped Sales
IF ([Margin Rank]<= MIN([PRODUCTS TO SHOW]) OR ([Selected SubCat Group] = MIN([SubCat Group])) ) THEN SUM([Sales]) ELSE [Sales For Others] END
Grouped Profit
IF ([Margin Rank]<= MIN([PRODUCTS TO SHOW]) OR ([Selected SubCat Group] = MIN([SubCat Group])) ) THEN SUM([Profit]) ELSE [Profit For Others] END
Grouped Margin
IF ([Margin Rank] <= MIN([PRODUCTS TO SHOW]) OR ([Selected SubCat Group]=MIN([SubCat Group]))) THEN [Margin] ELSE [Margin For Others] END
However while this works if you play with setting and clearing the parameter on the sheet, it won’t quite fully work if added as a dashboard action, as while the action can set the parameter we can’t ‘clear it’.
We need to ‘tie’ the parameter action to another field
SubCat Group for Reset
IF [Selected SubCat Group] = MIN([SubCat Group]) THEN ” ELSE MIN([SubCat Group]) END
Add this to the Detail shelf (if you want to see how it changes based on the parameter value, add it to the Rows and test changing the parameter).
It needs to be on the sheet so it can be referenced from the dashboard action.
Adding the Action
Add the sheet to a dashboard, then add Parameter Action as below where the Target Parameter is Selected SubCatGroup and the field it references is SubCat Group for Reset
And after all that, you should have a working solution. Phew!
Pretty pleased I got there without taking a detour 🙂 The table just now needs various formatting applied, which I’m going to leave to you to do 🙂 Just tweet me if you’re having problems!
The parameter action may be confusing you a bit – it took a while to really get my head round it, so I’ve tried to explain this a bit more below…
How the parameter action works
On initial load of the sheet, the Selected SubCat Group parameter is blank. So for the SubCat Group = Copiers, the SubCat Groupfor Reset will also be Copiers as SubCat Group is not the same as Selected SubCat Group parameter. Other fields are also set based on the fact these two fields aren’t the same (like the arrow pointing to the right etc).
When the right arrow is clicked on the dashboard, the value of SubCat Groupfor Reset is used to populate the Selected SubCat Group parameter. So in this example, Selected SubCat Group will now contain the value Copiers. As the Selected SubCat Group parameter is now the same as the SubCat Group, various fields change their behaviour (like the arrow now points down, and more rows are displayed). But also, the value of the SubCat Groupfor Reset is also changed; as the SubCat Group is the same as the Selected Sub Cat Group parameter, SubCat Group for Reset now contains a blank string.
So at the point the down arrow is now clicked again on the dashboard, the value of the SubCat Group for Reset is again used to populate the Selected SubCat Group parameter. As SubCat Group for Reset is blank, then the parameter will now be populated with a blank value, and so all the login in the fields will be based on the fact that SubCat Group is not the same as the Selected SubCat Group parameter, and the table will display just as it did on first load.
Hope that helps to demystify what’s going on… it’s certainly helped me!
Thanks for sticking with me if you got this far 🙂
The version of the challenge I built while writing this (my 3rd published version) is here.
