Week 15: Total Products by Sub-Category OR Top 5 Sub-Categories by Total Product

I’m back from a one month hiatus! Hopefully you enjoyed the guest posts from Curtis Harris and Ann Jackson.

This week were giving you two options: Intermediate or Jedi. Both are a play on an olympics visualization I put together a month ago.

The Intermediate requires just a handful of calculations. The Jedi looks just a little more difficult and will require about twice as much work to figure out.

 

Intermediate

Build a compound pictogram that counts the distinct number of products by sub-category and show the sub-category name and total number of products.

• Dashboard size is 850 x 600; tiled; 1 sheet
• Each member of the pictogram is a distinct product name.
• Each row in the pictogram has 10 members.
• Members of each new row are included from left-to-right.
• Sort the product name from highest to lowest frequency.
• Color each group of 100 a differently (I used orange tones).
• Add a small break between each set of 100 members.
• Place the product name and the total sales in the tooltip.
• Add a product label and the total number of distinct product names above the compound pictograms. Make sure the Product names is bold font and the count is regular font.
• Set the minimum and maximum values on the columns axis (x-axis) to -3 and 12, respectively.
• Set the minimum and maximum values on the rows axis (y-axis) to -1 and 32, respectively.

 

 

Jedi

Do basically the same thing, but now include color for the year the product was introduced and show all subcategories in a 5 x 4 grid.

• Dashboard size is 550 x 1200; tiled; 1 sheet
• Each member of the pictogram is a distinct product name.
• Each row in the pictogram has 10 members.
• Members of each new row are included from left-to-right.
• Sort the product name from the first order date in the dataset.
• Color each by the year of the first order date in the dataset.
• Place the product name and first order date in the tooltip.
• Add a product label, emoji, and the total number of distinct product names above the compound pictograms. Add the labels 6 units higher than the maximum number of rows in each pictogram. Center the labels on the 6th value of the row.
• Set the minimum and maximum values on the columns axis (x-axis) to -3 and 12, respectively.
• Set the minimum and maximum values on the rows axis (y-axis) to -1 and 40, respectively.
• Order the sub-categories from most-to-fewest products going left-to-right, then down the page.

 

Dataset

This week uses the superstore dataset.  You can get it here at data.world

 

Share

After you finish your workout, share on Twitter using the hashtag #WorkoutWednesday and tag @AnnUJackson, @LukeStanke, and @RodyZakovich.  (Tag @VizWizBI too – he would REALLY love to see your work!)

 

Track your progress

Also, don’t forget to track your progress using this Workout Wednesday form.

 

Hints & Detail

• You’ll want to use the INDEX() function.
• Remember index starts at 1, sometimes you’ll want your measures/dimensions to start at 0.
• You’ll probably use the modulus function (%).
• There a couple of ways to create the appropriate number of rows for each sub-category.
• I used an IF statement for when INDEX() = 6 to help create my labels.
• Have fun!

Helpful & Free Functions

 
## Emojis
CASE [Sub-Category]
WHEN "Paper" THEN "📝"
WHEN "Binders" THEN "📒"
WHEN "Phones" THEN "📞"
WHEN "Furnishings" THEN "📻"
WHEN "Art" THEN "🎨"
WHEN "Accessories" THEN "📫"
WHEN "Storage" THEN "📦"
WHEN "Appliances" THEN "💻"
WHEN "Chairs" THEN "💺"
WHEN "Labels" THEN "🔖"
WHEN "Machines" THEN "⏰"
WHEN "Tables" THEN "🔷"
WHEN "Bookcases" THEN "📚"
WHEN "Envelopes" THEN "✉️"
WHEN "Supplies" THEN "✂️"
WHEN "Fasteners" THEN "📌"
WHEN "Copiers" THEN "📠"
END

## Sub-Category order hack
CASE [Sub-Category]
WHEN "Paper" THEN 0
WHEN "Binders" THEN 1
WHEN "Phones" THEN 2
WHEN "Furnishings" THEN 3
WHEN "Art" THEN 4
WHEN "Accessories" THEN 5
WHEN "Storage" THEN 6
WHEN "Appliances" THEN 7
WHEN "Chairs" THEN 8
WHEN "Labels" THEN 9
WHEN "Machines" THEN 10
WHEN "Tables" THEN 11
WHEN "Bookcases" THEN 12
WHEN "Envelopes" THEN 13
WHEN "Supplies" THEN 14
WHEN "Fasteners" THEN 15
WHEN "Copiers" THEN 16
END