CREATING CHARTS USING TABLEAU

 Tableau is a catalyst to help employees and employers to have that insightful analysis. You may call it the ‘aha data moment.’ The sample charts here will guide those who are interested to learn Tableau B.I. tool and make him/her turn data into deeper understanding was what needed for success in a digital world.

The data used in this lecture be downloaded in my github: alcadelina/globalsuperstore: free data for Tableau user (github.com)

THE LINE GRAPH

The Line Graph is the standard way of showing trends over time. Line graphs are best used in continuous data.

Step 1. Drag Sales to the Rows Shelf

Step 2. Right Click and drag Order Date to the Columns Shelf.

Step 3. Select the Green, continuous month (Order Date)

Step 4.  Click the Label Card and check Show Mark Labels.

Step 5. Under Marks to Label, select Line Ends.

Step 6. Format the Marks as desired.

 

THE SLOPE CHART

The Slope Chart is best used to display relative change over two time periods and should only be used if key pieces of the story are not left out.

Step 1. Drag Profit to the Rows Shelf.

Step 2. Right Click and drag Order Date to the Columns Shelf.

Step 3. Select the Blue, Discrete Year (Order Date)

Step 4. Drag Segment to the Color Card

Step 5. Click the Year (Order Date) Drop Down and set the Filter to 2020 and 2021.

Step 6. Click the Label Card and Check Show Mark Labels.

Step 7.  Under Marks to Label, select Line Ends.

Step 8. Format the Marks as desired.

  

THE SPARK LINE

The Spark Line can be a great way to show trends of multiple values.

Step 1. Drag Profit to the Rows Shelf.

Step 2. Drag Region to Rows Shelf and place to the left of Sum (Profit).

Step 3. Drag Profit to the Row Shelf and place to the right of Sum (Profit).

Step 4. Right Click and Drag Order Date to Columns Shelf.

Step 5. Select the Green Continuous Month (Order Date).

Step 6. Click the Profit Drop Down, create a Dual Axis and Synchronize Axis.

Step 7.  Change the Profit Mark Type to Circle and Size, Color the Mark as desired.

Step 8. Remove Gridlines, Axes and Headers.

 

 

THE SCATTER PLOT

The Scatter Plot is the standard way of showing the relationship between two continuous variables each of which has its own axis.

Step 1. Drag Sales to Column Shelf.

Step 2. Drag Profit to Rows Shelf.

Step 3. Drag Sub-Category to the Detail Card.

Step 4. Change the Mark type to Circle.

Step 5. Drag Scatter color to the Color Card, set to compute using Sub-Category.

Step 6. Set the Circle Colors as desired.

Step 7. Ctrl + Click and drag Sum (Profit) to the right of itself.

Step 8. Create a dual Axis and Synchronize.

Step 9. Set the Opacity on the Sum (Profit) Marks Card to 25%.

Step 10. Change the Sum (Profit) (2) card to Shape, Unfilled Circle.

 

 

THE HEAT MAP

The Heat Map can be an effective way of showing the relationships between two variables, but it does not show the precise differences in values.

Step 1. Drag State to the Rows Shelf.

Step 2. Drag Order Date (Months) to the Columns Shelf.

Step 3. Drag Region to the Filters Shelf and set the filter to Central.

Step 4. Drag Profit Ratio to the Color Card.

Step 5. Edit the colors appropriately.

  THE DIVERGING BAR

The Diverging Bar is a standard bar that can display both negative and positive values.

Step 1. Drag Profit to the Columns Shelf.

Step 2. Drag Sub-Category to the Rows Shelf.

Step 3. Sort the Sub-Categories in Descending Order.

Step 4. Drag Regions to the Filters shelf and select the Southeast Asia.

Step 5. Drag Category to the Filters shelf and select Office Supplies.

Step 6. Drag Profit Color to the Color card.

Step 7. Set the Profit colors as desired.

Step 8. Set the x-axis to start at -8,500 and end at 8,500.

 

 THE HISTOGRAM

The Histogram is the standard way of showing a statistical distribution.

Step 1. Drag Quantity to the Rows Shelf.

Step 2. From Show Me select Histogram.

Step 3. Drag Quantity (Bin) to the Columns Shelf.

Step 4. Ensure the Mark type is a Bar.

Step 5. Click the Label card and select Show Mark labels.

Step 6. Set Marks to Label to Min/Max.

Step 7. Change the font to Tableau Medium 9pt and select match mark color.

  

THE UNIT HISTOGRAM

The Unit Histogram display distributions of individual data points as opposed to the data points being bucketed into bins like a traditional histogram.

Step 1. Drag Order ID to rows Shelf.

Step 2. Select CNTD (order ID).

Step 3. Drag Order date to the Columns Shelf.

Step 4. Select the Green, Day (Order Date).

Step 5. Click the Day (Order Date) drop down and select edit filter.

Step 6. Set a range Filter.

Step 7. Change the Mark Type to a Circle.

Step 8. Drag Order ID to the Detail Card.

Step 9. From Analysis, select Stack Marks , ON.

Step 10. Drag Profit Ratio to color card and set the colors as desired.

 

 THE DOT PLOT

The Dot Plot is an effective way of showing the Range or change in Data across several categories.

Step 1. Drag Profit to the Columns Shelf.

Step 2. Drag Region to the Rows Shelf.

Step 3. Change the Mark Type to Circle.

Step 4. Drag Category to the Color card

Step 5. Set the Color as desired.

Step 6. Ctrl + Click and Drag a copy of the Sum (Profit) pill to the right of itself on the columns shelf.

Step 7. Create a dual, synchronized axis.

Step 8. Change the Sum (Profit) (2) Mark Type to line and change the category pill from color to

             path.

Step 9. From the columns shelf, drag the left Sum (Profit) pill to the right of the second Sum

            (profit) pill.

THE JITTER PLOT

The Jitter Plot is a great alternative to the Dot Strip as it allows the Dots to be separated as opposed to plotted on top of one another.

Step 1. Drag sales to the Rows Shelf.

Step 2. Drag Sub-Category to Columns.

Step 3. Drag Category to the Filters Shelf and set the Filter to furniture.

Step 4. Change the Mark Type to Circle.

Step 5. S Drag Order ID to the Detail Card.

Step 6. Create Calculated filed name it as Jitter. Type RANDOM () – which you will see pooping up on the bottom left. Drag Jitter to the Column Shelf, to the right of sub-category. You can see it changes the whole view but you will need to make sure that you change the new calc to a Dimension rather than a Measure by clicking on the bullet and changing it

Step 7. Right click the X-Axis and change the Range to Independent.

Step 8. As an optional step, add AVG(Sales) reference line to the view via Analytics Pane.

 

THE WATER FALL

The Water Fall can be an effective way of showing how positive and negative values affect an overall value.

Step 1. Drag Profit to rows shelf and from the drop down, select quick table calculation, running total.

Step 2. Drag sub-category to columns.

Step 3. Change the Mark Type to Gannt Bar.

Step 4. Drag Profit to the size Card.

Step 5. Double click in the Sum (Profit) pill on the Size card, change to - Sum (Profit)

Step 6. Drag Profit Color (Waterfall) to the Color Card and set the Colors

Step 7. Drag Category to the Filters and set to Office Supplies

Step 8. Drag region to Filters Shelf and set to East

Step 9. From Analysis, select Totals, show Row Grand Totals, add a Label.

 

 

THE BAR CHART

The Bar Chart is the most effective way to visualize categorical data.

 

 Step 1. Drag Sales to Column Shelf.

Step 2. Drag Region to Rows Shelf.

Step 3. Ensure the Mark Type is set to Bar

Step 4. Click the label card and check Show Mark labels

Step 5. Change the font to Tableau Medium 9pt and select Match Mark Color

Step 6. Drag Order date to Filter shelf

Step 7. Select Years and select filter to the most recent year in your data set.

  

THE TREE MAP

The Tree Map is a hierarchical visualization that allows for comparison of dimensions across one or two measures. Measures can be used to represent size and/or color.

 Step 1. Drag Profit to the Color card.

Step 2. Drag Sales to the Size card.

Step 3. Drag Category to the detail card.

Step 4. Drag sub-category to the label card.

Step 5. From the Marks Card, drag Sub-category below Category

Step 6. Click on the Color Card and set the Colors as follows:

-          Palette: Custom Diverging

-          Stepped Color: 4

-          End: 35,000

-          Center: 0

 

THE STACKED BAR

The Stacked bar can be a nice way of showing part-to-whole relationships, but can become difficult to read when there are more than 3 dimensions.

 

 Step 1. Drag Sales to the Columns Shelf

Step 2.  Drag region to the Rows Shelf.

Step 3. Drag Category to the Color Card

Step 4. From the Sum(Sales) drop-down select Quick table Calculation, percent of total

Step 5. Click the Sum(Sales) drop-down again and select compute using, Category

 

 THE BUMP CHART

The Bump Chart can be used to display change in ranking over time.

 Step 1. Drag Sales to the Rows Shelf and select quick table Calculation, Rank.

Step 2. Drag Order Date to the Columns Shelf

Step 3. Drag Region to the Detail Card

Step 4. Click the Sum (Sales) pill drop down and select compute using, Region.

Step 5. Ctrl + Click and Drag a copy of the Sum (Sales) pill to the right of itself on the Rows Shelf.

Step 6. Create a Dual, Synchronized Axis and change the new mark type to a Line.

Step 7. Reverse the Y-Axis

Step 8. On the Circle Marks Card, select Show Mark Labels and Center in the Circles.

Step 9. On the Circle Marks Card, move the Region Pill from Detail to Color.

THE LOLLIPOP CHART

The Lollipop Chart is an alternative to the traditional bar chart, is useful when wanting to draw attention to the value.

 Step 1. Drag Sales to the Column Shelf.

Step 2. Drag Region to Rows Shelf.

Step 3. Set the Mark Type to Bar.

Step 4. Ctrl + Click and drag a copy of the Sum (Sales) pill to the right of itself on the Rows shelf.

Step 5. Create a dual, synchronized axis and change the new mark type to circle.

Step 6. Size the Bars and Circles Marks so the chart resembles a lollipop

Step 7. From the Circle Marks Card, click Label and select Show Mark Labels.

Step 8. Format the Labels to fit inside the Circles, Centered, formatted as follows $###K

 

 THE FILLED/SYMBOL MAP

The Filled/Symbol Map are best used when precise location of data is important. Symbol maps can be layered on top of filled maps to create another level of detail.

Step 1. Double click Longitude (Generated) and Latitude (Generated)

Step 2. Drag State to the Detail Card.

Step 3. Drag Region (Copy) to the Color Card and set the opacity to 25%

Step 4. From the Map Menu, select Map Layers and select Dark and the washout to 55%

Step 5. Ctrl + Click and drag a copy of the Longitude (Generated)Pill to the right of itself on the Columns Shelf.

Step 6. The original Longitude (Generated)Pill change the Mark type into map. Change the New Mark Type to Circle, set the opacity to 75%, remove Region (Copy) from Color and replace state on detail with City.

Step 7. Drag sales to the Size Card and size the Color as desired.

 

QUICK DATA ANALYSIS USING EXCEL

 

EXCEL- QUICK DATA ANALYSIS

In Microsoft Excel, it is possible to do data analysis with quick steps. Further, different analysis features are readily available. This is through the Quick Analysis tool.

Quick Analysis Features provides the following analysis features for instant data analysis.

Formatting

Formatting allows you to highlight the parts of your data by adding things like data bars and colors. This lets you quickly see high and low values, among other things.

Charts

Charts are used to depict the data pictorially. There are several types of charts to suit different types of data.

Totals 

Totals can be used to calculate the numbers in columns and rows. You have functions such as Sum, Average, Count, etc. which can be used. 

Tables

Tables help you to filter, sort and summarize your data. The Table and PivotTable are a couple of examples.

Sparklines

Sparklines are like tiny charts that you can show alongside your data in the cells. They provide a quick way to see the trends.

Quick Analysis of Data

Follow the steps given below for quickly analyzing the data.  

Step 1: Select the cells that contain the data you want to analyze.

The colored Data Bars that match the values of the data appear.

Step 3: Click on Color Scale.

The cells will be colored to the relative values as per the data they contain.

Step 4: Click on the Icon Set. The icons assigned to the cell values will be displayed.

Step 5: Click on the option-Greater than.

Values greater than a value set by Excel will be colored. You can set your own value in the Dialog Box that appears.

Step 6: Click on Top 10%.

Values that are in top 10% will be colored.

Step 2: Move over the charts recommended. You can see the Previews of the Charts. 

Step 3: Click on More as shown in the image given below.

More Recommended Charts are displayed.

Step 2: Click on %Total. This option is to compute the percent of the row that represents the total sum of the data values selected.

Tables

Tables help you sort, filter and summarize the data. 

 

EXCEL: Calculating Customer Churn and Average Customer Lifetime Value

 Customer churn is the measure of how many customers you lose in a given period. It’s an important metric in subscription-based businesses, although it’s applicable to other revenue models as well. If your growth rate (the rate at which you are adding new customers) is higher than your churn rate, then your customer base is growing. If not, you’re losing customers faster than you can add them, and something needs to change.

Figure below shows a churn calculation for a company with recurring monthly revenue. You need to know the number of customers at the beginning and end of the month and the number of new customers in that month.

Subscribers Lost

=C2+C3-C4

Churn Rate

=C6/C2

To determine the number of customers lost during the month, the number of new customers is added to the number of customers at the beginning of the month. Next, the number of customers at the end of the month is subtracted from that total. Finally, the number of customers lost during the month is divided by the number of customers at the beginning of the month to get the churn rate.

In this example, the business has a churn rate of 9.21%. It is adding more customers than it is losing, so that churn rate may not be seen as a problem. However, if the churn rate is higher than expected, the company may want to investigate why it’s losing customers and change its pricing, product features, or some other aspect of its business.

EXCEL : Calculating annual churn rate

If a business has monthly recurring revenue, it means that customers sign up and pay for one month at a time. For those companies, it makes sense to calculate the churn rate on a monthly basis. Any new customers during the month will not churn in the same month because they’ve already paid for the month.

A typical magazine, however, signs up subscribers for an annual subscription. A meaningful churn rate calculation for them would be an annual churn rate. If a business wants to calculate a churn rate for a longer period than its recurring revenue model, such as calculating an annual churn for a business with monthly subscribers, the formula changes slightly.

Figure below shows an annual churn rate calculation.

Annual churn rate: =C6/AVERAGE(C2,C4)

The number of lost subscribers is divided by the average of beginning and ending subscribers. Because the period of the churn rate is different than the period of the recurring revenue, some of those 614 new subscribers canceled their subscriptions within the year, albeit in a later month than they first subscribed.

EXCEL: Calculating average customer lifetime value

Customer lifetime value (CLV) is a calculation that estimates the gross margin contributed by

one customer over that customer’s life. The churn rate calculated in Figure 15.7 is a component

of CLV.

Figure 15.9 shows a calculation of CLV using the churn rate previously calculated. The first

step is to calculate the average gross margin per customer.

Gross Margin

=F2-F3

Average Customer Margin

=F4/AVERAGE(C2,C4)

Customer Lifetime Value

=F6/C7

To calculate CLV, follow these steps:

1. Calculate the gross margin.

2. Calculate the average customer margin by dividing the gross margin by the

average number of customers for the month. Because the gross margin was

earned over the month, you have to divide by the average number of customers

instead of either the beginning or ending customer count.

3. Calculate the CLV by dividing the average customer margin by the churn rate.

In this example, each customer will contribute an estimated P828.97 over their lifetime.

EXCEL: Calculating ROA and ROE

 Calculating return on assets

Return on assets (ROA) is a measure of how efficiently a business is using its assets to generate income. For example, a company with a higher ROA can generate the same profit as one with a lower ROA using fewer or cheaper assets.

Step 1. 

Step 2.

Step 3.

Step 4.

Step 5.

Step 6

Step 7.

Step 8. 

Step 9. 

Step 10. 

To compute ROA, divide the profits for a period of time by the average of the beginning and ending total assets. Figure 15.5 shows a simple balance sheet and income statement and the resulting ROA.

=G15/AVERAGE(C12:D12)

The numerator is simply the net profit from the income statement. The denominator uses the AVERAGE function to find the average total assets for the period.

Calculating return on equity

Another common profitability measure is return on equity (ROE). An investor may use ROE

to determine whether their investment in the business is being put to good use. Like ROA,

ROE divides net profit by the average of a balance sheet item over the same period. ROE,

however, uses average total equity rather than average total assets. 

The formula to calculate

ROE from Figure 15.5 is as follows:

=G15/AVERAGE(C25:D25)

Now you know how to compute ROA and ROE using Excel.