The date to date filter condition interface is shown below:
conType: the type of condition.
operator: the type of date filter to use
val: the start and end date to be filtered
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 have 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 (date)", "Years (date)", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Quarters (date)", "Quarters (date)", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Months (date)", "Months (date)", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Days (date)", "Days (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("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/@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/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;
}
