Qvc data analytics challenge

Tableau Homework 3 – Exploring Chart Types with QVC Data

Getting familiar with the data

You will focus on five dimensions as you start to explore the QVC data: • order date (Order Dt) • merchandise department (Merchandise Dept) • region of the country (Region) • customer state (Ship To State) • location of the originating warehouse (Warehouse Zip).

You will use five measures:

• price (Total Line Amt) • number of orders (Number of Records) • average order value (AOV to be calculated) • delivery time ([Days Shipped] to be calculated)

Create two calculated fields:

AOV = SUM([Total Line Amt])/SUM([Number of Records]). On the Data Pane, change the number format to Currency with 2 decimal places.

Days Shipped = CEILING( [Delivery Confirmation Dt]-[Shipped Dt])

In the next homework, we will explore additional measures to address the QVC analytics challenge more explicitly. In this homework, the primary goal is to continue to build basic Tableau skills for creating tables, maps, and charts.

Change the label in the Region dimension for Alaska and Hawaii:

Alaska and Hawaii were not assigned a region in the input data, but we are going to change the Null label to AK/HI. Depending on the context, we may filter out these states.

To change the label, go to the blue pill for Region and right-click (or click on the down arrow) to get the menu of actions. Select Aliases…. In the pop-up box, change the alias for Null to AK/HI.

For the rest of the course, you are expected to have complete titles on every worksheet you complete. I will guide you through this process for the first few worksheets.

Chapter 19 – Highlight Tables

1. Create a text table with merchandise departments for rows and the sum of sales (Total Line Amt) in the table. Sort in descending order by sales.

Add Region to the Columns Shelf. You should have a crosstab table with 5 columns and 11 rows of data. Drag the Total Line Amt to the Color marks card. Change the mark type to Square. Note that the East region has highest sales overall and the ordering within region is similar. Name the sheet Highlight Table.

Edit the title (double-click on the Highlight Table text and type over <Sheet Name>) to be something like ‘Total sales by region and merchandise department’.

Chapter 22 – Scatter Plot

2. Open a new worksheet. Create a scatter plot of average Days Shipped (Columns Shelf) and average order value AOV (Row Shelf). Make sure you change the default SUM aggregate function to AVG for Days Shipped. Drag Merchandise Dept to the Detail marks card. Drag Total Line Amt to the Size marks card.

At this point, you will want to change the axis settings so they do not include 0. Right-click on each axis, select Edit Axis, and uncheck the Include zero box.

Add an average line for each measure. This plot highlights that the jewelry department has high average ship times, though is a small revenue department. We will continue to investigate what types of orders have long delivery times. Electronics and IQVC Divisional have high average order values. IQVC Divisional is the online store.

Name this sheet Scatter Plot and edit the title to be something like ‘Average order value (AOV) by average days shipped for all merchandise departments’.

Chapter 23 – Tree Map

3. Open a new worksheet. Create a tree map with size = average of Days Shipped, color = total sales (sum of Total Line Amt), and two text labels -- Ship to State and average of Days Shipped. Change the format of the displayed text for the average of Days Shipped so there is only one decimal place. In the scatter plot, the overall average days shipped was around 3.8. This tree map suggests there is large variation by state, from a low of 2.5 days for Washington DC to a high of 6 days for Idaho. Notice that California (CA) stands out as the state with the highest total sales, but also has a relatively high average shipping time of 4.7 days.

Name this sheet Tree Map Delivery Days and add a descriptive title.

Chapter 28 – Histogram

4. Open a new worksheet. Create a histogram of Days Shipped. Change the size of the bins to 2. While most orders are delivered in less than 6 or 8 days, the histogram axis goes up to 32 because there are a small number of orders with very large shipping times. A histogram is not a good chart to visualize outliers. We will use boxplots to explore some of our measures with skewed distributions.

Name this sheet Histogram and add a descriptive title.

Chapter 29 – Box Plot

5. Create a boxplot that shows the price (Total Line Amt) by merchandise department for each order (data fully disaggregated). I’m going to change a few steps from the book:

• Create a vertical bar chart with Merchandise Dept on the Columns Shelf and SUM(Total Line Amt) on the Rows Shelf. I like to sort the data in descending order by total sales, largest to smallest).

• Go to Analysis and uncheck Aggregate Measures (second row in the drop-down menu.)

You should now have a bunch of circles instead of bars. Each circle represents a single price record from the list of orders. You can tell from this visualization that most orders are below $1,000. There are several larger orders in a few different departments.

• Click on the box-and-whisker icon on the Show Me menu. • Name your sheet Price Boxplots and add a descriptive title.

Chapters 30 – 32 Maps

6. Make a symbol map that shows the total sales by ZIP code (detail =Ship To Zip). Drag Total Line Amt to the Size marks card. Use the Region dimension to filter out AK/HI. Click on the Color marks card to change the format of the circles. Add a grey border and reduce the opacity. Drag Region to the Color marks card. Change the size of the circles to get a display that you like. Name the worksheet Symbol Map and add a descriptive title.

7. Create a filled map that shows the average delivery days (use the AVG aggregate function) by state (Ship To State). Use the Region dimension to filter out AK/HI. Edit the colors to use Stepped Color with 4 steps. (Click on the Color card, select Edit Colors…, and select Stepped Color / 4 steps as shown below.

Note that there is a clear increase in average delivery days from east to west with a secondary pattern of longer shipping times for more rural states. Name this sheet Delivery Days Map and add a descriptive title.

8. Follow the Warehouse Mapping Tutorial posted in Moodle and create a dual-axis map to show warehouse locations and states where orders were delivered. Name this sheet State Map and add a descriptive title that includes both sets of data displayed on the map. For example, ‘Number of orders shipped by warehouse location and destination’.

Chapter 9 – Line Chart with Weekly Data

9. To take a deeper dive into variations in delivery times by region of the county, we are going to create a line chart of weekly average delivery times by region. Drag Days Shipped to the Rows shelf and change the aggregate function to Average (AVG). Drag Order Dt to the Columns self. Select the continuous Week Number for the order date. Drag region to color and filter out AK/HI region. This chart verifies that the East region generally has the shortest delivery times, followed by the South and then Central and finally the West.

Label the lines directly with the region name by dragging Region to the Label marks card. Change the font size to 12 and hide the Region legend.

Name this sheet Weekly Delivery Days and add a descriptive title.

Chapter 38 Dual-Axis Slope Graphs and Chapter 15 Sets

10. You are going to create a slope graph that compares delivery days by merchandise department for the East and West regions. Here is some additional information to help you follow the steps in the book:

• Create a bar chart with AVG([Days Shipped]) on the Rows Shelf and Region on the Columns Shelf.

• Filter Region to include only East and West. • Add merchandise department to the Details marks card and change the mark type to Line. • Use a filter to keep the departments with the most sales revenue: Home Décor, Health/Beauty,

Apparel, Accessories, Electronics, Housewares, Jewelry.

• Drag Merchandise Dept to the Label marks card. Click on the Label card to format the labels. Under Options, check the box to allow labels to overlap other marks. Click on Line Ends and select Label start of line. Make the chart wider so you can see the lines and labels. Note that Jewelry has similar delivery times in both regions, but all other departments have longer delivery times in the west.

• Work on the format so this could be a final candidate for a dashboard element:

o Add a reference line equal to the Average Days Shipped. Format the Reference Line Label so it is right-justified

o Edit the Days Shipped axis to exclude zero. o Add markers to the line ends. Click on the Color marks card

and select the middle option for Markers. o Create a set that has Jewelry as the In member. Edit the

colors and assign Orange to In and Blue to out (or select your own preference for the palette.)

Save the worksheet as Single Slope Graph and change the title to ‘Average shipment days by region and merchandise department’. Duplicate the sheet.

11. Open the duplicated sheet from the prior problem and name it Small Multiples. Remove the merchandise department labels from the chart on the Small Multiples worksheet. Drag a pill for Merchandise Dept and place it to the left of the Region pill on the Columns Shelf. This layout eliminates the problem of overlapping labels. Enhance the visualization with these formatting changes:

• Challenge: Add labels that show the increase in delivery days as labels for the right end points. HINT: Add Days Shipped to the Label card. Change the function to AVG. Use Quick Table Calculation and select Difference. Format the labels to be Number (Custom) with 1 decimal place. Move the labels to the end of the line. Edit the label text to read + <Difference in AVG(Days Shipped)> Day(s).

• Manually sort the on Merchandise Dept to go from the largest to smallest difference. HINT: Right-click the Merchandise Dept pill on the Columns shelf. Change the Sort By option to Manual sort. Arrange in this order: Apparel, Accessories, Housewares, Home Décor, Health/Beauty, Electronics, Jewelry. See the example on the next page.

• Add a descriptive title.

Chapter 17 Introduction to Dashboards

12. Create a simple dashboard similar to the example in Practical Tableau. Use three worksheets from this homework assignment. If you want to change formats in your worksheets, create a duplicate copy and experiment with colors and fonts. In the dashboard, select Fixed Size Desktop Browser (1000 x 800). Add a title to your dashboard. I’ve included a sample on the next page after I changed the color scheme in the line chart from problem 9. This does not need to be fancy – just get a sense of the layout features.

Tableau Homework 3 – Exploring Chart Types with QVC Data