BYU Student Author: @MitchFrei
Reviewers: @Jonathan_Weston, @Donovon
Estimated Time to Solve: 45 Minutes
We provide the solution to this challenge using:
- Tableau
Need a program? Click here.
Overview
Vintage Voyage is a toy manufacturer that sells vintage toy vehicles to retailers across the world. Today is May 31st, 2005 and you were just promoted to be the global sales manager overseeing close to $2 Million in sales each year. The CEO overheard that you know how to use Tableau and has asked for a monthly sales dashboard. Here is his request:
“I want a dashboard that I can view at the beginning of each month to see how we performed the prior month. I want a title at the top that says Monthly Sales Dashboard and below that, I want to see three numbers from left to right: Last Month Sales, Prior Year Last Month Sales, and Year over Year comparison. For example, if today is May 31st, 2005, I want to see April 2005 Sales, April 2004 Sales, and the difference between the two. Below those three numbers and taking up the majority of the dashboard space, I want a vertical stacked bar chart that shows me sales by week in the prior month with each bar representing revenue for a certain week and each color in the stacked bar representing revenue for a certain product line. Label each section of the bar with the number of sales and make sure to include a legend for which product lines the colors represent.”
Instructions
Fulfill the CEO’s request by creating a Monthly Sales Dashboard in Tableau. Consider the following:
- This dashboard will need to be automated so that whenever the CEO views the dashboard it always shows last month’s data without any manual adjustments going forward. This means that you are not allowed to hard code any dates in a filter or calculation – you will need to use date calculations.
- Remember that for this exercise, today is May 31st, 2005. Normally to make the dates automatically update, you would need to use the TODAY function. This of course won’t work since the data ends in 2005. Instead, create a calculated field called “Today” with the value #2005-05-31# and reference that calculation rather than the TODAY function.
- When making the weekly bar chart, you must group the dates by week. This will require a calculation that adjusts the Order Date to its respective week ending date. For example, if an order took place on Wednesday April 12th, then the respective week ending date is Saturday April 15th.
- If you want an extra challenge, consider making the following additions to the dashboard:
- If the Year over Year comparison is positive, make that number green. If it is negative, make it red. (Have the dashboard do this automatically, not manually).
- Along with labeling each section of the stacked bar, add a label for the total of the bar that appears at the top of the bar.
- Label the Last Month Sales and Prior Year Last Month Sales with a dynamic title that displays the name of the last month and year. For example, if today is May 31st 2005, then the titles would say “April 2005 Sales” and “April 2004 Sales.” This would be automated so that there is no need for manual adjustments each month.
Data Files
Suggestions and Hints
- You will need a total of 4 sheets on your dashboard – one for each number along the top, and one for the stack bar chart.
- When creating the dashboard, consider using a text object for the title, a horizontal object for the three numbers below the title, place the stacked bar chart underneath that, and make the legend floating.
- Consider creating the following calculations:
- Today
- Week Ending
- Beg of Last Month
- End of Last Month
- PY Beg of Last Month
- PY End of Last Month
- Last Month Sales
- PY Last Month Sales
- YoY Last Month Sales
- Last Month Sales Title
- PY Last Month Sales Title
- YoY Color
- In order to make the above calculations, you will need the following functions:
- DATE
- DATEADD
- DATETRUNC
- DATENAME
- IF
- AND
- THEN
- ELSE
- END
Solution
