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
preceding_function
(Required) A row count preceding the current row.
[following_function]
(Optional) A row count following the current row.
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] or [@], the second indicates the row count after the current row and accept -1, [@+n] or [@], -1 indicates the bounding of the current partition, the n accepts a nonnegative integer which indicates the row count, [@] indicates the current row, for example:
FRAMERANGE
Syntax
Argument
Description
preceding_function
(Required) A distance preceding the current row.
[following_function]
(Optional) A distance following the current row.
Usage notes
FRAMERANGE limits the range of the window by specifying a nonnegative number as the distance around some values from peer rows that have the same value in the current row composed of the ORDERBY columns. The first parameter indicates the distance before peer rows of the current row and accepts -1, [@-n](if the order is descending, it should be [@+n]) or [@]. The second parameter indicates the distance after peer rows of the current row and accept -1, [@+n](if the order is descending, it should be [@-n]) or [@]. -1 indicates the bounding of the current partition, n accepts a nonnegative integer which indicates the distance, and [@] indicates the peer rows which have the same value in the current row. The range is a fully closed interval, and requires ORDERBY to provide the first column with a numeric data type. If there are more than 1 ordered columns, only -1 and [@] accepted, for example:
/*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);
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" },
}
}
});
//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: "=[@Price] * [@Quantity]", caption: "Amount", width: 100, conditionalFormats: [discountHighlightRule] },
// If the quantity exceeds 30, 20% off sales amount
{ value: "=LET(amount, [@Price] * [@Quantity], IF([@Quantity] > 30, amount * 0.8, amount))", 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]), PARTITIONBY([Category], [Product], YEAR([@OrderDate])), FRAMEROWS(-1,[@]))", caption: 'Running Total Quantity', width: 190, style: { backColor: "#D9E1F2" } },
// The total annual sales volume of each category and product
{ value: "=WINDOW(SUM([Quantity]), PARTITIONBY([Category], [Product], YEAR([@OrderDate])))", 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(MAP([#1:#last], LAMBDA(order, LET(amount, order.Price * order.Quantity, IF(order.Quantity > 30, amount * 0.8, amount))))), PARTITIONBY([Category], [Product], YEAR([@OrderDate])), FRAMEROWS(-1,[@]))", 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(MAP([#1:#last], LAMBDA(order, LET(amount, order.Price * order.Quantity, IF(order.Quantity > 30, amount * 0.8, amount))))), PARTITIONBY([Category], [Product], YEAR([@OrderDate])))", 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" },
}
}
});
//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: "=WINDOW(AVERAGE([Amount]), PARTITIONBY([Product]), ORDERBY([Year]), FRAMEROWS([@-2], [@]))", 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] - WINDOW(AVERAGE([Amount]), PARTITIONBY([Product]), ORDERBY([Year]), FRAMEROWS([@-2], [@]))) / [@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" },
}
}
});
//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: "=WINDOW(AVERAGE([Amount]), PARTITIONBY([Product]), ORDERBY([Amount]), FRAMERANGE([@-200], [@+200]))", 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(WINDOW(AVERAGE([Amount]), PARTITIONBY([Product]), ORDERBY([Amount]), FRAMERANGE([@-200], [@+200])) / 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: "=WINDOW(COUNT([Amount]), PARTITIONBY([Product]), ORDERBY([Amount]), FRAMERANGE([@-200], [@+200]))", 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, WINDOW(COUNT([Amount]), PARTITIONBY([Product]), ORDERBY([Amount]), FRAMERANGE([@-200], [@+200])) / 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();
});
}
<!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">
<!-- 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/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/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;
}
