Can you visualise percent difference from a selected date?

Donna ColesLeave a comment

Lorna Brown returned this week with her first challenge of 2022 which was aimed at practising tableau calculations, although when I completed the build, I realised I actually didn’t use any – oops! I’d ended up using an LoD based solution instead. As is often the case in Tableau, there can be multiple ways to solve a problem, so I created another solution involving table calcs. As I blog I will endeavour to provide both sets of calculations used and make it obvious when I do so. Hopefully, it’ll all make sense 🙂

Building the calculated fields

After connecting to the data, the first step I did was to create a parameter to store the ‘selected date’. I hard coded/defaulted this to 1st Nov 2020.

pSelectedDate

Date parameter defaulted to 01 Nov 2020

Show this on the worksheet, so you can test what happens when you manually change it later.

As I typically do, I’m going to work in a tabular format to sense check all the calculations we’re going to need before I start visualising the information graphically.

To start add Company then Date (set to exact date & discrete) to Rows then add Close to Text and change the aggregation to AVG.

I now want to work out what the value of the average Close is on the selected date (in this case 01 Nov 2020) for each company, and I want this value to be displayed against every row associated to that company.

Close on Selected Date per Company

LOD Option

{FIXED [Company]: AVG(IF [pSelectedDate]=DATETRUNC(‘month’,[Date]) THEN [Close] END)}

Table Calc Option

WINDOW_MAX(AVG(IF [pSelectedDate]=DATETRUNC(‘month’,[Date]) THEN [Close] END))

Drag this field onto the table. If you’re using the LoD option, change the aggregation to AVG (although as we’re working with the data at the lowest granularity, SUM gives the same value, it just makes ‘more sense’ to me). If you’re using the Table Calc option, change the table calculation so that it is computing by Date only. In the below screen shot, I have included both versions of the calculation which match each other. Scrolling down, you should be able to verify that the value displayed in the new field(s) for all rows for a specific company, matches the value in the first column for the selected date. Test by entering different dates in the parameter (though make sure you’re just entering 1st of month each time, as that is what our Date field contains).

Now that for each row we have an average Close value and the average Close value on a specific date, we can work out the percentage difference for each row.

% Diff from Selected Date

LOD option

(AVG([Close])-AVG([Close on Selected Date per Company]))/AVG([Close on Selected Date per Company])

Table Calc option

(AVG([Close])-[Close on Selected Date per Company])/[Close on Selected Date per Company]

There is a slight difference in the syntax as the table calc option is referring to a field that is a table calculation and which is already aggregated, so there’s no need to aggregate again (ie wrap in an AVG…. Tableau will error if you try to do this).

Format this to a Percentage with 0 dp.

Once again I am choosing to show both versions on the screenshot below. Again if you’re using the table calc version, you need to verify the calculation is set to compute by Date only.

So now we have the core fields we need to start building the viz.

I am now just going to refer to the calculated field names I’ve mentioned above and not differentiate between LoD or table calc version. The only thing to remember is when adding the table calc fields, set the compute by, and if you’re using the LoDs, set the aggregation to AVG (although SUM will work too due to the level of aggregation we’re at).

Building the viz

On a new sheet add Date to Columns and set to the continuous Month level or exact date (either will work in this instance, since we only have 1 date per month in the data set). Add % Diff from Selected Date to Rows. Add Company to Colour and adjust to suit (use the hue circle colour palette).

Add Company to the Label shelf and also add % Diff from Selected Date to the Label shelf.

Adjust the Label settings, so the labels only display at the end of the line.

To make the text match the mark colour, click Label >then expand the Font dialog by clicking the arrow to the right, and select Match Mark Colour.

To format the % value displayed, right-click on the % Diff from Selected Date pill on the Text shelf, and select format. In the formatting pane that appears on the left hand side, change the number formatting to custom â–²0.0%;â–¼0.0% ( I use this site to get the shapes I need – just copy and paste)

Now rearrange the text on the label so that company is displayed first.

To format the Date field within the tooltip and the axis, right click on the Date pill in Columns and select format. On the Format Date pane on the left hand side, set the Dates value on the Pane tab, in the Default section, to custom : mmm yy. Then click on the Axis tab and set the Dates value in the Scale section to the same custom mmm yy value.

The Tooltip

We need quite a few fields on the Tooltip to get the information required.

Add Close and Close on Selected Date per Company to Tooltip. We also need a couple more calculated fields

TOOLTIP – Selected Date

[pSelectedDate]

format this field to custom mmm yy

TOOLTIP – % Diff

[% Diff from Selected Date]

Format this to % with 0 dp.

Add both these to the Tooltip too, then adjust the wording on the tooltip as required.

The Reference Line

Add pSelectedDate to the Detail shelf. Then right click the date axis and Add Reference Line. Adjust the settings so there is a Line for the Entire Table, which references the pSelectedDate value and has a custom Label of Reference Date: <Value>. Set the tooltip to None and the Line to be dotted

Format the pSelectedDate value displayed, by right clicking on the pill on the Detail shelf and set the Dates format on the Pane tab to mmm yy.

The right click on the Reference Date: Nov 20 label itself and select Format, and change the alignment to the top.

Finally, remove the axes titles, remove the gridlines and all axes rulers and change the worksheet background colour. Again, format the reference line label, so there is 0 transparency.

Test the visualisation by manually changing the date of the parameter to the 1st of another month and seeing how the viz updates.

Building the interactivity

Add the sheet to a dashboard, then create a parameter action, the runs on select of a mark on your sheet. It should change (target) the pSelectedDate parameter passing in the Date field

The only problem with this, is that when selecting a mark, the line relating to the mark is highlighted while the others ‘fade’ into the background. To prevent this from happening :

  • Create a new calculated field called True which contains the value True
  • Create a new calculated field called False which contains the value False
  • Add both these fields to the Detail shelf on the line chart.
  • Add a Dashboard Filter action, which runs on select, and has the sheet on the dashboard as it’s source, and the sheet in the workbook as it’s target. Set to show all values when selection is cleared, and filter by selected fields, setting True = False.
And with that, you should have a functioning solution – there just may be a few further formatting tweaks to apply.

My published vizzes are here

Happy vizzin’! Stay Safe!

Donna

Can you build a weekly year-over-year line chart?

Donna Coles2 Comments

Week 2 of #WOW2022 was Kyle Yetter’s first challenge as official WOW coach. On first glance when it was posted on Twitter, I thought it didn’t look too bad… I figured they’d be some ‘baselining’ of dates that I’d need to do to get the axis to display.

However, it was a little trickier than I first anticipated, mainly in trying to ensure I got the right values to match with Kyle’s posted solution (which at one point seemed to change while I was building). I’ve also realised since clicking on the link to the challenge again, that it was tagged as an LoD challenge, although there was nothing specific in the requirements indicating this was a requirement. I don’t think I used any LoDs…

Anyway onto the build, and I’m going to start by getting the dates all sorted, as this I found was the trickiest part.

Firstly connect to the data, then verify that the date properties of the data source are set to start the week on a Monday (right click data source > Date Properties

Build a basic view that displays Sales by the week of Order Date and the year of Order Date. Exclude 2018 since we’re only focussing on up to the last 2 years of data.

Examining this data compared to the solution, the first points of each line relate to the data shown against the 7 Jan 2019, 6 Jan 2020 and 4 Jan 2021. Ie the first point for each line is the first Monday in the year.

For simplicity, to make some of the calculations easier to read , I’m going to store the start date of the order date week in a field.

Order Date Week Start

DATE(DATETRUNC(‘week’, [Order Date]))

This means I can easily work out the last day of the week

Order Date Week End

DATE(DATEADD(‘day’,6,[Order Date Week Start]))

Add these fields as discrete exact dates (blue pills) onto the table and remove the existing WEEK(Order Date) field since this is the same as Order Date Week Start

When we examine the end points of each line in the solution, the points relate to the data shown against the week starting 30 Dec 2019 to 5 Jan 2020, 28 Dec 2020 to 3 Jan 2021 and 22 Nov to 28 Nov 2021. For the first 2 points, we can see the data is ‘spread’ across 2 years (ie 2 columns), which means when categorising the data ‘by year’, using the Year associated to the Order Date itself isn’t going to work. We need something else

Year Group

YEAR([Order Date Week Start])

Move this field into the Dimensions pane (above the line), then add to the table, replacing the existing YEAR(Order Date) field. 2018 will appear due to the very first row of data, but don’t worry about this for now.

If you scroll down to the week starting 30 Dec 2019, all the data is now aggregated in a single Year Group.

So things are starting to take shape. We can now work on how to filter the data.

The requirements indicate we should imagine ‘today’ is 1st Dec 2021, so we’ll use a parameter to hold this value.

Today

Date parameter defaulted to 1 Dec 2021

We only want to show data for the last 2 years and for complete weeks up to today

Core Data to Include

[Order Date Week End] < [Today] AND YEAR([Order Date Week Start]) >= YEAR([Today])-2

Remove the existing filter and add this field instead, set to True. You should now have just 3 columns and the data starting and ending at the right points.

The next area of focus is to think about how the data is going to be presented – the lines are all plotted against a single continuous (green) date axis, so we need to ‘baseline’ the dates, that is adjust the dates so they are all on the same year.

Date To Plot

MAKEDATE(2021, MONTH([Order Date Week Start]), DAY([Order Date Week Start]))

this is basically setting the week start dates to the equivalent date in 2021.

In the table we’ve been building, add Date To Plot to Rows and set to the week level and be discrete (blue). Remove the Order Date Week Start pill and move the Order Date Week End to the Tooltip as this is where this pill will be relevant in the final viz.

We’re starting to now see how the data comes together, but we’ve still got some steps to go.

I’m going to adjust the Year Group, so we can present the Current, Previous, Last 2 Yrs labels. Change as follows

Year Group

YEAR([Order Date Week Start]) – YEAR([Today])

This returns values -2, -1, 0 which means the values will be consistent even if the ‘Today’ value changes. The values can then be aliased (right click Year Group > Aliases

Next focus is on the % difference in sales. Add a Percent Difference quick table calculation to the existing Sales pill. The vales will change to those we can see when hovering over the points in the solution.

Edit the table calculation and modify to explicitly compute by Year Group, which is important to understand as, when we build the viz, whether the data is going across or down may change, so ‘fixing’ like this ensures we retain the values we know are correct.

In order to manage the custom colour formatting in the tooltip, we’re going to ‘bake’ this field as a calculated field. Press CTRL, then click and drag the pill into the data pane and name the field accordingly. If you examine the field, it’ll probably look quite complex

% Sales Diff

(ZN(SUM([Sales])) – LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))

I’m going to start building the viz now, and then we’ll add the final calcs needed for the formatting later.

On a new sheet

  • Add Core Data to Include to Filter and set to True
  • Add Date to Plot to Columns and set to a continuous week level (green pill)
  • Add Sales to Rows
  • Add Year Group to Colour and adjust accordingly
  • Reorder the values in the Year Group colour legend, so the ‘current’ line in the chart is displayed on the top, and the ‘2 yrs ago’ line is at the bottom
  • Format the WEEK(Date To Plot) field to be a custom date of dd mmm (ie 10 Jan)
  • Format the Sales axis to be $ with 0 dp
  • Add Order Date Week End to the Tooltip shelf and format the field to custom date format of mmm dd (ie Jan 10).

Now we need a couple of additional calcs to help format the % sales difference displayed on the tooltip.

% Sales Diff +ve

IF [% Sales Diff] >= 0 THEN [% Sales Diff] END

% Sales Diff -ve

IF [% Sales Diff] < 0 THEN [% Sales Diff] END

Format both these fields with a custom number format as â–²0.0%;â–¼0.0%

Add both these fields onto the Tooltip and adjust the table calculation settings of each to compute using by Year Group only

The 2 yrs ago line has no % Sales Diff value, and also no label, so we need a field to help this too

TOOLTIP: Label YoY%

IF NOT(ISNULL([% Sales Diff])) THEN ‘YoY%:’ END

This means the text ‘YoY%’ will only display if there is a % Sales Diff value.

Add this field onto the Tooltip too, and again adjust the table calculation settings.

Now format the text and tooltip as below, setting the two % Sales Diff pills to be side by side and colouring accordingly.

Hopefully, this means you now have a completed viz

My published viz is here. And nope… no LODs used 🙂 There are a few more calculated fields than what Kyle mentioned, but I could have condensed these by not having so many ‘building blocks’, but this may have made it harder to read.

I’m now off to check out Kyle’s solution and see whether I really over complicated anything…

Happy vizzin’!

Donna

Let’s analyse wildlife strikes

Donna ColesLeave a comment

Sean Miller began the start of #WOW2022 with this challenge where the focus was on layout, interactivity and maps.

  • Building the map
  • Building the bar
  • Building the area chart
  • The Unknown indicator
  • Adding interactivity

Building the map

I’m starting with this as in order to build the map, I found after a bit of trial and error I needed to build a data model which related the wildlife strike data source provided by Sean with the spatial file data source provided via the link to the community post. I downloaded all the files, but found the link to the us_ak_hi_territories_shift_conformal_faux_WM.hyper.zip was the file I needed once unzipped (the middle file to download from Sarah’s post on 19 July 2020 (see below)

Once I unzipped the downloaded file I copied the us_ak_hi_territories_shift_conformal_faux_WM.hyper file to my usual data sources repository on my laptop.

I then connected to Tableau and built a data model by first connecting to the wildlife strikes csv file, then adding a relationship to us_ak_hi_territories_shift_conformal_faux_WM on State Name = Name

To make things clearer, I created a fields to store the number of incidents

Wildlife Incidents

COUNT([2022_01_05_WW01_FAA Wildlife strikes (1990-2021).csv])

This is simply referencing the ‘count’ field that is automatically generated that is related to the wildlife strike data source.

I then built the map by

  • add Geometry and Name to Detail
  • add Wildlife Incidents to Colour

This should have created the below

Remove all the background imagery via the Map > Map Layers menu – uncheck all the options from the left hand pane.

Add Name to the Filter shelf and exclude American Samoa, Guam, Northen Marianas, Puerto Rico and the Virgin Islands. This will remove the cluster of shapes to the right (I’m not sure if this is the expected method or not..).

Change the colour palette to use the red-gold colour range.

Finally amend the Tooltip accordingly and also remove the row and column dividers.

Building the bar

The bar chart displays the top 10 incidents by species type, with the rest all grouped under ‘other’, and displayed at the bottom. We need to create a set for this. Right click on Species Type and Create > Set. Create a set based on the top 10 of the count of the wildlife incidents data source.

Species Type Set

We then need a field to display the info in the bar

Species Type to Display

IF [Species Type Set] THEN [Species Type] ELSE ‘Other’ END

ie if the Species Type is in the Species Type Set then display the Species Type, otherwise display Other.

Add Species Type Set and Species Type To Display to Rows and Wildlife Incidents to Columns and sort descending (just click the sort descending button in the toolbar)

Add Species Type Set to the Colour shelf and adjust accordingly. Remove the column and row dividers and the row gridlines. Adjust the Tooltip.

The final step we need is to make the title dynamic and display a state name if filtered.

Firstly we will need a parameter to capture the selected state

pSelectedState

A string parameter defaulted to <empty string>

We will use a parameter action to populate this parameter later. We need an additional field to use in the chart title

Title: Selected State

IF [pSelectedState] <> ” THEN ‘in ‘ + [pSelectedState] ELSE ” END

Add this field onto the Detail shelf, then adjust the chart title

Building the area chart

Add Incident Year to Columns and Wildlife Incidents to Rows and change to mark type = Area. Adjust colour accordingly, and remove the column gridlines. Adjust the Tooltip.

Again the chart title needs to be dynamic based on state name and the species type selected, so we’ll need another parameter

pSelectedSpecies

string parameter defaulted to <empty string>

Add Title: Selected State to Detail and adjust the title as below

The Unknown indicator

On a new sheet double click in the space below the marks card to create a ‘type in’ pill

Enter the text ‘Unknown Location’ (including the single quotes) and the add this pill onto the Text shelf.

Change the mark type to square and adjust the Size to the maximum.

Add Wildlife Incidents to the Tooltip shelf. Then add Name to the Filter shelf and filter to only show the Null values. Adjust the colour and the Tooltip.

Adding interactivity

Create a dashboard and use layout containers to position the charts in the relevant places. The colour legend and the ‘unknown’ indicator will need to be ‘floated’ into position.

We’re going to need 4 dashboard actions; one to set the selected State, one to set the selected Species Type, one to filter the bar and area chart based on the the selected state, and one to filter the map and area chart based on the selected species.

Select Species

this is a parameter action to set the pSelectedSpecies paramater on selection of the bar chart, using the value in the Species Type To Display. The parameter should be reset to <empty string> when unclicked.

Select State

similar to above, but runs on selection of the map chart, and passes the Name field into the pSelectedState parameter.

Filter by State

This is a filter action that runs on selection of a state in the map chart. It affects all other charts on the dashboard except the unknown sheet. It should only filter on the Name field and not All fields.

Filter by Species

Another filter action, that runs on selection of the bar chart. This one does impact all the other charts on the dashboard, but again only filters based on the Species Type to Display field rather than all fields.

Hopefully, with all this, you should have a working solution. My published viz is here.

Happy vizzin’!

Donna

Can you build a table with one measure?

Donna Coles1 Comment

For the final challenge of 2021, Luke set this challenge, to present a table containing both actual Sales values and % of Total Sales values using a single measure only.

The basic principal of this is explained within this Tableau KB article, although to get the exact display, will require a bit extra.

Building the table

Add Category & Sub-Category to Rows and Order Date to Columns. Change the Order Date so it is just displaying the month only. Add Sales to Text. Add Grand Totals and all Sub-Totals (Analysis > Totals menu). Set the totals to display at the top (Analysis > Totals menu again).

What we’re looking to do is change all the Total values in each Category section to be a % of Total instead, but just display the actual Sales value for all the other cells.

Custom Measure

IF [Size] = 1
THEN SUM(Sales)
ELSE SUM(Sales)/TOTAL(SUM(Sales))
END

Add this onto the Text shelf too and edit the table calculation so both the Custom Measure and Size nested calculations are computing by the Category field only. This should give you 0s in the Total fields for the Custom Measure value.

Remove Sales from the Text shelf, as we don’t need this now.

Format the Custom Measure

Click on the Custom Measure pill and select Format. The Format dialog should present on the left hand pane.

On the pane tab, set the default format to be $ 0dp, set the Totals format to be % 0 dp and set the Grand Totals format to be $ 0dp

Change the Total Labels

Right click on one one of the Categories (eg Furniture) and Format. On the left hand pane, change the Grand Total label to be Total.

The right click and format one of the Sub-Categories and change the Totals label to be % of Total.

Colouring the cells

Add Custom Measure to Colour (ensure the table calculation is set as described above) and change the mark type to square.

Edit the colour legend to use the green-blue-white diverging palette and set to include totals and full colour range. Set the centre of the range to be 0.

This doesn’t quite give us what we need, as the % of Totals aren’t coloured as we need.

The values for these although displaying as % are essentially the values between 0 and 1. But the ‘green’ will only display for values less than 0. So we can amend the Custom Measure to deal with this

Custom Measure

IF [Size] = 1
THEN SUM(Sales)
ELSE -SUM(Sales)/TOTAL(SUM(Sales))
END

We make the % of total to display as -ve, which now gives the colouring we need,

Finally, we need to ensure the -ve doesn’t display so we just need to re-custom format the Totals field of the Custom Measure to be 0%;0%

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Let’s visualise #WorkoutWednesday submissions

Donna Coles1 Comment

Sean Miller decided to use the data gathered by the #WorkoutWednesday team in this week’s challenge, to visualise how people use the submission tracker. I try to fill it in immediately after publishing and tweeting my solution, which is usually within the same week as when the challenge is set, unless I’m on holiday. Sometimes I do forget and as I know I’ve completed every challenge, I will fill it in if I see gaps in the tracker. However, I do think there’s something a bit awry with my submission data as there’s a few holes that don’t seem to tally properly with the weeks in the month…. there’s probably a typo or duplication in my data (if any of the team happen to read this, could you have a look.. or let me see my data to see if I can find the problem 😉 )

Anyway onto the challenge – we’re going to build a heat map and 2 bar charts and then add some interactivity between the two – nothing majorly taxing this week, so hopefully this blog won’t take too long to write… I’m prepping for Christmas so have lots to do 🙂

Building the heat map

The heat map displays the week number in the year across the top, and year and day of week down the side. We need to create some calculated fields to extract some of these values from the Timestamp date field provided (note if your Timestamp field doesn’t import automatically as a date, then right click > Change Data Type > Date to convert it).

Week Submitted

DATEPART(‘week’,[Timestamp])

Format this to custom format of 00, so you display 01 rather than 1. Drag the pill into the top half of the data pane, so it’s stored as a dimension.

Weekday Submitted

LEFT(DATENAME(‘weekday’,[Timestamp]),3)

Add Week Submitted to Columns and then YEAR(Timestamp) and Weekday Submitted to Rows.

To get the numbers and display to match Sean, you will need to ensure your week starts on a Sunday. If you’re in the UK like me, you may have it set to Monday. To change this right-click on the data source > Date Properties > Week start = Sunday.

To colour the cells, we need

Submissions

COUNT([2021-12-22-WW51-WOW_Challenge_Tracker.txt])

where the field referenced is automatically generated field that is created (ie what used to be Number of Records).

And then we need to calculate the Rank Percentile, which isn’t as scary as it sounds – there’s a handy function…

Percentile of Submissions

RANK_PERCENTILE([Submissions])

Format to a percentage with 1 dp.

Add this onto the Colour shelf, and adjust the table calculation so it is computing by all the fields.

Edit the colour range to use the one specified, and adjust the settings so it only uses 4 colours, and ranges from 0 to 1

Add Software to the Filter shelf and select Tableau

This is the core of the chart complete. Add column and row dividers, rotate the Year headings, narrow the columns and hide the row label headings.

Now we just need to sort the Tooltip. Add Timestamp to the Detail shelf, and change to the Day May 8, 2015 display. This will change the viz, but don’t panic. Re-edit the table calculation so Day of Timestamp is also checked.

Then add Submissions to Tooltip and adjust the tooltip text as required.

Finally, also add Submissions to the Label shelf and edit so the value on shows on selection.

Building the bar charts

Firstly, amend the Software filter on the heatmap so it is set to apply to worksheets > all using this data source.

Next wee need to identify whether the challenge was submitted in the same wee as it was set

Same Week Submission

([Challenge Week]=[Week Submitted]) AND ([Challenge Year]=YEAR([Timestamp]))

This returns a boolean of true or false, but we can alias these values (right click field > Aliases) to give the displayed options

Now add Same Week Submssions to Columns, Submissions to Rows and Same week Submissions to Colour and adjust accordingly. Amend gridelines, borders etc to get the required display format

For the next bar chart we need to build the text for

Challenge Submitted

STR([Challenge Year])+’w’+STR([Challenge Week])

Add this to Rows and Submissions to Columns and add Same Week Submission to Colour. Again adjust formatting accordingly.

Adding the Interactivity

On a dashboard, add a Vertical container. Drop the Heat map into it. Then underneath, still within the Vertical container, add a Horizontal container. Add the two bar charts side by side. You may have other objects, but part of your layout hierarchy should look like

Add a dashboard filter action to the heatmap chart that on select affects the two bar charts, but when unselecting, excludes all values.

As you click on the heatmap and then unclick, the bar charts should disappear and the heat map should fill out the space.

Finalise the dashboard adding a title, supplementary text, the software filter and colour legend.

My published viz is here.

Happy vizzin’!

Donna

Can you recreate this drill down?

Donna Coles1 Comment

Lorna’s final #WOW challenge of 2021 was a parameter action based challenge, where the line chart at the bottom reflects the choices made by interacting with the bar chart at the top.

When I saw this was a drill down challenge, I immediately thought of previous similar challenges, so built based on the techniques I’d applied before (see this blog). This involved a parameter to capture the ‘level’ and a ‘drill down’ calculated field to pass through into the parameter on click. The solution I built (here) worked fine on Desktop (see tweet), but when published to Tableau Public, failed to display the Sub-Categories on selection :-(. I don’t know why. Having checked Lorna’s solution after, I realised I’d over complicated my solution, and had no need for the ‘level’ or ‘drill down’ fields. So I rebuilt to see if that fixed my issue with Tableau Public, and it did. So that is the solution I’ll blog about.

As stated in the requirements, I used Superstore v2021.3, but my numbers didn’t match Lorna’s. This isn’t an issue, but explains why my solution looks different from Lorna’s if you’re comparing. Having spoken to Lorna, we assume she didn’t use that version after all, but can’t recall what she may have used instead.

Anyway, onto the build.

First up, we need to define 2 parameters

pSelectedCategory

A string parameter defaulted to ‘nothing’ / empty string. This will be used to store the Category that the user clicks on in the bar chart.

pSelectedSubCat

Another string parameter defaulted to ‘nothing’. This will be used to store the Sub-Category that the clicks on in the bar chart.

We can’t just display the Sub-Category field in the bar chart, as it should only display a value ‘on click’. So we need a calculated field to store the value that needs to be displayed in the 2nd column of the bar chart – ie ‘nothing’ or the Sub-Category.

Sub Cat Display

IF [pSelectedCategory]=[Category] THEN [Sub-Category]
ELSE ”
END

If there’s a value in the pSelectedCategory, then display the Sub-Categorys, otherwise display a blank.

Build out the bar chart by adding Category and Sub Cat Display to Rows, and Sales to Columns. Order both Category & Sub Cat Display descending. Add Category to Colour and adjust. Label the marks, align the headings to the left, remove gridlines, hide the axis and column headings. If you show the parameter you can ‘type in’ a Category and see how the view looks.

On a new sheet, build out the line chart by adding Order Date to Columns set to the continuous (green pill) month format, and Sales on Rows. Label the max & min marks and remove all gridlines.

Create a new field which will be used to colour the line

Colour : Line

[pSelectedCategory]

Add this to the Colour shelf.

Show the pSelectedCategory parameter on the sheet and as you type in each Category value the colour legend will change. Adjust the colour for each value you type in, and for the empty value.

The line chart needs to change based on the selections made by the user, ie the values set in the parameters. A calculated field is required

FILTER

([pSelectedCategory]=”)
OR ([pSelectedCategory]=[Category] AND [pSelectedSubCat]=”)
OR ([pSelectedCategory]=[Category] AND [pSelectedSubCat]=[Sub-Category])

This is a boolean field. Add to the Filter shelf and set to True.

Finally, just to make things a bit more complete (and help me check the line chart was matching the selections), I created some additional fields to present on the Tooltip

TOOLTIP : Category

IF [pSelectedCategory]=” THEN ‘All’ ELSE [pSelectedCategory] END

TOOLTIP: SubCat

IF [pSelectedSubCat]=” THEN ‘All’ ELSE [pSelectedSubCat] END

I added these to the Tooltip shelf and amended the text as below

The final step is to place the two charts on a dashboard and then create 2 parameter actions, one which passes Category into the pSelectedCategory field on select. Clearing the selection needed to reset the value to ‘blank’.

The other parameter action needs to do similar, but set the pSelectedSubCat parameter from the Sub Cat Display field.

My revised published viz is here.

Happy vizzin’!

Donna

Which Sub-Categories are frequently ordered together?

Donna ColesLeave a comment

Week 49 of 2021, and it was Ann Jackson’s turn to set her final #WOW challenge (sniff sniff!). Ann’s been setting challenges for the last 4 years, and we’re all going to miss her (I’m sure she may pop up as a guest challenger at some point in the future ….).

Ann decided to revisit her first ever challenge which I did complete here.

Obviously in 4 years, Tableau has moved on, and new features make building this concept a bit more straightforward. So let’s crack on.

Modelling the data

Over the years Ann has adapted her style which includes capitalisation of text. Whilst the challenge can be built with the Tableau provided Superstore Sales data source, Ann very kindly provided her own version which already contained the capitalised fields. I used this version.

To do this type of ‘basket analysis’ you need to use 2 instances of the data source which you model using relationships as follows :

Add a relation of Order ID = Order ID and Sub-Category <> Sub-Category

This results in a data pane which lists a set of fields from the Ann Jackson Superstore data source and a set of the same fields from the Ann Jackson Superstore1 data source. In this blog, I’ll reference these data sources as AJS and AJS1. If you examine a single order with multiple sub-categories you’ll see how the data is being related

The first 2 columns above are from the AJS data source – the order contains 4 different sub-categories. The last 2 columns are from the AJS1 data source, and you can see that for each AJS.Sub-Category, only 3 AJS1.Sub-Category records are listed. This allows us to see the combination of what was ordered with what.

Building the matrix

Add AJS.Sub-Category to Rows and AJS1.Sub-Category to Columns and you have the start of your matrix.

The number of orders is displayed in each cell, which is

# Orders

COUNTD([Order ID])

Add this onto Text

This adds Null column, which is the number of orders containing a single Sub-Category.

Right-click on the word Null in the column to select the column, and Edit Alias to rename the Null to NONE.

To remove the top right hand side of the matrix, I indexed each row/column.

Sub Cat Index

INDEX()

Sub Cat 2 Index

INDEX()

Add Sub Cat Index to Rows, change to discrete (blue pill) and move to be in front of AJS.Sub-Category. Change the table calculation so that it is computing by the AJS.Sub-Category field. Each row should now be numbered from 1 to 17.

Now add Sub Cat 2 Index to Columns, again change to discrete and move to be in front of the AJS1.Sub-Category pill. Edit the table calculation to verify it is computing by the AJS1.Sub-Category field. All the columns should now be numbered 1-18.

We want to remove the data for the records where the Row Index is greater then the Column Index

FILTER

[Sub Cat Index]>=[Sub Cat 2 Index]

Add this field to the Filter shelf, and set to true.

To colour the cells, add # Orders to the Colour shelf, and change the mark type to Square. Then edit the colour palette to use the Colour Brewer Blue Purple colour palette Ann advises (she’s used these palettes in the past, so I already have them installed, but you should be able to get them quickly from here).

Format the text to match mark colour and to be aligned centre. I also set the font to be Tableau Medium and bold. Set the column and row border divider lines to white.

Now uncheck show header against the two Sub Cat Index fields, and hide labels for column/ rows on the other column/row headings to remove them. Rotate the label for the columns and adjust the alignment, Adjust the font on both the column and row headers to bold and you should have the finished visual.

The Tooltips

We need to calculate the average sales contribution per order. We need 2 calculations for this, as we need to show a value for the AJS.Sub-Category on the rows and a value for the AJS1.Sub-Category displayed on the columns.

Average Sales (Rows)

SUM([Sales])/[# Orders]

Average Sales (Cols)

SUM([Sales (Ann Jackson Superstore1)])/[# Orders]

Add both of these to the Tooltip shelf. It’ll make a Null row appear – just right click and exclude.

Ann’s also been particular about some of the text displayed, so we need calculated fields to apply the required logic.

TOOLTIP – Sub Cat 2 Title

“& ” + [Sub-Category (Ann Jackson Superstore1)]

TOOLTIP- Sub Cat Text

IF [Sub Cat 2 Index]=1 THEN “SUB-CATEGORY” ELSE “SUB-CATEGORIES” END

TOOLTIP – Sub Cat 2

IF NOT(ISNULL([Sub-Category (Ann Jackson Superstore1)])) THEN [Sub-Category (Ann Jackson Superstore1)] + “:” END

Add all three fields to the Tooltip shelf, and adjust the tooltip as below

And with that, you should now have the fully completed challenge. My published version is here.

Finally, once again I want to thank Ann for all her contributions to the #WOW community over the last 4 years. I will miss her capitals and bold colour palettes – I’ll be storing away her pre-capitalised version of Superstore for sure :-)!

Happy vizzin’!

Donna

Tableau Coaches’ Favourite Challenges Advent Calendar

Donna Coles3 Comments

Candra set this seasonal fun challenge this week. It may not deliver data visualisation best practice, but as a ‘seasoned’ #WOW participant, this provided a chance to use a technique I rarely use (background images) and learn a brand new technique (colouring a bar chart with a gradient).

Getting set up

To start you’ll need to download all the files Candra provided, which will consist of the data (across 2 sheets) and 3 images.

The bauble and star image will need to be saved into your Tableau Shapes repository – I copied them into a new folder I called Xmas in this directory ..\My Tableau Repository\Shapes

The tree image just needs to be saved somewhere you’ll remember.

Building the Tree Chart

Connect to Sheet1 of the downloaded WOW Advent Calendar excel file.

Drag X to Rows and Y to Columns and disaggregate the marks (Analysis -> uncheck Aggregrate Measures)

You should get the basic layout of where the baubles are positioned. You may also notice that the X & Y values in the dataset were labelled wrong. The x-axis is your horizontal axis, but these have been stored in the Y field, while the vertical y-axis values have been stored in the X field. You may wish to rename these to save you getting confused.

Add Day to the Label shelf, and align middle centre.

We need to distinguish the bauble marks from the star mark. The star mark has a NULL value for the Day field, so we can utilise that

Day Is NULL

ISNULL([Day])

Add this onto the Shape shelf, and adjust the shape images to source the new images you saved into your shapes repository (if you can’t find them try clicking ‘reload shapes’). Adjust the initial size of the images via the slider on the Size shelf.

Now add Day Is Null to the Size shelf too. Edit the size and adjust the mark size range to suit, so the star is larger than the baubles.

Adjust the Label of the bauble so the text is larger and white.

To make the tooltip slightly more readable than that in the provided solution, I created my own custom version

Tooltip

IF [Day is Null] THEN “It’s Christmas Day!”
ELSEIF [Day]=1 THEN STR([Day]) + ” day until Christmas”
ELSE STR([Day]) + ” days until Christmas”
END

Add this to the Tooltip shelf, and adjust so it’s just referencing this field.

Add Link to the Detail shelf – this will be needed for the interactivity later.

Now we’ll add the background image via the Maps -> Add Background Image -> <datasource> menu option. Click to add image, then browse to where you’ve saved the Xmas tree image.

We need to define the min/max coordinates for where the tree image should be positioned, and based on this, we want the horizontal X axis (the Y field) to range from 0-4 and the vertical Y axis (the X field) to range from 0 to 6. Press the Apply button and you see whether things look to be placed correctly before you close all the dialog windows.

Next we need to fix the axis, so that when interacting later, we keep the whole tree visible. The vertical axis should be fixed from 0-7, and the horizontal axis should be fixed from 0 to 4.

Then we need to remove all the axis from displaying (uncheck show header), and all row/column borders and gridlines and axis rulers.

The final step is to remove the map options from displaying when you hover over the chart – Map ->Map Options and uncheck all the options displayed in the dialog

Building the Gradient Coloured Bar Chart

Candra provided a hint in her instructions pointing to the Flerlage Twins blog. A quick search on the site for the keyword ‘gradient’, and I landed on this post from 3 years ago https://www.flerlagetwins.com/2019/02/gradient-colors.html.

Since the process is already documented, I don’t have to write all the steps out myself 🙂 I’ll just point out the fields I used/created, since we’re working with a different data set.

Firstly I created a new data source for this chart, which combined Sheet1 with the Range sheet, and used a relationship calculation of 1 = 1 to combine the data (Relationships didn’t exist when the blog post was written).

The equivalent of the Sales Adjusted field is the existing pre-computed Sheet1(Count) field, so we don’t need any calculated field for this.

Max Segments in my solution is

Max Range

WINDOW_MAX(MAX([Range (Range)]))

Note – Range (Range) is referencing the Range field from the Range table. If you just drag the field into the calculation window, it will automatically present in this way.

Total in my solution is

Total Count

TOTAL(COUNT([Sheet1]))

Size in my solution is

Size

[Total Count]/[Max Range]

and finally Color in my solution is

Colour

([Max Range]-[Index]) * [Size]

Add Challenge Year to Rows (rather than Category), change the mark type to bar if it doesn’t change automatically. Reverse the axis so 2018 is listed at the top, then change the Challenge Year pill to be discrete (blue) rather than green.

If you follow the other blog post through, you should hopefully end up with

You then need to show the labels and adjust the tooltip against the CNT(Sheet1) marks card only.

As before, remove all gridlines borders etc.

Adding the interactivity

Add the 2 sheets to a dashboard. We need to allow the tree to filter on click of the bar chart. I used the ‘use as filter’ option on the context menu of the the bar chart object to quickly set this.

This adds a filter action to the dashboard, but is a quick way of getting it created rather than having to manually set it up via the dashboard -> actions menu.

I did use this menu though to add the URL action to the Tree sheet, which just needs to reference the Link to the Challenge field.

So a relatively short blog this week, since the most complicated section is already written up be Ken Flerlage (thanks Ken!). It was a great fun little challenge, which still allowed for plenty of learning opportunities. My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Thanksgiving Day NFL Games

Donna ColesLeave a comment

Sean Miller posted this week’s challenge based on the results of the annual NFL games hosted on Thanksgiving Day. It immediately reminded me of a previous #WOW challenge that Lorna posted in 2019 when she visualised Rugby League wins (see my viz here).

This is a table calculations based challenge. I did start using FIXED LoDs to help calculate the summary measures (Total Games and Win %) displayed at the front, but found that as there are 2 years (1975 and 1977) when the Dallas Cowboys did not host a game, I ended up with some pesky NULL values displaying which affected how the running sum area chart displayed.

Defining the calculations

As its a table calc challenge, I’ll build out what I can into a table to start with, to sense check I’m getting the correct numbers.

First up add Home Team, Game Date and Visiting Team to Rows and display Home Score and Visiting Score.

We start by determining the result of the fixture, based on whether it’s a home or away win or a tie. In the lollipop chart home wins are plotted at 1 and away wins at -1, so we’re going to store the result as a numeric value rather than text.

Result

FLOAT(IF [Home Score]>[Visiting Score] THEN 1
ELSEIF [Home Score]<[Visiting Score] THEN -1
ELSE 0 END)

The output is wrapped within a FLOAT, as this will help how the axis displays. Without it, by default Tableau will define the field to be a whole number, and the axis will extend to +/-2 which is too much room. We can’t adjust (fix) the axis to a decimal if the field itself is an integer, and adjusting to +/-1 chops off the displayed marks.

If you add this to the display, it will show 1, 0 -1 as you expect. You’ll notice though that the Axis on the lollipop chart is labelled as Win/Loss. This is achieved by applying a custom format to the field – “Win”;”Loss”;”Tie”

This is a sneaky but effective trick. The information stated before the first semi-colon applies to positive numbers, the info after the first semi-colon applied to negative numbers, and the information after the optional second semi-colon applies to zero.

Unfortunately though, it would appear that, at the point of writing, Tableau Public, isn’t honoring the zero formatting, and is displaying Win rather than Tie. The display works on Desktop though.

The win/loss/tie text is just a formatting feature and affects what is displayed, but the underlying value is still a number.

The Result field will be used to plot the lollipop chart. We now want a field to plot the area chart against. This is a running total of the Result values (ie win =1, win, win = 1+1, win, win, loss = 1+1 -1) and we need a table calculation.

However, as stated above due to a couple of missing years, I had to make an adjustment to ensure the running total displayed as Sean had in his challenge. I created another field

Result Adjusted

IIFNULL(SUM([Result]),0)

If the Result field doesn’t exist, as there is no data, then use 0 instead.

To see what’s going on, we’re going to need a different view of the data where the date field is continuous (green) rather than discrete (blue).

Build the below, and filter just for the first 10 years – you’ll see the gaps where the are no marks in 1975 and 1977 for Dallas

Use the context menu of the green YEAR(Game Date) pill and select the option to Show Missing Values. Marks will now display

Add Result to Label. Each mark is labelled Win or Loss, except the ones for Dallas for 1975 & 1977 as there is no data

Now add Result Adjusted to Label. A 0 value is now displayed against those two marks.

We can now build a running total off of this measure instead

Running Total Wins

RUNNING_SUM(([Result Adjusted]))

Add this to the Label too and verify the table calculation is computing by the Game Date field only. The running total for the 2 ‘missing’ dates is displaying a value which is the same as the previous value (since we’ve added 0 onto the running total). This will give us the flat line in the area chart when we come to build it.

Now back to our table of data, we can focus on the other calculated fields we need….

Total Games

WINDOW_COUNT(COUNTD([Game Date]))

This is a table calculation and is simply counting the number of distinct dates displayed. Add this to the table display we were building to start with, and adjust the table calculation to compute by all fields except Home Team. The total should display the same value for all the rows against each Home Team.

Next we want a field to indicate if the row is a win.

Is Win?

INT([Home Score]>[Visiting Score])

This is taking a boolean of true or false and converting to an INT (1 or 0).

From this we can work out the Win rate

Win %

WINDOW_SUM(SUM([Is Win?]))/[Total Games]

Add up all the Is Win? values associated to the Home Team as a proportion of the Total Games played. Format this field to a percentage with 0 dp. Again, add to the table and adjust the table calc to compute by all fields except Home Team, and verify the same settings applied to both the calculations nested in this calculation

For the All-Time Record, we need to know the number of wins and number of losses. We have a field to help us with the wins, but need an equivalent for the losses

Is Loss?

INT([Home Score]<[Visiting Score])

And from this we can work out

All-Time Record

STR({FIXED [Home Team]: SUM([Is Win?])}) + ‘-‘ +
STR({FIXED [Home Team]: SUM([Is Loss?])})

This is the one field I kept from my LoD based attempt.

The circles on the lollipop chart are coloured based on the difference in the score, so lets’s create that

Score Difference

[Home Score]-[Visiting Score]

And finally we need some fields to help display the tooltips properly. The tooltip indicates whether the result was ‘won’ or ‘lost’ which is different text to the axis labels.

TOOLTIP-Result

IF [Result]=1 THEN ‘won’
ELSEIF [Result]=-1 THEN ‘lost’
ELSE ‘tied’
END

The tooltip also displays the scores, but the scores are always presented as highest score – lowest score and not home score – visiting score. So we need fields to store the right values

TOOLTIP–Higher Score

IF [Is Win?]=1 THEN [Home Score] ELSE [Visiting Score] END

TOOLTIP – Lower Score

IF [Is Loss?]=1 THEN [Home Score] ELSE [Visiting Score] END

Pop all these fields out onto the table, so you can validate you’ve got all your calcs right before building the viz.

Building the area chart

Add Home Team to Rows, Game Date (continuous, show missing values) to Columns and Running Total Wins to Rows (ensure table calculation set as required). Change to mark type of Area. You should have 2 horizontal lines from 1974-1975 and 1976-1977 against the Dallas Cowboys row.

Adjust the tooltip, edit the label of the Running Total Wins axis , and remove the label of the Game Date axis.

Building the lollipop chart

Now add Result to Rows directly after the Home Team pill. Change the mark type to circle.

Add Score Difference to the Colour shelf of the circle mark, and adjust the starting colour range to a dark grey. Readjust the colour of the area chart to blue too. Add a border to the area chart too (via the colour shelf).

Add another instance of Result to the Rows shelf, next to the existing one. Set the mark type of this to bar. Reduce the size to the smallest possible, set the colour to grey and remove the border.

Now set this to be dual axis, synchronise the axis, and set the marks of the 2nd Result axis displayed on the right hand side to move marks to back. Uncheck Show Header to remove this axis from displaying.

Add Visiting Team, TOOLTIP-Result, TOOLTIP-Higher Score and TOOLTIP-Lower Score to the Tooltip shelf of both the Result marks cards, and adjust the tooltip on both to

Remove the Column dividers.

Now drag Total Games to Rows and drop next to the Home Team field. Change to be discrete (blue). Verify the number is what you expect and adjust the table calc if need be.

Add All-Time Record and Win % (set to discrete) to the view too. Then format these 4 fields so the text is larger and aligned centrally.

All that’s left now is to add the sheet to a dashboard. My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you recreate this difference chart?

Donna Coles2 Comments

Lorna created this challenge for #WOW2021 this week incorporating tips from the Speed Tipping session she and fellow WOW leader Ann Jackson had presented at TC21.

Defining the calculations

The requirements were to ensure there were only 7 calculated fields used, and no date hardcoding (including in the title – a feature I missed to start with). So let’s start by just going through the required calculations.

We need to identify the latest year in the data set

Current Year

YEAR({FIXED:MAX([Order Date])})

This uses an LoD (Level of Detail) calculation to identify the maximum date in the whole data set, which is 31st Dec 2021, and then extracts the Year of this ie 2021.

From this, we work out

Previous Year

[Current Year] – 1

Both of these fields return numbers, so automatically sit in the measures section of the left hand data pane (ie under the horizontal line). I want to treat these as dimensions, so I just drag the fields above the line.

We now need to create dedicated fields to store the Sales values for both years

CY Sales

IF YEAR([Order Date])=[Current Year] THEN [Sales] END

PY Sales

IF YEAR([Order Date])=[Previous Year] THEN [Sales] END

and with both of these, we can work out the

Difference

SUM([CY Sales])-SUM([PY Sales])

[TIP] This is custom formatted to â–³#,##0;â–½#,##0.

I googled ‘UTF 8 triangles’ and used this link to find the suitable shapes which I just copied and pasted into the number format field.

We’re going to need to determine whether the difference is positive or not.

Is Loss?

MAX(0,[Difference]) =0

This is another [TIP] making use of the array function. If the Difference is negative, it will return 0 as this is the maximum of the two numbers. I’m not entirely sure if this is more efficient than simply writing Difference<=0, but I wanted to incorporate another of the tips presented.

The final calculation we need is another of the PY Sales field, as we need another distinct Measure Name value to display. I simply chose to duplicate the existing field to have a PY Sales (copy) field.

Building the viz

Add Category to Columns, Segment to Rows and then add CY Sales to Columns, which will create a horizontal bar chart. Then drag PY Sales to the CY Sales axis, and when the ‘two columns’ icon appears, drop the field.

This will automatically change the pills so Measure Values is on Columns and Measure Names is on Rows.

Swap the order of the pills on the Measure Values section on the left hand side, so PY Sales is listed before CY Sales.

Add Measure Names to the Colour shelf and adjust. Increase the width of the rows.

Check the Show Mark Labels option on the Label shelf and adjust alignment to display the text to the left

Increase the Size of the bars to the maximum size, and add a white border (via option on Colour shelf)

Add PY Sales (Copy) to Columns, and change the mark type to Gantt Bar. Remove Measure Names from the Colour shelf of this marks card, as it will automatically have been added. Instead add the Is Loss? field to Colour and adjust.

Add Difference to the Size shelf, then click on Size, and reduce it to as small as possible. Set the border of this mark to Automatic (it should become a little thicker).

Next add the Difference field to the Label shelf, align right and set the font colour to match mark colour.

Now make the chart dual axis, synchronise axis, and set the mark type of the Measure Names mark type back to a bar.

On the All marks card, add CY Sales, PY Sales and Difference to the Tooltip shelf. And add Current Year and Previous Year to the Detail shelf.

Adjust the Tooltip against the All marks card, so it is the same when you hover on all of the marks. And edit the title of the chart, referencing the Current Year and Previous Year fields.

The challenge has a ‘space’ between each Segment, and this is the final TIP I used.

On the Measure Values section on the left below the marks card, type in MIN(NULL). This will initially create a new ‘blank’ row between the bars and the gantt marks, which isn’t where we want the blank row to be.

To resolve this, simply click on the MIN(NULL) text in the chart and drag the text below the PY Sales (copy) text

And now you just need to uncheck Show Header against the Measure Names pill on Rows, and the Measure Values and PY Sales (copy) fields on the Columns. Then remove all row and column borders and gridlines and hide labels for rows and columns.

Hopefully you’ve got the final viz which you can now add to a dashboard. My published version is here.

Happy vizzin’! Stay Safe!

Donna