About cross-tabulating

Cross-tabulating groups the records in a table based on unique combinations of values in two or more character fields, and counts the number of records in each group. You can also subtotal one or more numeric fields for each group. The resulting groups are displayed in a grid of rows and columns, similar to a pivot table, that allows you to visualize relations and patterns in the data. For example:

Cross-tabulating is similar to summarizing using two character fields. In both operations the counts and subtotals in the output results are the same, but the information is arranged differently. Cross-tabulating also displays counts and subtotals of zero, which summarizing does not. Depending on the type of analysis you are doing, displaying counts and subtotals of zero can be useful. Cross-tabulating works on sorted and unsorted tables.

Cross-tabulating in detail

Cross-tabulating performs the following operations:

Table 1. Operations performed by cross-tabulating

Operation

Location in Figure 1

Groups the records based on unique combinations of values in two or more character fields, and displays the groups in a grid of rows and columns

intersections of Cust Number field (rows) and Type field (columns)

Optionally subtotals the values of one or more numeric fields for each group

Amount field

Optionally subtotals the number of records for each group

Count field

Provides totals for all columns included in the output results

Totals row

Figure 1 provides an example of the results produced by cross-tabulating customer number and transaction type, and subtotaling on transaction amount, in an accounts receivable table (the ‘Ar’ table in ACL DATA\Sample Data Files\Sample Project.ACL) and outputting the results to screen.

Figure 1. Cross-tabulate results

Related tasks
Cross-tabulating data


(C) 2015 ACL Services Ltd. All Rights Reserved.