PivotTable Subtotal

In PivotTable value fields, the value range is the statistical data area. Through the value area, the user can choose the statistical data and summary types.

In PivotTable, there are 11 kinds of subtotal types. Each type defines the way that values are calculated in the data area. Enumeration Value Description average (Average) The average of the values. count (Count) The number of data values. The Count consolidation function works the same as the COUNTA worksheet function. countNums (CountNums) The number of data values that are numbers. The Count Nums consolidation function works the same as the COUNT worksheet function. max (Maximum) The largest value. min (Minimum) The smallest value. product (Product) The product of the values. stdDev (StdDev) An estimate of the standard deviation of a population, where the sample is a subset of the entire population. stdDevp (StdDevP) The standard deviation of a population, where the population is all of the data to be summarized. sum (Sum) The sum of the values. var (Variance) An estimate of the variance of a population, where the sample is a subset of the entire population. varp (VarP) The variance of a population, where the population is all of the data to be summarized. In multiple value fields, the ∑Value field will be added to column area automatically. The ∑Value field worked as the row / column field to define the data distribution in PivotTable, row / column header. User can get or set SubTotalType of the PivotTable field by below API: Before set subTotalType, user must have added the field to current pivotTable. For example: All the supported subtotal types are in the below enumeration: The user can get or set the position of ∑Value:
const SubtotalType = ["Average of amount", "Count of amount", "Count numbers of amount", "Max of amount", "Min of amount", "Product of amount", "StdDev of amount", "StdDevp of amount", "Sum of amount", "Varr of amount", "Varp of amount"]; var currentFieldName = "Sum of amount"; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 2 }); initSpread(spread); var pivotLayoutSheet = spread.getSheet(0); var pt = initPivotTable(pivotLayoutSheet); doAutoFitColumn(pivotLayoutSheet); bindEvent(spread, pt); }; function initSpread(spread) { spread.suspendPaint(); var sheet1 = spread.getSheet(1); sheet1.name("DataSource"); sheet1.setRowCount(700); sheet1.setColumnWidth(5, 150); sheet1.setArray(0, 0, pivotSales); sheet1.tables.add('tableSales', 0, 0, 642, 6); var sheet0 = spread.getSheet(0); sheet0.name("PivotLayout"); sheet0.setColumnCount(50); spread.resumePaint(); } function initPivotTable(sheet) { var myPivotTable = sheet.pivotTables.add("myPivotTable", "tableSales", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.dark2); myPivotTable.suspendLayout(); myPivotTable.options.showRowHeader = true; myPivotTable.options.showColumnHeader = true; myPivotTable.add("region", "Region", GC.Spread.Pivot.PivotTableFieldType.rowField); myPivotTable.add("country", "Country", GC.Spread.Pivot.PivotTableFieldType.rowField); var groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.years }, { by: GC.Pivot.DateGroupType.quarters }] }; myPivotTable.group(groupInfo); myPivotTable.add("Years", "Years", GC.Spread.Pivot.PivotTableFieldType.columnField); myPivotTable.add("date", "Qt", GC.Spread.Pivot.PivotTableFieldType.columnField); myPivotTable.add("amount", currentFieldName, GC.Spread.Pivot.PivotTableFieldType.valueField); myPivotTable.subTotalType(currentFieldName, GC.Pivot.SubtotalType.sum); myPivotTable.add("amount", "Test Amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.average); myPivotTable.resumeLayout(); return myPivotTable; } function doAutoFitColumn (sheet) { sheet.suspendPaint(); var colCount = sheet.getColumnCount(); for (var i = 0; i < colCount; i++) { sheet.autoFitColumn(i); } sheet.setColumnWidth(0, 20); sheet.setRowHeight(0, 20); sheet.resumePaint(); } function bindEvent (spread, pt) { _getElementById("subtotalType").addEventListener("change", () => { if (pt) { var type = parseInt(_getElementById("subtotalType").value, 10); var newFieldName = SubtotalType[type]; pt.subTotalType(currentFieldName, type); pt.updateFieldName(currentFieldName, newFieldName); currentFieldName = newFieldName; } }); _getElementById("subtotalType2").addEventListener("change", () => { if (pt) { var type = parseInt(_getElementById("subtotalType2").value, 10); pt.subTotalType("Test Amount", type); } }); _getElementById("positionType").addEventListener("change", () => { updateDataPosition(pt); }); _getElementById("positionIndex").addEventListener("change", () => { updateDataPosition(pt); }); } function updateDataPosition (pt) { if (pt) { var positionType = parseInt(_getElementById("positionType").value, 10); var positionIndex = parseInt(_getElementById("positionIndex").value, 10); pt.dataPosition(positionType, positionIndex); } } function _getElementById(id) { return document.getElementById(id); }
<!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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/pivotSales.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.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 class="options-container"> <div class="option-row"> Changes the subtotal type for the first amount field. <select id="subtotalType"> <option value="0">average (Average)</option> <option value="1">count (Count)</option> <option value="2">countNums (CountNums)</option> <option value="3">max (Maximum)</option> <option value="4">min (Minimum)</option> <option value="5">product (Product)</option> <option value="6">stdDev (StdDev)</option> <option value="7">stdDevp (StdDevP)</option> <option value="8" selected>sum (Sum)</option> <option value="9">var (Variance)</option> <option value="10">varp (VarP)</option> </select> </div> <div class="option-row"> Changes the subtotal type for Test Amount field. <select id="subtotalType2"> <option value="0" selected>average (Average)</option> <option value="1">count (Count)</option> <option value="2">countNums (CountNums)</option> <option value="3">max (Maximum)</option> <option value="4">min (Minimum)</option> <option value="5">product (Product)</option> <option value="6">stdDev (StdDev)</option> <option value="7">stdDevp (StdDevP)</option> <option value="8">sum (Sum)</option> <option value="9">var (Variance)</option> <option value="10">varp (VarP)</option> </select> </div> <div class="option-row"> Changes the data position. <div class="option-item"> Position Type: <select id="positionType"> <option value="0">row</option> <option value="1" selected>col</option> </select> </div> <div class="option-item"> Position Index: <input type="number" value="0" id="positionIndex" min="0" max="2"/> </div> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; } .sample-spreadsheets { width: calc(100% - 220px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 220px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } .option-item { margin-top: 5px; } .option-item #positionType { float: right; width: 80px; } .option-item #positionIndex { float: right; width: 72px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } #app { height: 100%; }