A tabulation allows you to group the values in a column (or columns) based on the values in another column (or columns) and summarize the data for each group.
To perform a tabulation on one or more columns in a table or worksheet:
- In an open table or worksheet, click
The Tabulation dialog is presented.
- Enter a Title for the tabulation. (optional)
- For each column by which you would like to group the records:
- From the Column drop-down list, select the desired column.
- If you want the data in this column sorted, select the order from the Sort drop-down.
- If you would like a subtotal of the summarizations for this particular group included as a row in the resultant table, select Roll up.
All rows that have the same values for all of the columns specified will be considered part of one group.Note: To change the number of grouping columns that appear in the Tabulation dialog, click and change the Number of Grouping Columns for Tabulation setting under the User Interface section.
- For each column of data that you want to summarize:
Note: To change the number of summarizations that appear in the Tabulation dialog, click and change the Number of Summarizations for Tabulation setting under the User Interface section.
- From the Column drop-down list, select the column you would like to summarize.
- From the Type of Summary drop-down list, select the type of summarization. Note: For the number of records per group (i.e., number of records), you don't have to specify a column.
- If the type of summarization you are performing requires a second column, select that column from the Reference Column drop-down list.
See Types of Summarizations for Tabulations for which calculations require a reference column.
By default, 1010data gives the resultant summarization columns generic headings (e.g., Sum of Sales, Highest Cost) and system-generated column names (e.g., t0, t1). If you want to give the resultant summarization columns more meaningful headings and/or names, or if you would like to specify display formats for the data in those columns (i.e., type, width, decimal places), provide that information as described below:
- To display the inputs for the additional attributes of the resultant summarization columns, either click the icon to show the inputs as a table or click the icon to show the inputs sequentially in a list.
- In the Result Name text box, enter the name that you wish to give the result column. (optional) The result name is a way of referring to this column internally in value expressions and selection expressions. The result name must be a single word consisting of alphanumeric characters or underscores, and must begin with an alphabetic character (e.g., total_sales_by_store). It may not contain any spaces or other special characters.Note: This is different from the Result Heading, which is the text displayed at the top of the result column.
- In the Result Heading text box, enter the text that will appear at the top of the result column when the table is displayed. (optional) The result heading may contain any combination of uppercase and lowercase letters, numbers, spaces, and special characters. If you would like the result heading to have more than one line, use ` to separate the lines (e.g., Total Sales`by Store).Note: On most American keyboards, the ` character is immediately to the left of 1.
- Select the Display Format from the drop-down list. (optional) The display format for the result column specifies how the values will be displayed in the table.Note: If set to Default, the format is determined by the data.
For example, dates are stored as integers in 1010data (in YYYYMMDD form). To display a date as MM/DD/YYYY, choose the appropriate display format from the drop-down.
- Select the Column Width from the drop-down list. (optional) This is the desired width of the result column (number of characters).Note: If set to Default, the width is determined by the data.
- Select the Decimal Places from the drop-down list. (optional) This is the number of decimal places to show for numbers in the result column.Note: If set to Default, the number of decimal places is determined by the data, and may be different for each row.
- Click Submit.
The result of a tabulation is a dynamic table that may be manipulated just like any other table. You may download the results, select rows, sort rows, rearrange columns, create computed columns, link in other tables, and even apply another tabulation or cross tabulation.
Missing values in the data are handled automatically. For example, if you are computing the average value of a column that has some missing (N/A) values in a particular group of rows, those values are ignored when computing the average. See the descriptions of the individual types of summarizations to see how N/A values are handled for each.
The following table is the result of a tabulation that calculates the sum of sales for each unique grouping of both store and transaction ID, with a roll up selected for transaction ID. (The roll ups are circled in red.)