Calculated Field

You can add custom calculated fields into the value field area of a pivot table which calculates values using formulas. You can use constants or refer to other fields of the pivot table in the formula.

Description
app.js
index.html
styles.css
Copy to CodeMine

PivotTable offers the ability to add a calculated field with a formula

    pivotTable.addCalcField('Tax', '= total * 0.08');

It also supports removing calculated field.

    pivotTable.removeCalcField('Tax');

You can get all calculated fields that are set in the specified PivotTable.

    pivotTable.getCalcFields();

The returned result has two properties:

  • fieldName: the calculated field name.
  • formula: the formula of the calculated field name.
PivotTable offers the ability to add a calculated field with a formula It also supports removing calculated field. You can get all calculated fields that are set in the specified PivotTable. The returned result has two properties: fieldName: the calculated field name. formula: the formula of the calculated field name.
window.onload = function () { let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 2}); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); let sheet1 = spread.getSheet(0); let sheet2 = spread.getSheet(1); let tableName = getDataSource(sheet2, pivotSales); let pivotTable = initPivotTable(sheet1, tableName); bindEvent(spread, pivotTable); spread.resumePaint(); } function getDataSource(sheet, tableSource){ sheet.name("DataSource"); sheet.setRowCount(117); sheet.setColumnWidth(0, 120); sheet.getCell(-1, 0).formatter("YYYY-mm-DD"); sheet.getRange(-1,4,0,2).formatter("$ #,##0"); let table = sheet.tables.add('table', 0, 0, 117, 6); for(let i=2;i<=117;i++) { sheet.setFormula(i-1,5,'=D'+i+'*E'+i) } table.style(GC.Spread.Sheets.Tables.TableThemes["none"]); sheet.setArray(0, 0, tableSource); return table.name(); } function initPivotTable(sheet, source){ sheet.suspendPaint(); sheet.name("PivotTable"); sheet.setRowCount(1000); sheet.setColumnCount(100); let option = { showRowHeader: true, showColumnHeader: true, bandRows: true, bandColumns: true }; let pivotTable = sheet.pivotTables.add("PivotTable", source, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8, option); pivotTable.suspendLayout(); pivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField); let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] }; pivotTable.group(groupInfo); pivotTable.add("Quarters (date)", "Quarters (date)", GC.Spread.Pivot.PivotTableFieldType.columnField); pivotTable.add("total", "Revenue", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); pivotTable.addCalcField('tax', '=total * 0.08'); pivotTable.add("tax", "Tax", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); pivotTable.resumeLayout(); sheet.resumePaint(); pivotTable.autoFitColumn(); return pivotTable; } function bindEvent(spread, pivotTable){ document.getElementById("add-calc-field").addEventListener("click", function(event){ pivotTable.suspendLayout(); let fieldName = document.getElementById("fieldName").value; let formula = document.getElementById("formula").value; pivotTable.addCalcField(fieldName, formula); pivotTable.add(fieldName, fieldName, GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); pivotTable.resumeLayout(); }); document.getElementById("remove-calc-field").addEventListener("click", function(event){ pivotTable.suspendLayout(); let fieldName = document.getElementById("fieldName").value; pivotTable.removeCalcField(fieldName); pivotTable.resumeLayout(); }); document.getElementById("list-calc-fields").addEventListener("click", function(event){ spread.suspendPaint(); var sheet = new GC.Spread.Sheets.Worksheet(); var sheetCount = spread.getSheetCount(); spread.addSheet(sheetCount, sheet); sheet.setColumnWidth(0,150); sheet.setColumnWidth(1,150); sheet.setValue(0, 0, "Field Name"); sheet.setValue(0, 1, "Formula"); var calcFieldInfos = pivotTable.getCalcFields(); for (var i = 0; i < calcFieldInfos.length; i++) { sheet.setValue(i + 1, 0, calcFieldInfos[i].fieldName); sheet.setValue(i + 1, 1, calcFieldInfos[i].formula); } spread.setActiveSheet(sheet.name()); spread.resumePaint(); }); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/data.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> <div id="container" class="options-container"> <div> <div class = "select-option-class">Field Name:</div> <input type="text" name="" id="fieldName" class="missingCaption select-option-select"/> </div> <div> <div class = "select-option-class">Formula:</div> <input type="text" name="" id="formula" class="rowLabelIndent select-option-select"/> </div> <hr /> <div> <label class = "select-option-class">- To add a Calculated Field enter Field Name and Formula and click the below button.</label> <label class = "select-option-class"><u>For Example</u>: Add <b>Commission</b> to field name, and <b>=total*0.05</b> to formula, to have a calculated field for Commission. You can find the inserted fields after the current ones (at columns L-O and R).</label> </div> <input type="button" value="Add Calculated Field" class="set-option" id="add-calc-field" /> <div> <label class = "select-option-class">- To remove a Calculated Field, enter the name in the Field Name box and click the below button.</label> <label class = "select-option-class"><u>For Example</u>: Tax.</label> </div> <input type="button" value="Remove Calculated Field" class="set-option" id="remove-calc-field" /> <div> <label class = "select-option-class">- Click the List Calculated Fields to see the list of all Calculated Fields that have been created.</label> </div> <input type="button" value="List Calculated Fields" class="set-option" id="list-calc-fields" /> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 300px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 300px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .set-option { display: block; margin-top: 20px; width: 250px; } #reportFilterFieldsPerColumn { width: 28px; } .select-option-class{ display: block; margin-top: 10px; margin-bottom: 10px } .select-option-select{ width: 250px; display: block; margin-bottom: 20px; } .option-item{ display: block; height: 20px; margin-bottom: 20px; }