Chapter 2Preparing Reports, Dashboards, Balanced score card and Analysis of Reports using TableauPractical No 2AIM: Preparing Reports, Dashboards, Balanced score card and Analysis of Reports using Tableau.

Tableau: Tableau is a visual analytics platform transforming the way we use data to solve problems – empowering people and organizations to make the most of their data.

Installation Procedure:

Step 1: Install Tableau Desktop using the following url:

https://www.tableau.com/products/desktop/download

Step 2: Register and download for 14 day trial.

Step 3: Fill up the form and download free trial.

1) Find the customer with the highest overall profit. What is his/her profit ratio?

Step 1: Open the superstoreus2015 excel data set (Provided by Tableau Community)

Step 2: Drag Orders sheet to sheet area

Step 3: Go to sheet 1 and add Customer name as rows and profit as column

Step 4: Sort the data by clicking on Profit label on bottom

Step 5: To calculate profit Ratio

Profit Ratio= (Sum([Profit])/Sum([Sales]))

This formula needs to be entered as tooltip or label

Click on Analysis>Create Calculated Field and enter the formula

OUTPUT:

2) Which state has the highest Sales (Sum)? What is the total Sales for that state?

Step 1: Open the superstore excel data set

Step 2: Drag Orders sheet to sheet area

Step 3: Go to sheet 2 and add Sum(Sales) as rows and State or province as column

Step 4: Sort the data by clicking on Sales on bottom.

Step 5: To calculate profit Ratio

Profit Ratio= (Sum([Profit])/Sum([Sales]))

OUTPUT:

3) Which customer segment has both the highest order quantity and average discount rate? What is the order quantity and average discount rate for that state?

Step 1: Open the superstore excel data set

Step 2: Drag Orders sheet to sheet area

Step 3: Go to sheet 2 and add Customer Segments as column

Step 4: Add Measure names

Step 5: Double click on measure names and tick only discount and quantity ordered now

Step 6: Select line from drop down list of marksOUTPUT:

4) Which Product Category has the highest total Sales? Which Product Category has the worst Profit? Name the Product Category and $ amount for each.

a) Bar Chart displaying total Sales for each Product Category

Step 1: Open the superstore excel data set.

Step 2: Drag Orders sheet to sheet area

Step 3: Go to sheet 2 and add Product Category as column and Sum sales as rows.

Step 4: To calculate profit RatioProfit Ratio= (Sum([Profit])/Sum([Sales]))

Step 5: Drag calculation to Marks

OUTPUT:

b) Add a color scale indicating Profit

Step 1: Add profit from Measures to marks

Step 2: Go to sum(Profit) from the marks and click it and click on the color option

c) Each Product Category labeled with total Sales and Each Product Category labeled with Profit

Step 1: Move the cursor to the bar window will appear.

5) Use the same visualization created for question 4. What was the profit on technology (product category) in Boca Raton (city)?

Step 1: Drag City in filter and select city Boca Raton from the list.

Apply a filter for Technology

Step 1: Drag Product Category in filter and select Technology from the list

OUTPUT:

6) Which Product Department has the highest Shipping Costs? Name the Department and cost.

a. Packed bubble chart showing each Product Department as a colored bubble

Step 1: Drag product category to column and sales to rows

Step 2: Select packed bubbles

b. Use Shipping Cost to display the size of each bubble

Step 1: Add shipping cost from measures and take the cursor to the bubble

7) Use the same visualization created for Question #6. What was the shipping cost of Office Supplies for Xerox 1905 in the Home Customer Segment in Cambridge?

Step 1: Filter product category and apply filter as office supplies

Step 2: Apply City filter

Step 3: Apply customer segment as office suppliers and city as Cambridge

OUTPUT:

8) Find the profit ratio of customer in Bonnie potter.

Step 1: Filter product category as technology

Step 2: Filter customer name as Bonnie potter

OUTPUT:

9) Find profit ratio of all customers having city Acton.

Step 1: Filter city as Acton

Step 2: Add sum(sales), City and profit to measures

OUTPUT:

10) Prepare a report showing sub category wise sales.

Step 1: Add Product sub category to rows and add sales

Step 2: Select text option from sales

11) Report showing statewise sales.

Step 1: Open Sample-Superstore Subset excel data set.

Step 2: Drag orders sheet to the sheet area.

Step 3: Drag ‘state from Dimensions to rows and ‘sales’ to the marks column.

12) What is the percent of total sales from the Home Office customer segment in July 2012.

Step 1: Open Sample-Superstore Subset excel data set.

Step 2: Drag orders sheet to the sheet area.

Step 3: Apply Filter ‘Home Office’ on Customer Segment and drag Customer Segment and ‘(Order Date)’ to the Column.

Step 4: Apply table calculation on SUM(Sales) for percent of total sales for the respective customer segment.

Step 5: Drag modified SUM(Sales) to the Row.

Step 6: Apply filter on Year and month.

OUTPUT:

13) Find the top 10 Product Names by Sales within each region. Which product is.Step 1: Drag Region to Column and Product Name to Row.

Step 2: Drag a Sales measure to the text label. So for getting the Top 10 “product name” by sales, we need to add the “Product name” on Filter

Step 3: Right click on Product name and select filters

Step 4: Select by field top 10 and apply

14) Find the Country-Wise Sales.

Step 1: Open County_Sales excel data set.

Step 2: Drag Longitude to Column and Latitude to Row.

Step 3: Drag SUM(Sales) and County to Marks.

Step 4: Use geographical map under Show Me to display the obtained data.

15) Make hierarchy with Sales

Step 1: Drag Longitude to Column and Latitude to Row.

Step 2: Under Dimensions, choose hierarchy. Add Country ->State -> City -> Postal Code under hierarchy.

Step 3: Drag SUM(Sales) and Hierarchy to Marks.

OUTPUT:

16) Find the Product name Xerox 1967 profit ratio.

Step 1: Create a Hierarchy for County, State, City, Post Code.

Step 2: Drag the Hierarchy to Marks.

Step 3: Add filter to Product Name: Xerox 1967

Step 4: Add new calculated field CountryPR with the formula: Sum(Profit)/Sum(Sales)

Step 5: Drag Product Name to Marks.

OUTPUT:

17) Create a dashboard having three reports.

Step 1: Go to dashboard tab on menu and click on new dashboard.

Step 2: Drag all the sheets on dashboard area.

OUTPUT:

18) Using Tableau Desktop connect to the ‘Global Superstore Orders 2016.xlsx’ spreadsheet data source. It consists of the dataset comprising the purchasing transactions of customers buying specific products from stores spread across the globe.

a) Visualize the Total Sales figures per month across all the different years such that the month wise total sales for each year is stacked on top of one another as different colored line graphs. Note down any particular pattern you observe in the month wise rise and fall in total sales across the years? (Hint: Change the order-date aggregation from ‘Year’ to ‘Month’ from the ‘Order Date’ pill’s drop-down options. You may drop Order Date once more on Color in Marks Card for year-wise colored graphs).

Step 1) Drag the sales column from Measures to column field.

Step 2) Drag the Order date to row field and click on arrow symbol and select year.

Step 3) Again Drag the order date to row field and click on arrow and select month.

Step 4) Drag the order date to marks area with color.

OUTPUT:

b) Add the Total Profits to the visualization such that month wise profits appear as a tooltip across all the years line graphs.

Step 1) Drag the Profit column from Measures to column field.

Step 2) Drag the Order date to row field and click on arrow symbol and select year.

Step 3) Again Drag the order date to row field and click on arrow and select month.

OUTPUT:

c) Which year and month has the highest total sales? Which year and month has lowest total sales? What are the corresponding total profit values for them?

Step 1) Drag the sales column from Measures to column field.

Step 2) Drag the Order date to row field and click on arrow symbol and select year.

Step 3) Again Drag the order date to row field and click on arrow and select month.

Step 4) Drag the profit column to marks area.

Step 5) Sort sales in descending order to see highest total sales with profit

Step 6) Sort sales in ascending order to see lowest total sales with profit.

19) Using Tableau Desktop connect to the ‘Global Superstore Orders 2016.xlsx’ spreadsheet data source. It consists of the dataset comprising the purchasing transactions of customers buying specific products from stores spread across the globe. Prepare the following tabular reports

a) A report showing product sub-category wise sales.

Step 1) Drag the sales from measure to columns.

Step 2) Drag the Product Sub-category to rows.

b) A report showing region-wise and product sub-category wise sales (region as row and sub-category as column).

Step 1) Drag the product sub category to column and region to Row and sales to marks area.