Can you build a Control Chart?

Donna ColesLeave a comment

Lorna Brown returned this week to set another table calculation based challenge involving a line chart which ‘on click’ of a point, exposed the ability to ‘drill down’ to view a tabular view of the data ‘behind’ the point. This is classic Tableau in my opinion – show the summarised data (in the line chart) and with ‘drill down on demand’. Lorna added some additional features on the dashboard; hiding/showing filter controls to change how the data is displayed in the chart, and a back navigational button on the ‘detail’ list.

The areas I’m going to focus on in this blog are

  • Setting up the parameters
  • Defining the date to plot on the chart
  • Restricting the data to the relevant years
  • Defining the reference bands
  • Colouring the marks
  • Working out the date range for the tooltip
  • Building the table
  • Drill down from chart to table
  • Un-highlight selected marks
  • Hide/Show filter controls
  • Add navigation button

Setting up the parameters

This challenge requires 3 parameters.

Select a Date

a string parameter containing the 2 options available (Date Submitted & Date Selected) for selection, which when displayed on the dashboard will be set to a single value list control (ie radio buttons)

Latest X Years

an integer parameter, defaulted to 3, which allows a range of values from 1 to 5.

NOTE – Ensure the step size is set to 1, as this is what allows the Show buttons option to be enabled when customising the Slider parameter control type.

STD

another integer parameter, defaulted to 1, that allows a range of values from 1 to 3

Defining the date to plot on the chart

The Select a Date parameter is used to switch the view between different dates in the data set. This means you can’t plot your chart based on date field that already exists in the data set. We have to create a new field that determines which date field to select based on the parameter

Date to Plot

DATE(DATETRUNC(‘week’, IIF([Select a Date]=’Date Submitted’,[Date sent to company], [Date received])))

The nested IIF statement, is basically saying, if the parameter is ‘Date Submitted’ then use the Date sent to company field, else use the Date received field. This is all wrapped within a DATETRUNC statement to reset all the dates to the 1st day of the week (since the requirement is to report at a weekly level).

Note – there was some confusion which field the parameter option should map to. I have chosen the above, but you may see solutions with the opposite. Don’t get hung up on this, as the principal of how this all works is most important.

Restricting the data to the relevant years

The requirement is to show ‘x’ years worth of data, where 1 year’s worth of data is the data associated to the latest year in the data set (ie from 01 Jan to latest date, rather than 12 months worth of data). So to start with I calculated, rather than hardcoded, the maximum year in the data

Year of Latest Date

YEAR({MAX([Date to Plot])})

Then I could work out which dates I wanted via

Dates to Include

[Date to Plot]>= MAKEDATE([Year of Latest Date] – ([Latest X Years]-1),1,1)

In the MAKEDATE function, I’m building a date that is the 1st Jan of the relevant based on how many years we need to show.

So if Year of Latest Date is 2020 and Latest X Years =1 then Year of Latest Date – (Latest X Years -1) = 2020 – (1-1) = 2020 – 0 = 2020. So we’re looking for dates >= 01 Jan 2020.

So if Year of Latest Date is 2020 and Latest X Years =3 then Year of Latest Date – (Latest X Years -1) = 2020 – (3-1) = 2020 – 2 = 2018. So we’re looking for dates >= 01 Jan 2018.

This field is added to the Filter shelf and set to true.

So at this point, our basic chart can be built as

  • Year on Columns (where Year = YEAR([Date to Plot])), and allows the Year header to display at the top
  • Date to Plot on Columns, set to Week Number display via the pill dropdown option, and also set to be discrete (blue pill). This field is ultimately hidden on the display.
  • Number of Complaints on Rows (where Number of Complaints = COUNT([XXXX.csv], the auto generated field relating to the name of the datasource).

To get the line and the circles displayed, this needs to become a dual axis chart by duplicating the Number of Complaints measure on the Rows, synchronising the axis and setting one instance to be a line mark type, and the other a circle.

Defining the reference bands

The reference bands are based on the number of standard deviations away from the mean/ average value per year.

Avg Complaints Per Year

WINDOW_AVG([Number of Complaints])

Once we have the average, we need to define and upper and lower limit based on the standard deviations

Upper Limit

[Avg Complaints Per Year] + (WINDOW_STDEV([Number of Complaints]) * [STD])

Lower Limit

[Avg Complaints Per Year] + (WINDOW_STDEV([Number of Complaints]) * -1 * [STD])

Add both these fields to the Detail shelf of the chart viz (at the All marks card level) and set the table calculation of each field to Compute By Date to Plot

This ‘squashes’ everything up a bit, but we’ll deal with that later.

Add a Reference Band (right click on axis – > Add Reference Line) that ranges from the Lower Limit to Upper Limit.

If an Average Line also appears on the display, then remove it, by right clicking on the axis -> Remove Reference Line – > Average

Colouring the marks

I created a boolean field based on whether the Number of Complaints is within the Upper Limit and Lower Limit

Within STD Limits?

[Number of Complaints]<[Upper Limit] AND [Number of Complaints]>[Lower Limit]

Add this to the Colour shelf of the circle mark type, and set to Compute Using Date to Plot. The values will be True, False or Null. Right click on the Null option in the Colour Legend, and select Exclude. This will add the Within STD Limits? to the Filter shelf, and the chart will revert back to how it was. Adjust the colours accordingly.

The Tooltip doesn’t show true or false though, so I had another field to use on that

In or Out of Upper or Lower Limits?

If [Within STD Limits?] THEN ‘In’ ELSE ‘Out’ END

Working out the date range for the tooltip

The Tooltip shows the start and end of the dates within the week. I simply built 2 calculated fields to show this.

Date From

[Date to Plot]

This 2nd instance is required as Date to Plot is formatted to dd mmm yyyy format and also used in the Tooltip. Whereas Date From is displayed in a dd/mm/yyyy format.

Date To

DATE(DATEADD(‘day’, 6, [Date to Plot]))

Just add 6 days to the 1st day of the week.

Building the table

Create a new sheet and add all the relevant columns required to Rows in the required order. For the last column, Company response to consumer, add that to the Text shelf instead (to replace the usual ‘Abc’ text). The in the Columns shelf, double click and type in ‘Company response to consumer’ which creates a ‘fake’ column heading. Format all the text etc to make it all look the same.

Add the Dates to include = true filter.

Also add the WEEK(Date to Plot) field to the Rows shelf, as a blue discrete field (exactly the same format as on the line chart). But hide this field (uncheck Show Header). This is the key linking field from the chart to the detail.

Drill down from chart to table

Create one dashboard (Chart DB) that displays the chart viz. And another dashboard that displays the table detail (Table DB). On the Chart dashboard, add a Filter Dashboard Action (Dashboard menu -> Actions -> Add Action -> Filter), that starts from the Chart sheet, runs as a Menu option, and targets the Detail sheet on the Detail dashboard. Set the action to exclude all values when no selection has been made. Name the action Click to Show Details

On the line chart, if you now click a point on the chart, the tooltip will display, along with a link, which when clicked on, will then take you to the Detail dashboard and present you with the list of complaints. The number of rows displayed should match the number you clicked on

Un-highlight selected marks

What you might also notice, is when you click on a point on the chart, the other marks will all ‘fade’ out, leaving just the one you selected highlighted. It’s not always desirable for this to happen. To prevent this, create a new field called Dummy which just contains the text ‘Dummy’. Add this onto the Detail shelf of the All marks card on the chart viz.

Then on the chart dashboard, add another dashboard action, but this time choose a highlight action. Set the action to run on select and set the source & target sheets to be the same sheet on the same dashboard. But target highlighting to selected fields, and select the Dummy field only

Hide/Show filter controls

Check out this post by The Data School that explains very simply how to work with floating containers to show/hide buttons. When creating in Desktop, the ‘onclick’ interactivity won’t work, you’ll have to manually select to show and hide, but once published to Tableau Public, it’ll behave as desired.

You have options to customise what the button looks like when the container contents are hidden, and what it looks like when they’re shown, via the Button Appearance

Add Navigation Button

On the Detail dashboard, simply add a Navigation object to the dashboard

and edit the settings to navigate back to the chart dashboard, as well as customise the appearance

Hopefully I’ve covered all the key features of this challenge. My published viz is here.

Happy vizzin’! Stay safe!

Donna

Can you build a Customer Lifetime Value Matrix?

Donna Coles2 Comments

Ann Jackson challenged us this week to build this matrix depicting the average worth of customer cohorts during their lifetime.

This challenge involves a mix of LoDs (Level of Detail calculations) and table calculations.

First up , we need to define our customer cohorts (ie group the customers), which for this challenge is based on identifying the quarter they placed their first order in. This will involve an LoD calculation. For a good introduction to LoDs with some worked examples (including a similar cohort analysis example), check out this Tableau blog post.

ACQUISITION QUARTER

DATE(DATETRUNC(‘quarter’,{FIXED [Customer ID] : MIN([Order Date])}))

The 2nd part of the formula in the { … } returns the earliest Order Date associated to the Customer ID, which is then truncated to the 1st day of the quarter that date falls in ie 23 Feb 2019 is truncated to 01 Jan 2019.

For the ‘quarters since birth’ field, we need to calculate the difference in quarters, between the ACQUISITION QUARTER and the ‘quarter’ associated to the Order Date of each order in the dataset.

QUARTERS SINCE BIRTH

DATEDIFF(‘quarter’,[ACQUISITION QUARTER],DATETRUNC(‘quarter’,[Order Date]))

Drag this field into the ‘dimensions’ area of the left hand data pane (above the line if you’re using later versions of Tableau).

Lets sense check what this looks like, by adding

  • ACQUISITION QUARTER to Rows (Discrete, Exact Date)
  • ORDER DATE to Rows, set to Quarter (quarter year ie May 2015 format which will make a green pill), then set to discrete to change to blue
  • QUARTERS SINCE BIRTH to Rows

You can see that while the first row against each cohort starts with a different quarter, the QUARTERS SINCE BIRTH always starts at 0 and counts sequentially down the table.

Next we want to count the number of distinct customers in each cohort, and we’ll use another LOD for this.

CUSTOMERS

{FIXED [ACQUISITION QUARTER]: COUNTD([Customer ID])}

Once again move this field into the Dimensions section of the data pane.

Add this onto the Rows of the above data table, and you should get every row for the same cohort displaying the same number

Add Sales onto Text to get the value of sales made by the customer in each cohort in each quarter. The ‘customer lifetime value’ we need is defined as the total sales so far / number of customers in the cohort.

Remove the QUARTER(Order Date) field from the table, as we’re not going to need this for the display, and it’ll affect the next steps if it’s left.

To get the cumulative sales, we need a Running Total Quick Table Calculation. Click on the Sales pill on the Text shelf and select Quick Table Calculation -> Running Total. The click again and Compute By -> QUARTERS SINCE BIRTH. Add Sales back into the table, so you can see the quarterly Sales value and how it’s cumulating until it reaches the next cohort.

We’ve now got the building blocks we need for the CLTV value we need to plot

Avg Lifetime Value

RUNNING_SUM(SUM([Sales])) / SUM([CUSTOMERS])

Note – I purposefully haven’t called this field what you might expect, as I’m going to ‘fill in the gaps’ that Ann describes in the requirements, and I’ll use that name then.

Pop this field into the table above, again setting the table calculation to compute by QUARTERS SINCE BIRTH

You can now use the data table above to validate the calculation is what you expected.

Now let’s build the viz out.

On a new sheet

  • QUARTERS SINCE BIRTH to Columns
  • ACQUISITION QUARTER (exact date, discrete blue pill) to Rows
  • Avg Lifetime Value to Text, setting the table calculation to Compute By QUARTERS SINCE BIRTH

From this basic text table, you can see the ‘blank’ fields, Ann mentioned. In the data table view, it’s not so obvious. The blank is there because there are no sales in those quarters for those cohorts. To fix we need another table calculation

CUSTOMER LIFETIME VALUE (CLTV)

IF ISNULL([Avg Lifetime Value]) AND NOT ISNULL(LOOKUP([Avg Lifetime Value],-1)) AND NOT ISNULL(LOOKUP([Avg Lifetime Value],1))

THEN LOOKUP([Avg Lifetime Value],-1) ELSE [Avg Lifetime Value] END

This says, if the Avg Lifetime Value field is NULL but neither the previous or the subsequent values are NULL, then use the Avg Lifetime Value value from the previous column (LOOKUP).

Replace the Avg Lifetime Value with the CUSTOMER LIFETIME VALUE (CLTV) field (setting the Compute By again), and the empty spaces have disappeared.

If you hover over the cells in the lower right hand side of the view, you’ll see tooltips showing, indicating that a mark has been drawn on the viz with Null data. To fix this, add CUSTOMER LIFETIME VALUE (CLTV) to the Filter shelf and specify non-null values only to show via the Special tab.

Now if you hover over that area you don’t get any tooltips displaying, as there aren’t any marks there.

Now it’s just a case of formatting the viz a bit more

  • Add CUSTOMERS to Rows
  • Add CUSTOMER LIFETIME VALUE (CLTV) to the Colour shelf by holding down the Ctrl key then clicking on the field that’s already on the Text shelf, and dragging via the mouse onto the Colour shelf. Using Ctrl in this way has the effect of copying the field including the table calculation settings, so you don’t need to apply them again. This will change the colour of the Text.
  • Then change the mark type to Square, which will then fill out the background based on the colour.
  • Then edit the colour legend to the relevant palette (which you may need to install via Ann’s link).
  • Set the border of the mark via the Colour shelf to white
  • Remove the row & column dividers
  • Set the row Axis Ruler to a dark black/grey line
  • Format the 1st 2 columns so the font is the same and centred. Widen the columns if required.
  • Update the tooltip

And then you should be ready to add the viz to your dashboard. My published version is here.

This blog is a bit more detailed that my recent posts, but I’m also conscious I’ve skipped over some bits that if you’re brand new to Tableau, you may not be sure how to do. Feel free to add comments if you need help!

Happy vizzin’! Stay Safe!

Donna

Can you find the variance along a line?

Donna ColesLeave a comment

So after a couple of weeks off blogging due to Christmas, I’m back providing solutions for #WOW2021! New contributor Candra Mcrae started off the year with this gentle workout focussing on table calculations. For those who have followed my previous blogged solutions, you’ll probably recall that table calcs don’t phase me as much as they do some – I started using Tableau before LODs existed, so they were the only tool at my disposal in the past. That said, I’m still not such an expert that I get it right first time – there’s often plenty of trial and error as I choose which fields I want to compute by, although I’ve got much better at this since I read Andy Kriebel’s Table Calculations Overview a couple of years ago.

As with any challenge that involves table calcs, I tend to start by building out a tabular view of all the data I’m going to need to build the viz. This ensures I can validate the data much easier and set the table calculation settings to what I need.

So let’s start….

For the line chart itself, we’re simply going to be plotting Year against Food insecurity which has been formatted to 1 decimal place and displays the % as a suffix

We will also need to capture 2 other values which will represent the coloured circles. Parameters are needed to help identify what these circles will be.

pSelected Year is an integer parameter that can be built by right clicking on Year and selecting Create -> Parameter. This will populate the parameter with all the values of the Year field. Default the value to 2018 and adjust the formatting of the displayed value so that it is 0 decimal places and include thousand separators is unchecked.

With this parameter, we can capture the value associated which is represented by the pink dot in the viz

Selected Year %

IF [Year] = [pSelected Year] THEN [Food insecurity (includes low and very low food security) Percent of households] END

It’s a bit more work to identify the black dot, as this will vary based on another parameter

pComparison

I created this an integer parameter storing the values 0, 1 & 2 (defaulted to 2), but aliased for display as First Year, Most Recent Year, Previous Year. I’ll be using this is an case statement/if clause later and comparing integers to strings is much more efficient in Tableau.

I like to take things step by step, especially when there’s table calcs involved, to ensure all the values I’m referencing are correct, so rather than identifying the selected value in a single calculated field, I’m using multiple.

Firstly I want to identify the 1st year in the dataset (without hardcoding).

First Year

WINDOW_MIN(MIN([Year]))

This will store the value of the earliest year (1995) against every row in the data that is outputted in the view (a bit like you would get with {FIXED : MIN([Year])}, but this is a no LOD challenge).

From this I can work out

First Year %

IF MIN([Year]) = [First Year] THEN MIN([Food insecurity (includes low and very low food security) Percent of households]) END

Notice the MIN() functions used in this statement, as opposed to the Selected Year % above. This is because First Year is a table calc which is an aggregation, and subsequently other fields referenced in the calculation also need to be aggregated. In this case other aggregations such as AVG, MAX, ATTR would also suffice.

Similarly, I’m going to derive the Latest Year % with

Latest Year

WINDOW_MAX(MAX([Year]))

Latest Year %

IF MIN([Year]) = [Latest Year] THEN MIN([Food insecurity (includes low and very low food security) Percent of households]) END

Finally, I’m also going to work out the value for the previous year

Previous Year

[pSelected Year]-1

Previous Year %

IF [Year] = [Previous Year] THEN [Food insecurity (includes low and very low food security) Percent of households] END

With these 3 % fields, I can now create a fourth field which stores the value of the % I want to compare with

Selected Comparison %

CASE [pComparison]
WHEN 0 THEN [First Year %]
WHEN 1 THEN [Latest Year %]
WHEN 2 THEN SUM([Previous Year %])
END

This ultimately just stores the 2nd value required for the black dot. This could all have been written within this single calculation, but I find it easier to troubleshoot if things are broken down a bit.

Putting these onto a table we can see how the values in each row change, as the parameters are changed.

Note, for all the table calculation fields (all denoted by the triangle symbol on the pill), I have explicitly set them to Compute Using the specific dimension of Year rather than the default of table down. While this will give the same result, I prefer to be explicit to ensure the values don’t change if pills get subsequently moved around the canvas (in the case of the Selected Comparison % field, all Nested Calculations within the Edit Table Calculation dialog box need to be set).

This is enough information to build the main viz itself by

  • adding Year to Columns (green continuous pill)
  • adding Food insecurity to Rows
  • adding Selected Year % to Rows next to Food insecurity
  • then drag Selected Comparison % to the Selected Year % axis which will automatically change the display to have Measure Values on Rows instead. Set the table calculation setting to compute by Year

This chart can then be set to be dual-axis and the axes synchronised. The Food insecurities should remain as as line mark, and the Measure Values should be a circle. The colours, formatting and tooltip then need to be applied.

Now we need to go back to our table of data to build out other calculations. The requirement is a single viz, so we need to provide the % value of the selected year within the title of the chart, along with the difference from the comparison value. For this to work, we need to store the relevant values against every row in the data set.

We already have the Selected Year % value identified, but this is only captured against the row of the selected year in the data output. To get it to display against every row we need

Window Max – Selected Year %

WINDOW_MAX(MIN([Selected Year %]))

This is formatted to 0 dp with the % sign as suffix, as this is the field that will be displayed in the title. Added to our data table, with the table calc set to compute by Year, you can see the value replicated across every row

Similarly, we already have the Selected Comparison % captured, but in order to work out the difference, we also need to get this value against every row too

Window Max – Selected Comparison %

CASE [pComparison]
WHEN 0 THEN WINDOW_MAX([First Year %])
WHEN 1 THEN WINDOW_MAX([Latest Year %])
WHEN 2 THEN WINDOW_MAX(MAX([Previous Year %]))
END

Adding this in to the table (and remembering to set the table calc settings), you can also see the relevant value perpetuated against every row. Change the pComparison value and you’ll see the values change accordingly, but still the same on every row.

So for the difference…. at this point I chose to deviate from the solution published. We’re already dealing with a measure that is quantified as a % (as opposed to a measure that is quantified as £ say). So to get the % difference between two percentage measures, I simply chose to show the difference between the two values (ie selected – comparison). Some would refer to this a the point difference between the values. This makes most sense to me for this particular scenario. The alternative is to calculate the % difference in the more traditional way of (selected – comparison) / comparison as you may do if you were presenting the % difference between the value of sales in different years. But I personally found the result could be confusing to the reader .

Difference

[Win Max – Selected Year %]-[Win Max – Selected Comparison %]

BUT, the value displayed within the title is the absolute value (ie no -ve sign) and actually doesn’t display if the value is 0 (which you get if you select the year to be 2019 and comparison to most recent year). So I resolved this with

DISPLAY : Difference

IF [Difference] <> 0 THEN ABS([Difference]) END

which is formatted to 1 dp with % suffix

Finally, we have a symbol that is used to indicate if the change is +ve, -ve or remains the same.

Difference Indicator

IF [Difference] > 0 THEN ‘â–²’
ELSEIF [Difference] < 0 THEN ‘â–¼’
ELSE ‘N/C’
END

Add this into the table too, once again remembering to set the table calc properties for all nested calculations.

So now you have these fields, you can add Window Max – Selected Year % and DISPLAY : Difference and Difference Indicator to the Detail shelf of the All marks card on the chart viz, and these will then be available in title dialog to add. Once again, ensure you set the table calcs to compute by Year.

You’ll also need to add some spacing in the title, to allow the parameter controls to be ‘floated’ into place on the dashboard. Getting the position right is really tricky. I positioned it very carefully on Desktop, but when I published, the controls were in completely different places. The easiest way I found to resolve this, was to use the Edit feature in Tableau public to edit the dashboard online and move the objects that way.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you build a divergent histogram?

Donna ColesLeave a comment

It was Ann Jackson’s last #WOW2020 challenge of the year, to build this divergent histogram depicting the average life expectancy by gender, with each bar indicating the number of countries with that life expectancy.

Ann hinted that reshaping the data may help, so I chose to do this by pivoting the data. To do this, in the data pane, I selected both the Life Expectancy Male and Life Expectancy Female columns, then right-clicked and selected Pivot.

This results in the number of rows in the data set being doubled, with a field called Pivot Field Names containing the values Life Expectancy Male and Life Expectancy Female, and then a field called Pivot Field Values which contains the life expectancy value. This field I renamed to Life Expectancy Age and moved it to the top section of the fields pane (above the line), so it is treated like a dimension rather than a measure.

We need to count the countries, so I created

Country Count

COUNTD([Country/Region])

Let’s see what this all looks like

Filtering by Year (2012), adding Life Expectancy Age to Rows, Pivot Field Names to Columns and Country Count to Text we get

Filtering out the Null and we have the basic data we want to plot, which we can do by

  • Year on Filter set to 2012
  • Life Expectancy Age on Filter set to exclude Null
  • Pivot Field Names on Columns
  • Life Expectancy Age on Columns (continuous)
  • Country Count on Rows
  • Mark Type set to Bar
  • Size set to Fixed

If we move Pivot Field Names from Columns to Colour, we get an overlapping bar.

But we want the males to be going down the chart, so let’s change the Country Count field to be

Country Count

IF CONTAINS(ATTR([Pivot Field Names]),’Female’) THEN COUNTD([Country/Region]) ELSE COUNTD([Country/Region])*-1 END

which is basically inverting the values for the male, and is pretty much the crux of the challenge 🙂

We need to label the max bar for each gender, or in this case the max & min. So showing mark labels and setting to just label the max and min values, we have a couple of issues….

… the labels are positioned in the centre, and males is labelled -13, rather than 13. To fix the positioning, we need to turn stacked marks off via the Analysis menu (took a bit of while to figure this one out).

To resolve the negative labelling, I formatted the County Count field to custom format to #,##0;#,##0

The final requirement is to ensure the axis range is identical. To do this I created reference lines based on the maximum value

Max Count Ref Line

WINDOW_MAX([Country Count])

which I then copied

Max Count Ref Line (Copy)

WINDOW_MAX([Country Count]) *-1

Adding both of these to the Detail shelf, and setting the table calculation to compute by the fields below

And apart from some formatting and the tooltips, that should be complete. My published viz is here. When I looked at Ann’s version, she tackled it differently, which is the beauty of these challenges – there’s often more than one solution.

Happy vizzin’! Stay Safe!

Donna

Can you highlight profits with Measure Names?

Donna ColesLeave a comment

Luke Stanke provided his last challenge of #WOW2020 for the year with this viz to utilise Measure Names & Measure Values. This plots Cost against Sales per Sub-Category (the circles), which are then bounded by a lozenge. The Sales circle and the lozenge are coloured based on profitability, and the lozenges are labelled by Profit, although the position of the label is also based on profitability.

Obviously as the title of the challenge suggests, using Measure Names & Measure Values is key to this. As usual I’ll try to just pull out the main points I think you might find most tricky.

  • Building the core chart
  • Aligning the Sub-Categories against a horizontal line
  • Colouring the circles & lozenge
  • Positioning the labels
  • Formatting the labels
  • Creating a border around the white Cost circle

Building the core chart

You’ll need to create a calculated field to store the cost

Cost

SUM([Sales]) – SUM([Profit])

Once done, then add Sub-Category to Rows and sort by descending by Profit. Add Measure Values to Columns and filter just to Sales and Cost. Change the mark type to circle.

Add a 2nd instance of Measure Values to Columns and change the mark type to Line. Add Measure Names to Path.

Then make this chart dual axis and synchronise the axis. This will give the base chart.

Aligning the Sub-Categories against a horizontal line

Whenever you add a blue discrete pill to the rows or columns, you’ll get a ‘header’ pane, which means the label for the pill is by default positioned in the centre of a row, as demonstrated below if I add row dividers to the chart

The requirement is to show the row label aligned with a horizontal line. To achieve this, we need to add a MIN(0) field the Rows. Adding this green pill, creates an axis which has a 0 horizontal 0 line, which is positioned exactly in alignment with the row label

Removing the row dividers, and uncheck Show Header on the MIN(0) pill, and the right aligning the row label, we get the desired result.

Colouring the circles & lozenge

The circles are either coloured white for Cost or dark red or green for Sales depending on the profitability of the Sub-Category.

So we need a new calculated field

Is Profitable?

SUM([Profit])>0

This returns true or false.

On the circles Measure Names mark card, add Is Profitable to the Colour shelf. If it’s not already there, then add Measure Names to the Detail shelf, then select the detail icon and choose the colour icon to also add Measure Names to the Colour shelf

This has the effect of giving you 4 colour combinations

which can then be edited to set the 2 combinations relating to Cost to white, Sales & True to dark green and Sales & False to dark red (at this point, you probably want to be setting the background colour of the whole worksheet to light grey (right click on sheet, format, select the Shading icon)

For the lozenge, you just need to add the Is Profitable field the the Colour shelf of the line Measure Names card, and adjust the size of the to make it slightly bigger than the circles (you may need to move marks to back to get the lozenge to sit behind the circles).

Positioning the labels

So the tricky thing about the labels is where they are positioned – they are to the right for the profitable sub-categories, and to the left for the non-profitable sub-categories. When you add a label, you can’t provide that sort of conditional logic to control the positioning. However, we happen to have 2 sets of marks cards (as dual axis), and so can use different fields to apply the labelling.

Label Profit is +ve

IF [Is Profitable] THEN SUM(Profit) END

This will only return a value for profitable sub-categories, otherwise the field will store blank.

Label Profit is -ve

IF NOT([Is Profitable]) THEN SUM(Profit) END

Conversely, this will only return a value for the non-profitable sub-categories.

Add the Label Profit is +ve field to the Label shelf of the Circle marks card. Adjust the positioning to be middle right, and adjust the settings as below (tbh this was just a bit of trial and error to find this worked :-)). You may want to additionally add a few <spaces> to the start of the label text to add a bit of breathing room.

Then on the Line marks card, add the Label Profit is -ve field to the Label shelf, but this time choose to Label Line Ends, and select Label end of line. Note – you can try to apply the same settings as above, but I found for some reason it increased the size of the mark around the line end… weird…

Formatting the labels

Right click on the Label Profit is +ve/-ve fields created and custom format with

+”$”#,##0;-“$”#,##0

Creating a border around the white Cost circle

Finally, a sneaky requirement was to add a border around the white circle that represented Cost only. This implied that using the formatting options on the Colour shelf that allows a border to be added to the mark wasn’t going to work, as this will apply to all marks and not just to the Cost circle.

I handled this by creating a 2nd instance of the Cost field (right click Cost and select duplicate).

I then added this Cost (copy) field to the Measure Values section of the chart on the left hand side. This doesn’t appear to do much, since the mark is directly on top of an existing mark, but you will see the Colour legend associated to the Circle marks card change and add 2 new entries.

I changed the colours of the 2 new entries in the Colour legend to a dark grey, then added Measure Names to the Size shelf of the Circles marks card. I then adjusted the range and size of the circles so that the Cost measure is slightly smaller than the Cost (copy) measure. Ultimately all 3 circles are slightly different sizes, but the range is so narrow, it’s hard to tell.

And that’s it. A lovely looking viz with a fair bit going on under the bonnet, but nothing too complex! My published version is here.

Happy vizzin’! Stay Safe!

Donna

Can you toggle between AND|OR filtering logic?

Donna ColesLeave a comment

Week 49 of #WOW2020 was a guest challenge by Sam Epley to demonstrate the ability to toggle between filtering using AND logic and filtering using OR logic. Sam stated that it had been something he and colleagues had been pondering for a while but v2020.2 provided a possible solution.

So the first thing I did was a quick check on what functionality had been released in v2020.2 which included set controls. I also noticed when examining the published solution, that if I hovered over the Select Values filter controls, a small set icon would appear, which indicated these filters were indeed based on set controls

This blog will focus on

  • Select a Field for Slicer x parameter
  • Select Values for Slicer x filter
  • Building the Logic

Select a Field for Slicer x parameter

I had a slight ‘moment’ with this, until I realised I just had to copy the â•š character and paste it into the parameter list I created

You’ll need to create 3 instances of this parameter (or create once, duplicate and rename).

Select Values for Slicer x filter

Note – what I describe here will need to be duplicated for each slicer.

There’s a chance I may well have been a bit long-winded in how I went about this, but it worked for me…

First up, I extracted the value selected in the Select a Field parameter, only returning a value if it contained the special â•š character. So this would return the word ‘Region’ or ‘Segment’ but would return NULL if ‘Location’ or ‘Customer’ had been selected.

Selected Slicer 1

IF CONTAINS([Select a Field for Slicer 1:],’â•š ‘) THEN REPLACE([Select a Field for Slicer 1:],’â•š ‘,”)
ELSE NULL
END

I then needed to ‘map’ this value to the actual field in the data source, so I could get a handle on the actual values associated to the field

Selected Slicer 1 Values

IF [Selected Slicer 1] = ‘Region’ THEN [Region]
ELSEIF [Selected Slicer 1] = ‘State’ THEN [State]
ELSEIF [Selected Slicer 1] = ‘Category’ THEN [Category]
ELSEIF [Selected Slicer 1] = ‘Sub-Category’ THEN [Sub-Category]
ELSEIF [Selected Slicer 1] = ‘Segment’ THEN [Segment]
ELSEIF [Selected Slicer 1] = ‘Ship Mode’ THEN [Ship Mode]
ELSE ‘No Selection’
END

From this I could create a set to store the values, by right-clicking on the Selected Slicer 1 Values field and choosing Create -> Set, and selecting the Use all option.

The values in the set can then be accessible on the sheet foe selection by right-clicking on the set field and choosing Show Set

Building the Logic

To help with this, you can build out a basic view by Region that returns In or Out for each of the sets

As you can see when everything is set to ‘None Selected’, everything is In.

As we change the filter options, you can see the values change to be In and Out

For the AND logic to work, we’re looking for the rows of data where every In/Out column is In.

For the OR logic to work, we’re looking for the rows of data where In exists in at least one column.

However, when there is a No Selection option selected, all values are In the set, and when we’re using the OR logic, we don’t want these.

So we need to identify when No Selection is selected in the filter

Slicer 1 is Not Selected?

[Selected Slicer 1 Values] = ‘No Selection’

and we need this for the other slicers too.

Then we need to build a new calculated field that is going to handle all this logic, which is driven by a pLogic parameter which simply contains the values of AND and OR.

In Combined Set

IF [pLogic]=’AND’ THEN [Select Values for Slicer 1:] AND [Select Values for Slicer 2:] AND [Select Values for Slicer 3:]
ELSE //It’s OR
IF [Slicer 2 Is Not Selected?] AND [Slicer 3 Is Not Selected?] THEN [Select Values for Slicer 1:]
ELSEIF [Slicer 1 Is Not Selected?] AND [Slicer 3 Is Not Selected?] THEN [Select Values for Slicer 2:]
ELSEIF [Slicer 1 Is Not Selected?] AND [Slicer 2 Is Not Selected?] THEN [Select Values for Slicer 3:]
ELSEIF [Slicer 3 Is Not Selected?] THEN [Select Values for Slicer 1:] OR [Select Values for Slicer 2:]
ELSEIF [Slicer 2 Is Not Selected?] THEN [Select Values for Slicer 1:] OR [Select Values for Slicer 3:]
ELSEIF [Slicer 1 Is Not Selected?] THEN [Select Values for Slicer 2:] OR [Select Values for Slicer 3:]
ELSE [Select Values for Slicer 1:] OR [Select Values for Slicer 2:] OR [Select Values for Slicer 3:]
END
END

This field returns a true or false against each row in the data set, and can be used to build the bar chart display.

This covers the most ‘complex’ bit of this challenge – below shows what one of the charts looks like

If you’re not familiar with how to use measure swapping, which is another feature of this challenge, then check out a previous blog I wrote.

I also created a field to add to the Tooltip to show a $ symbol in the event the measure selected was Sum of Sales.

My published solution is here.

Happy vizzin’! Stay Safe!

Donna

Can you create a variable width bar chart?

Donna ColesLeave a comment

For #WOW2020 Week 48, Jami Delagrange set the challenge based on a problem she’d been puzzling over for a while.

The data set was one Jami created herself about turkeys creating a ‘conference’ (it is Thanksgiving week in the US).

The above is basically 2 bar charts plotting the time an event happened (on the x-axis) against the number of turkeys attending the event (on the y-axis). One bar chart is coloured based on the location, the other is a white bar which has a width based on the duration of the event.

There are a couple of calcs needed for the basic data

# Turkeys

COUNTD([User Name (Original Name)])

Duration (mins)

DATEDIFF(‘minute’,[Start time],[End time])

With these we can get a basic set of data

The Start Time and End Time fields can be custom formatted to hh:mm AMPM.

The first bar chart we need is very simple, plotting Start Time against # Turkeys, coloured by Location.

For the second chart, we need to make use of the Size shelf, and the feature that allows you to fix the size based on a field. If we add Duration (mins), we don’t get what we need

The bars are far too wide. As the Start Time being plotted is at the ‘minute’ level, each unit of the axis is 1 minute of a day. There are 24 * 60 = 1440 minutes in a day. So the size needs to be based as a proportion of the number of minutes in a day ie

Duration Proportion of Day

[Duration (mins)] / (24 * 60)

Adding into our data table, you can see what these values translate to

and if we drop this onto the Size shelf instead, we get the required display

Build these charts as a dual axis, and you’ve got the output. Tableau’s own help article, discusses the Size feature a bit more.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you create a clustered histogram?

Donna ColesLeave a comment

Ann Jackson returned with this week’s #WOW2020 challenge, to create a ‘clustered histogram’, whereby the orders in 2020 were placed in ‘bins’ based on the sale value.

I found this challenge quite straightforward this week, as its very similar to a previous challenge I’ve already blogged about in week 23, which created a side by side bar chart by month.

So what are the main points for this blog

  • Creating the bins
  • Getting the bars side by side
  • Tooltips

Creating the bins

I first created an LoD to store the total value of the sale for an Order

Order Value

{FIXED [Order ID]: SUM([Sales])}

then I need to ‘bin’ this, but as the sales over $2000 needs to be lumped together, we can’t use the traditional binning functionality. We also don’t want to have a massive case statement to assign the values. Instead we can do a bit of maths…. we want to essentially round each order value to the nearest 100.

Round Up to 100

(CEILING([Order Value]/100) * 100)

So if the Order Value is 39 for example, when divided by 100 this will be 0.39. The CEILING function always rounds up to the nearest whole number, so in this case will return 1, which is then multipled by 100 to give us 100.

Doing this, every order value is then assigned a ‘bin’ of 100, 200, 300 etc

I then created

Sales Bin

IF [Round Up to 100] >2100 THEN 2100 ELSE [Round Up to 100] END

to apply the grouping of all the values which were greater than 2100 (since 1999 would be rounded up to 2000 and 2001 would be rounded up to 2100).

Getting the bars side by side

We have the Sales Bin measure and will also need to plot the count of orders

# Orders

COUNTD([Order ID])

Plotting this out and splitting by Segment we get

but we don’t want the segments stacked, we want them side by side.

The Sales Bin axis is ‘continuous’, which means a value can be plotted at any number along the line, it just happens to be at the ‘100’ marks as that’s where are bins are.

So we use a sort of ‘jittering’ to plot each bar at a slightly different value depending on the segment

SALE AMOUNT

CASE [Segment]
WHEN ‘Consumer’ THEN [Sales Bin] – 75
WHEN ‘Corporate’ THEN [Sales Bin]- 50
ELSE [Sales Bin]- 25
END

So for all the orders in the ‘100’ bin (ie the order value was between 1 and 100), all the Consumer orders will actually get plotted at 25, Corporate at 50 and Home Office at 75.

All this is explained in much more detail in the Week 23 blog post referenced at the top of this post.

Tooltips

The tooltip has different text depending on where you hover.

We need the lower value for the bin range (eg $0-100$), so I created

Round Up to 100 minus 100

[Round Up to 100]-100

And I then created 4 different ‘tooltip’ calculations which I could place on the tooltip to give me the display I needed:

TOOLTIP Upper

IF [SALE AMOUNT]<2000 THEN [Round Up to 100] END

TOOLTIP Lower

IF [SALE AMOUNT]<2000 THEN [Round Up to 100 minus 100] END

TOOLTIP between

IF [SALE AMOUNT]< 2000 THEN ‘ between’ END

TOOLTIP Symbol

IF [SALE AMOUNT]< 2000 THEN ‘ – ‘ ELSE ‘$2000+’ END

The final thing needed is to create a reference band to colour the section at the end, and fix the axis to start after 0 and end at 2099, so you don’t get 0 and 2100 displayed on the axis.

So a relatively short write up today – think this is the quickest blog I’ve written.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you build a Premier League table?

Donna ColesLeave a comment

Luke provided this fun challenge this week. It was nice to see an English sport that I’m very familiar with feature, even if I had to keep retyping the word ‘Draw’ for ‘Tie’ 🙂

This blog will cover

  • Re-modelling the data
  • Building the table & bar chart
  • Building the Last 5 matches chart
  • Getting the data for the Last 5 matches chart tooltip

Re-modelling the data

Sometimes the #WOW challenges are set with a very specific direction in mind eg no LODs, use 1 sheet only, no data modelling etc. This challenge had no real restrictions, and there were minimal clues. The Latest Challenges page indicated they’d be table calculations involved

and Luke’s tweet introducing the challenge suggested there was a ‘trick’ that would make this a lot simpler to solve…. hmmmm???

I looked at the data referenced to get a feel of the shape and layout – it showed 1 row per game, with the home & away team for each game stored in different columns. Trying to count the number of games a specific team played was likely to be complicated in this format. Given Luke had suggested a ‘trick’, I decided I was going to pivot the data to expand the data to give me 1 row per game per team, hoping this was the ‘trick’ he implied, and there was no requirement to state that data modelling wasn’t allowed.

I did this in Tableau itself, in the data source pane. After connecting to the data, highlight the columns Home Team and Away Team, right click and select Pivot.

This has the effect of duplicating the rows of data and adding 2 additional columns to the end Pivot Field Names containing the column names that had been pivoted, and Pivot Field Values containing the values of those fields.

I then simply renamed Pivot Field Names to Home or Away and Pivot Field Values to Team.

With the data structured this way it was very simple to calculate the various measures required :

Win-Loss-Draw

IF [Home or Away] = ‘Home Team’ AND FTR = ‘H’ THEN ‘W’
ELSEIF [Home or Away] = ‘Away Team’ AND FTR = ‘A’ THEN ‘W’
ELSEIF FTR = ‘D’ THEN ‘T’
ELSE ‘L’
END

FTR in the data set stands for full-time result and indicated whether it was the Home (H) or the Away (A) team that won, or whether it was a Draw (D). So this field indicates whether the Team in each row won, lost or drew (tied) the match.

From this we can then work out the points the Team gained in the match.

Points

IF [Win-Loss-Draw] = ‘W’ THEN 3
ELSEIF [Win-Loss-Draw] = ‘T’ THEN 1
ELSE 0
END

To determine the total number of Wins for each team I created

Wins

FIXED [Team]: SUM(IF [Win-Loss-Draw] = ‘W’ THEN 1 ELSE 0 END)}

and then repeated this similarly to get a Losses and Ties measure.

Finally I determined the total number of games each team played by using the provided tablename (count) field that is now added in more recent versions of v2020 (supersedes the Number of Records field).

Matches Played

{FIXED [Team]: (COUNT([2020_11_11_WW46_EPL.csv]))}

Building the table & bar charts

In my initial built, I created one sheet to show both the table summary with the bar chart, and a separate sheet to show the last 5 matches, with the intention the charts would be lined up side by side on the dashboard, removing padding to make the charts look to be one.

But with the table and bar on the same chart, I couldn’t get the labels of each column and the ‘Total Points’ to be aligned in the way displayed. So I ended up using 3 different sheets.

Table

Given we’ve already got all the measures, this was a very simple table, with the Team field sorted descending by the Points measure.

Bar Chart

Again this is a pretty basic chart with Team on Rows,(which is then hidden) and Points on Columns, and Win-Lose-Draw on Colour.

To label the end of the chart with the total points, I created an additional measure

Total Points

{FIXED [Team]: SUM([Points])}

which I added to Columns and made dual axis, with the Total Points being a Gantt type, and the mark being labelled.

Building the last 5 matches chart

This chart is slightly more complex, and where the table calculations clue comes into play.

In the data, a match for each Team is identified by the Date, but we can’t use Date to plot against as not everyone has their matches on the same date. We just want to number each match played by each team in order of the date played.

We can use a field Index = INDEX() to number our matches per team – you’ll see that some teams have played 8 matches while others 7.

But we only want the last 5 matches, which for some teams will be matches 3-7 and others 4-8.

To do this, we need to determine the number of matches played. In hindsight I should have realised, I’ve already got this in a variable, but as my head was in ‘table calc’ mode, I created a field Size = SIZE()

As you can see, this returns the number of rows for each team against every row for that team.

I then created another calculated field

Last 5 matches only

[Index] > [Size]-5

Adding this into the table, and verifying the table calc settings for both the nested table calcs are set as above, you can see the last 5 rows of each team are set to True

This can then be added to the Filter shelf to reduce the rows shown.

However, as some teams have played less matches than others, the Index values shown aren’t consistent for every team, and we need this.

So another field is required

Index to Plot

//want a value from 1 to 5 based on the index
[Index] – ([Size]-5)

This gives us the 1-5 sequence for each row we need to build the chart

The layout below is the basic premise.

The circles need to be coloured based on the Win-Loss-Draw field, and the labelled with the same field which is set to match mark colour. Reducing the transparency of the colour will fade the circle colour and make the label stand out.

Getting the data for the Last 5 matches chart tooltip

So just by pivoting the data, I was able to crack through the measures and the charts pretty quickly, and felt happy that I’d worked out Luke’s trick.

But then I came to adding the tooltip to the circle chart which needs information about the opposition, and the score.

The score is always displayed on the tooltip as

Team ‘s score – Opposition score.

For example when Arsenal played Sheffield United at Home where Arsenal won 2-1, the score is displayed on the tooltip as 2-1.

And when Arsenal played Manchester City Away, where Manchester City won 1-0, the score for Arsenal is displayed on the tooltip as 0-1.

So to get the team’s score I created

Team Score

IF [Home or Away]=’Home Team’ THEN [Fthg] ELSE [Ftag] END

Where FTHG is Full Time Home Goals and FTAG is Full Time Away Goals. So if the team is listed as the Home team, their score is identified by FTHG, but if they are the Away team, their score is identified by FTAG.

Correspondingly, we need

Opposition Score

IF [Home or Away]=’Away Team’ THEN [Fthg] ELSE [Ftag] END

If the team we’re interested in is listed as the Away Team, then the opposition will be the Home team, so FTHG identifies the goals they scored, otherwise the opposition is the Away team, so FTAG is the goals they scored.

Both of these fields may sound complicated, but it was retrievable in the data I had.

However the actual name of the Opposition team wasn’t – this was lost once I pivoted the data – the Opposition is now located on a completely different row of data and as we have no ‘match id’ or similar in the data, I can’t actually identify who the opposition might have been, since multiple matches can play on the same date.

This is where I started to question whether ‘my trick’ was the right one…

I had a bit of a think, and came up with a solution, but it was a bit ‘off piste’ for a #WOW challenge as it required blending… typically when blending is required they’d be some mention of it in the requirements, but since there was nothing stated to say it couldn’t be used, I went with it.

I added the original unpivoted data set to my workbook as an additional data source. I renamed this with the word ‘Home‘ on the end.

When I work with blending, I try to be specific which fields the data sources are going to be linked/blended with.

So in the pivoted data source I’d been working with for most of the challenge, I create a new field

BLEND – Team

[Team]

Then in the Home data source I’d added, I also created a field with the same name, but it contained a different field.

BLEND – Team

[Home Team]

I then added another instance of the original unpivoted data source to my workbook, this time renaming it with the word Away on the end. In this data source I created

BLEND – Team

[Away Team]

so from the 3 data sources, I could now build up a table to verify I could get to the data I needed:

  • Pivoted Data.Team
  • Pivoted Data.Date
  • Home Data.Away Team
  • Away Data.Home Team

and when blended to the secondary data sources, the links for both BLEND – Team and Date fields are enabled

From this data, we can see that either the Home Team column or the Away Team column is populated, and provides the name of the opposition. So this means we can then create in the primary data source (the pivoted data source)

Opposition

IF ISNULL(ATTR([2020_11_11_WW46_EPL – Home].[Away Team])) THEN ATTR([2020_11_11_WW46_EPL – Away].[Home Team])
ELSE ATTR([2020_11_11_WW46_EPL – Home].[Away Team])
END

If the Away Team is NULL, then store the Home Team value, else store the Away Team value

And from this we now have all the building blocks we need to finalise the Last 5 matches chart.

Just make sure that as you add fields to the chart, you may need to alter the various table calculations to ensure they are still computing correctly (typically all the fields other than Team should be selected in the table calc dialog). And also keep an eye on the fields being blended – BLEND – Team and Date should always be selected for both.

After I posted my solution up, I did indicate I thought I might have done something a bit ‘out of the ordinary’, but apparently my approach match Luke’s! That was a real surprise.

My published version is here.

Happy vizzin’! Stay Safe!

Donna

Can you show the top 10 products for each state?

Donna ColesLeave a comment

For this week’s #WOW2020 challenge, Sean Miller stripped things right back and went ‘back to basics’.

This blog should be brief as I’m only going to touch on the bits that I think some people might find a little tricky.

The Map Colours

Use the Red-Black Diverging colour palette, centred at 0 to ensure the colours match exactly (this is most noticeable on the Viz in Tooltip table if it’s not centred at 0).

Map Background

On the Map -> May Layers menu, ensure all the items under the Map Layers section are unchecked

Seaboard States

I used the MIN(1) on the Columns shelf and fixed the axis from 0-1 to fill it up.

Top 10 Products

Orders Count

I dragged Order ID into the ‘measures’ section (below the line on the left hand pane if you’re using later versions of Tableau), and chose the COUNTD aggregation. When I added this to the table, I then changed the alias of the field and called it ‘Orders’

Top 10

Add Product Name to the Filter shelf and select the Top tab.

Colouring the columns

This uses the Legend Per Measure functionality. Add Measure Values to the Colour shelf and select the Use Separate Legends option

This will add 3 colour legends onto the canvas. Set the colours of the Profit measure to the Red – Black diverging as with the map.

For the other 2 legends select any diverging colour palette, then click on the coloured square at each end, and select white from the palette displayed. Change the stepped colour to 2, and you’ll find that the measures now don’t look like they actually have a background colour.

Viz in Tooltip

When adding the sheet as a tooltip, I adjusted the size to 500×350

<Sheet name=”Top 10 Products” maxwidth=”500″ maxheight=”350″ filter=”<All Fields>”>

The size of the Top 10 Products sheet should be set to Entire View to ensure you don’t get a ‘View is too large to display’ message on the tooltip

Getting the Top 10 filtered properly

Once the viz has been added as a ‘viz in tooltip’ a State related filter pill will automatically be added to the Filter shelf of the the Top 10 Products sheet. To ensure the top 10 products gets filtered by the state BEFORE the top 10 products by sales are identified, the filter needs to be Added to Context

Arranging on the Dashboard

I managed to tile all the items, except for the ‘Eastern Seaboard States’ title which I floated.

My published viz is here.

Happy vizzin’! Stay Safe!

Donna