TableSheet Calculations

TableSheet uses an enhanced calculation engine to help users to create and analyze data in the current view as well as a normal spreadsheet.

Calculated Columns

You can add calculated columns by using simple column references or advanced summary and logical functions. These calculated columns can be created using structured references like "=[@column1]" and they also support CalcEngine functions.

The reference consists of at least one row or column reference. You can find all the available row and column references in the following table.

Reference	Description and Example
Row References
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 a comma. [#1,@-1:@+1,#last[column1]] - Returns column1 values in first row, previous row to the next row, and the last row.
Column References
Regular Field	Reference the current field. [@column1] - The data in the current field column1.
Attribute of an object field	Reference the attribute of the current field. [@column1.abc] - Returns the attribute value of "abc" in the current row of field column1.
Item of an array field	Reference the index item of the current field. [@column1.1] - Returns the first value in the current row of field column1.
Special Field	Reference the attribute of the current field with spaces. [@[column 1].[a aa]] - Returns a certain field using the square brackets.
Whole Column	Reference the whole field. [column1] - Returns all the data in column1.

The following code sample shows how to create a calculated column where the "Total Price" column is defined by the formula "=([@unitPrice] * [@taxRate])/100 + [@unitPrice]".

//bind a view to the tablesheet
myTable.fetch().then(function () {
    var view = myTable.addView("myView", [

        { value: "stockItem", width: 300, caption: "Stock Item" },
        { value: "unitPrice", width: 120, caption: "Unit Price" },
        { value: "taxRate", width: 120, caption: "Tax Rate" },
        { value: "=([@unitPrice] * [@taxRate])/100 + [@unitPrice]", caption: "Total Price", width: 150 } // calculated column
    ]);
    sheet.setDataView(view);
});

Reference Calculated Columns in View

You can add virtual columns in the DataManager table using the formula datatype, which can refer to columns such as data columns, table columns, or other formula columns. These formula columns can be used in the View of the tableSheet.

For example, the following image illustrates how the formula datatype is used to create the virtual columns “Running Sum” and “Stock Value Ratio”.

ts-formulacalculatedcolumn

The following code sample shows how formula datatype is used to create the virtual column, and then those virtual column definitions are used to create the tablesheet’s view.

// Add product table.
var productTable = dataManager.addTable("productTable", {
    remote: {
        read: {
            url: baseApiUrl + "/Product"
        }
    },
    schema: {
        columns: {
            TotalUnits: {
               dataType: "formula",
               value: "[@UnitsInStock] + [@UnitsOnOrder]"
            },
            StockValue: {
                dataType: "formula",
                value: "[@UnitPrice] * ([@TotalUnits])"
            }
        }
    }
});

// Bind a view to the table sheet
var myView = productTable.addView("myView", [
    { value: "Id", caption: "ID", width: 50 },
    { value: "ProductName", caption: "Name", width: 170 },
    { value: "UnitPrice", caption: "Unit Price", style: { formatter: "$#,##0.00" }, width: 120 },
    { value: "UnitsInStock", caption: "Units In Stock", width: 120 },
    { value: "TotalUnits", caption: "Total Units", width: 120 },
    { value: "StockValue", caption: "Stock Value", style: { formatter: "$#,##0.00" }, width: 120 },
    { value: "=SUM([#1:@[UnitPrice]]*([#1:@[UnitsInStock]]+[#1:@[UnitsOnOrder]])", caption: "Running SUM", style: { formatter: "$#,##0.00" }, width: 150 },
    { value: "=[@StockValue]/SUM([UnitPrice] * ([UnitsInStock] + [UnitsOnOrder]))", caption: "Stock Value Ratio", style: { formatter: "0.00%" }, width: 160 }
]);

Reference TableSheet in a Worksheet

You can use structured references to reference the tablesheet in a worksheet just like a table. This helps to cross-reference and summarize data in the tablesheet to create advanced report views.

Like a table reference, you can set the tablesheet name and the column caption in a formula. It also supports the A1/R1C1 reference style, although it is not as easy to read.

For example, the following GIF illustrates the use of functions such as SUM, COUNT, and UNIQUE where the tablesheet reference is “SUM(Tablesheet1[Total Price])" and so on.

The reference style can also be used in cell formulas, charts, and conditional formats.

The following code sample shows how to set a tablesheet reference in a worksheet.

// Create a new sheet and add it at 2nd position.
var sheet1 = new GC.Spread.Sheets.Worksheet();
sheet1.name("TS Reference in Formula");
spread.addSheet(1, sheet1);
sheet1.setColumnWidth(0, 250);
sheet1.setColumnWidth(1, 150);

// TS Reference in WorkSheet Formulas
sheet1.setValue(0, 0, "Stock Items Count")
sheet1.setFormula(0, 1, "=COUNTA(TableSheet1[Stock Item Key])");
sheet1.setValue(1, 0, 'Sum of Total Price')
sheet1.setFormula(1, 1, '=SUM(TableSheet1[Total Price])');
// unique tax rates
sheet1.setValue(3, 0, 'Different Tax Rates are:-')
sheet1.setFormula(3, 1, '=UNIQUE(TableSheet1[Tax Rate])'); // it returns a dynamic array and we have already set allowDynamicArray to true initially

You can use the QUERY function to fetch data from Data Manager tables. To know more, refer to the QUERY function in Formula Reference.