A tabulation summarizes large amounts of data into a small, easy-to-read table. Perform a tabulation to group the values in a column based on the values in another column.
A tabulation allows you to calculate various metrics, such as the sum, average, or highest value of a particular column, grouping those calculations based on common values in one or more other columns. For instance, in a table containing weather data, you could calculate the highest temperature during the year for every unique combination of ZIP Code and month in the table.
To perform a tabulation:
- In the New operation panel, click Tabulate. The 1010data Insights Platform displays the Tabulation panel.Note: By default, a new Tabulation panel contains the cnt (count) tabulation function.
- If applicable, remove the default tabulation function by clicking the Delete Function () icon in the field.
- For each column by which you want to group the records, complete the following:
- Drag the column from the Worksheet Columns section to the Row Breaks section. This groups your tabulation by the selected column. Grouping is a way of pooling all the records for a single entity or value into a single entry in the table.Note: In most cases, the order by which you choose to group the data is important because it can affect the results of the tabulation.The Insights Platform moves the column to the Row Breaks section.
- Optionally, set the sort order of the column by either clicking the Sort Up () or Sort Down () icon.
All rows that have the same values for all of the columns specified will be considered part of one group.
- Drag the column from the Worksheet Columns section to the Row Breaks section.
- Optionally, use a rollup in your tabulation. A rollup provides a subtotal of the summarizations as a row in the resultant table. For instructions, see Use a rollup in a tabulation.
- For each column of data that you want to summarize, complete the following:
- Drag the column containing the data you want to summarize from the Worksheet Columns section to the lower area of the Column Breaks section. This selection chooses the column of data you want to summarize.The Insights Platform adds a tabulation function field and returns the column to the Worksheet Columns section.
- Click the tabulation function field. The Insights Platform displays the Tabulation Function menu.
- Select the type of summarization you want to perform. For information about the types of summarizations available, see Types of Summarizations for Tabulations.Note: If the type of summarization you are performing requires a second column (a reference column), the available columns are displayed after selecting the summarization. Select the reference column from the Tabulation Function menu.The Insights Platform updates the tabulation function with the selected summarization.
- Drag the column containing the data you want to summarize from the Worksheet Columns section to the lower area of the Column Breaks section.
By default, columns in the resultant worksheet are named t0, t1, t2, and so on. You can give tabulated columns more meaningful names within the More Options view of the Tabulation panel. In addition, you can define the various formats of the tabulation.
- Optionally, define the format of the tabulation results. For instructions, see Format the tabulation results.
- Click the Submit operation () icon. The Insights Platform displays the results of your tabulation.