I would like to start this blog by thanking my dear colleague Erica Hughes for creating this absolute behemoth of a Workout Wednesday challenge. Here is the challenge link: https://workout-wednesday.com/2024w11tab/ Additionally, here is my completed version: https://public.tableau.com/app/profile/tyler.ha7603/viz/WoW2024W11FilteringSmallMultiplesCharts/Dashboard1?publish=yes
Never in a million years could I have ever imagined coming up with something this complicated. However, it is an excellent opportunity to practice applying your deep theoretical knowledge of Tableau's behind-the-scenes logic. I'm not going to mince my words here—this challenge is HARD. But if you're willing to commit a LOT of brain energy (and maybe a couple tears) this challenge will be very rewarding and well worth your time. There are a variety of ways to get to the end result, each with its own challenges and loopholes. I've already seen multiple, and I'm interested to see what other methods people end up using.
In this blog, I will be documenting all of the "road blocks" that I faced throughout the 3 days that I spent completing this challenge and some "hints" to orient your way of thinking. I hope that this blog can help at least one person get past a road block. As I hinted at before, depending on how you decide to tackle this task, you will have different challenges/road block points.
Roadblock 1) Remembering how to set up small multiples charts in Tableau.
Hint 1) Okay my answer for this one is really bad, sorry. Just google how to make small multiples charts in Tableau. If you've never made one before, I think it'd be very hard for me to provide a hint that would help you "figure it out". This is kind of one of those things that you need to be shown. BUT YOU SHOULD ABSOLUTELY TAKE THE TIME TO REVERSE ENGINEER/LOGIC HOW IT WORKS. Doing this will help you understand how Tableau's rows and columns TRULY operate behind the scenes.
Roadblock 2) Setting the threshold reference line at AVG(Sales Modified)*2 for each state.
Hint 2) To make the sales data more interesting, Erica decided to make a new Sales (Modified) field. She provides the corresponding calculation: IF SUM([Sales])<5000 THEN SUM([Sales])*10 ELSE SUM([Sales]) END While I'm all for more interesting data, this modification does complicate things a bit for our reference lines. This is because the modification makes Sales (Modified) an aggregate measure. If we were just using the native Sales measure, getting average quarterly sales at the state level would be relatively simple. I would simply make a FIXED LOD with the appropriate levels of detail: AVG(Sales) and then wrap the whole LOD in parentheses * 2. However, since Sales (Modified) is an aggregate measure, you can't put AVG() around it. I can't tell you too much more than that, but I was on track with my way of thinking. You're just gonna have to pick up where I left off and get a little creative. USE SENSE TABLES AS YOU GO TO TRACK WHAT IS HAPPENING AS YOU MODIFY YOUR CALCULATIONS! Oh, one more thing: If you figured out how to do this with table calculations, good job! But I would highly highly recommend trying to figure out a way to do it without table calcs. Your future self will thank you. Seriously.
Roadblock 3) Getting red dots on the quarters where a state's sales exceeded the threshold.
Hint 3) If you think that you can get dots to populate natively on your line graphs... I'm sorry, but you are sadly mistaken. You're gonna need a dual axis here. And the dots have to be conditional. Try to think of a calculation that will provide you with Sales (Modified) when the condition (above threshold) is met, but NULLS when the condition is not met.
Roadblock 4) SETTING A REFERENCE BAND AROUND THE FINAL POINT (Q4 2023) IF IT EXCEEDS THE THRESHOLD.
Hint 4) Holy moly I could write a novel about this part. I was stuck on this for so long. Number one thing: Reference bands only work on continuous measures. This might seem obvious to some of you, but it's easy to forget if you haven't worked with reference bands in a while. Unfortunately, if you've been using Quarter(Order Date) as a discrete measure, then you might have a lot of breaking and fixing to do. It could also just be as simple as right clicking the pill and changing it. If it doesn't break anything in your charts, then great! Again, this all depends on how you approached the challenge from the start.
Roadblock 5) Classifying States as urgent/not urgent with a filter
Hint 5) If you've been using table calcs for the threshold... here is where you might see some problems. When you filter between the two options, if your graphs aren't shifting up to fill in the empty spaces, then you are probably going to have to circle back. You need to use LOD's here due to Tableau's Order of Operations (table calcs happen last). My biggest hint here is that you should try to isolate both the last Q sales and the threshold for each state in their own calculated fields. This way, you can make a simple Boolean filter using those two fields.
Roadblock 6) Sorting the states by urgency and then by the number of points above threshold.
Hint 6) First, you should try to isolate the number of points above threshold for each state in a calculated field. While you could probably make it work from here, Erica provided a little hint in the challenge that makes this all much easier. Once you create this initial calculation, you can bring that into another conditional calculation where if a state is classified as "urgent," you manually "add" +1000 (or any other big number) to the count of points. By doing this, the urgent states are automatically all pushed to the top.
That's about it! If you've made it this far in both this blog entry + the actual Workout Wednesday challenge, I applaud you. Great work and happy learning!