The first WorkoutWednesday hit me hard with lots of challenges and pushed my beginner Tableau brain to bend in wa I could never have imagined!
The post focused on my approach to WoWs and this week is where I put the strategy to the test. Along the way, I discovered various tips about dates, filters, parameters, and learned a lot about Window Calculations.
Keep reading for a trip inside my mind as I explore the wonders of the first WoW.
What's happening horizontally:
I started out the WoW by tackling the dates section on the X-axis. In my mind, dates are one of the most confusing things for a beginner Tableau user and this particular WoW was no exception.
I realized I needed continuous days on the x-axis, which was fairly simple. However, it was quite difficult for me to get anything but numbers, 1 to 31. After all, if I just dragged order date and then select Day from the pill, I would end up with only 31 days!
Tip: There is a ‘daysofyear’ date part. This date part will take your current date system – order date in this case – and convert it into the days of the year ordered from 1 to 365.
With this knowledge, I created a calculated field and used the DATEPART function to pull one piece of information from my date – in this case, the ‘dayofyear.’ When I place this calculation on the columns shelf:
With that I get a continuous axis of 0 to 365 days – exactly what I want! ✔️
What do we see vertically:
The first step in pegging down the Y-axis was breaking down what the label - “cumulative percent of total orders” - was really saying. 🕵️
I started with cumulative, here the WoW team is saying I needed the order quantities of the day before to add to today, and then today to add to tomorrow, and so on…well, that’s great because there’s a table calculation that does that: Running Sum.
Tip: Learn as many formulas/table calculations as you can! Then you know if Tableau has a built-in table calculation/formula that will do the work for you! Rather than trying to create the calculation by hand, which is sadly what I did originally. 😬
Now, that I used a table calc to get cumulative Order Quantity, I also needed to calculate the % of total orders. This time I had learned from my last tip: check the table calculations, and in doing so I was pleasantly surprised.
I could take the running sum calculation, add a second table calculation - percent of total - and I had my Order Quantity on the rows shelf with two table calcs on it, to create “cumulative percent of total orders.”
Tip: When doing table calc’s paying attention to the specific dimension you want to calculate by is essential. Otherwise shifting around the data in your table might change your results.
Tip: Andy Kriebel wrote a great article about Table Calc's that was super helpful to me and I highly suggest everyone check it out.
My Marks Card:
Until this point, the rough structure might have been right, but the marks were still nowhere close to those of the WoW! 😟
The first thing that’s glaringly obvious is I only had one line, while the WoW had four and if I scrolled over, it seems there are different colors for each year. So, I just dragged year of order date onto the color section of my own Marks Card and…that gave me a different color for each year. 🌈
Now for the Filter Card:
If you were really paying attention 🔎, you’d have noticed that unlike my own coloring, each year in the WoW is grey except for the last year which is highlighted – and this was where filters came into play!
Tip: If you see a select box, drop down, etc, there is usually either a parameter or filter there and if the variable represented is external (not directly in your data) then it’s probably a parameter.
So, since I saw a selection box to check off the years I wanted in my viz, this was a good indicator that I need a filter. I dragged order date and selected: year, then use all values, and I could now filter by year.
Tip: If you want to see your filter as a drop down/selection card on your viz go to the filter click the drop down and say show filter. Now it should appear on your sheet!
It seemed that the select/drop-down boxes: year, product, region, and segment were all filters, since they were internal to the data source, so I filtered by all these. However, the color had still not changed to show the max year as highlighted.
For that I need a calculated field:
This was no easy feat for me and took some help, so let’s break it down: 🕵️
This part of the calculation gave me an aggregation of the year values.
Then by taking the WINDOW_MAX(), with specific dimensions of Day of year and year, I could see the maximum year in the entire data view.
Finally, I compared the aggregated order date ‘years’ to the maximum year in the window, which was only true if I was in the maximum year. Then I could drag this field to color and we have a viz whose greatest year is highlighted: 👏
Tip: ATTR() allows you to solve a lot of the problems with: “cannot mix aggregate and non-aggregate fields” because it aggregates the data it takes in and returns one value as long as all the values in that group/row are equal, otherwise ATTR() will return an *.
The Extra Thing:
Ultimately, this was by far the hardest part of WoW - getting the dots to appear above the target - and in my mind what took the challenge from fun to totally mind-bending 🤔!
When I saw the selection panel for the target that moved the bar on the viz, I knew it was either a filter or parameter. Since the target was not represented in my data at all, it was an external value chosen by the viewer, I knew it was a parameter.
Tip: external fields that aren’t in dataset should probably be set by a parameter.
But it wasn’t that easy. Sure, I created a parameter and updated it so that it moved across the page – but getting the dots to highlight was another matter entirely.
The first criteria of the dots were to establish where they should start and it’s fairly easy to use a calculated field to partition the data to grab only the segment above the parameter.
Now that I had the points above the target, I just needed to find the minimum day. But this is tricky because you can’t find the minimum of an already aggregated result such as the “Points above target” calculated field.
This is where I had to search to Emma’s solution to eventually find some clues - and props to her because her solution was quite ingenious!
Each time you take a window minimum of the points above the target you get one column that is the minimum value of the entire data source. Only at one specific point will the window min actually be equal to the lowest point above the target. Therefore if you locate that equivalent point and then calculate the % of cumulative total orders again, you’re left with your value.
Evidently, this part of the workout really forced us to understand what WINDOW_MIN() was doing and think outside the box in a way that teaches us more about tableau. 💪💪💪
What a Workout!
I don’t know about you but I am totally exhausted from this mental challenge. I learned stuff about dates, table calculations, filters, and in particular gained a new appreciation for the strength of the WINDOW_MAX() calculation.
I hope you can keep track of some of my tips from this week, and stay tuned for when I delve into the next WoW!
Please feel free to check out my solution to this workout on my Tableau Public Profile and check me out on LinkedIn.