Summarizing data

Concept Information

SUMMARIZE command

Summarizing groups the records in a table based on identical values in one or more key fields, and counts the number of records in each group. You also have the option of performing various statistical calculations for each group.

If you summarize by more than one key field (nested summarizing), groups are based on identical combinations of values across the key fields.

Key fields can be character, numeric, or datetime.

Summarizing by one key field

Summarizing by one key field is the simplest form of summarizing.

For example, you could summarize a transactions table on the customer number field to find the total number of transactions for each customer.

In the example below, there are ten values in the Customer Number field in the input table. Some values are unique, and some values are identical. After summarizing, the values are grouped into four unique groups. The Count tells you how many records, or transactions, are in each customer number group.

Input table Output results
Key field: Customer Number Summarized group Count
795401 230575 1
518008 518008 5
518008 795401 3
925007 925007 1
518008    
795401    
518008    
230575    
795401    
518008    

Summarizing by multiple key fields

Summarizing by multiple key fields, or nested summarizing, lets you perform more detailed analysis of data.

For example, you could summarize a transactions table on the customer number and the transaction date fields to find the total number of transactions for each customer for each date that the customer had transactions.

In the example below, there are ten values in the Customer Number field in the input table, with accompanying dates in the Invoice Date field. Some combinations of customer number and date are unique, and some are identical. After summarizing, the customer number-date combinations are grouped into seven unique groups. The Count tells you how many records, or transactions, are in each group.

Input table Output results
Key field 1: Customer Number Key field 2: Invoice Date Nested summarized group Count
795401 08/20/2016 230575 06/13/2016 1
518008 10/15/2016 518008 04/30/2016 1
518008 07/17/2016 518008 07/17/2016 3
925007 05/21/2016 518008 10/15/2016 1
518008 04/30/2016 795401 06/30/2016 1
795401 08/20/2016 795401 08/20/2016 2
518008 07/17/2016 925007 05/21/2016 1
230575 06/13/2016      
795401 06/30/2016      
518008 07/17/2016      

Nested summarizing in detail

If you summarize by more than one key field, you create nested summarized groups in the output results.

Nesting hierarchy

The order in which you select the key fields dictates the nesting hierarchy. The records are summarized by the first field you select, and within each of these primary groupings the records are then summarized by the second field you select, and so on.

Note

Reversing the order in which you select two summarize key fields may give quite different results.

Field order in the output results

The order in which you select the key fields is also the order in which the columns appear in the output results. If you are appending results to an existing Analytics table, the column selection and order must be identical to the column selection and order in the existing table.

Summarizing and sorting

Summarizing can process either sorted or unsorted data. The Presort option allows you to include initial sorting of the data with summarizing.

If you use Presort

If you use Presort, the output results are sorted and contain a single, unique group for each set of identical values, or identical combination of values, in the key field or fields.

Tip

If the input table is already sorted, you can save processing time by deselecting the Presort option.

If you do not use Presort

If you do not use Presort, the output results use the sort order of the input table.

If the key field or fields contain non-sequential identical values, the output results contain more than one group for each set of identical values, or identical combination of values.

Note

Depending on the context, more than one group for each set of identical values, or identical combination of values, can defeat the purpose of summarizing.

Subtotaling numeric fields

When summarizing, you can optionally subtotal one or more numeric fields. In the examples above, you could subtotal the transaction amount field to calculate:

  • The total transaction amount for each customer

  • The total transaction amount for each customer for each date that the customer had transactions

The statistical options

You have the option of performing statistical calculations on any subtotal field you specify. The statistical calculations are broken down by group in the output results.

In the examples above, if you subtotal the transaction amount field, you could also use one of the statistical options to calculate:

  • the average, minimum, and maximum transaction amounts for each customer
  • the average, minimum, and maximum transaction amounts for each customer for each date that the customer had transactions

Subtotal and statistical options in detail

The table below provides details about the subtotal and statistical options and calculations.

Option Alternate column title (display name) in output table Field name in output table Calculation performed on subtotaled field
Subtotal Fields Total + subtotaled alternate column title subtotaled field name Subtotaled values for each group
Avg, min, max Average + subtotaled alternate column title

a_subtotaled field name

The average value for each group

Minimum + subtotaled alternate column title

m_subtotaled field name

The minimum value for each group

Maximum + subtotaled alternate column title

x_subtotaled field name

The maximum value for each group
Std deviation, % of field

STDDEV + subtotaled alternate column title

d_subtotaled field name

The standard deviation for each group

% Field + subtotaled alternate column title

f_subtotaled field name

Each group’s subtotal expressed as a percentage of the field total
Median, Mode, Q25, Q75

Median + subtotaled alternate column title

c_subtotaled field name

The median value for each group

  • Odd-numbered sets of values: the middle value
  • Even-numbered sets of values: the average of the two values at the middle

Mode + subtotaled alternate column title

o_subtotaled field name

The most frequently occurring value for each group

  • Displays “N/A” if no value occurs more than once
  • In the event of a tie, displays the lowest value

Q25 + subtotaled alternate column title

q_subtotaled field name

The first quartile value for each group (lower quartile value)

  • The result is an interpolated value based on an Analytics algorithm
  • Produces the same result as the QUARTILE and QUARTILE.INC functions in Microsoft Excel

Q75 + subtotaled alternate column title

p_subtotaled field name

The third quartile value for each group (upper quartile value)

  • The result is an interpolated value based on an Analytics algorithm
  • Produces the same result as the QUARTILE and QUARTILE.INC functions in Microsoft Excel
% of Count Percent of Count COUNT_PERCENTAGE The percentage of source table records belonging to each group

Note

Does not require a subtotal field

The Other Fields option

The Other Fields option allows you to select additional character, numeric, or datetime fields to include in the output. This option can provide useful information if the fields you select contain the same value for all records in each summarized group.

For example, if you summarize a table on customer number, an appropriate “other field” could be customer name. The customer name should be identical for all records with the same customer number.

If you specify an “other field” that contains values that are different for a summarized group, only the value for the first record in the group is displayed, which is not meaningful.

For example, if you summarize a vendor table by state, and select city as an “other field”, only the first city listed for each state appears in the output. In this instance, the better approach is to summarize using both state and city as key fields, in that order.

Summarize results

The example below shows the results of summarizing an accounts receivable table on customer number and transaction type. The transaction amount is subtotaled, with some associated statistics. The results are output to screen.

The example uses a subset of customer numbers from the Ar table in ACL DATA\Sample Data Files\Sample Project.ACL.

Summarizing in detail

Summarizing performs the following operations:

Operation Location in Summarize results above
Groups the records based on identical values, or identical combinations of values, in one or more character, numeric, or datetime key fields Cust Number field

Trans Type field
Optionally subtotals the values of one or more numeric fields for each group Total Trans Amount field
Optionally performs statistical calculations on each subtotaled numeric field Average, Minimum, and Maximum Trans Amount fields

Note

Additional statistical calculations not shown
Optionally calculates the percentage of source table records belonging to each group Percent of Count field
Counts (subtotals) the number of records for each group Count field
Optionally displays additional character, numeric, or datetime fields with complementary information Name field

Provides totals for all numeric fields included in the output results

Note

The Totals row is only provided when you output the results to screen.

 Totals row

Steps

You can summarize data by grouping the records in a table based on identical values, or identical combinations of values, in one or more character, numeric, or datetime fields.

You can optionally subtotal associated numeric fields. You can also perform statistical calculations on any subtotal field you specify. The results of the statistical calculations are broken down by group in the summarized output table.

  1. Select Analyze > Summarize.
  2. On the Main tab, do one of the following:

    Select the field(s) to summarize from the Summarize On list.

    Click Summarize On to select the field(s), or to create an expression.

    Note

    If you select more than one field you create nested summarized groups in the output results. For more information, see Nested summarizing in detail.

  3. Optional. Select one or more Subtotal Fields, or click Subtotal Fields, to select the subtotal field(s), or to create an expression.

    The order in which you select the subtotal fields is the order in which the columns appear in the results. If you are appending results to an existing Analytics table, the column selection and order must be identical to the column selection and order in the existing table.

  4. Optional. Do one of the following:

    From the Other Fields list, select the other field(s) to include in the output results.

    Click Other Fields to select the field(s), or to create an expression.

    Note

    Select only fields that contain the same value for all records in each summarized group. For more information, see The Other Fields option.

  5. If the field(s) you are summarizing are already sorted, you can optionally deselect Presort.

    Note

    You can summarize unsorted fields, but the results may contain more than one summarized group for the same value, which may defeat the purpose of summarizing.

    Depending on the nature of your analysis, summarizing unsorted fields may be appropriate.

  6. If there are records in the current view that you want to exclude from processing, enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.

    Note

    The If condition is evaluated against only the records remaining in a table after any scope options have been applied (First, Next, While).

    The IF statement considers all records in the view and filters out those that do not meet the specified condition.

  7. Optional. Select one or more of the statistical options to perform statistical calculations on subtotal fields:

    Avg, min, max

    Std deviation, % of field

    Median, Mode, Q25, Q75

    % of Count

    For more information, see The statistical options.

    Note

    You must select at least one subtotal field in order to include statistics.

    % of Count does not require a subtotal field.

    Calculating these statistics requires additional computer memory. You may exceed your computer's memory and get an error message if you calculate the statistics for very large data sets.

  8. Click the Output tab.
  9. Select the appropriate output option in the To panel:
    • Screen – Select this option to display the results in the Analytics display area.

      Tip

      You can click any linked result value in the display area to drill down to the associated record or records in the source table.

      If the output table contains a large number of records, it is faster and more useful to save the results to a file than to display the results on the screen.

    • Print – Select this option to send the results to the default printer.
    • Graph – Select this option to create a graph of the results and display it in the Analytics display area.
    • File – Select this option to save or append the results to an Analytics table. The table is added to the open project if it is not already in the project.

    Note

    Output options that do not apply to a particular analytical operation are disabled.

  10. If you selected File as the output type, specify the following information in the As panel:
    • File Type – Analytics Table is the only option. Saves the results to a new Analytics table, or appends the results to an existing Analytics table.
    • Name – Enter a table name in the Name text box. Or click Name and enter the table name, or select an existing table in the Save or Save File As dialog box to overwrite or append to the table. If Analytics prefills a table name, you can accept the prefilled name, or change it.

      You can also specify an absolute or relative file path, or navigate to a different folder, to save or append the table in a location other than the project location. For example: C:\Results\Output.fil or Results\Output.fil.

    • Local – Only enabled when connected to a server table. Select Local to save the output table to the same location as the project, or to specify a path or navigate to a different local folder. Leave Local deselected to save the output table to the Prefix folder on a server.

    Note

    For output results produced from analysis or processing of AX Server tables, select Local. You cannot deselect the Local setting to import results tables to AX Server.

  11. Depending on the output type, you can optionally specify a Header and/or a Footer in the text box(es).

    Headers and footers are centered by default. Type a left angle bracket (<) before the header or footer text to left align the text. Click Header or Footer to enter a header or footer of more than one line. Alternatively, you can enter a semi-colon (;) as a line-break character in the header or footer text box. Left aligning multiple lines requires a left angle bracket at the beginning of each line.

  12. Click the More tab.

  13. Select the appropriate option in the Scope panel:

    • All
    • First
    • Next
    • While
    All This option is selected by default. Leave it selected to specify that all records in the view are processed.
    First Select this option and enter a number in the text box to start processing at the first record in the view and include only the specified number of records.
    Next Select this option and enter a number in the text box to start processing at the currently selected record in the view and include only the specified number of records. The actual record number in the leftmost column must be selected, not data in the row.
    While

    Select this option to use a WHILE statement to limit the processing of records in the view based on a particular criterion or set of criteria. You can enter a condition in the While text box, or click While to create a WHILE statement using the Expression Builder.

    A WHILE statement allows records in the view to be processed only while the specified condition evaluates to true. As soon as the condition evaluates to false, the processing terminates, and no further records are considered. You can use the While option in conjunction with the All, First, or Next options. Record processing stops as soon as one limit is reached.
     

    Note

    The number of records specified in the First or Next options references either the physical or the indexed order of records in a table, and disregards any filtering or quick sorting applied to the view. However, results of analytical operations respect any filtering.

    If a view is quick sorted, Next behaves like First.

  14. If you selected File (Analytics Table) as the output type, select Use Output Table if you want the output table to open automatically upon completion of the operation.

  15. If you selected File as the output type, and want to append the output results to the end of an existing Analytics table, do one of the following:

    Select Append To Existing File if you are certain the output results and the existing table are identical in structure.

    Leave Append To Existing File deselected if you want Analytics to compare the record lengths of the output results and the existing table. If the record lengths are not identical, the data structure is not identical, and the append will not work correctly.

    Note

    Leaving Append To Existing File deselected is recommended if you are uncertain whether the output results and the existing table have an identical data structure. For more information about appending and data structure, see Appending output results to an existing table.

  16. Click OK.
  17. If the overwrite prompt appears, select the appropriate option.

    If you are expecting the Append option to appear and it does not, click No to cancel the operation and see Appending output results to an existing table.