Create a computed column from a cross-tabulated column

After you have a completed a cross tabulation, you can work with the resultant columns of the new worksheet the same as you would with any other 1010data Insights Platform table. This makes it easy to create new metrics based on your summaries.

In this tutorial, you will use the Trillion-Row Spreadsheet to perform a simple day-over-day calculation by first creating a cross tabulation and then creating a computed column.

In your analysis, you want to see the sales of one date compared to the sales of a different date for all three of your fictional stores. For this example, the dates are consecutive.

To create a computed column from a cross-tabulated column:

  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.

Next, perform a row selection to limit the data to the two days in which you are interested.

  1. In the New operation panel, click Select.
    TRS displays the Simple Comparisons options in the Select rows panel.
  2. Clear the Simple Comparisons checkbox.
    TRS displays the expression field.

  3. In the expression field, enter the following: between(date;20120517;20120518)
  4. Press Enter.
    TRS displays the results of your selection.

    Next, you will create your cross tabulation. This will give you the totals for both the stores overall, and the totals for each of the two dates you selected.

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

  6. Remove the default cnt (count) tabulation function by clicking the Remove () icon.
  7. 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.
  8. In the Store column within the Row Breaks section, click the Sort Up () icon.
    This selection sorts the cross tabulation results in ascending order by store number.
  9. Drag the Date column from the Worksheet Columns section to the upper area of the Column Breaks section.
    This selection creates a column for each date for which the store had sales.
  10. 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 tabulation function and returns the Sales column to the Worksheet Columns section.

The next step of a cross tabulation is to select the tabulation function you want to perform on the column. In this case, you want to summarize the data in the sales column. The summary applies to both the row grouping and the column grouping. In other words, you are calculating total sales by date for each store. This is done by clicking the summarization (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.

Next, define the format of the tabulation.

  1. Click the More Options switch.
    TRS displays the formatting options.
  2. Complete the following fields:
    Tabulation Label Enter Sales by Store for 5/17 and 5/18
    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.

    Since you eliminated all the dates except the two you are interested in, you can now create a computed column that shows the difference in sales from one day to the next.

  4. In the New operation panel, click Compute.
    TRS displays the Computed column panel.

  5. Complete the following fields:
    Name Enter dod.

    This is a required field.

    Label Enter Day-Over-Day.

By default, resultant columns in cross tabulations are named m0, m1, m2, and so on.

In this case, to calculate the day-over-day sales, simply subtract the values in the column with the less recent date (m0) from the values in the column with the more recent date (m1).

  1. In the expression field, enter the following: m1-m0

Next, define the format of the computed column.

  1. Click the More Options switch.
    TRS displays the formatting options.
  2. Complete the following fields:
    Display Format Select Currency: $1,234.56.
    Column Width Select Default.
    Decimal Places Select Default.
  3. Click the Submit operation () icon.
    TRS creates the Day-Over-Day computed column.

    From the results, it is clear that the sales for store 2 plummeted on the last day.

  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're not sure about why a summary looks the way it does, rely on cross tabulations to better understand your data and your business.