|Cross tabulations ||
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:
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.
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.
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.
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.
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.
|Tabulation Label||Enter Department Sales By Store|
|Display Format||Select Currency: $1,234.56.|
|Column Width||Select Default.|
|Decimal Places||Select Default.|
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.