Using JavaScript to imitate Excel's data perspective analysis function _javascript Skill

What is a data perspective analysis?

The data perspective analysis is to summarize, filter, analyze, compare and draw the data in different dimensions. Used to discover trends in data and differences caused by different factors. This is useful in sales, statistics, and finance, and is often used in some management software.

Next, you use Excel to describe what a PivotTable analysis and PivotTable report is.

Below I use the Excel PivotTable report to analyze iphone 2013,2014 and 2015 sales figures in China and the U.S. to summarize the iphone's sales trends.

Statement: All data are fabricated by themselves, without any reference value.

Excel PivotTable and PivotChart reports

This is the iphone's sales in China and the US in 2013,2014,2015 years.

----in order to discover the changes in the sales volume of different handsets in each country in 2013,2014,2015 years, you need to insert a PivotTable report and configure the data. Here, if you add fields in rows, they are sorted by row. (sorted by country First, then year and product);

Get a PivotTable report based on configuration

With a PivotTable report, it's easy to see China's sales totals and U.S. sales totals.

Pivot Chart

According to this figure, the iphone's sales in China have fallen sharply over the years.

----in order to observe the difference between China and the United States, only need to configure the data panel as follows. (in product and country categories)

Pivot table

Pivot Chart

It can be found that since 14, iphone sales in China began to lower than in the United States, and its price and the difference between the United States has become less and less.

By using an Excel PivotTable report to analyze iphone sales, we found that Excel's pivot PivotTable and PivotChart reports have the following benefits:

By analyzing data from multiple dimensions, you need only one table to analyze data changes from multiple angles.

You can create dynamic, summarized views by dragging and dropping by anyone.

It is a very complicated and complex data processing, analysis, can help us to visually find the trend of data changes and differences.

Rich report types (histogram, line chart) to meet a variety of needs.

It can be filtered for data to perform certain specific analyses.
Well, Excel and its PivotTable report are really powerful!

It is because Excel is so powerful that it is now required to use JavaScript to implement the same functionality on the Web side! (My Days-):)

Using JavaScript to implement

First, define the data with an array of objects (for relational databases, which are ordered here, but the real data is unordered)

 var json = [{year
  ]: 2013,
  "Country": "USA",
  "Product": "iphone 5",
  "Sales": 8000, "Price
  ": 6000< c6/>}, {
  "year": 2013,
  "Country": "USA",
  "Product": "iphone 6",
  "sales": 0,
  "price": 0
 }, {   
  ...
  ...
 }]

Or use a key value pair

var data = {"2013": {"I": {"iphone": {"
    Sales": 8000,
    "price": 6000
   },
   " Iphone5 ": {...}}
  ,
  " USA ": {
   ...}}
 ,
 " 2014 ": {
 ...} ...
}

Pivot table

First, I'll solve the PivotTable problem first.

According to the above data, there are two ways to process the data rollup (here is demo, the real code is much more complicated)

Use the normal data traversal method. (Complete a sort function, where you use the Array.prototype.sort() method.) ）

Data.sort (function (A, b) {return
 a.year > B.year | |
  A.country > B.country | |
  A.product > B.product | |
  A.sales > B.sales | |
  A.price > B.price;
});

This is sorted by country first, and if country is the same, then sort by product ...

And then, based on the sorted array, the totals are computed (circular traversal, where only the year and country are categorized)

var gettotal = function () {
 var total = {};
 for (var i = 0; i < json.length i++) {
  var item = json[i];
  Obtain the total sales volume of each country
  total[item.country] = Total[item.country] | | {};
  Total[item.country].sales = Total[item.country].sales = = undefined?
   Item.sales:
   total[item.country].sales + item.sales;

  Total sales in different years in each country
  total[item.country][item.year] = total[item.country][item.year] = = undefined?
   Item.sales:
   total[item.country][item.year] + item.sales;
 }
 var sum = 0;

 Total sales for
 (Var key at total) {
  sum + + total[key].sales;
 }
 Total.sum = sum;
 return total;
};

This is the result of the summary

OK, now it's time to make a table out of this data (no more details here).

Use SQL statements to sort and summarize data

The second way to sort the data is to use SQL.

A statement is done.

SELECT *, total from 
data as A,
select SUM (sales) from data group by product as B 

The final is to improve the interface, through this method can do a similar effect, a simple perspective even if completed.

However, because the project has many tables, even do not know the name of the column headings, so the above method is not available at all.

Now, there is another way to solve this problem, just in the Excel IO section has already used this plugin.

Solved by Wijmo

Here is a demo that uses WIJMO to complete the data perspective.

In the first pure JavaScript implementation Excek IO has introduced its basic usage.

Pivot table

First, import the required packages into

<script src= "./lib/wijmo/wijmo.min.js" ></script>
<script src= "./lib/wijmo/wijmo.input.min.js" ></script>
<script src= "./lib/wijmo/wijmo.grid.min.js" ></script>
<script src= "./ Lib/wijmo/wijmo.grid.filter.min.js "></script>
<script src="./lib/wijmo/wijmo.chart.min.js "> </script>
<script src= "./lib/wijmo/wijmo.olap.min.js" ></script>

Then instantiate a pivot panel based on the data

<div id= "Pivot_panel" ></div>

var app = {};
App.pivotpanel = new Wijmo.olap.PivotPanel (' #pivot_panel ');

Engine is the data engine for this panel, and the associated chart shares a data engine.
var ng = app.pivotPanel.engine;
Ng.itemssource = App.collectionview;
Ng.rowFields.push (' Country ', ' year ', ' product ');
Ng.valueFields.push (' Sales (set) ');
Ng.showrowtotals = wijmo.olap.ShowTotals.Subtotals;

The following is the effect of this piece of code:

To generate a table based on the pivot configuration panel

<div id= "Pivot_grid" ></div>

App.pivotgrid = new Wijmo.olap.PivotGrid (' #pivot_grid ', {
 itemsSource:app.pivotPanel,
 showselectedheaders: ' All '
});

Add Filter

You can filter the column data by right-clicking the column and choosing Filter.

Pivot Chart

App.pivotchart = new Wijmo.olap.PivotChart (' #pivot_chart ', {
 charttype: ' Column ',//Bar
 ItemsSource: App.pivotpanel
});

Well, the PivotTable and perspective is finished.

This is the full effect of the demo.

With the pivot control, the data features of this project are also quickly completed.

Summarize

The above is the entire content of this article, I hope that the study or work can help, if you can leave a message to communicate.