A pivot table is a fundamental technique when it comes to data processing and visualization. Undoubtedly, it’s a must-have tool for financial and marketing analysts, startup founders, and experienced entrepreneurs.
So today I’ll try covering the main aspects of using pivot tables and illustrate their practical importance with a real-situation example. Additionally, I’ll share with you some online tools which may come in handy for performing analytics.
Let’s start with 4 main objectives to use a pivot table. They involve:
- Summarization
- Analysis
- Exploration
- Presentation
Core concepts
Before addressing our use case, let’s get acquainted with the structure and basic concepts of the pivot table.
Every pivot table consists of three parts — rows, columns, and cells with values. The following aggregations can be applied to get the values: sum, average, standard deviation, minimum, maximum, count and more.
Fields in your data represent dimensions which are treated in a pivot table as hierarchies. Each hierarchy contains members — the values of the field.
Each cell contains the aggregated value at the intersection of the row and the column.
Data can be sorted right on the grid dependently on the order you need (alphabetical/reverse alphabetical order of members, ascending/descending order of values, etc).
How to create a basic pivot report
As an illustration, let’s create a retail sales performance report based on the raw data from the database to get insights related to monthly sales growth by region. We’ll use all the above-mentioned functions for this.
Before starting to work on the report, define its goals and ask yourself a question what information you want to see in the long run.
Nextly, choose the fields which describe this information and drag them to the rows, columns or values.
In our case, the most logical option is putting to the rows the hierarchies standing for the geographical location of points of sales.
Keeping to our sample, let the order dates belong to columns so as to see the sales over certain time spans.
Finally, put the sum of sales into the measures of your pivot table.
Now you can see the level of sales in every region. Expand the country to discover the sales in each city. Such an almost microscopic view can provide you with full awareness of the total picture.
Later try looking at your data from a different perspective. Simply transpose your data dynamically using drag & drop feature. Move rows to columns and vice versa with a few clicks. That is what is called rotating, pivoting and restructuring — the main operations of the pivot table.
What else can be done?
Though the data is already aggregated and shaped to the compact form, we can narrow it down even more. To focus on its specific parts, the filtering comes into play. Using it, it’s very easy to find records that meet the specific criteria or to show only those members of hierarchies that we need.
For example, you may want to find out which countries showed the highest level of gross and/or net sales over the previous year/quarter.
After getting the results, add more data to the rows to know the exact address of a store and the names of the salespeople who performed the best.
See how much you can learn using such a seemingly simple tool as a pivot table?
Why invest in web-based pivot tables?
Most entrepreneurs choose to provide their services online since it opens up new opportunities and enhances the quality of interaction with the customers. That’s why it’s a good idea to think about online tools which will contribute to the growth of your business.
One of these tools is a web pivot table which outperforms the common spreadsheet software in the flexibility and can be included into any project.
Interactivity is its another especially important aspect: you are able to interact in real-time with the data for performing ad-hoc analysis and reporting. It saves your time a lot.
Of course, it doesn’t solve all possible problems. A whole set of approaches and tools exist for diagnostic, predictive and prescriptive analytics and it’s up to you how to combine them.
But the pivot table remains the leading tool for doing online business, performing interactive analysis of the constantly incoming data and gauging the effectiveness of the enterprise.
What solutions do exist?
Let me show you a few solutions of online pivot tables which cope well with the foreknown tasks. Both can be included as web components into the projects. Their advantage is the easiness of access to the functionality via UI.
1. Flexmonster Pivot Table & Charts
Flexmonster is a JavaScript pivot table for presenting the multi-dimensional data in a summarized form. Its built-in functionality is more than enough for web reporting: 16 aggregation functions, sorting and filtering the data, drag & drop features, exporting to Excel, HTML, PDF, and others. It’s convenient for both developers and end-users.
To demonstrate, I’ll show you another ready-made sales report:
2. DevExtreme HTML5 JavaScript Pivot Grid
DevExtreme is a bundle of widgets which include PivotGrid. It’s suitable for developers who need the pivot table and some other components.
All you need to create an analytical report is already built into PivotGrid: 12 aggregations, drag & drop functionality, report filtering, and custom sorting. Exporting to Excel is available as well. To my mind, its interface is quite user-friendly.
Here is an example of the sales report:
Summary
Being designed to present your business data in a concise summarized form, pivot table leads you to answer the specific questions about the raw data: whether your business is growing and how better off are you compared to the previous years or quarters?
The best thing about the pivot table is that it helps greatly in descriptive analytics the purpose of which is an effective and accurate visualization of the collected data. It can be used separately or with other visual techniques such as charting.
I truly hope that you’ll work out which tools are the best for you.
Feedback
I’ll be glad to hear any feedback on the topic: which other techniques or their combinations do you use to extract the valuable data and gain insights?
