Late post this week on #WorkoutWednesday was set by Ann Jackson and it was about using dot plots to compare current year sales with previous year sales. Generally, we use some form of bar chart to spot the trend over a period but it can get bit messy if we want to compare the same on multiple data points with sorted years. With Dot plots, we can have powerful yet simple visualisation technique to display comparative analysis on multiple data points. There are additional indicator as well to spot the current year values with different color for good and poor against average of sales for selected years.
Requirements
- Dashboard size: 1250 x 900, 2 sheets, tiled
- Each dot represents annual sales by subcategory
- Color of dots:
- Most recent year below average = pink
- Most recent year above average = blue
- Not most recent year = gray
- Calculation for most recent year should work if data updates and 2018 gets added in (don’t hard code 2017!)
- Match tooltip language – don’t hard code the number of years, build a calculation!
- Match formatting: specifically horizontal line, banding, reference line
- Don’t forget about the legend
Additional year filter so you can see what the tooltips and colors look like
Data for the workout can be downloaded from here
Here is my output for the challenge (Click on Image for interactive version):
Lets go through step by step process of re-creating this dashboard and understand the process of generating similar dashboards in future for our business problems
Step 1: Import the dataset given in above link into Tableau file and create following 3 calculated fields first:
Yearly average sales: This is an dynamic filter to ensure right average amount is picked based on years selected:
Color: To allocate color for current year sales performance (blue or pink) and grey for other years
Tooltip: This is for dynamic tooltip on mouse hover over dots
And last field on Legend (dummy field): This is dummy field to show legend in circle
Now, we are ready to create the worksheets to meet the requirements mentioned above.
Step 2: Drag Category and sub-category from dimensions to rows then convert them into upper-case by using Upper() function:
Then drag sales field from measures to columns and change the chart type to circle
now, create a dummy field in columns using following text min(1) and select the chart type as bar then convert the chart into dual axis
Remove measure names from bar chart and change the axis in bar chart to fixed from 0 to 1 with size almost 0:
Drag upper(sub-category) field into Label for bar chart and change the alignment to top left
Step 3: Once we are through with Bar chart, we will now work on circle chart for dot plot.
Drag Order date to details and remove measure names from color:
Drag Color field which we created in step 1 to color:
Drag tooltip into tooltip and modify the tooltip to match the requirement:
Next is to send bar chart back, for that right click on min(1) on axis and select move marks to back:
Hide the upper(sub-category) field header from rows and apply necessary formatting required along with context filter of Order date and show filter with multiple drop down:
Step 4: create an new sheet for legend and drag legend field to column and colors
Now, combine both the sheets into Dashboard as shown in above complete dashboard image.
Happy Data Visualisation!!!!
Thanks for visiting this post. Please do let me know your feedback and if any particular topic you would like me to write on.
Do subscribe to Tabvizexplorer.com to keep receive regular updates.
