Perform a cross tabulation

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

1. Open the Sales Item Detail table (pub.demo.retail.item).
   1010data displays the Sales Item Detail table.

   

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.

2. Click Columns > Link in Another Table.
   1010data displays the Link in Another Table dialog.
3. Under the Select table section, click the Product Master table link.
   1010data displays the Select columns section in the dialog.
4. Select Item SKU from the first drop-down list.
5. In the first drop-down list under the Corresponding Column(s) section, select SKU.
6. In the Suffix field, enter _pm.
7. Click Submit.
   1010data links the Product Master table into the Sales Item Detail table and displays the results of the link.

   

8. Click Analysis > Cross Tabulation.
   1010data displays the Cross Tabulation dialog.

   

9. Enter Sales by Store by Department in the Title field.
10. Under the What values do you want to use to group the records? section, complete the following:
    1. In the first drop-down list under Rows of Result, select Store.
       This selection creates a row for each store in the cross tabulation.
    2. In the first drop-down list under Columns of Result, select Department Description.
       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, we are calculating total sales by department for each store.

11. Under the Which summary data would you like to see? section, complete the following:

    Column	Select Sales. This selection chooses the column of data you want to act on. In this instance, you want to calculate the total sales.
    Type of Summary	Select Sum. This selection chooses the action you want to perform on the selected column. In this case, you want to summarize the data in the sales column.
    Reference Column	Leave this field blank.
    Display Format	Select Currency: $1,234.56.
    Column Width	Select Default.
    Decimal Places	Select Default.

    

12. Click Submit.
    1010data 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.

13. When you are finished with this tutorial, close the Sales Item Detail worksheet.