WINDOW
Syntax
Argument
Description
window_function
(Required) The window functions.
[partitionby_function]
(Optional) Divide the rows into partitions.
[orderby_function]
(Optional) Define the logical order of the rows within each partition
[frame_function]
(Optional) Specify start and end points to combine the rows to a window within the partition against the current row
Usage notes
WINDOW can only be passed with window functions, treats the entire rows as a window, and affects the sequence of the rows by PARTITIONBY, ORDERBY.
PARTITIONBY
Syntax
Argument
Description
field_function
(Required) The field name or formula be partitioned by.
Usage notes
PARTITIONBY divides the rows into partitions, and the window functions are applied in each partition separately, should have 1 or more parameters, for example:
ORDERBY
Syntax
Argument
Description
field_function
(Required) The field name or formula be ordered by.
Argument
Description
field_function
(Required) The field name or formula be ordered by.
Usage notes
ORDERBY defines the logical order of the rows within each partition. It will affect the window specified and calculation of the window functions, and should have 1 or more parameters. Use ORDERASC and ORDERDESC to indicate the sort order as ascending or descending. The default sort order is ORDERASC, for example:
FRAMEROWS
Syntax
Argument
Description
beginning_function
(Required) A row count beginning against the current row.
[ending_function]
(Optional) A row count ending against the current row.
[exclude_mode]
(Optional) Specify the exclude mode:0 - This is the default case, no rows are excluded.1 - The current row is excluded, the other peers of the current row remain for FRAMEGROUPS and FRAMERANGE.2 - The current row and the peers are all excluded.3 - The current row remain, the other peers are excluded.
Usage notes
FRAMEROWS limits the rowset of the window by specifying a nonnegative integer row count preceding or following the current row. The first parameter indicates the row count before the current row and accepts -1, [@-n], [@+n] or [@], the second indicates the row count after the current row and accepts -1, [@-n], [@+n] or [@]. The -1 indicates the beginning or ending of the current partition, The [@-n] or [@+n] indicate the row count beside current row, the n accepts a nonnegative integer which indicates the row count, [@] indicates the current row, for example:
FRAMERANGE
Syntax
Argument
Description
beginning_function
(Required) A distance beginning against the current row.
[ending_function]
(Optional) A distance ending against the current row.
[exclude_mode]
(Optional) Specify the mode for excluding the special rows, be same as FRAMEROWS.
Usage notes
FRAMERANGE limits the range of the window by specifying a nonnegative number as distance around same values from peer rows which have same value in the current row composed of the ORDERBY columns. The first parameter indicates the range beginning against the tied rows of the current row and accepts -1, [@-/+n](if the order is descending, it should be [@+/-n]) or [@], the second indicates the range ending against the tied rows of the current row and accept -1, [@+/-n](if the order is descending, it should be [@-/+n]) or [@]. The -1 indicates the beginning or ending of the current partition, the n accepts a nonnegative integer which indicates the distance, [@] indicates the peer rows which have same value in the current row, the range is fully closed interval, it requires the ORDERBY to provide the first column with numeric data type, if there are more than 1 ordered columns, only -1 and [@] accepted, for example:
FRAMEGROUPS
Syntax
Argument
Description
beginning_function
(Required) A grouping counting beginning against the current group.
[ending_function]
(Optional) A grouping counting ending against the current group.
[exclude_mode]
(Optional) Specify the mode for excluding the special rows, be same as FRAMEROWS.
Usage notes
The FRAMEGROUPS means that the starting and ending boundaries are determine by counting "groups" relative to the current group. A "group" is a set of rows that all have equivalent values which be affected by the window ORDERBY. The first parameter indicates the group count beginning against current group and accepts -1, [@-n], [@+n] or [@], the second indicates the group count ending against current group and accept -1, [@-n], [@+n] or [@]. The -1 indicates the beginning or ending of the current partition, the [@-n] or [@+n] indicate the group count beside current group, the n accepts a nonnegative integer which indicates the grouping counting, [@] indicates the current group, for example:
Window Chaining
Syntax
Usage notes
Window chaining is a shorthand that allows one window to be defined before and reuses by the new window which implicitly specify the PARTITYIONBY, ORDERBY or Window Frame. The PARTITIONBY, ORDERBY, Window Frame in the pre-defined window will be override by the expressions from the new window. Using WINDOWDEF to define the base window in the schema:
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 });
initSpread(spread);
};
function initSpread(spread) {
spread.suspendPaint();
spread.clearSheets();
spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
spread.options.allowDynamicArray = true;
spread.options.highlightInvalidData = true;
spread.options.calcOnDemand = true;
discountAmount(spread);
revenueTrends(spread);
quantityRevenueTrends(spread);
monthlyRevenueTrends(spread);
spread.resumePaint();
}
function discountAmount(spread) {
//init a data manager
var dataManager = spread.dataManager();
var discountAmountTable = dataManager.addTable("discountAmountTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/orderPriceQuantityData.csv"
}
},
schema: {
type: "csv",
columns: {
OrderDate: { dataType: "date" },
Quantity: { dataType: "number" },
Price: { dataType: "number" },
Amount: { dataType: 'formula', value: "=[@Price] * [@Quantity]" },
DiscountAmount: { dataType: 'formula', value: "=IF([@Quantity] > 30, [@Amount] * 0.8, [@Amount])" },
},
window: {
BeginCurrent: '=WINDOWDEF(PARTITIONBY([Category], [Product], YEAR([@OrderDate])), FRAMEROWS(-1,[@]))',
BeginEnd: '=WINDOWDEF(PARTITIONBY([Category], [Product], YEAR([@OrderDate])))',
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(0, "Discount Amount", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = false; //hide new row
//bind a view to the table sheet
discountAmountTable.fetch().then(function () {
var discountHighlightRule = {
ruleType: "formulaRule",
formula: "[@Quantity]>30",
style: {
foreColor: "purple"
}
};
var myView = discountAmountTable.addView("myView", [
{ value: "Category", width: 90 },
{ value: "Product", width: 90 },
{ value: "=YEAR([@OrderDate])", caption: "Year", width: 80 },
{ value: "Amount", caption: "Amount", width: 100, conditionalFormats: [discountHighlightRule] },
// If the quantity exceeds 30, 20% off sales amount
{ value: "DiscountAmount", caption: "Discount Amount", conditionalFormats: [discountHighlightRule], width: 160 },
{ value: "Price", width: 70 },
{ value: "Quantity", width: 90, conditionalFormats: [discountHighlightRule] },
// The cumulative annual sales volume of each category and product
{ value: "=WINDOW(SUM([Quantity]), \"BeginCurrent\")", caption: 'Running Total Quantity', width: 190, style: { backColor: "#D9E1F2" } },
// The total annual sales volume of each category and product
{ value: "=WINDOW(SUM([Quantity]), \"BeginEnd\")", caption: 'Total Quantity', width: 140, style: { backColor: "#D9E1F2" } },
// The cumulative annual sales amount of each category and product, if the quantity exceeds 30, 20% off sales amount
{ value: "=WINDOW(SUM([DiscountAmount]), \"BeginCurrent\")", caption: 'Running Discount Total Amount', width: 240, style: { backColor: "#E2EFDA" } },
// The total annual sales amount of each category and product, if the quantity exceeds 30, 20% off sales amount
{ value: "=WINDOW(SUM([DiscountAmount]), \"BeginEnd\")", caption: 'Discount Total Amount', width: 190, style: { backColor: "#E2EFDA" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function revenueTrends(spread) {
//init a data manager
var dataManager = spread.dataManager();
var revenueTrendsTable = dataManager.addTable("revenueTrendsTable", {
data: orderYearProductDataSource,
schema: {
type: "csv",
columns: {
Quantity: { dataType: "number" },
Amount: { dataType: "number" },
MovingAverageRevenue: { dataType: 'formula', value: '=WINDOW(AVERAGE([Amount]), "ProductYear")' },
},
window: {
ProductYear: '=WINDOWDEF(PARTITIONBY([Product]), ORDERBY([Year]), FRAMEROWS([@-2], [@]))'
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(1, "Revenue Trends", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = false; //hide new row
//bind a view to the table sheet
revenueTrendsTable.fetch().then(function () {
var myView = revenueTrendsTable.addView("myView", [
{ value: "Year", width: 150 },
{ value: "Product", width: 105 },
{ value: "Amount", caption: "Revenue", width: 100, style: { formatter: "#,##0.00" } },
// Calculate the 3-year moving average of earned per product
// FRAMEROWS([@-2], [@]): the preceding two years and current
{ value: 'MovingAverageRevenue', caption: 'Moving Average Revenue', width: 200, style: { backColor: "#E2EFDA", formatter: "#,##0.00" } },
// To show a ratio of increase and decrease between the current and moving average amount
{ value: '=VARISPARKLINE(ROUND(([@Amount] - [@MovingAverageRevenue]) / [@Amount], 2),0,,,,0.2,TRUE)', caption: 'Revenue Trends %', width: 200, style: { backColor: "#E2EFDA" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function quantityRevenueTrends(spread) {
//init a data manager
var dataManager = spread.dataManager();
var quantityRevenueTrendsTable = dataManager.addTable("quantityRevenueTrendsTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/orderAmountData.csv"
}
},
schema: {
type: "csv",
columns: {
OrderDate: { dataType: "date" },
Amount: { dataType: "number" },
AverageRevenue: { dataType: 'formula', value: '=WINDOW(AVERAGE([Amount]), "ProductRangeAmount")' },
QuantityOfRevenue: { dataType: 'formula', value: '=WINDOW(COUNT([Amount]), "ProductRangeAmount")' },
},
window: {
ProductRangeAmount: '=WINDOWDEF(PARTITIONBY([Product]), ORDERBY([Amount]), FRAMERANGE([@-200], [@+200]))'
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(2, "Quantity Of Revenue Trends", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = false; //hide new row
//bind a view to the table sheet
quantityRevenueTrendsTable.fetch().then(function () {
var myView = quantityRevenueTrendsTable.addView("myView", [
{ value: "Product", width: 150 },
{ value: "Amount", caption: "Revenue", width: 100, style: { formatter: "#,##0.00" } },
// The trend comparison of average selling prices and the quantity of the proximate orders
// When the price is within a certain range, there are more orders
// When the price is higher or lower, the order quantity decreases
// This situation of the quantity of the orders are close to the normal distribution
// The analytic function AVERAGE can obtain the average of proximate selling prices within each product
// FRAMERANGE([@-200], [@+200]): the amount range which minus or plus 200 against current amount will help to retrieve the orders as a window
{ value: "AverageRevenue", caption: 'Average Revenue', width: 150, style: { backColor: "#E2EFDA", formatter: "#,##0.00" } },
// Calculating the ratio of the average amount of the proximate orders against max amount in each product to show the bars
{ value: "=HBARSPARKLINE([@AverageRevenue] / WINDOW(MAX([Amount]), PARTITIONBY([Product])), \"#347B98\")", caption: 'Average Revenue Trends', width: 200, style: { backColor: "#E2EFDA" } },
// The analytic function COUNT can obtain the quantity of proximate selling prices within each product
// FRAMERANGE([@-200], [@+200]): the amount range which minus or plus 200 against current amount will help to retrieve the orders as a window
{ value: "QuantityOfRevenue", caption: 'Quantity Of Revenue', width: 180, style: { backColor: "#E2EFDA" } },
// Using LET to cached the ratio of the number of the proximate orders against the total number of the orders in each product to show the bars which indicates the trends through the ratio and colors
{ value: "=LET(ratio, [@QuantityOfRevenue] / WINDOW(COUNT([Amount]), PARTITIONBY([Product])),color,IF(ratio >= 0.32,\"green\", IF(ratio >= 0.2, \"#66B032\", IF(ratio >= 0.1, \"#B2D732\", \"red\"))), HBARSPARKLINE(ratio, color))", caption: 'Quantity Of Revenue Trends', width: 200, style: { backColor: "#E2EFDA" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function monthlyRevenueTrends(spread) {
//init a data manager
var dataManager = spread.dataManager();
var monthlyRevenueTrendsTable = dataManager.addTable("monthlyRevenueTrendsTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/orderAmountData.csv"
}
},
schema: {
type: "csv",
columns: {
OrderDate: { dataType: "date" },
Amount: { dataType: "number" },
MonthlyAverageRevenue: { dataType: 'formula', value: '=WINDOW(AVERAGE([Amount]), "ProductMonthly")' },
NearlyAverageRevenue: { dataType: 'formula', value: '=WINDOW(AVERAGE([Amount]), "ProductMonthly", FRAMEGROUPS([@-2], [@+2], 2))' },
},
window: {
ProductMonthly: '=WINDOWDEF(PARTITIONBY([Product]), ORDERBY(VALUE(DATEPART([@OrderDate], "M"))), FRAMEGROUPS([@], [@]))'
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(3, "Monthly Revenue Trends", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = false; //hide new row
//bind a view to the table sheet
monthlyRevenueTrendsTable.fetch().then(function () {
var myView = monthlyRevenueTrendsTable.addView("myView", [
{ value: "Product", width: 150 },
{ value: '=VALUE(DATEPART([@OrderDate], "M"))', caption: 'Month', width: 150 },
{ value: "Amount", caption: "Revenue", width: 100, style: { formatter: "#,##0.00" } },
// Compare the monthly and nearly average of earned per product
// Current month average revenue
{ value: 'MonthlyAverageRevenue', caption: 'Monthly Average Revenue', width: 220, style: { backColor: "#E2EFDA", formatter: "#,##0.00" } },
// The nearly average revenue without the current month
// For FRAMEGROUPS, [@-2] indicates two months before current, [@+2] indicates two months after current, and the exclude mode is 2 that indicates the current row and the peers are all excluded.
{ value: 'NearlyAverageRevenue', caption: 'Nearly Average Revenue', width: 200, style: { backColor: "#E2EFDA", formatter: "#,##0.00" } },
// To show a ratio of increase and decrease between the current and nearly average amount
{ value: '=VARISPARKLINE(ROUND(([@MonthlyAverageRevenue] - [@NearlyAverageRevenue]) / [@MonthlyAverageRevenue], 2),0,,,,0.2,TRUE)', caption: 'Revenue Trends %', width: 200, style: { backColor: "#E2EFDA" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
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">
<!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill -->
<script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script>
<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-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/orderDataSource.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/orderYearProductDataSource.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>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 210px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
label {
display: block;
margin-bottom: 3px;
margin-top: 3px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width: 100%;
text-align: center;
}
input[type=text] {
width: 230px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
margin: 0;
}
