This week #workoutwednesday challenge was set up by Ann Jackson. It was slightly difficult challenge as this week it was about finding signals or patterns in data with use of control chart. It was more about finding statistical signals using standard deviations and mean/ medians with signals to indicate change in pattern or trend or finding an outlier within the dataset. This is good way to analyze the data and get statistical insights/ signals from the data about pattern and behavior.
Requirements:
Available here
Data for the workout can be downloaded from here
Here is my output for the challenge (Click on Image for interactive version):
It took me time to understand the requirement and figure out the way to design the visualisation. lookup() & windows functions did help a lot to build this requirement
Let me share the steps required to create this visualisation post data is imported into Tableau:
Step 1: Create 2 parameters for the requirement
Select a Middle line: This parameter to provide drop down option for either median or mean
Select A Test: This parameter to select the type of test we want to perform (outliers, Trend or change)
Step 2: Create the calculated fields required for the visualisation
Middle Line: This field is based on parameter “SELECT A MIDDLE LINE” and based on selection we will have either median or mean of sales
+3SD: This field is to calculate + 3 standard deviation based on middle line
-3SD: This field is to calculate – 3 standard deviation based on middle line
TEST – Outliers: Boolean field to highlight the outliers in the dataset i.e. if the sales value is either above +3SD or -3SD
TEST – TREND: Boolean field to highlight the trend whether current sales > previous month > previous to previous month or current sales < previous month < previous to previous month.
TEST – CHANGE: Boolean field to highlight whether 3 consecutive fields are below or above middle line.
TEST – SELECTION: This field is based on “SELECT A TEST” Parameter and uses above 3 calculated field to show the required value.
Tooltip – Signal: Tooltip to show signal if the sales value matches with any of the parameter selection criteria
Show Text: This is the field to show the necessary text based on parameter selection
STEP 3: There are 3-4 more fields which we need to create but lets start creating data visualisation for first 2 sheets
Drag Order date to column shelf (convert to month)
Drag Sum(sales) and TEST Selection to rows shelf then change it to dual axis followed by sync axis
Drag Middle line, +3SD, -3SD to details in Marks Area
Add a 3 reference lines on the basis of Middle line, +3SD and -3SD
Add Parameter control for Parameters “Select a Middle line” and “Select A TEST”
Then format the sheet as per requirement with tooltips and it will look like following:
Create an new sheet for Text data and drag Show text field to Text
Step 4: Final sheet for Monthly strip chart
For Strip chart, we will need to add following fields into the dataset
TREND: This is boolean field which we will use to highlight the pattern for Trend
CHANGE:This is boolean field which we will use to highlight the pattern for Change
SymbolCOLOR: Based on Parameter selected for the test, this is will return values for the test select and we will use this as also to segregate data into 3 color bucket of blue, amber and orange to show following:
- Meets the test criteria
- Part of test pattern
Finally, we will create 3rd sheet with strip chart:
Drag Order date to column shelf (convert to month)
Then Drag symbolColor to color and shape area under Marks area then create the tooltip to show the values
Step 5: Create the Dashboard
Add all 3 sheets into dashboard as per below image:
Now we are ready with visualization with user experience which we wanted to show.
Thanks Ann Jackson for this workout.
Happy Data Visualisation!!!!
Thanks for visiting this post. Please do let me know your feedback or if you have any questions about the blog do not hesitate to contact me on twitter (@Desaimithun)
Do subscribe to Tabvizexplorer.com to keep receive regular updates.