/ Data at Work

What is a Pivot Table?

What is a Pivot Table?

Pivot Table is a concept that allows you to summarize and aggregate a set of data by different groups of fields. The resulting table is a summarized table like below:
image-2-6
The feature was first popularized from Lotus Improv, a spreadsheet program. Since then, it has become a very popular concept, present in ubiquitous programs like Excel, to data tools working with structured data.

Why Pivot Table?

  • It helps you to summarize and categorize a large amount of raw data into meaningful insights in a single table (e.g. what are my quarterly sales figures this year, compared to last)?
  • It helps you create tabular insights quickly and concisely without using Excel functions or SQL

A Quick Example
Imagine that you have a Data Table regarding each and every transaction of different customers in different locations and across different times, for an E-Commerce company that contains hundreds or millions of rows of raw data.

You need to answer these 2 questions. How would you do it?

  • The amount of products purchased by each customer
  • The amount of products purchased in a specific date
    Untitled

It would be quite complicated if you want to generate this report with Excel, since you have to set column names, copy column user emails to another sheet, remove duplicates and execute a "SumIf" function to return the total quantity of each customer.

However, with Pivot Tables, it would be much easier, which you can see in the video below:
ezgif-1-82d8b18660
You can simply drag User Email to Row and Order Quantity to Values, just 2 clicks and it will give you a perfect report, with all the Data you need without using any formulas or functions.

Another example would be generating a report regarding Order status through time by gender
cloudpivot.co_cp_sample_2575

How It Works

The concept is simple, you have a raw dataset, and you apply some transformations (called pivoting), to get the resulting pivot table.
The raw dataset would usually be a spreadsheet of data.

Concepts:

  • Values: This usually takes a field that has numerical values that can be used for different types of calculations.
    For example: the total number order.
    ezgif-1-9fb57b8c6d

  • Rows: used to apply a filter to one or more rows that have to be shown in the pivot table.
    For example: if Category is dragged onto this area, then the other output table constructed will have values from the column "Category"
    ezgif-1-4b6e9a9bb1

  • Columns: used to apply a filter to one or more columns that have to be shown in the pivot table.
    For example: if Order Created At is dragged onto this area then the other output table constructed will have values from the column "Order Created At"
    ezgif-1-7c9b6bb4fa

  • Filters: used to apply a filter to an entire table.
    For example: Order Created before 15-09-2017
    ezgif-1-688da9fdc3

  • A closer look at the Pivoting mechanism
    Pivoting-mechanism

How to create Pivot Tables

Take this example on the Cloud Pivot free tool. This example illustrates the transactions of an E-Commerce company which includes data like Product Information, Customer Information, Order Details and so on.

This is an example of a dataset that you need to pviot.
cloudpivot.co_cp_sample_2390

Example 1: How many products has each customer purchased?

cloudpivot.co_cp_sample_2942

You need to define the table rows and values

  1. Add User Email to Rows Area
  2. Add Order Quantity to Values Area
    Just like that, the Pivot table has automatically aggregated the number of Orders (a Count of Order id) that each customer has made:

Example 2: The amount of each type of product that has been purchased through time

In this case, you would have to add Order created at to Column area.
cloudpivot.co_cp_sample_2582



FAQs

Blank Cell in your Pivot Table

createPivot1
As you can see, the Pivot Table contains Blanks and this happens most commonly because the source data just does not contain an entry for that particular Sub-total. In the example above, this could be because the particular product category did not have any orders at Oct 01 2016.

From SQL Queries To Beautiful Charts

Connect to your database and build beautiful charts with Holistics BI

Learn More
Grab Logo

"Holistics is the solution to the increasingly many and complex data requests from the operational teams"


Tang Yee Jie - Senior Data Analyst, Grab

Tang Yee Jie

Senior Data Analyst, Grab