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;
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.
|
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.