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.
Users can get or set SubTotalType of the PivotTable field with the below API:
Before setting the subTotalType, a user must have added the field to the current PivotTable. For example:
All of the supported subtotal types are in the below enumeration:
The user can get or set the position of ∑Value:
The user can get or set the subtotal position (top/bottom) for a single field:
const SubtotalType = ["Average of total", "Count of total", "Count numbers of total", "Max of total", "Min of total",
"Product of total", "StdDev of total", "StdDevp of total", "Sum of total", "Varr of total", "Varp of total"];
var currentFieldName = "Sum of total";
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);
bindEvent(spread, pt);
};
function initSpread(spread) {
spread.suspendPaint();
let sheet = spread.getSheet(1);
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('tableSales', 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, pivotSales);
var sheet0 = spread.getSheet(0);
sheet0.name("PivotLayout");
sheet0.setColumnCount(50);
spread.resumePaint();
}
function initPivotTable(sheet) {
let myPivotTable = sheet.pivotTables.add("myPivotTable", "tableSales", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8);
myPivotTable.suspendLayout();
myPivotTable.options.showRowHeader = true;
myPivotTable.options.showColumnHeader = true;
myPivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField);
myPivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField);
myPivotTable.add("quantity", "Quantity", GC.Spread.Pivot.PivotTableFieldType.rowField);
let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] };
myPivotTable.group(groupInfo);
myPivotTable.add("Quarters (date)", "Quarters (date)", GC.Spread.Pivot.PivotTableFieldType.columnField);
myPivotTable.add("total", this.currentFieldName, GC.Spread.Pivot.PivotTableFieldType.valueField);
myPivotTable.subTotalType(this.currentFieldName, GC.Pivot.SubtotalType.sum);
myPivotTable.add("total", "Average of total", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.average);
myPivotTable.resumeLayout();
myPivotTable.autoFitColumn();
return myPivotTable;
}
function getUniqueName(pt, name, index = 0) {
let realName = index === 0 ? name : name + (index + "");
if (pt.getField(realName)) {
return getUniqueName(pt, name, index + 1);
} else {
return realName;
}
}
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);
newFieldName = getUniqueName(pt, newFieldName);
pt.updateFieldName(currentFieldName, newFieldName);
pt.autoFitColumn();
currentFieldName = newFieldName;
}
});
_getElementById("subtotalType2").addEventListener("change", () => {
if (pt) {
var type = parseInt(_getElementById("subtotalType2").value, 10);
pt.subTotalType("Average of total", type);
pt.autoFitColumn();
pt.refresh();
}
});
_getElementById("positionType").addEventListener("change", () => {
updateDataPosition(pt);
});
_getElementById("positionIndex").addEventListener("change", () => {
updateDataPosition(pt);
});
_getElementById("pt_subtotalsPosition").addEventListener("change", () => {
if (pt) {
var subtotalPosition = parseInt(_getElementById("pt_subtotalsPosition").value, 10);
pt.options.subtotalsPosition = subtotalPosition;
pt.autoFitColumn();
}
});
_getElementById("field_subtotalsPosition").addEventListener("change", () => {
if (pt) {
var subtotalPosition = parseInt(_getElementById("field_subtotalsPosition").value, 10);
pt.subtotalPosition("Cars", subtotalPosition);
pt.autoFitColumn();
}
});
}
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/@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/pivot-data.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">
<label><b>Settings</b>
</label>
</div>
<hr />
<div class="option-row">
Change the subtotal type for the <b>Sum of Total</b> fields (col D-G):
<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 the <b>Average of total</b> fields (col H-M):
<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 class="option-row">
Change pivotTable subtotals position:
<div class="option-item">
Subtotals Position:
<select name="" id="pt_subtotalsPosition" class="select-option-select">
<option value="0">none</option>
<option value="1">top</option>
<option value="2" selected>bottom</option>
</select>
</div>
</div>
<div class="option-row">
Change subtotals position for the <b>Cars</b> fields:
<div class="option-item">
Subtotals Position:
<select name="" id="field_subtotalsPosition" class="select-option-select">
<option value="1">top</option>
<option value="2" selected>bottom</option>
</select>
</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%;
}
