Group By Formula or Related Field
TableSheet supports not only the fields but also formulas and related fields as the group or slice column.
Description
app.js
index.html
styles.css
Copy to CodeMine
You can set a formula or a related field as the grouping option:
tableSheet.groupBy([
{
caption: 'Company Name',
field: 'Customers.CompanyName', // grouping related field for company name of the customer
},
{
caption: 'Year',
field: '=YEAR([@OrderDate])', // grouping formula field for year of the order date
},
{
caption: 'Freight Level',
field: '=IFS([@Freight]<30,0,AND([@Freight]>=30,[@Freight]<60),1,[@Freight]>60,2)', // grouping formula field for numerical range
style: { formatter: `=SWITCH(@,0,"Low",1,"Medium",2,"High","no match")` },
}
])
The slice column can support formulas and related fields in the same way:
tableSheet.groupBy([
{
caption: 'Year',
field: '=YEAR([@OrderDate])',
summaryFields: [
{
formula: '=SUM([Freight])',
slice: 'Customers.CompanyName' // slice related field for company name of the customer
},
{
formula: '=SUM([Freight])',
slice: '=MONTH([@OrderDate])' // slice formula field for month of the order date
},
]
}
])
Using DATEPART formula can help to group the field by date type, the syntax as follows:
=DATEPART(date_value, format_string, [week_num_type])
| 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:
=CALCULATE(formula_string, expand_context)
| 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. |
=REMOVEFILTERS([ group_field_string [, group_field_string [, … ] ] ])
| 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.
tableSheet.groupBy([
{
caption: "Company Name", field: "Customers.CompanyName",
},
{
caption: "Year Quarter", field: `=DATEPART([@OrderDate],"yyyyQQQ")`,
},
{
caption: "Ship Via", field: "ShipVia",
summaryFields: [
{
formula: `=SUM([Freight]) / CALCULATE( SUM([Freight]), REMOVEFILTERS("ShipVia"))` // ratio of sum of freight under freight level to sum of freight under ship name
},
{
formula: `=SUM([Freight]) / CALCULATE( SUM([Freight]), REMOVEFILTERS("ShipVia", "=DATEPART([@OrderDate],""yyyyQQQ"")"))` // ratio of sum of freight under freight level to sum of freight under year quarter
},
{
formula: `=SUM([Freight]) / CALCULATE( SUM([Freight]), REMOVEFILTERS())` // ratio of sum of freight under freight level to sum of freight under all records
},
]
}
]);
Feedback
Submit and view feedback for