Rody’s back with a new #WorkoutWednesday challenge for week 7. We’re to make a simple crosstab table showing Sub-Category sales by month. We are to also color the min and max sub-categories for each month and the Sub-Category grand total.

Similar to last week, this one is pretty simple. This took me maybe 15 minutes.

1. Filter year to 2017
2. Filter Category to Furniture & Technology
3. Sub-Category on rows
4. Discrete Month([Order Date]) on columns
5. Analysis menu ->Totals ->Show Row Grand Totals
6. Sort the rows based on Grand Totals
7. Then for the colors
   • What I see from the picture are colors for the max and min values for each month and the totals.
   • I immediately think window calc. I need to compare each cell against the min and max for each month
   • My calc is gonna look like this:
     • IF SUM([Sales])=WINDOW_MIN(SUM([Sales])) THEN -1
       ELSEIF SUM([Sales])=WINDOW_MAX(SUM([Sales])) THEN 1
       ELSE 0 END
   • When I drop this pill on color, I’m going edit the “Compute Using” menu to set it to “Table (down)”
     • This will set the “window” to each month
   • Now that the color calc is working properly it’s time to set the color. Because I’m only using three values (-1,0,1) I can easily use a diverging palette and sure enough, there’s a red, white and blue one.
   • Now, by default, Tableau doesn’t include Totals but click that checkbox and all is well!

Here’s my final product, click for the interactive version.

Until next time!