Documentation/Calc Functions/GETPIVOTDATA

From The Document Foundation Wiki
Jump to: navigation, search

Function name:

GETPIVOTDATA

Category:

Spreadsheet

Summary:

The GETPIVOTDATA function returns a result value from a pivot table. The value is addressed using field and item names, so it remains valid if the layout of the pivot table changes.

Syntax:

Two different syntax definitions can be used:

GETPIVOTDATA(TargetField; pivot table[; Field 1; Item 1][; ... [Field 126; Item 126]])

or

GETPIVOTDATA(pivot table; Constraints)

Im-jabber.svg

Note:
The second syntax is assumed if exactly two parameters are given, of which the first parameter is a cell or cell range reference. The first syntax is assumed in all other cases. The Function Wizard shows the first syntax.

Returns:

Returns a value stored in given the pivot table which is a single result from the calculation of a data pilot table addressed using field and the item name.

Arguments:

Syntax 1

TargetField is a string or a reference to the cell containing that string which will select one of the pivot table's data fields. The string can be the name of the source column, or the data field name as shown in the table (like "Sum - Sales").

pivot table is a reference to a cell or cell range or a named area that is positioned within a pivot table or contains a pivot table. If the cell range contains several pivot tables, the table that was created last is used.

It is possible that your document contains more than one pivot table. Enter here a cell reference that is inside the results area of your pivot table. It might be a good idea always to use the upper left corner cell of your DataPilot, so you can be sure that the cell will always be within your pivot table even if the layout changes.

Field 1 selects one of the data pilot table's data fields. It can be the name of the source column or the given name of the data field (such as “Sum of Sales”).

Item 1 is the corresponding item in the field-item pair for Field 1. It can be a string or a name or a reference to a cell (ranges).

Field 2 and all are following the same description as Field 1.

Item 2 and all are following the same description as Item 1.

If no Field i / Item i pairs are given, the grand total is returned. Otherwise, each pair adds a constraint that the result must satisfy, where Field i, Item i are the names of a field and item from the pivot table

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 several ones without a subtotal for them, an error is returned. These conditions apply to results that are included in the pivot table.

If the source data contains entries that are hidden by settings of the pivot table, they are ignored. The order of the Field/Item pairs is not significant. Field and item names are not case-sensitive.

If no constraint for a page field is given, the field's selected value is implicitly used. If a constraint for a page field is given, it must match the field's selected value, or an error is returned. Page fields are the fields at the top left of a pivot table, populated using the "Page Fields" area of the pivot table layout dialog. From each page field, an item (value) can be selected, which means only that item is included in the calculation.

Subtotal values from the pivot table are only used if they use the function "auto" (except when specified in the constraint, see Second Syntax below).

  • If a Field or Item is not available then the function returns #REF!.

Syntax 2

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

Constraints is a space-separated list. Entries can be quoted (single quotes). The whole string must be enclosed in quotes (double quotes), unless you reference the string from another cell.

One of the entries can be the data field name. The data field name can be left out if the pivot table contains only one data field, otherwise it must be present.

Each of the other entries specifies a constraint in the form Field[Item] (with literal characters [ and ]), or only Item if the item name is unique within all fields that are used in the pivot table.

A function name can be added in the form Field[Item;Function], which will cause the constraint to match only subtotal values which use that function. The possible function names are Sum, Count, Average, Max, Min, Product, Count (Numbers only), StDev (Sample), StDevP (Population), Var (Sample), and VarP (Population), case-insensitive.

Additional details:

  • A pivot table (formerly known as DataPilot) allows you to combine, compare, and analyze large amounts of data. You can view different summaries of the source data, you can display the details of areas of interest, and you can create reports. A table that has been created as a pivot table is an interactive table. Data can be arranged, rearranged or summarized according to different points of view.
  • For more details on pivot tables, visit pivot table or download SamplePivotTable

Examples:

We will use the below Pivot table for applying all formulas:

Getpivotdata table.png
Formula Description Returns
=GETPIVOTDATA("Sum - sales";A1;"employee";"Hans";"category";"sailing") The function returns the partial sum of "Hans" for "sailing" from the above pivot table. Here we are using Syntax 1. $14,060
=GETPIVOTDATA("Sum - sales";A1) If you enter only the first two arguments, then the function returns the total result of our pivot table. Here we are using Syntax 1. $408,765
=GETPIVOTDATA("sales";A1) It will also return the total result of our pivot table. The field name can be entered exactly as it looks on the sheet, or as its root. Here we are using Syntax 1. $408,765
=GETPIVOTDATA(A1;"sales value employee[Hans] category[sailing]") The function returns the partial sum of "Hans" for "sailing" from the above pivot table. Here we are using Syntax 2. $14,060
=GETPIVOTDATA("Sum - sales";A1;"employee";"Tron";"category";"sailing") The function returns a reference error since the "Tron" named empolyee is not available in the above pivot table. Here we are using Syntax 1. #REF!

Related LibreOffice functions:

None

ODF standard:

Section 6.14.4, part 2

Equivalent Excel functions:

GETPIVOTDATA