Using Pivot Tables

Pivot tables enable you to quickly and easily obtain and visualize various statistical information about data coming from flat source record tables. This includes (but is not limited to) counting, summarizing, sorting or finding partial maximum/minimum values.

In GS-Calc pivot tables are represented by pivotData() array formulas. Such a formula requires up to several parameters, thus it may be easier to use the Insert > Pivot Table composer rather then enter everything manually.

Pivot tables usage examples:

For a given table of customers (where that table contains among others the "Country" and "City" fields):

*

To find out what is the number of customers from each country:

  1. Open the Insert > Pivot Table dialog box.
  2. Select the entire customers' table range and click the Source Range button. Alternatively you can enter that range manually and click the Source Range > Reload Fields command.
  3. Choose the "Country" field as the only Row field.
  4. Select "Count" as the default function on the functions list in the "Data fields" section.
  5. Click a cell where the formula is to be inserted and click the Insert button.
*

To find out what is the number of customers from each country and how many of them come from a given city in that country:

  1. Open the Insert > Pivot Table dialog box.
  2. Select the entire products' table range and click the Source Range button. Alternatively you can enter that range manually and click the Source Range > Reload Fields command.
  3. Choose the "Country" field as the first Row field.
  4. Choose the "City" field as the second Row field.
  5. Select "Count" as the default function on the functions list in the "Data fields" section.
  6. Click a cell where the formula is to be inserted and click the Insert button.
*

For a given table of products (where that table contains among others the "ProductName", (order) "Date", "Qty" and "Total" fields):

*

To display the products sales (sales volume only) breakdown based on days:

  1. Open the Insert > Pivot Table dialog box.
  2. Select the entire products' table range and click the Source Range button. Alternatively you can enter that range manually and click the Source Range > Reload Fields command.
  3. Choose the "ProductName" field as the only Row field.
  4. Choose the "Date" database field as the only Column field.
  5. Choose the "Qty" field as the only Data field and select the Sum function for it.
  6. Click a cell where the formula is to be inserted and click the Insert button.
*

To display the products sales (sales volume and quantity) breakdown based on days:

  1. Open the Insert > Pivot Table dialog box.
  2. Select the entire products' table range and click the Source Range button. Alternatively you can enter that range manually and click the Source Range > Reload Fields command.
  3. Choose the "ProductName" field as the only Row field.
  4. Choose the "Date" field as the only Column field.
  5. Choose the "Total" field as the first Data field and select the Sum function for it.
  6. Choose the "Qty" field as the second Data field and select the Sum function for it.
  7. Click a cell where the formula is to be inserted and click the Insert button.
*

PivotData formula

Pivot formulas have the following form:

pivotData(source, rows, columns, data, functions, options [, field1, filter1, field2, filter2, ...])

The above formula creates and returns a pivot table for the data in the 'source' range.

The 'rows', 'columns' and 'data' parameters represent arrays/ranges containing the respective pivot field indices. The indices are relative to the top-left corner of the 'source' range and the numbering starts from 1.
For example: {1, 5}, {4}, {1}
If some of the indices are incorrect, pivotData returns the #VALUE! error. In GS-Calc 9.0 the 'columns' array can contain only one element. The 'data' fields can be omitted, in which case the last specified row field and the default pivot function will be used (for the last row field).

The 'functions' argument is an array of predefined functions constants/IDs associated with the specified data fields. The possible values are:

For example: {PIVOT::Sum}, {PIVOT::Sum, PIVOT::Count}

Note: You must specify either the PIVOT::ColumnGrandTotals or at least one column field.
Otherwise the function will return the #NUM! error code.

The 'options' parameter can be any sum of the following constants:

For example: {PIVOT::ColumnGrandTotals}, {PIVOT::ColumnGrandTotals + PIVOT::RowGrandTotals + PIVOT::SubTotals}

The optional [field, filter] pairs specify the 1-based field index and the condition. Only source data meeting all the specified conditions will be included in the pivot table. Conditions can have the following form:

Pivot formula examples:

=pivotData(C3:H19, {1},,, {PIVOT::Sum}, PIVOT::ColumnGrandTotals)

=pivotData(sheet1!C3:H19, {1}, {2}, {3, 4}, {PIVOT::Sum, PIVOT::Count}, PIVOT::RowGrandTotals + PIVOT::ColumnGrandTotals + PIVOT::SubTotals)

=pivotData(sheet1!B3:F8, {5, 1}, {3},, {PIVOT::Sum}, PIVOT::RowGrandTotals + PIVOT::ColumnGrandTotals + PIVOT::SubTotals, 2, "Jones", 4, ">2010-01-01")