Using a function to group records by month

To keep things simple in previous tutorials, a number of the examples of Analytics functions use literal input values – "john SMITH", "VT-123-45", and so on. But how do you apply a function to the entire set of values in a field in an Analytics table?

Applying a function to an entire field can help you perform useful tasks such as grouping the records in a table by month.

Computed field

One way to apply a function to all the values in a field is to create a computed field. A computed field is one that you create, often based on an actual physical field, but composed entirely of values computed by Analytics.

Similar to the output of a function, you can think of a computed field as virtual data, calculated by Analytics, that exists in memory. Once calculated, this virtual data can be used in subsequent operations.

Create a computed field to help group records by month

We can create a computed field called Month that uses the MONTH( ) function to extract the month portion from every date in an invoice date field. We can then group the records in the table by month.

Create the computed field

  1. In Analytics, open Sample Project.ACL, and open the Ap_Trans table (Tables\Accounts_Payable\Ap_Trans).

    If Sample Project.ACL is not available, open any table with a date field. To work with this example, the field must use the Datetime data type.

  2. Perform the following steps to create the Month computed field:
    1. At the top of the table view, click Edit Table Layout .
    2. In the Table Layout dialog box, click Add a New Expression .
    3. In the Name field type Month, and in the Default Value field copy and paste this version of the MONTH( ) function:

      MONTH(Invoice_Date)

      If you not using the Ap_Trans table, update the field name to match your data.

    4. Click Accept Entry , and close the Table Layout dialog box.
    5. In the table view, right-click the header of the Invoice Date column, select Add Columns, under Available Fields double-click Month, and click OK.

      Result: The Month computed field is added to the view. It contains the month portion of each date in the Invoice Date column, displayed as a number from 1 to 12.

    6. Click Save the Open Project to save your changes.

Group the records by month

Now that you've created the Month computed field, you can use it to group the records in the Ap_Trans table by month.

  1. From the main menu, select Analyze > Summarize.
  2. From the Summarize On list, select the Month field.
  3. From the Subtotal Fields list, select the Invoice_Amount field.
  4. Click the Output tab, select File, type Ap_Trans_grouped in the Name field, and click OK.

    Result: Analytics outputs the new table, which groups the records from the Ap_Trans table by month. For each month, there is an invoice amount subtotal, and a count of the number of records that occur in the month.

    MonthInvoice AmountCount
    185,670.2212
    24,496.566
    32,941.805
    4467.401
    58,272.575
    61,582.862
    73,101.984
    821,146.962
    932,577.3220
    1041,595.8919
    1170,779.2619
    126,008.517

Suggested activity: display the names of the months

You can use the CMOY( ) function to create a second computed field if you want to display the names of the months. CMOY is an abbreviation for "Character Month of Year".

  1. In the Ap_Trans table, follow the same steps you used to create the Month computed field and add it to the table view, but with these differences:
    • In the Name field type Month_2.
    • In the Default Value field copy and paste this version of the CMOY( ) function:

      CMOY(Invoice_Date, 9)

    Result: The Month_2 computed field is added to the view with the name of each month.

  2. Follow the same steps you used to group the records from the Ap_Trans table by month, but with these differences:
    • In the Other Fields list, select Month_2.
    • On the Output tab, specify the output file name Ap_Trans_grouped_2.

    Result: Analytics outputs the new table, which groups the records from the Ap_Trans table by month, but now the month names are included.

    MonthInvoice AmountCountMonth_2
    185,670.2212January
    24,496.566February
    32,941.805March
    4467.401April
    58,272.575May
    61,582.862June
    73,101.984July
    821,146.962August
    932,577.3220September
    1041,595.8919October
    1170,779.2619November
    126,008.517December

Skip creating the computed field

In many cases, creating a computed field as a way of applying a function to multiple values is useful. However, you can achieve the same result and streamline your work in Analytics by embedding functions directly inside Analytics commands.

Embed a function to help group records by month

We'll use the same example as above, but without creating the computed fields. Instead, we'll embed the functions directly in the summarize command.

  1. Open the Ap_Trans table.
  2. From the main menu, select Analyze > Summarize.

Embed the MONTH( ) function

  1. Click Summarize On, and then click Expr.
  2. In the Expression Builder, double-click MONTH( date/datetime ) in the Functions list.

    Tip

    To make it easier to locate the MONTH( ) function, select Date & Time from the drop-down filter at the top of the Functions list.

  3. In the Expression text box, select date/datetime and double-click Invoice_Date in the Available Fields list.

    You should have MONTH( Invoice_Date ) in the Expression text box.

    Note

    The expression should look familiar. It's the same as the computed field from the previous example, only now it's embedded in the summarize command.

  4. Click OK to exit the Expression Builder, and click OK to exit the Select Fields dialog box.

Embed the CMOY( ) function

  1. Click Other Fields, and then click Expr.
  2. In the Expression Builder, double-click CMOY( date/datetime , length ) in the Functions list.
  3. In the Expression text box, replace date/datetime with Invoice_Date, and length with 9.

    You should have CMOY( Invoice_Date , 9) in the Expression text box.

  4. Click OK to exit the Expression Builder, and click OK to exit the Select Fields dialog box.

Finalize the summarize operation

  1. From the Subtotal Fields list, select the Invoice_Amount field.
  2. Click the Output tab, select File, type Ap_Trans_grouped_3 in the Name field, and click OK.

    Result: Analytics outputs the new table, which groups the records from the Ap_Trans table by month. You can see the two embedded functions.

    MONTH(Invoice_Date)Invoice AmountCountCMOY(Invoice_Date, 9)
    185,670.2212January
    24,496.566February
    32,941.805March
    4467.401April
    58,272.575May
    61,582.862June
    73,101.984July
    821,146.962August
    932,577.3220September
    1041,595.8919October
    1170,779.2619November
    126,008.517December

Key point

You used two different methods to achieve exactly the same result:

  • Computed field Creating a computed field before using the field in a command is a more literal, step-by-step approach. It may be an appropriate approach if you want to use the computed field for more than one purpose.
  • Embedded function Bypassing the creation of a computed field, and embedding a function in a command, is a more streamlined approach. It may be an appropriate approach in the context of scripting, producing more efficient scripts.

Suggested activity: group records by day of the week

If you want additional practice with using functions to create computed fields, or with embedding functions in commands, redo some or all of the activities above and substitute the DOW( ) and CDOW( ) functions.

DOW( ) and CDOW( ) are very similar to MONTH( ) and CMOY( ) except that they extract the day of the week from a date rather than the month of the year.

Tip

Using DOW( ) and CDOW( ), you could analyze how sales figures compare for different days of the week.

Another little trick for testing functions

You can use the DISPLAY method in the command line to get a sense of the output when you create a computed field, or embed a function in a command.

  1. With the Ap_Trans table open, paste this version of the UPPER( ) function into the command line, type DISPLAY and a space before the pasted function, and press Enter.

    UPPER(Vendor.Vendor_Name)

    The UPPER( ) function converts all input text to uppercase.

    If the first record in the Ap_Trans table is selected, the function output is MORE POWER INDUSTRIES.

  2. In the table, select record number 6.

    Click the record number to select the record. The selected record number is highlighted green.

  3. Type or reload the function into the command line and press Enter.

    The function output is UNITED EQUIPMENT.

  4. Select one or two other records and repeat the process.

    Key point: On a record-by-record basis you are seeing what a computed field, or an embedded function, would do to all the values in the Vendor.Vendor_Name field.

    You can use this testing method with any Analytics function that takes a field for input.

Where to next?

Learn how to use variables with a function to create interactivity: Using variables with a function to allow user input