PivotTable Date To Date Filter

SpreadJS PivotTables support the date to date filter. With this, a user can use any of the six built-in date to date filters in the context menu. We also provide the ability of to use parallel date filter, which are useful when analyzing data in parallel time periods.

The date to date filter condition interface is shown below: startDate: the start time. endDate: the end time. isDynamicEndDate: if value is true, will use current time for endDate. if value is false, we need to customize end time for endDate. isParallel: it means whether to use the parallel mode. by: month/quarter/year On this basis, we added six built-in Date To Date filters in the pivot date filter context menu. month to date(MTD) quarter to date(QTD) year to date(YTD) parallel month to date(parallel MTD) parallel quarter to date(parallel QTD) parallel year to date(parallel YTD) You can use the Date To Date Filter as follows:
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 = getSource(sheet2, pivotSales); let pivotTable = addPivotTable(sheet1, tableName); bindEvent(pivotTable,spread); spread.resumePaint(); } function getSource(sheet, tableSource) { sheet.name("DataSource"); sheet.setRowCount(2000); 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, 1984, 6); for(let i=2;i<=1984;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 addPivotTable(sheet, source) { sheet.suspendPaint(); sheet.name("PivotTable"); sheet.setRowCount(10000); let pivotTable = sheet.pivotTables.add("PivotTable", source, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8); pivotTable.suspendLayout(); pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.columnField); let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.years},{ by: GC.Pivot.DateGroupType.quarters },{ by: GC.Pivot.DateGroupType.months}, { by: GC.Pivot.DateGroupType.days}] }; pivotTable.group(groupInfo); pivotTable.add("Years", "Years", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("Quarters", "Quarters", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("Months", "Months", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("date", "date", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("quantity", "Quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); pivotTable.resumeLayout(); sheet.resumePaint(); pivotTable.autoFitColumn(); return pivotTable; } function _isNullOrUndefined (obj) { return obj === null || obj === undefined; } function bindEvent(pivotTable) { document.getElementById("clearFilter").onclick = function () { pivotTable.labelFilter("Years", null); pivotTable.labelFilter("Quarters", null); pivotTable.labelFilter("Months", null); pivotTable.labelFilter("date", null); } document.getElementById("setFilter").onclick = function () { let startTime = new Date(document.getElementById("startTime").value); let endTime = new Date(document.getElementById("endTime").value); let by = +(document.getElementById("by").value); let isParallel = document.getElementById("isParallel").checked; let isDynamicEndDate = document.getElementById("isDynamicEndDate").checked; var condition = { conType: GC.Pivot.PivotConditionType.date, operator: GC.Pivot.PivotDateFilterOperator.dateToDate, val: [new Date(startTime), new Date(endTime)], isDynamicEndDate, isParallel, by }; var filterInfo = { condition }; pivotTable.labelFilter("date", filterInfo); } }
<!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-shapes/dist/gc.spread.sheets.shapes.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/js/license.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/pivot-data-filter.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> <div class="options-container"> <div>custom date to date Filter:</div> <div class="pivottable-filter date-filter"> <label class="filter-input">startTime: </label> <input class="filter-input" type="text" id="startTime"> <label class="filter-input">endTime: </label> <input class="filter-input" type="text" id="endTime"> <div class="filter-input"> <label>isDynamicEndDate: </label> <input type="checkbox" id="isDynamicEndDate"> </div> <div class="filter-input"> <label>by: </label> <select id="by"> <option value=1>month</option> <option value=2>quarter</option> <option value=3>year</option> </select> </div> <div class="filter-input"> <label>isParallel: </label> <input type="checkbox" id="isParallel"> </div> <button class="filter-input set-filter" id="clearFilter">clearFilter</button> <button class="filter-input set-filter" id="setFilter">setFilter</button> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .pivottable-filter{ height: 100px; } .pivot-filter{ width: 200px; height: 25px; display: block; margin-bottom: 10px; float: left; } .filter-input{ width: 200px; height: 20px; display: block; /* margin-left: 15px; */ margin-top: 10px; } .set-filter{ width: 200px; margin-top: 20px; /* float: right; */ } .dateFilterSettingItemDiv{ margin: 11px; } .set-filter{ width: 200px; margin-top: 20px; /* float: right; */ } .filter-input{ width: 200px; height: 20px; display: block; /* margin-left: 15px; */ margin-top: 10px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .whole-field{ margin-bottom: 10px; }