Posts tagged Level of Detail

2019 Week 24: Can you build a side-by-side bar chart?

This week you need to build this side-by-side bar chart. There are a couple of tricks but you should be able to do this in under 30 minutes (for real!). There are at least 4 ways to solve this problem. There are also two problems an intermediate and an advanced.

Challenge – Intermediate


Click image to view on Tableau Public

Requirements – Intermediate

  • Format: 600px by 600px
  • Create side-by-side bar chart that shows:
    • profit ratio for the Home Office segment and category.
    • Overall Profit ratio for all segments by category.
    • Profit Ratio: sum(profit)/sum(sales)
  • Add a label for the Overall bar and the Home Office bar
  • Inside the Home Office bar show the percent difference between Home Office and Overall — ([Home Office] – [Overall])/[Overall]
  • Make sure a footer of the categories shows up at the bottom of the dashboard.
  • Color choices are up to you.
  • Challenge – Advanced


    Click image to view on Tableau Public

    Requirements

    • Follow all intermediate requirements.
    • Create bars so there is more space between categories.
    • No annotations allowed
    • SUPER DUPER WAYYYYYYY ADVANCED OPTIONAL: Do not use Measure Names or Measure Values. Do not use table calculations.
    • Dataset


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

      Share

      After you finish your workout, share on Twitter using the hashtag #WorkoutWednesday2019 and tag @AnnUJackson, @LukeStanke, @lorna_eden, and @curtisharris_!

      Track your progress

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

      Attribute

      When you publish your solution on Tableau Public make sure to take the time and include a link to the original inspiration.


Week 17: Can you build an automated monthly report

A few weeks ago I had this problem: I needed to create a dynamic table that showed the last 12 full months of sales for a particular channel/category of the business but I also needed to show the month-over-month and year-over-year totals. I didn’t know how to do it right away so I asked the Workout Wednesday team and they responded with many solutions. So I changed this week to match that exact problem. I’m also including some information on financial reporting since I wasn’t familiar until recently. We’ll be calculating basis points. The basic formula is (%a -%b)*10000. Where a and b are just two percentages. This means 1 bps = 0.01% and 100 bps = 1.00%.

Challenge


Click image to view on Tableau Public

Requirements

Intermediate

  • Format: 1100px by 400px
  • Create a table that shows the following metrics for the last 12 full months by category:
    • Percent of total sales that are from the Consumer segment.
    • Month-over-month change in % Consumer sales using basis points (bps). (Current Month % – Prior Month %) * 10000
    • Year-over-year change in % Consumer sales using basis points (bps). (Current Month % – Same month prior year %) * 10000
  • Add a filter so that you can toggle dates. Note the months will only show when data is for a complete month of data.
  • Match formatting border and fill formatting.
  • ├ and └ will be useful.

    Dataset


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

    Share

    After you finish your workout, share on Twitter using the hashtag #WorkoutWednesday2019 and tag @AnnUJackson, @LukeStanke, @lorna_eden, and @curtisharris_!

    Track your progress

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

    Attribute

    When you publish your solution on Tableau Public make sure to take the time and include a link to the original inspiration.


Week 28: Order Profit

Workout Wednesday in August

August is community submission month. For all 5 of those weeks we’ll have our favorite submissions as challenges. You can submit here.

 
 

This week’s challenge.

Sometimes we want to look at the spread of our data, but we often end up with one or two groups that much, much higher levels. This often leaves us with many bars that are very small. In these cases – specifically cases where we want to emphasize the smaller values – I like to use a log axis. This week we’ll take a look at log axes by looking at the variability in per-order profits by sub-category.

click to view on Tableau Public

Requirements

  • Dashboard size is 500 x 700; tiled; 4 sheets
  • Use the superstore dataset. Focus on the Sub-category of the “Technology” category.
  • Show profit per order on the columns (x-axis).
  • Use a parameter to round the profit to the nearest $25, $50, $100, $250, $500, or $1000.
  • Show distinct count of orders on the rows (y-axis). Set your axis to run from 0 to 999.
  • Set the axis to be log.
  • Make sure to assign the highlight color to each sub-category.
  • Filter to orders with profits from -1500 through 1500.
  • Create bars and center them on the appropriate value. Set the width of the bar to the rounded value.
  • Add annotations so they only show when $100 is selected on the parameter.
  • Set the height of the Accessories, Copiers, and Office Supplies to 110 pixels. Set Phones to 160 pixels.
  • Match color, tooltips, and formatting. I’ll be paying attention to all parts.

 

Dataset

This week uses the superstore dataset for Tableau 2018.1.  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.

 
 
 

#SPOILERS

  • You need to create a parameter and 5 calculated fields.
  • 3 of the calculated fields are just duplicating the sub-category field.
  • One of the calculated fields is equal to the parameter values.
  • Make one sheet first before you do the rest. This way you can just duplicate them.
  • You can use FIXED or INCLUDE level of detail functions to solve this problem. Or you can use a table calculation.
  • ROUND({FIXED [Order ID], [Sub-Category] : SUM([Profit])}/[Round Parameter]) * [Round Parameter]
  • You can always download the workbook for more help.

 

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.

click to view on Tableau Public
  • 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.

click to view on Tableau Public
  • 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