Perform a cross tabulation

A cross tabulation provides extra granularity from a summary without losing the highest level of data summarization.

In this tutorial, you will learn how to perform a cross tabulation in the 1010data Insights Platform Trillion-Row Spreadsheet (TRS) to determine the sales amount by store. The sales totals for each store will be broken out by department so you can see how much each department contributed to the total sales.

To perform a cross tabulation:

  1. From the menu bar, select Tools > Trillion-Row Spreadsheet.
    The Insights Platform displays the TRS window.

  2. Open the Sales Item Detail table (pub.demo.retail.item).
    TRS displays the New operation panel in the analysis pane on the left and the Sales Item Detail table in the results pane on the right.

To get the department information for your cross tabulation, you need to link in the Product Master table. This is identical to the process used in Link in a table.

  1. In the New operation panel, click Link.
    TRS displays the table browser in the Link tables panel.
  2. In the Suffix field at the bottom of the table browser, enter _pm.
  3. Browse for and select the Product Master table (pub.demo.retail.prod).
    TRS displays the link column options in the Link tables panel.
  4. In the After linking drop-down, select Keep all rows.
  5. Link in the table by the SKU columns.
    1. Drag the Item SKU column from the Sales Item Detail section to the blank section directly to the right.
    2. Drag the SKU column from the Product Master section to the blank section directly to the left.
  6. Click the Submit operation () icon.
    TRS links the Product Master table into the Sales Item Detail table and displays the results of the link.

  7. In the New operation panel, click Tabulate.
    TRS displays the Tabulation panel.

  8. Select the Show with column name checkbox.
    The Show with column name checkbox displays the column names instead of the column labels.

    Notice the _pm suffix that you specified is added to the end of the certain column names. This indicates that a column is a linked column from the Product Master table.

  9. Clear the Show with column name checkbox.
  10. Remove the default cnt (count) tabulation function by clicking the Remove () icon.
  11. Drag the Store column from the Worksheet Columns section to the Row Breaks section.
    This selection creates a row for each store in the cross tabulation.
  12. Drag the Department Description column from the Worksheet Columns section to the upper area of the Column Breaks section.
    This selection creates a column for each department in the cross tabulation. The Department Description will be parsed for its individual values and then summarized. In this case, a column for each department that had sales will be created.

Next, you need to select the actual summary type. The summary applies to both the row grouping and the column grouping. In this case, you are calculating total sales by department for each store.

  1. Drag the Sales column from the Worksheet Columns section to the lower area of the Column Breaks section.
    This selection chooses the column of data you want to act on. In this instance, you want to calculate the total sales.

    TRS adds the sum of Sales summarization and returns the Sales column to the Worksheet Columns section.

    The summarization is a combination of the selected column and the default tabulation function for that column's data type. In this case, sum is the default tabulation function for Sales. This means the sum of Sales summarization will calculate the total amount in the Sales column.

    The next step of a cross tabulation is to select the tabulation function you want to perform on the column. This is done by clicking the summarization (in this case, sum of Sales) and choosing a tabulation function from the menu. However, since the default tabulation function happens to be exactly what you are looking for, you do not need to make this selection.

    Notice that the selections you made in the Tabulation panel represent how your worksheet will display your analysis. In this example, your worksheet will contain stores as rows and department descriptions as columns. The intersection between the two will list the sales total for the store by department.

Next, define the format of the tabulation.

  1. Click the More Options switch.
    TRS displays the formatting options.

    Select sum of Sales in the Tabulation Column drop-down. This indicates that the name and formatting options you define apply to the sum of Sales tabulated column.

  2. Complete the following fields:
    Tabulation Label Enter Department Sales By Store
    Display Format Select Currency: $1,234.56.
    Column Width Select Default.
    Decimal Places Select Default.

  3. Click the Submit operation () icon.
    TRS displays the results of your cross tabulation.

    As you can see above, the totals for all the stores are still available in the first column outlined in red. However, you can also see how much each department contributed to those totals. For instance, the snacks department accounted for $24.30 of the $59.85 in total sales across all the stores in our fictional retail chain.

    Further granularity shows that in store 1, $12.30 was from the snacks department.

  4. When you are finished with this tutorial, close the TRS window.
Cross tabulations are a great way to get further insight into the numbers of regular tabulations. You can keep your totals, but see the story behind the numbers by breaking them down into their constituent parts. Next time you are not sure about why a summary looks the way it does, rely on a cross tabulation to better understand your data and your business.