Calculated Columns
TableSheet uses SpreadJS powerful calculation engine to add calculated columns to your views. You can create columns with simple column references or advanced summary and logical functions.
In the following sample, we have multiplied Unit Price and Unit In Stock to have a calculated column for Stock Value.
You can add the calculated columns to the table, and the calculated columns can be referenced by the View, Window function, and Query method. The calculated columns can reference the data columns, the related table columns, and other calculated columns.
The calculated columns in the table are defined as follows:
var productTable = dataManager.addTable("productTable", {
data: productTable,
schema: {
columns: {
TotalUnits: {
dataType: "formula",
value: "[@UnitsInStock] + [@UnitsOnOrder]"
},
StockValue: {
dataType: "formula",
value: "[@UnitPrice] * [@TotalUnits]"
}
}
}
});
The custom view supports calculated columns too, and it can reference the data columns and calculated columns in the table.
This is the sample code.
//add a view with column formula
var myView = productTable.addView("myView", [
{ value: "productId", caption: "ID"},
{ value: "productName", caption: "Name", width: 400 },
{ value: "unitPrice", caption: "Unit Price", width: 100 },
{ value: "unitsInStock", caption: "Units In Stock", width: 100 },
{ value: "unitsOnOrder", caption: "Units On Order", width: 100 },
{ value: "TotalUnits", caption: "Total Amount", width: 100 },
{ value: "=[@unitPrice] * [@TotalUnits]", caption: "Total Price", width: 100 },
{ value: "=[@StockValue]/SUM([UnitPrice] * [TotalUnits])",
caption: "Stock Value Ratio", style: {formatter: "0.00%"}, width: 160 }
]);
The calculated columns can reference other columns by using structured references like "=[@column1]"; The reference must consist of at least one row ref or column ref.
Row Ref
| Reference | Description | Sample |
|---|---|---|
| Current Row | Reference the current row | [@column1] the data in the current row of field column1 |
| Relative Row | Reference the relative row | [@+1column1] returns column1 value in the next row [@-1column1] returns column1 value in the previous row. |
| Absolute Row | Reference the row of absolute position by row number or "last"/"odd"/"even" | [#1[column1]] returns column1 values in first row. [#last[column1]] returns column1 value in the last rows |
| Absolute Row by ID | Reference the row by the primary key | [$1234[column1]] returns column1 values of data with primary key "1234" |
| Row Range | Reference the range of rows by from and to | [@:#last[column1]] returns column1 values of rows from current row to the last row |
| Several Rows | Reference several rows by comma | [#1,@-1:@+1,#last[column1]] returns column1 values in first row, previous row to the next row, and the last row. |
Column Ref
| Reference | Sample |
|---|---|
| Regular Field | [@column1] |
| Attribute of object field | [@column1.abc] get the attribute value of "abc" in the current row of field column1 |
| Item of array field | [@column1.1] get the first value in the current row of field column1 |
| Special Field | [@[column 1].[a aa]] use the square brackets means a certain field |
| Whole Column | [column1] get all the data in the column1 |
Feedback
Submit and view feedback for