You can set a formula or a related field as the grouping option:
The slice column can support formulas and related fields in the same way:
Using DATEPART formula can help to group the field by date type, the syntax as follows:
Argument
Description
date_value
(Required) The date value.
format_string
(Required) The format string of date.
[week_num_type]
(Optional) The same as the second argument of WEEKNUM.
The format string for DATEPART formula and the samples:
Format
Result
Description
yyyyQ
20214
Numeric: 1 digit(Quarter number/name.)
yyyyQQ
202104
Numeric: 2 digits + zero pad
yyyyQQQ
2021Q4
Abbreviated
yyyy QQQQ
2021 4th quarter
Wide
YYYY w
2021 8
Numeric: minimum digits(Week of Year (numeric). When used in a pattern with year, use ‘Y’ for the year field .)
YYYY ww
2021 08
Numeric: 2 digits, zero pad if needed
MM-yyyy
09-2021
Provide partial date formatter in cell formatting
Using CALCULATE and REMOVEFILTERS formulas can help to expand the group context, the syntax as follows:
Argument
Description
formula_string
(Required) The formula will evalute by the context from the expand_context.
expand_context
(Required) The expand_context is from REMOVEFILTERS.
Argument
Description
[group_field_string]
(Optional) The group field indicates the scope that is expanded to.
The CALCULATE formula should only be used in the summaryFields section, and the REMOVEFILTERS should only be used for the combination of REMOVEFILTERS and CALCULATE.
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
var baseApiUrl = getBaseApiUrl();
function getApiUrl(tableName) {
return baseApiUrl + "/" + tableName;
}
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;
//init a data manager
var dataManager = spread.dataManager();
var ordersTable = dataManager.addTable("ordersTable", {
remote: {
read: {
url: getApiUrl("Order")
}
},
schema: {
columns: {
orderDate: { dataType: "date" },
requiredDate: { dataType: "date" },
shippedDate: { dataType: "date" }
}
}
});
var customersTable = dataManager.addTable("customersTable", {
remote: {
read: {
url: getApiUrl("Customer")
}
}
});
dataManager.addRelationship(ordersTable, "CustomerId", "Customers", customersTable, "Id", "Orders");
var view = ordersTable.addView("orderView", [
{ value: "Id", width: 65 },
{ value: "OrderDate", width: 100 },
{ value: "RequiredDate", width: 120 },
{ value: "ShippedDate", width: 110 },
{ value: "ShipVia", width: 110 },
{ value: "Freight", width: 80 },
{ value: "ShipName", width: 200 },
]);
//init a table sheet
var sheet = spread.addSheetTab(0, "MyTableSheet", GC.Spread.Sheets.SheetType.tableSheet);
view.fetch().then(function () {
sheet.setDataView(view);
sheet.groupOutlinePosition(GC.Spread.Sheets.TableSheet.GroupOutlinePosition.none);
sheet.groupBy([
{
caption: "Company Name", field: "Customers.CompanyName", width: 160,
},
{
caption: "Year Quarter", field: '=DATEPART([@OrderDate],"yyyy-QQQ")', width: 160,
},
{
caption: "Ship Via", field: 'ShipVia', style: { formatter: '=SWITCH(@,1,"Speedy Express", 2,"United Package", 3, "Federal Shipping","")' }, width: 135,
summaryFields: [
{
caption: "Freight", width: 100,
formula: "=SUM([Freight])",
},
{
caption: "Company Ratio", width: 130, style: { formatter: "0.00%" },
formula: '=SUM([Freight]) / CALCULATE( SUM([Freight]), REMOVEFILTERS("ShipVia","=DATEPART([@OrderDate],""yyyy-QQQ"")"))', // ratio of sum of freight under freight level to sum of freight under year quarter
},
{
caption: "% Grand Total", width: 120, style: { formatter: "0.00%" },
formula: '=SUM([Freight]) / CALCULATE( SUM([Freight]), REMOVEFILTERS())', // ratio of sum of freight under freight level to sum of freight under all records
}
]
},
{
caption: "Freight Level", width: 135,
field: '=IFS([@Freight]<30,0,AND([@Freight]>=30,[@Freight]<60),1,[@Freight]>60,2)',
style: { formatter: '=SWITCH(@,0,"Low",1,"Medium",2,"High","no match")' },
}
]);
});
spread.resumePaint();
}
function getBaseApiUrl() {
return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api';
}
<!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/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>
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
fieldset {
padding: 6px;
margin: 0;
margin-top: 10px;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
fieldset span,
fieldset input,
fieldset select {
display: inline-block;
text-align: left;
}
fieldset span {
width: 50px;
}
fieldset input[type=text] {
width: calc(100% - 58px);
}
fieldset input[type=button] {
width: 100%;
text-align: center;
}
fieldset select {
width: calc(100% - 50px);
}
.field-line {
margin-top: 4px;
}
