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.

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 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.