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
