Building a Control Chart

This week’s WorkoutWednesday comes to us from Lorna Brown. Using data from Mark Bradbourne’s Real World Fake Data, we are challenged to create a control chart where the user can select the date field to use, the amount of years to show, and the number of standard deviations to visualize on the control chart.

A control chart traditionally is designed to determine if a manufacturing or business process is in a state of control. To do this, data is plotted over time and the range of standard deviation over that data is plotted. Points that fall outside the standard deviation are considered to be out of control. Even before points fall outside the standard deviation, the control chart can be used to as a warning signal of shifts in the data.

The challenge had two views:

  • Control Chart – based on selected date field, number of years, and number of standard deviations. Create control chart with the upper and lower bounds of standard deviation and denote those points that are out of control vs those in control.
  • Complaint Details – From a point on the control chart, allow the user to jump and filter a second dashboard to see the details for all complaints from the selected week.

Control Chart

We need to create three parameters to allow user input to drive the display of the control chart:

  • Select Date – parameter list of two choices: Date Submitted and Date Received
  • Last N Years – parameter range from 1 to 5, with a step of 1 year
  • STD – parameter list of three choices (1,2,3) standard deviations. This could also be a range from 1-3, with a step of 1.

Parameters don’t do anything until they are applied somewhere. In this case, we will create calculations that will take the parameter selection and create output based on that selection.

First, let’s address the dates to display on our control chart. We can create a calculation, Show Date, that based on the parameter selection uses either the Date Submitted or Date Received as the value.

Show Date

CASE [Select Date]
WHEN 1 THEN [Date Sumbited]
WHEN 2 THEN [Date Received]
END

The control chart is to be displayed at the week level, within each year. We can pull out the year as it’s own field:

YEAR(DATETRUNC(‘week’, [Show Date]))

In our view, we can start creating the control chart by using Year (as a discrete dimension) in conjunction with our Show Date field, displayed as a continuous week. Next, we can create a Complaints field and plot it against our selected dates:

COUNTD([Complaint ID])

Because the WEEK(Show Date) is continuous and Year is discrete, we see what looks like gaps in our data where the year of Show Date does not align with the Year field. We can remove these gaps by editing the axis and changing the range to use independent axis ranges for each row or column.

Next, we need to create a calculation to filter the control chart to just the last N years based on the parameter by comparing the Year of data to the difference between the max of all the years and the number of years entered for the parameter.

Filter by Years

[Year]> {MAX([Year])}-[Last N Years]

The filter is set to include only when the calculation is True.

To create the range for the standard deviation, we create two calculations to the upper and lower bounds. The Window Average returns the average of all the weeks and to this we need to add or subtract the standard deviation times the amount of standard deviations specified from the parameter.

Upper Bounds

WINDOW_AVG(([Complaints])) + ( WINDOW_STDEV([Complaints]) * [STD] )

Lower Bounds

WINDOW_AVG(([Complaints])) – ( WINDOW_STDEV([Complaints]) * [STD] )

These fields are brought to detail on the marks card to make them available for use in a reference band. As these fields are table calculations, we must set the scope and direction for the window average. In this case, we want the window average to be computed using Pane across, such that it restarts with each year. The reference band ranges from the Upper Bounds to the Lower Bounds.

The shell of our control chart is now created. We want to add some additional context to the graph. We can add a dual axis of our Complaints field but set the mark for this dual axis as a circle. The benefit of the dual axis is that you get an additional marks card to format those marks independently. We can color code the points based on if they are outside the standard deviation bounds or not.

Outside Bounds

[Complaints]>[Upper Bound]
OR
[Complaints]<[Lower Bound]

On the marks card for our secondary axis, use the Outside Bounds field on color. We can modify the size of both the circles and the width of the line until we like the appearance. The axes can also be hidden. Labels are added, and set to show only the min and max values. In comparing this view against Lorna’s I noticed my values were just a little off. I couldn’t quite put my finger on it at first. But then, I determined that the weeks were starting on Mondays in Lorna’s example. The start date of the week can be changed for the entire data source by selecting the data source and editing the Date Properties.

Once the visualization is created and added to the dashboard, we can create the collapsible container to store the parameters. By default, Tableau will bring any parameters or filters that are already visible on a view into the dashboard into a vertical container on the right of the dashboard. Note: if you did not already show your parameter controls prior to bringing in the control chart view, you will need to bring in a vertical container and add your three parameters to the floating vertical container. We can change this container from being fixed to floating. Once floating, select the container and Add Show/Hide Button.

By default, Tableau will display two button images depending on if the container is hidden or shown. Edit the Button and change the button style to Text Button. You need to specify the button appearance for both when the container is shown and when it is hidden. Add a title for the button, select the font, border, and background as needed.

When interacting with these buttons in Tableau Desktop, you must select and click Alt at the same time. On Tableau Server/Online you will only need to click the button to view the hidden content.

Complaint Details Dashboard

The Complaint Details dashboard is simply a crosstab of information to provide supplemental information for all claims from a selected week. The dashboard also contains a button to bring users back to Control Chart dashboard. It functions just like the button from the collapsible container except that you specify where to navigate to instead of what item to Show/Hide.

Jumping to Details Dashboard

From the Control Chart dashboard, we will use a filter action to select the week, jump to the details dashboard and show only the data for that week. The name of the action will serve as the link that appears within the tooltip for users to click on, a feature of running the action on Menu. The key to making the jump from one dashboard to the other is in selecting the Target Sheet as the Complaint Details dashboard. Using a different dashboard than the source sheet filters the content on the different dashboard. In this case, we are isolating the target filter field to only use the Week(Show Date) so that it filters to the specific week.

When selecting a week to filter the Complaint Details dashboard, it was noted that year header highlights. We can remove this highlighting by adding a separate highlight action. This technique is very helpful to prevent the default funtionality in Tableau of highlighted selected values. We fcan create a dummy highlighter placeholder field (I just use ‘Highlighter’ as the string value for the field) and drag it to detail on the marks card. We then create a highlight action that highlights on itself but only highlights based on that Highlight Placeholder field. As all values has the same value for the Highlight Placeholder, it “highlights” all items and in so doing, the highlight is no longer differentiated.

As with all dashboards/visualizations, spend some time cleaning up the tooltips, removing any excess items from the view/dashboards that are not needed. Be sure to test out the functionality of the actions as well as the Show/Hide controls and Back buttons.

The control chart is a powerful tool to easily identify those weeks, in this case, where the complaints are outside the norm. By integrating the parameters with the view, we have given more flexibility in the hands of the users to determine how they want to see their data.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s