Create a computed column from a cross-tabulated column

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

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

   

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

2. Click Rows > Select Rows.
   1010data displays the Select Rows dialog.
3. From the drop-down list preceding the first is between label, select Date. In the next field, enter 20120517, and in the last field, enter 20120518.

   

4. Click Select.
   1010data 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. Click Analysis > Cross Tabulation.
   1010data displays the Cross Tabulation dialog.

   

6. Enter Sales by Store by Date in the Title field.
7. Under the What vales 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 Sort, select Up.
      This selection sorts the new rows in ascending order by store number.
   3. In the first drop-down list under Columns of Result, select Date.
      This selection creates a column for each date for which the store had sales.

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

8. 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.

   

   Here, your horizontal (row) summary is grouped by store, and the vertical (column) summary is grouped by date.

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

10. Click Column > Create Computed Column.
    1010data displays the Create Computed Column dialog.

    

11. Complete the following fields in the Create Computed Column dialog.

    Column Name	Enter dod.
    Column Heading	Enter Day-Over-Day.
    Value Expression	Enter m1-m0. Note: By default, resultant columns in cross tabulations are named m0, m1, etc. 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).
    Display Format	Select Currency: $1,234.56.
    Column Width	Select Default.
    Decimal Places	Select Default.

    

12. Click Submit.
    1010data creates the Day-Over-Day computed column.

    

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

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