DAO1704X & DSC1007X
Week 7
Data Intelligence
1
Learning Objectives
• Master basic Excel skills, in particular PivotTable
• Perform descriptive analytics
• Develop data visualization
2
Basic Analytics Steps
Model or
Data Descriptive Data
Data Input Processing Statistics Visualization
Report Output Presentation
Generation
3
Every analytics project starts with
questions
• Data on Elecmart Sales
• Q1. How are sales transactions distributed among regions?
• Q2. On average, which gender spends more, male or
female?
• Q3. What is the distribution of total cost?
• Q4. If Elecmart wants to conduct a promotion, which day in
a week is the best and where?
4
Understanding of your data set
Categorical Variable
• Also known as Qualitative Variable
• Example: Gender, Occupation
Numerical Variable
• Also known as Quantitative Variable
• Example: Salary
5
Excel Function
• There are functions in Excel to count data based on
condition(s)
• countifs
6
Answering Q1.
•How are sales transactions distributed among
regions?
• Count the number of transactions by regions
7
Describing Categorical Data
• Example: Elecmart Sales
70
Male
60
50
40
Male MidWest
30 Female NorthEast
West
20 South
10
0
MidWest NorthEast West South
8
Practice
• Please explore which time slot has the largest
number of transactions?
9
Describing Numerical Data
• Example: ElecMart Sales Column1
• Summary Statistics Mean
Standard Error
153.818
4.868652
Median 126.72
Mode 113.95
Standard Deviation 97.37304
Sample Variance 9481.508
Kurtosis 0.774828
Skewness 1.093076
Range 478.19
Minimum 6.82
Maximum 485.01
Sum 61527.21
Count 400
10
Numerical vs Categorical
• On average, which gender spends more, male
or female?
• Split data into different small partitions based on
condition(s)
11
Excel Function
• There are functions in Excel to calculate the average of
numerical variables based on condition(s)
• averageifs
12
Answering Q2.
• On average, which gender spends more, male or
female?
• Find out the gender-specific average spending (total cost)
• Find out region variability in average spending
13
Multi-Objective Visualization
• Combo Chart
14
Answering Q3
•What is the distribution of total cost?
•Histogram to show the basic shape
15
Finding Relationship
• Explore a possible relationship between two
numerical variables
The number of items in a transaction versus average
spending per item in a transaction
16
Pivot Table
• An interactive worksheet table that
• Provides a powerful tool for summarizing large amount of
data;
• Calculates totals, averages, counts, percentages, etc. based
on any numeric fields in your table;
• Generates charts on summarized information
17
Flexible
Visible
Pivot Interactive
Table
Dynamic
18
Data Preparation
• Data should be arranged in a list
• Columns represent fields
• Rows represent a record of related data
• First row = column label
• Remove subtotals
• Convert list into Excel table
19
Fundamentals of Pivot Table
Construct Pivot Table
Arrange Data
Calculate Data
Format the table
Construct Pivot Chart
20
Fundamentals of Pivot Table
Construct Pivot Table
Arrange Data
Calculate Data
Format the table
Construct Pivot Chart
21
Arranging Data
• Arrange rows and columns based on your business
questions
• Filter data if necessary
• Sorting
22
Construct a Pivot table
• Place mouse cursor inside the region of data.
• Choose “Insert | Pivot Table” from the menu bar.
23
Data fields for selection
Fields to be placed in columns
Filtering factor
Fields to be
placed in rows Values to be summarised
24
Practice
• Organize ElecMart data by region and gender,
filtered by card type.
• Build Pivot Chart to visualise the data.
25
Fundamentals of Pivot Table
Construct Pivot Table
Arrange Data
Calculate Data
Format the table
Construct Pivot Chart
26
Calculate Data
• Change field settings
• Multiple calculation
27
Change field setting
Right click on the field Select your desired field
28
Multiple Calculations
You can add multiple
calculations of the same
field in pivot table
29
Practice
• Please explore which time slot has the largest
spending
• Consider time and region at the same time
30
Summarize by count
31
Answering Q4
•If Elecmart wants to conduct a promotion, which
day in a week is the best and where?
• Using Pivot table
32
Fundamentals of Pivot Table
Construct Pivot Table
Arrange Data
Calculate Data
Format the table
Construct Pivot Chart
33
Formatting Table
• Display data in various forms
• Grouping Data
34
Show value as
35
Grouping
• Select both Date and Total Cost in the pivot table.
• Right click on any date data and select “Group”.
• Select group by “Month”.
36
Grouping
37
Grouping by value
• Select “TotalCost” and “HighItem” in pivot table.
• Right click on any highitem data. Select “Group”
• Specify the range of value you want to summarize.
38
Grouping by value
39
Fundamentals of Pivot Table
Construct Pivot Table
Arrange Data
Calculate Data
Format the table
Construct Pivot Chart
40
Pivot Chart
41
Take-home Message
• Excel is powerful to do the basic descriptive analytics
• Pivot table is a quick way to summarize your data and
draw visualization
• If possible, using charts is a more effective way to
convey information.
42
