PIVOTBY
Description
Summarizes data by grouping, aggregating, sorting, and filtering based on specified row and column fields. This function is the same as WD.PIVOTBY but
without
a style parameter. This function is useful for dynamic cross-tabulation and advanced data summarization directly within a formula.
Syntax
PIVOTBY(
row_fields
, col_fields
, values
, function
, [field_headers
], [row_total_depth
], [row_sort_order
], [col_total_depth
], [col_sort_order
], [filter_array
], [relative_to
]) - row_fields: The values to use when grouping rows.
- col_fields: The values to use when grouping columns.
- values: The values to aggregate.
- function: The calculation to run when aggregating.
- field_headers: A number that specifies whether the data has headers and whether you want to include them in the results. Options include:
- Omitted value (default): Headers are not shown.
- 0: No headers.
- 1: Yes, but don’t show headers.
- 2: No headers, but generate them.
- 3: Yes, and show headers.
- row_total_depth: A number that specifies whether to show totals and subtotals. For subtotals,row_fieldsmust have at least 2 columns. Options include:
- Omitted value (default): Grand totals and, where possible, subtotals.
- 0: No totals.
- 1: Grand totals at the bottom.
- 2: Grand totals and subtotals at the bottom.
- -1: Grand totals at the top.
- -2: Grand totals and subtotals at the top.
- row_sort_order: A number that indicates how rows should be sorted. Numbers correspond to columns inrow_fields, followed by columns in values. A positive number sorts rows in ascending order. A negative number sorts rows in descending order.
- col_total_depth: A number that specifies whether to show totals and subtotals. For subtotals,col_fieldsmust have at least 2 columns. Options include:
- Omitted value (default): Grand totals and, where possible, subtotals.
- 0: No totals.
- 1: Grand totals on the right.
- 2: Grand totals and subtotals on the right.
- -1: Grand totals on the left.
- -2: Grand totals and subtotals on the left.
- col_sort_order: A number that indicates how columns should be sorted. Numbers correspond to columns incol_fields, followed by columns in values. A positive number sorts rows in ascending order. A negative number sorts rows in descending order.
- filter_array: A column-oriented array of booleans that indicates which rows should be included or excluded from the output.
- relative_to: A number that indicates where to find values for the second argument of an aggregation function. This is typically used when PERCENTOF is supplied to function. Options include:
- 0 (default): Column totals.
- 1: Row totals.
- 2: Grand total.
- 3: Parent column total.
- 4: Parent row total.