Documentation/Calc Functions/GETPIVOTDATA

From The Document Foundation Wiki
Jump to navigation Jump to search
Other languages:
English • ‎Nederlands



Function name:

GETPIVOTDATA

Category:

Spreadsheet

Summary:

Determines a value inside a pivot table by using the specific identifying categories for that value (rather than a cell reference). Since the value is addressed using field and item names, the correct value is returned when the underlying data or layout of the pivot table changes. GETPIVOTDATA can be used in a formula when you need to access pivot table results elsewhere in your spreadsheet.

Note pin.svg

Note:
It is beyond the scope of this wiki page to provide a full description of Calc’s pivot table functionality. Generic information about pivot tables can be found on Wikipedia’s Pivot table page. For specific information about pivot tables within Calc, visit our English documentation page and see Chapter 8 (Using Pivot Tables) in the latest Calc Guide.

Syntax:

Two different syntax definitions can be used:

First syntax
GETPIVOTDATA(Data field; Pivot table[; Field 1; Item 1][; ... [Field 126; Item 126]])
Second syntax
GETPIVOTDATA(Pivot table; Constraints)

Calc assumes the second syntax if exactly two arguments are given and the first argument is a reference to an existing pivot table; the first syntax is assumed in other cases. The Function Wizard uses the first syntax.

ODF 1.2 considers the first to be the "preferred" syntax, while the second is provided for "compatibility".

Returns:

Returns a value from the specified pivot table. The type of value returned depends on the nature of the data displayed in the pivot table.

Arguments:

First Syntax

Data field is a string (in quotation marks), or a reference to a cell containing that string, that indicates which data field of the pivot table is to be used. Enter the required field name from the underlying data source (for example "Sales Value") or the name of the data field itself (for example "Sum – Sales Value"). If the pivot table has more than one data field, then enter the required field name. Even if the pivot table has only one data field, you must enter this argument correctly anyway.

Pivot table is a reference to a cell or a cell range that lies within the results area of the relevant pivot table, bearing in mind that a sheet can contain multiple pivot tables. The name of a named area can be passed in this argument. It is recommended that you always use the upper left corner cell of the area, to ensure that the chosen cell(s) will always be within the pivot table even if its layout changes.

Field n is a string (in quotation marks), or a reference to a cell containing that string, that identifies one of the pivot table's fields to be matched to calculate a partial sum (for example, "Employee" or "Region"). For each Field n argument, there should be a matching Item n argument.

Item n is a string (in quotation marks), or a reference to a cell containing that string, that corresponds to a value of Field n that is to be used to calculate a partial sum (for example, "Fritz" or "North"). For an item that is a number, Item n can alternatively be that numerical value.

  • If you enter only the first two arguments (Data field and Pivot table), then GETPIVOTDATA returns the total result of the pivot table.
  • Each pair of Field n / Item n arguments adds a constraint that the result shall satisfy. The order of the Field n / Item n pairs is not significant. Field n and Item n names are case-insensitive.
  • If the pivot table contains only a single result value that fulfills all of the constraints, or a subtotal result that summarizes all matching values, that result is returned. If there is no matching result, or there are several with no subtotal for them, GETPIVOTDATA returns the #REF! error.
  • There are several other conditions that will lead to GETPIVOTDATA returning the #REF! error. These include: the Data field argument does not correspond to one of the pivot table's data field names; the Pivot table argument does not reference a cell or cell range within the pivot table; a Field n argument does not correspond to one of the pivot table's field names; an Item n argument is not a valid option for the corresponding Field n value; a Field n argument has no corresponding Item n argument.

Second Syntax

Pivot table has the same meaning as in the first syntax.

Constraints is a string (in double quotation marks), or a reference to a cell containing that string, that specifies the data to be extracted from the pivot table. The string provides a space-separated list of constraints and entries can include single quotes where necessary. Entries in the list are case-insensitive and their order is not significant.

  • One of the entries in the list of constraints can be a name that indicates which data field of the pivot table is to be used. Enter the required field name from the underlying data source (for example "Sales Value") or the name of the data field itself (for example "Sum – Sales Value"). The data field name can be omitted if the pivot table contains only one data field, otherwise it must be present.
  • Each of the other entries in the list specifies a constraint, normally in the form Field[Item] (for example, "Employee[Hans]" or "Region[South]"). However, if the item name is unique across all fields in the pivot table then the field name and square brackets may be omitted.
  • If Constraints is set to the empty string, then GETPIVOTDATA returns the total result of the pivot table.

For the second syntax, GETPIVOTDATA reports a #REF! error in similar circumstances to those described for the first syntax.

Additional details:

None

Examples:

The following pivot table will be used as the basis for the examples that follow.

Sample-pivot-table-data.png
Formula Description Returns
=GETPIVOTDATA("Sum - Sales Value"; $A$1) Here the function returns the grand total of the pivot table. Using the second syntax, equivalent formulas would be =GETPIVOTDATA($A$1; "") and =GETPIVOTDATA($A$1; "Sum - Sales Value"). 408765
=GETPIVOTDATA("Sales Value"; $A$1; "Employee"; "Fritz") Here the function returns the partial sum for employee "Fritz". Using the second syntax, equivalent formulas would be =GETPIVOTDATA($A$1; "Sales Value Employee[Fritz]") and =GETPIVOTDATA($A$1; "Fritz") 105453
=GETPIVOTDATA("Sales Value"; $A$1; "Category"; "Tennis") Here the function returns the partial sum for category "Tennis". Using the second syntax, equivalent formulas would be =GETPIVOTDATA($A$1; "Sales Value Category[Tennis]") and =GETPIVOTDATA($A$1; "Tennis") 142699
=GETPIVOTDATA("sales value"; $A$1; "employee"; "hans"; "category"; "sailing") Here the function returns the partial sum for employee "Hans" and category "Sailing". Using the second syntax, equivalent formulas would be =GETPIVOTDATA($A$1; "Employee[Hans] Category[Sailing] Sales Value") and =GETPIVOTDATA($A$1; "Hans Sailing") 14060

Related LibreOffice functions:

None.

ODF standard:

Section 6.14.4, part 2

Equivalent Excel functions:

GETPIVOTDATA