2.2.4.1 wpivot


Contents

Menu Information

Restructure: Pivot Table

Brief Information

Create pivot table to summarize worksheet data

Additional Information

Minimum Origin Version Required: Origin 2015 SR0

Command Line Usage

wpivot row:=col(4) col:=col(6) data:=col(11) method:=sum total:=1 sort_total:=descend sum:=1;

wpivot row:=(col(4):col(5)) col:=col(8) data:=col(9) method:=sum total:=1 sort_total:=no sum:=1 sort_col:=des;

wpivot row:=(col(4),col(6)) col:=col(8) method:=count dir:=col vmode:=limitValTotal threshold:=3;

X-Function Execution Options

Please refer to the page for additional option switches when accessing the x-function from script

Variables

Display
Name 		Variable
Name 		I/O
and
Type 		Default
Value 		Description
Pivot Table Row Source row

Input

Range

<active>
 Specify the range that will be used as the row source of the Pivot Table. Every distinct value of the selected range will correspond to a row in the pivot table. For example, if the table row source contains 5 cells and the values are "East", "South", "West", "South" and "East", there will be 3 rows in the pivot table. To do this analysis, you must active or specify at least 1 column for the row source.

There are three popular options:

  • col(1)
    select column 1 as row source
  • (col(1):col(3))
    select from column 1 up to column 3 three columns as row source
  • (col(1), col(3))
    select column 1 and column 3 two columns as row source
Pivot Table Column Source col

Input

Range

Specify the range that will be used as the column source of the Pivot Table. Every distinct value of the selected range will correspond to a column in the pivot table. To do this analysis, you must specify at least 1 column for the column source. Options syntax follows Pivot Table Row Source.
Pivot Table Data Source data

Input

Range

This is available only when Count is not selected for Summarize by (the Method variable). It provides the data values to be summarized.
Summarize by method

Input

int

0
 Specify the way to summarize the data.

Option list:

  • count:Count {0}
    Output the count of each Row/Column pair.
  • sum:Sum {1}
    Output the sum of each Row/Column pair.
  • mean:Mean {2}
    Output the mean of each Row/Column pair.
  • min:Min {3}
    Output the minimum value of each Row/Column pair.
  • max:Max {4}
    Output the maximum value of each Row/Column pair.
Combine Direction dir

Input

int

0
 This variable defines whether to combine the count/sum/mean/min/max for smaller values into an "others" column/row.


Option list:

  • none:None {0}
    Do not combine any values
  • col:Column {1}
    Combine the columns for the smaller values.
  • row:Row {2}
    Combine the rows for the smaller values.
Mode vmode

Input

int

0
 Specify the mode for qualifying smaller values. This option applies only when dir is set to Column (1) or Row (2).


Option list:

  • perTotal:By Percent of Grand Total{0}
    Merge columns/rows whose Total value is less than threshold percent of the Grand Total.
  • perRefRow:By Percent of Reference Row/Col{1}
    Merge columns/rows whose value is less than threshold percent of the Total of the reference row/column (refrow).
  • limitValTotal:Top n of Grand Total{2}
    Merge columns/rows whose Total values are not among the top N values. That is, show top N values and merge all others.
  • limitValRefRow:Top n of Reference Row/Col{3}
    Merge columns/rows whose values in the reference row/column are not among the top N.
Reference Row/Col refrow

Input

string

<unassigned>
 Specify the reference row/column when either By Percent of Reference Row/Column or Top N of Reference Row/Column is selected for Mode.
Threshold threshold

Input

double

Specify the percent or number for merging smaller values when either By Percent of Grand Total or By Percent of Reference Row/Column is selected for Mode
Value Label label

Input

string

Others
 Specify the label for the column/row which is formed by merging the smaller value columns/rows.
Totals for Rows total

Input

int

0
 Specify whether to add a Row Total column. Each cell in this column contains the sum of the data in the same row.
Sort Output Rows sort_total

Input

int

1
 Specify the method used to sort the rows in the pivot table.

Option list:

  • no:Row Labels Ascending{0}
    Sort the rows according to the Row Labels in ascending order.
  • descend:Descending by Row Totals{1}
    Sort Row Totals in descending order.
  • ascend:Ascending by Row Totals{2}
    Sort Row Totals in ascending order.
  • ldescend:Row Labels Descending{3}
    Sort the rows according to the Row Labels in descending order.
  • none:None{4}
    Do not sort the rows.
Totals for Columns sum

Input

int

0
 Specify whether to add a Column Total row. Each cell in this row contains the sum of the data in the same column.
Sort Output Columns sort_col

Input

int

0
 Specify the method used to sort the columns in the pivot table.

Option list:

  • lasc:Column Labels Ascending{0}
    Sort the columns according to the Column Labels in ascending order.
  • ldes:Column Labels Descending{1}
    Sort the columns according to the Column Labels in descending order.
  • asc:Ascending by Column Totals{2}
    Sort Column Totals in ascending order.
  • des:Descending by Column Totals{3}
    Sort Column Totals in descending order.
  • no:None{4}
    Do not sort the columns.
Normalize by Column Totals normalize

Input

int

0
 Specify whether to normalize the output data by the Column Totals.

Option list:

  • no:None{0}
    Do not normalize the data.
  • frac:Fraction{1}
    Normalize the data in each column by the column total and show them in fraction notation.
  • perc:Percent{2}
    Normalize the data in each column by the column total and show them in percent notation.
Show Zeros when Empty zero

Input

int

1
 Specify whether to show missing values in the pivot table as zeros.
Row Source Extra Values exrow

Input

string

<unassigned>
 ' character. This is useful when you must have a standard report, but your data does not include some values.
Column Source Extra Values excol

Input

string

<unassigned>
 ' character. This is useful when you must have a standard report, but your data does not include some values.
Put Column Info. to pos

Input

int

1
 Specify where to output column source info in Pivot Table.

Options List

  • name:Long Name{0}
    Output source info into Long Name row.
  • comments:Comments{1}
    Output source info into Comments row.
  • append:Append to Column's Long Name{2}
    Append source info to current Column's Long Name.
  • udl:User Defined Parameters{3}
    Output source info into User Defined Parameters row. By selecting this item, you can specify the identity in the following drop list to substitute the characters of "User Defined Parameters" displayed in Column Label Rows.
From Column udlabel

Input

string

<unassigned>

Specify the identifier of the pivot table column label row. Available only when pos:=3 or udl. Option list:

  • G:Short Name
  • L:Long Name
  • C:Comments
  • U:Units
  • <custom>

<custom> corresponds to the name of user defined parameters.

Output Result Table to rd

Output

ReportData

[<input>]<new>
 Specify where to output result table.

Description

This X-Function allows you to specify two or more columns to create a pivot table to visualize data summarization by a number of options including statistics of a third column.

Examples

About the detail examples, please refer to the Pivot Table tutorial page.