Pivot Table – The Cornerstone of Data Analysis

As you might have noticed, we often refer to Flexmonster as a pivot table. But what does that term exactly mean? How do pivot tables function and what can you actually do with them? Let us get back to the basics of pivot tables and find out.

Pivot table is a data processing tool, a two-dimensional table used to summarize, explore, and present large amounts of data. Pivot tables have been a crucial part of data analysis for the past 20+ years, and it is now almost impossible to imagine real Business Intelligence or Business Analytics cases where this tool is not used. Historically, pivot tables are closely related to Excel – a spreadsheet developed by Microsoft. Back in 1994 Microsoft brought a new functionality to Excel, called a ‘PivotTable’. This term is now trademarked in the United States, although the pivot table itself is a generic term. Today, pivot tables are acknowledged as one of the most powerful tools in Excel.

MECHANICS AND TYPICAL USAGE

One of the key characteristics of a pivot table is its dynamic nature. Pivot tables interact with data in such a way that allows the end user to quickly change what is being displayed in the report.

With just a few mouse clicks, you can completely reorganize your report in a new meaningful way, focusing on some parts of the data and ignoring the others.

Such changes to the table can be viewed as rotational, or pivotal, which is exactly what gives a pivot table its naming. Essentially, pivot tables keep some sort of the database at the backstage, allowing you to easily query, manipulate, and effectively present large amounts of data.

The usage of a pivot table is extremely broad. This can be a financial analyst studying latest investment trends; a sales manager preparing a quarterly report on their team’s performance; a marketing expert analyzing a data set in preparation for a new marketing campaign; or a member of a non-governmental organization collecting medication statistics. In other words, pivot tables are used widely in practically all areas where some sort of data analysis is needed.

WHAT DO I WANT TO DISPLAY? – THAT IS THE QUESTION

We have briefly touched some basics of data manipulation with a pivot table, now let us explore some of the practical things you can do in more detail. For a better illustration, let’s take an example where you work for an FCMG company and found yourself in a need to analyze a raw dataset related to the sales of your goods. This simple dataset includes information on a number of products and their sales stats over three weekdays in two local shops:

In general, data is usually stored in non-aggregated form. One example of that is a flat table, which consists of row and columns, as in the snippet of our dataset above. Flat tables may have a very large number of rows and columns, often making it very difficult to summarize your data. But this is exactly the area where pivot tables shine. With their help, you can quickly derive the needed summarized information from your data and present it a nice and simple way. To do that, you first need to ask yourself what you want to display. Answering this important question will help you understand what manipulations you need to perform, and your pivot table will do the rest.

THE FEATURES

Below are some of the most important features found in pivot tables, which will help you explore your data and get answers to the questions you ask.

1) Filtering
Filtering is the basic feature used to quickly highlight the pieces of data you want to draw attention to. Let us build a simple pivot table and imagine that you only want to show sales numbers related some of your products. In this case, we simply apply a filter to the corresponding column (‘Item’) and select only desired items (the 'filter' symbol next to the gear in the column header indicates that filtering has been applied):

2) Sorting
Another elementary feature of a pivot table is an ability to sort your values in a certain order. In this example, we apply a descending sorting order to ‘Sold Units’ column, which immediately shows which products sold better than the others (notice an arrow facing down in the column header):

3) Crosstab
If you’d like to dig deeper into your data and compare how the products performed in two local shops, you can use crosstab. Crosstab (or cross tabulation) is used to aggregate and simultaneously display the distribution of multiple variables by tabulating their results one against the other. By simply placing the ‘Shop’ dimension into the columns, we can immediately see that Shop #1 performed worse in terms of total number of units sold, but performed particularly well in selling Mineral Water:

4) Drill-through
In the image above, you can see that Shop #1 sold 39 units of Mineral Water in the given period of time. But what if you want to see the data behind that summarized number? This is exactly when the drill-through feature comes in handy. Usually accessed by double-clicking a certain cell, it will display you the full set of values that back up that aggregated number, in our case the raw sales scores for the three days of the week:

5) Aggregations
In all previous examples we analyzed the total number of units sold by summing up their values. But in some cases, you would want to use other aggregations to analyze the same measure. For example, let’s see how many units of each product were sold on average across the three weekdays. We use the very same measure – ‘Sold Units’ – but instead of using ‘Sum’ aggregation, we now use ‘Average’ aggregation. This brings some new information to the report:

6) Calculated measures
In the given dataset, we do not have a specific information indicating what revenue our sales generated. However, we do have ‘Units Sold’ and ‘Price’ measures for each of our items. Calculated measures (or calculated values) feature allows us to multiply ‘Units Sold’ and ‘Price’ and present the results as the new measure – we’ll call it ‘Revenue’. This now brings some new information to the table:

As you can see, with just a few mouse clicks we were able to summarize our data in various meaningful ways. But in doing so, we have merely scratched the surface of a pivot table’s capabilities. There are lots of other possible use cases and sophisticated features, so take your time to explore and experiment.

PIVOT TABLES – AT YOUR SERVICE ON THE WEB

Probably, the most powerful implementation of a pivot table concept can be found in Excel. However, due to the nature of modern-day technology more and more specialists find themselves using web-based data analysis tools, for example as an integral part of BI platforms. As opposed to desktop programs, online applications are more flexible, are much easier to access, offer better scalability, and typically are more affordable. It is therefore no surprise that pivot tables have found their way to web applications as well.

Flexmonster is an example of such application – it is actually the most powerful JavaScript pivot table for web reporting. It supports native integration with a wide range of data sources, including SQL and NoSQL databases (i.e. MongoDB, BigQuery), CSV and JSON files, Elasticsearch, SQL Analysis Services and other OLAP cubes. It is extremely flexible and easy to integrate with practically any tech stack. You can download the fully-functional 30-day trial of Flexmonster and see yourself how it fits your needs: flexmonster.com/download-page.