Introduction
Happy New Year! I’m excited to be back for yet another year of Workout Wednesday. This year we’ve expanded the team and made a commitment to the community to provide solution videos. We’ve also got some great collaborations lined up, and a few exciting enhancements to come (custom color palette anyone?)!
To get the year started off right, I thought we could focus on an essential – building beautiful and dynamic bar charts. Bar charts are likely the number one chart you’re making in your daily data viz life. They’re easy to understand, useful in comparing information, and can be scaled large and small and still look good.
While this is only a bar chart, don’t think I’ve gone too easy on you. This bar chart includes an interactive way to change between metrics – and perfect formatting for different number types. In fact, I’d bet you’ve likely been asked to build some variation (or parts) of the final product.
We’re focusing on three major components today:
- Dynamically changing metrics
- Dynamically changing date ranges
- Precision formatting for maximum understanding
As with many of my challenges, the devil is in the details – so I’ll be paying extra close attention to the final products to ensure you’ve mastered everything.
Requirements
- Dashboard Size: 1100px by 800 px
- # of Sheets – up to you
- Create a bar chart that switches between 3 metrics
- Sales
- Profit Ratio
- Items per Order (Quantity/Orders)
- Bar chart must switch between 3 time ranges
- Last 12 months
- Last 13 weeks
- Last 14 days
- Since the data only goes through 12/31/2019, you can use a parameter to set a fixed “Today” date of 1/1/2020
- Formatting
- Match the headers for the dates
- Month: Short Month and Year
- Week: Week and number
- Day: mm/dd
- Match the labels for the metrics
- Sales – this should be expressed as currency, with no decimals. Commas should show up for thousands, millions, etc.
- Profit Ratio – Percentage with one decimal
- Items per Order – Decimal number, with one decimal
- Use RegEx for formatting the numbers!
- Finishing Elements
- Create a dynamic button system that changes based on metric selection
- Match colors (more official palette to come, for now you get hex codes)
- Sales: #9264a5
- Profit Ratio: #86b35e
- Items per Order: #63ccc6
- Match tooltips, which are pretty simple
- Match the headers for the dates
A small hint: you can choose to use another data set for some elements if needed or preferred. I was able to accomplish this with Superstore, but it does involve being creative.
Dataset
This week uses the superstore dataset for Tableau 2019.4. You can get it here at data.world
Attribute
When you publish your solution on Tableau Public make sure to take the time and include a link to the original inspiration. Also include the hashtag #WOW2020 in your description to make it searchable!
Share
After you finish your workout, share on Twitter using the hashtag #WOW2020 and tag @AnnUJackson, @LukeStanke, @lorna_eden, and @HipsterVizNinja
Solution
Interactive
Hi Ann,
I just got my Tableau Associate certification last week and this is the first time I attend the Workout Wednesday. Just want to make my mind clear, we are going to create a dashboard through the requirements and it is something looks like the one you provide on tableau public (bar chart). And I need to finish mine in one week and publish on my Tableau public with description to make it searchable, right?
You’re almost completely correct, except we don’t enforce a due date! Most people do try to complete the challenge with a few days of release, but you aren’t required to.
Congratulations on your achievement and welcome to Workout Wednesday!
Hi Ann. It seems the dataset only has order date till 12/31/2018.
Hi Ann,
The Superstore data set only goes through December 2018. Is that right?
Thank you and I am excited to join this community !
Hi – I may be looking at the wrong file, but the file I downloaded from data.world only has orders up to the end of 2018? I’ve started building my viz based on that but would appreciate any links anyone has to the file with orders up to the end of 2019?
Hey Ann,
Just noticed that the latest order date in the data-set is 2018-12-30. You may be pointing to a wrong file on data.world.
Best,
Sujay
Thanks for letting me know – I’ve updated the file link for data.world to the newer data set!
The link has been updated to include the data set that runs through 2019!
Lynne – The link has been updated, it now has the newer data set!
This has been updated to the newer 2019 data set!
Thanks Ann for the video! Now I see what a clumsy calc I used for the labeling…and I need to do some work on mastering regex_replace. I really enjoyed seeing how you think through the parameters.
More general question. New to parameters. Do they always need a case statement calculation to work properly? Wondering if this is a standard process or specific to this solution.
Thanks!
Great Challenge Ann, enjoyed working on it., learnt some new stuff like reg expression and parameter action.
Hi Ann,
I am fairly new to Tableau and I am following the solution and I am getting error on the Profit Ratio of the Selected Metric Calculation
CASE [Select Metric]
WHEN 1 THEN SUM([Sales])
WHEN 2 THEN [Profit Ratio]
WHEN 3 THEN SUM([Quantity])/COUNTD([Order ID])
END
error – Cannot mix aggregate and non aggregate comparisons or results in ‘Case’ expressions
Did you create a different Profit Ration field?
Hi Zee – you probably want to double check your Profit Ratio calculation. This should be the SUM(Profit) / SUM(Sales)!
They don’t always need a CASE statement, but typically do when you’re swapping metrics. You can use them in more simplistic ways (like a dynamic goal/target), where the calculated field may be SUM(Sales) < [Sales Target]
Hi Ann,
Nice!
It took some days to master the regex…https://public.tableau.com/profile/marcodegola#!/vizhome/WorkoutWednesday2020Q1_15801420581770/W02
The method of metric select legend is a little bit different to the method(2020 W1) by Luke Stanke. Thanks to you guys of providing different ways to achieve the goals. By the way the parameters for changing the metrics really makes me faint!
Why you use ‘segment’ to make parameter measure names? What if there is no such categorical dimension to use?
Hi,
I downloaded the csv data set, when I push this in tableau using text file connection all my metric fields like sales, profit, profit ratio appear in dimension and when i change in to measure it appear these field appear with 0 value. Secondly if I change data type at data source page from string to number then also that field appears 0. what is wrong here unable to capture ?
Hey there – I would check the underlying data you downloaded and see if the % and/or $ symbols are in the data. If so, remove the number formatting in Excel and then try to connect via Tableau Desktop!