Demo - Calculated Fields

HTML

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">
<link rel="stylesheet" href="https://cdn.grapecity.com/wijmo/5.latest/styles/wijmo.min.css">
<script src="https://cdn.grapecity.com/wijmo/5.latest/controls/wijmo.min.js"></script>
<script src="https://cdn.grapecity.com/wijmo/5.latest/controls/wijmo.input.min.js"></script>
<script src="https://cdn.grapecity.com/wijmo/5.latest/controls/wijmo.grid.min.js"></script>
<script src="https://cdn.grapecity.com/wijmo/5.latest/controls/wijmo.grid.filter.min.js"></script>
<script src="https://cdn.grapecity.com/wijmo/5.latest/controls/wijmo.olap.min.js"></script>
<div class="container">

  <h1>
    Calculated Fields
  </h1>
  <p>
    In some cases, you may want to summarize calculated values 
    instead of raw values.
  </p>
  <p>
    You can do this by adding a calculated value to the raw data
    items, or by adding a custom field with the <b>getValue</b>
    property set to a function that calculates the value for the
    raw item.
  </p>
  <p>
    This sample demonstrates the second approach. It adds two 
    calculated fields to a <b>PivotEngine</b>:
  </p>
  <ol>
    <li>
      <b>Range</b>: This is a calculated dimension field that
      contains the value "High", "Low", or "Medium" based on
      the value of the "sales" property.
    </li>
    <li>
      <b>Conversion</b>: This is a calculated measure field that
      contains the ratio between sales and downloads.
    </li>
  </ol>
  <div class="row">
    <div class="col-xs-6">
      <div id="pivotPanel"></div>
    </div>
    <div class="col-xs-6">
      <div id="pivotGrid"></div>
    </div>
  </div>
</div>

CSS

.wj-pivotgrid {
	max-height: 400px;
	box-shadow: 0 10px 20px rgba(0,0,0,0.19), 0 6px 6px rgba(0,0,0,0.23);
}
.wj-pivotgrid .wj-cell.spark {
	padding: 8px;
  overflow: visible;
}
	.wj-pivotgrid .wj-cell.spark svg {
  	overflow: visible;
    stroke: currentColor;
    fill: currentColor;
  }
	  .wj-pivotgrid .wj-cell.spark.spark-up svg {
  		color: #009000; /* green for up */
		}
  	.wj-pivotgrid .wj-cell.spark.spark-down svg {
	  	color: #d00000; /* red for down */
	  }

body {
  margin-bottom: 48pt;
}

JavaScript

onload = function() {

	// create a PivotEngine with a custom view
  var ng = new wijmo.olap.PivotEngine({
  	autoGenerateFields: false,
    itemsSource: getData(10000),
    showColumnTotals: 'GrandTotals',
    showRowTotals: 'Subtotals',
    fields: [
    	{ binding: 'product', header: 'Product' },
      { binding: 'date', header: 'Date', format: 'yyyy \"Q\"q' },
      {
      	header: 'Range',
        dataType: 'String',
        aggregate: 'Cnt',
        getValue: function (item, formatted) {
        	var sales = item.sales;
          return sales <= 13 ? 'Low' : sales >= 17 ? 'High' : 'Medium';
        }
      },
      { binding: 'sales', header: 'Sales', format: 'n0' },
      { binding: 'downloads', header: 'Downloads', format: 'n0' },
      {
      	header: 'Conversion',
        dataType: 'Number',
        aggregate: 'Avg',
        format: 'p0', 
        getValue: function (item) {
        	return item.downloads ? item.sales / item.downloads : 0;
				}
			}
    ],
    rowFields: ['Date', 'Range'],
    valueFields: ['Sales', 'Conversion']
	});

  // show panel
  var pivotPanel = new wijmo.olap.PivotPanel('#pivotPanel', {
  	itemsSource: ng
	});

  // show summary
  var pivotGrid = new wijmo.olap.PivotGrid('#pivotGrid', {
  	isReadOnly: true,
    itemsSource: ng
	});

	// gets a simple data set for basic demos
  function getData(cnt) {
  	var year = new Date().getFullYear(),
    		data = [];
		for (var i = 0; i < cnt; i++) {
    	data.push({
      	product: randomInt(0, 1) ? 'Wijmo' : 'Aoba',
        country: randomInt(0, 1) ? 'USA' : 'Japan',
        active: i % 2 == 0,
        date: new Date(year - randomInt(0, 2), randomInt(0, 11), randomInt(0, 27) + 1),
        sales: randomInt(10, 20),
        downloads: randomInt(10, 200)
      });
    }
    return data;
	}
  function randomInt(min, max) {
  	return Math.floor(Math.random() * (max - min + 1) + min);
	}
}