New features in GS-Calc 21.1:
- It’s now possible to display pivot table data fields horizontally, in one line along with given row fields’ values.
For example, the previous PivotData() function could only generate multiple data fields one below another:
In this version you have an option to display them in one row (and the function names are added automatically):
Setting this new layout option in cell formulas is performed by specifying the Pivot::HorzData flag, e.g:
=pivotData(‘Pivot tables\orders’!A1:G44, {3}, {7}, {6, 5}, {PIVOT::Sum, PIVOT::Sum}, PIVOT::RowGrandTotals + PIVOT::ColumnGrandTotals + PIVOT::HORZDATA, 7, “>=44256”, 7, “<=44270”)
In case of the pivot tables displayed in the separate view panes, this option can be turn on/off with the corresponding option in the pivot table setup dialog:
- The “Copy As Location” command that pre-creates a list of references for all “Insert [Formula]” dialog box composers has been split into three commands to make it easier to create the desirable reference. In the previous ver. the single copy command requires you to change the global settings which might not be obvious and could cause confusion when using these composers.
-
Like previously, in case of pivot tables in separate view panes, to copy them exactly in their forms, use the p. tables copy commands:
Saving reports as time-stamped worksheets generates a list of all column field values repeated for all row field values along with the data fields. -
Other minor changes: resizing columns in the pivot table view panes works same as in the worksheet: all columns within a given selected range are resized automatically.
-
A bug fix: when using the “pivot table setup” dialog box and editing pivot tables created in the separate view pane, if there are multiple row fields in the above dialog box “row fields” field, they were re-ordered according to their order in the source spreadsheet range, thus each table setup modification might require reordering them again manually in the setup dialog box.: