Posts tagged table calculations

Week 38: Can you build a sales comparison chart with performance indicators?

This week I’m tackling a topic that nearly everyone has been asked – visualizing good/bad as performance indicators that are red or green.  At least here in the western world, the busy executive tends to think about things in terms of traffic lights.  So this week your task is to create something that satisfies their ask of seeing immediate good/bad and adding on some additional analytical components for context (essentially the “why” behind the good/bad).

click to view on Tableau Public (screenshot taken 9/17/19)

This dashboard also tackles the idea of automating the visualization to update as time ticks on.  The goal is that you build something today and it will continue to work in the future (in fact you’ll probably see based on when you’re viewing this blog post the visual will change).

And a last side benefit of this challenge will be learning how to build something that can be automated/pre-built, even if you don’t yet have the data.  In this specific example we’ll be pretending it’s 2018 to use the static Superstore data set.  So if you find yourself working with a static data set in the future and trying to anticipate and build out the visuals, you can use this method to construct the final view.

Requirements

  • Dashboard Size: 1100 x 700 (you choose # of sheets)
  • Create a running total sales chart (MTD) for the 3 different Segments
    • Show current year as a running total line chart, colored red/green depending on if it’s above/below the same MTD value from the prior year
    • Show prior year as area chart – if it’s part of the prior comparative time period it should be dark gray, yet to come should be light gray
    • Create a dynamic reference line for Today
    • Add on a dynamic title that states the days left in the month
    • Add on an indicator to the left of BANs that is red/green depending on if sales is up or down
    • Match labels, tooltips, and all additional formatting
    • Include a Category filter so you can test the functionality
    • Hint: pretend we’re in the year 2018 by subtracting X # of years from Today

Dataset

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

Attribute

When you publish your solution on Tableau Public make sure to take the time and include a link to the original inspiration.  Also include the hashtag #WorkoutWednesday2019 in your description to make it searchable!

Share

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

Watch the Solution

2019 Week 18: Let’s Compare Quarterly Sales Performance

Have you ever been asked to make a tool that allows end users to dynamically select two periods and compare them?  The challenge this week takes inspiration from this all too likely to be asked request.  At surface level it can be an easy ask, but how can you add on additional context along with a responsive and intuitive design that allows for a good user experience.

In this challenge you’ll be creating 3 often used chart types: a line chart, bar chart, and BAN (big ass number).  The twist?  You’ll be adding on custom axes that reference color identifiers throughout the dashboard and ensuring that the most recent quarter selected is always highlighted.  And what happens if someone accidentally selects the same quarter and tries to compare it to itself?  Well you’ve got that covered too with some nice flavor text to help them along.

CLICK TO VIEW ON TABLEAU PUBLIC

 

Requirements

  • Dashboard size: 1500 x 900; maximum of 3 sheets; No text boxes!
  • Create a line chart that does the following after a user selects 2 different quarters
    • Shows the running total of sales by day
    • The older quarter is gray, newer quarter is blue
    • The axis at the top displays dates related to the newer quarter
    • The axis at the bottom displays dates related to the older quarter
    • The tooltip references the order date, # of days into the quarter and the running total sales amount
  • Create a BAN that shows the percentage difference
    • The older quarter is always the “compared to” or prior value
    • When the quarters are the same, text displays “Change Comparisons”
  • Create a bar chart that shows total sales for each selected quarter
    • Bars should always have the blue bar on the right (newer quarter)
  • Create a dynamic title that always shows the newer quarter first and in blue
  • Match all tooltips, labels, and formatting (including axes)
    • Blue: #2cb5c0
    • Gray: #b4b7b7

I highly recommend you view the original on Tableau Public to see functionality in action.

Dataset

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

Attribute

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

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.

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.


2019 Week 10: How Has Our Profit Ratio Changed Nationally?

This week’s challenge is plucked directly from #IronQuest.  If you’re not aware of what #IronQuest is, it’s a monthly challenge during the off season of Tableau Public’s Iron Viz feeder contest.  It’s run by Sarah Bartlett and each month a different theme is chosen.  Participants are allowed to submit for feedback (the idea is to prepare for Iron Viz and to get more comfortable building out new content).  The month of February just closed with a very appropriate theme voted on by the public, Business Dashboards.  You can check out the full list of dashboards (there’s 30+) at Sarah’s blog.  Since the theme falls heavily in line with Workout Wednesday (most of our challenges come directly from our day-to-day work), I decided to bring my own submission for #IronQuest over as a challenge.

The final dashboard you’ll be rebuilding is a major component of a dashboard I recently built.  It was born out of the challenge of showing all 50 states plus DC in a single view.  The size of the states was problematic for showing a metric – as the size of the geography was dwarfing the overall performance of all the states.  And of course Hawaii and Alaska fell victim to not fitting neatly on a map, so our first goal was to create a view that more cleanly displayed all the states.  In addition to the first concept, we wanted to compare performance over a specific time period (it’s one year for the sake of the workout) and quickly identify improvements and performance gaps.

The final visualization you’ll be rebuilding is a tile map (I’ve chosen to go NPR style) that allows for YoY comparison of profit ratio.

Click to view on Tableau Public

 

Requirements

  • Dashboard size: 1200 x 900; 1 sheet
  • Create a tile map showing 2017 vs 2018 profit ratio
    • 2018 = smaller square; 2017 = larger square (Thanks Bryan!)
    • Can use this blog post as inspiration and base file for map from Brittany Fong
  • Build a calculated field that shows the percentage change in profit ratio YoY and place on label
    • Formatting must match (use AZ, IL, and MI as references)
  • Color profit ratio using the scale in the upper right
  • Match all additional labels, tooltips, and formatting that you spot (including the year labels on Florida!)

Lastly – you are not allowed to use Level of Detail (LOD) Expressions!

Dataset

This week you’ll have to use a modified version of Superstore to allow for more variety and all 50 (+1) states from data.world

Attribute

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

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.

2019 Week 9: Can you remove the scroll bar?

When I started Workout Wednesday I made a list of potential challenges. This challenge was the second challenge I wrote down. After more than 20 challenges I’ve finally gotten around to sharing it!

Challenge


Click to view on Tableau Public

Requirements

  • Format: 1000px by 600px
  • Create a visualization with bar charts that show total sales, total profit, and total quantity by city-state (See below for state abbrevation calculation). Sort by sales.
  • Create a calculation that you can use as a filter that shows cities in groups of 10. Make sure the labels based on the rank i.e. the top ten are 1-10, the 2nd 10 are 11-20, ect.
  • Make sure the bar size remain proportional with the maximum value for each. Do not used a fixed axis.
  • Add a zero line for profit only.
  • Match formatting: Size 11 font, bar color #ED7470.
  • Dataset

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


    Abbreviation Calculation

    CASE [State]
    WHEN "Alabama" THEN "AL"
    WHEN "Alaska" THEN "AK"
    WHEN "Arkansas" THEN "AR"
    WHEN "Arizona" THEN "AZ"
    WHEN "California" THEN "CA"
    WHEN "Colorado" THEN "CO"
    WHEN "Connecticut" THEN "CT"
    WHEN  "Delaware" THEN "DE"
    WHEN "District of Columbia" THEN "DC"
    WHEN "Florida" THEN "FL"
    WHEN "Georgia" THEN "GA"
    WHEN "Idaho" THEN "ID"
    WHEN "Illinois" THEN "IL"
    WHEN "Indiana" THEN "IN"
    WHEN "Iowa" THEN "IA"
    WHEN "Kansas" THEN "KS"
    WHEN "Kentucky" THEN "KY"
    WHEN  "Louisiana" THEN "LA"
    WHEN "Maine" THEN "ME"
    WHEN "Maryland" THEN "MD"
    WHEN "Massachusetts" THEN "MA"
    WHEN "Michigan" THEN "MI"
    WHEN "Minnesota" THEN "MN"
    WHEN "Mississippi" THEN "MS"
    WHEN "Missouri" THEN "MO"
    WHEN "Montana" THEN "MT"
    WHEN "Nebraska" THEN "NE"
    WHEN "Nevada" THEN "NV"
    WHEN "New Hampshire"	THEN "NH"
    WHEN "New Jersey" THEN "NJ"
    WHEN "New Mexico" THEN "NM"
    WHEN "New York" THEN "NY"
    WHEN "North Carolina" THEN "NC"	
    WHEN "North Dakota" THEN "ND"
    WHEN "Ohio" THEN "OH"
    WHEN "Oklahoma" THEN "OK"	
    WHEN "Oregon" THEN "OR"
    WHEN "Pennsylvania" THEN "PA"
    WHEN "Rhode Island" THEN "RI"
    WHEN "South Carolina" THEN "SC"
    WHEN "South Dakota" THEN "SD"
    WHEN "Tennessee" THEN "TN"
    WHEN "Texas" THEN "TX"
    WHEN "Utah" THEN "UT"
    WHEN "Vermont" THEN "VT"
    WHEN "Virginia" THEN "VA"
    WHEN "Washington" THEN "WA"
    WHEN "West Virginia" THEN "WV"
    WHEN "Wisconsin" THEN "WI"
    WHEN "Wyoming" THEN "WY"
    END
    

2019: Week 6: Weekly Orders & Sales by Segment

Those of you who know me well know that I like to create visualizations that include both detail and aggregate in the same view.  I have a particular interest in showing how the smaller components contribute to the overall.  When there’s one major chart on a dashboard I think it works out particularly well – keeping high level attention focused on the macro data shapes, but providing exploratory capabilities in the micro.

This week’s workout is also a little more on the arty side.  The original inspiration for this comes from something I built at work, but once I retooled it using Superstore I was struck by how the result looked like trees.  To add to the whimsy, I’ve included tree trunks which contribute to the overall look/feel and provide the macro details.

But don’t be fooled, although this viz looks pretty, it’s packed with some real takeaways that I’m confident you’ll be able to take back and implement at work.  You’ll want to pay close attention to the viz in tooltip.

Click to view on Tableau Public

Requirements

  • Dashboard size: 1300 x 800, 2 sheets – one for the main viz and one for the VIT
  • Create a tree plot that shows individual orders by week
    • Size of order = sales
    • Circles sorted by sales
    • Make trunk that is the # of orders for the week/segment
  • Create a highlight table VIT with a order summary
    • Sales and quantity by subcategory
    • Sales should have more orange = more sales
    • No color for quantity
  • Include a Region filter (to see the beautiful forests change)
  • Match any additional formatting and tooltip language

Dataset

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

Attribute

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

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.

Week 49: Where Do Sub-Categories Succeed?

Last week I had the honor of attending Tapestry Conference in Miami.  While I was there Jon Schwabish gave a quick 6 minute talk that connected every chart to every other chart.  This along with some of Elijah Meek’s keynote mentioning that data viz is getting more custom and funky got me curious about some neglected chart types.  Combine this with a recent interest in how clustering works in Tableau and you’ve arrived at the genesis for this week’s challenge.  Your goal is to create a Parallel Coordinates chart (click the link if you’re not sure what it is).

This chart is perfect for multivariate analysis and seeing relationships among more than 2 measures (in this case 3).  It can also be useful for finding commonalities among things.  Traditionally I think most people may shy away from implementing this in Tableau because quite often different measures have different scales, so as part of the challenge, you’ll have to figure out how to overcome that obstacle to present a parallel coordinate chart with 3 measures of different magnitudes.

Also to help reinforce some recent challenges using table calculations – you are not allowed to use LODs and must only use table calculations and regular calcs.

Click to view on Tableau Public

Requirements

  • Dashboard size: 1200 x 850; you choose # of sheets
  • Create a parallel coordinate chart that shows Sales, Profit Ratio, and # Customers (CountD Customer Name) per sub-category
  • Do not use LODs, use table calculations (and normal calculated fields)
  • Each sub-category should be positioned based on its value, but all measures should be on one sheet
  • Ensure there is a dark gray vertical line for each measure
  • Label the top and bottom of each vertical line with the measure name and respective minimum or maximum
  • Color the lines based on which measure the sub-categories have the highest value in
  • Colors are based off of the Viridis color palette, which I encourage you to paste into your .TPS file
  • Create a color legend that has a hover action based on the newly defined colors (high sales, high profit ratio, high customers)
  • Match formatting & tooltips

Dataset

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

Attribute

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

Share

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

Track your progress

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