Calculated Fields

Calculated fields are very popular and give a higher level of abstraction from the raw data values. To summarize calculated values instead of raw values, you can add a custom field with the getValue property set to a function that calculates the value or the raw item. This sample adds two calculated fields to a PivotEngine instance: Range and Conversion.

import 'bootstrap.css'; import '@grapecity/wijmo.styles/wijmo.css'; import './styles.css'; import * as wjOlap from '@grapecity/wijmo.olap'; import { getData } from './data'; // document.readyState === 'complete' ? init() : window.onload = init; // function init() { let ng = new wjOlap.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: (item) => { let 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: (item) => { return item.downloads ? item.sales / item.downloads : 0; } } ], rowFields: ['Date', 'Range'], valueFields: ['Sales', 'Conversion'] }); // // show panel let pivotPanel = new wjOlap.PivotPanel('#pivotPanel', { itemsSource: ng }); // // show summary let pivotGrid = new wjOlap.PivotGrid('#pivotGrid', { isReadOnly: true, itemsSource: ng }); } <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <title>Grapecity Wijmo OLAP Fields Setting Dialog</title> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <!-- SystemJS --> <script src="node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('./src/app'); </script> </head> <body> <div class="container-fluid"> <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> </body> </html> export function getData(cnt) { let year = new Date().getFullYear(), data = []; // for (let 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); } .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; } import 'bootstrap.css'; import '@grapecity/wijmo.styles/wijmo.css'; import './styles.css'; import * as wjOlap from '@grapecity/wijmo.olap'; // import { Component, Inject, enableProdMode, NgModule } from '@angular/core'; import { platformBrowserDynamic } from '@angular/platform-browser-dynamic'; import { BrowserModule } from '@angular/platform-browser'; import { WjOlapModule } from '@grapecity/wijmo.angular2.olap'; import { DataService, TDataItem } from './app.data'; // @Component({ selector: 'app-component', templateUrl: 'src/app.component.html' }) export class AppComponent { ng: wjOlap.PivotEngine; // constructor(@Inject(DataService) private dataService: DataService) { this.ng = new wjOlap.PivotEngine({ autoGenerateFields: false, itemsSource: dataService.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: (item: TDataItem) => { let 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: (item: TDataItem) => { return item.downloads ? item.sales / item.downloads : 0; } } ], rowFields: ['Date', 'Range'], valueFields: ['Sales', 'Conversion'] }); } } // @NgModule({ imports: [WjOlapModule, BrowserModule], declarations: [AppComponent], providers: [DataService], bootstrap: [AppComponent] }) export class AppModule { } // enableProdMode(); // Bootstrap application with hash style navigation and global services. platformBrowserDynamic().bootstrapModule(AppModule); <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <title>Grapecity Wijmo OLAP Fields Setting Dialog</title> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <!-- Polyfills --> <script src="node_modules/core-js/client/shim.min.js"></script> <script src="node_modules/zone.js/dist/zone.min.js"></script> <!-- SystemJS --> <script src="node_modules/systemjs/dist/system.js"></script> <script src="systemjs.config.js"></script> <script> // workaround to load 'rxjs/operators' from the rxjs bundle System.import('rxjs').then(function (m) { System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators)); System.import('./src/app.component'); }); </script> </head> <body> <app-component></app-component> </body> </html> <div class="container-fluid"> <div class="row"> <div class="col-xs-6"> <wj-pivot-panel [itemsSource]="ng"></wj-pivot-panel> </div> <div class="col-xs-6"> <wj-pivot-grid [itemsSource]="ng"></wj-pivot-grid> </div> </div> </div> import { Injectable } from '@angular/core'; // export type TDataItem = { product: string; country: string; active: boolean; date: Date; sales: number; downloads: number; } // @Injectable() export class DataService { getData(cnt: number) { let year = new Date().getFullYear(), data: TDataItem[] = []; // for (let i = 0; i < cnt; i++) { data.push({ product: this._randomInt(0, 1) ? 'Wijmo' : 'Aoba', country: this._randomInt(0, 1) ? 'USA' : 'Japan', active: i % 2 == 0, date: new Date(year - this._randomInt(0, 2), this._randomInt(0, 11), this._randomInt(0, 27) + 1), sales: this._randomInt(10, 20), downloads: this._randomInt(10, 200) }); } // return data; } // private _randomInt(min: number, max: number): number { return Math.floor(Math.random() * (max - min + 1) + min); } } .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; } <template> <div class="container-fluid"> <div class="row"> <div class="col-xs-6"> <wj-pivot-panel :itemsSource="ng"></wj-pivot-panel> </div> <div class="col-xs-6"> <wj-pivot-grid :itemsSource="ng"></wj-pivot-grid> </div> </div> </div> </template> <script> import '@grapecity/wijmo.styles/wijmo.css'; import 'bootstrap.css'; import Vue from 'vue'; import '@grapecity/wijmo.vue2.olap'; import * as wjcOlap from '@grapecity/wijmo.olap'; import { getData } from './data' let App = Vue.extend({ name: 'app', data: function() { return { ng: new wjcOlap.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: (item) => { let 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: (item) => { return item.downloads ? item.sales / item.downloads : 0; } } ], rowFields: ['Date', 'Range'], valueFields: ['Sales', 'Conversion'] }) }; } }); new Vue({ render: h => h(App) }).$mount('#app'); </script> <style> .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; } </style> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <title>Grapecity Wijmo OLAP Pivot Fields Setting Dialog</title> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <!-- SystemJS --> <script src="node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('./src/app.vue'); </script> </head> <body> <div id="app"> </div> </body> </html> export function getData(cnt) { let year = new Date().getFullYear(), data = []; // for (let 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); } import './app.css'; import 'bootstrap.css'; import '@grapecity/wijmo.styles/wijmo.css'; // import * as React from 'react'; import * as ReactDOM from 'react-dom'; // import * as Olap from '@grapecity/wijmo.react.olap'; import * as wjcOlap from '@grapecity/wijmo.olap'; import { getData } from './data'; class App extends React.Component { constructor(props) { super(props); this.state = { ng: new wjcOlap.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: (item) => { let 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: (item) => { return item.downloads ? item.sales / item.downloads : 0; } } ], rowFields: ['Date', 'Range'], valueFields: ['Sales', 'Conversion'] }) }; } render() { return (<div className="container-fluid"> <div className="row"> <div className="col-xs-6"> <Olap.PivotPanel itemsSource={this.state.ng}></Olap.PivotPanel> </div> <div className="col-xs-6"> <Olap.PivotGrid itemsSource={this.state.ng}></Olap.PivotGrid> </div> </div> </div>); } } ReactDOM.render(<App />, document.getElementById('app')); <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <title>AutoComplete</title> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <!-- SystemJS --> <script src="node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('./src/app'); </script> </head> <body> <div id="app"></div> </body> </html> .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; } export 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); }