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.

MinMax

Until next time!

Advertisements