Putting it all together: using functions in a script

In the final Analytics function tutorial, we'll put everything together by using variables with a number of functions in a script that performs a real-world task.

What the script does

The example script allows anyone running the script to apply a date filter to any Analytics table with a date field, and then group by month the records included by the filter.

The script combines operations already explained in previous function tutorials.

How functions relate to a script

Within a single script, an Analytics scriptwriter might make use of multiple functions to perform various small but important helper tasks that contribute to the overall data analysis performed by the script.

Including a function in a script does not change the way the function works. Functions in scripts behave in exactly the same way they behave when you test them in isolation in the Analytics command line.

Suggested activities

• Review the script

  Review the example script at the bottom of the tutorial. Analytics scripts are executed in sequence, line by line. So you can proceed sequentially down the script and read each COMMENT to get a general idea of what the script logic is doing.

  COMMENT lines are not part of the script logic and are not executed.

• Understand what the functions are doing

  Pay special attention to the functions contained in the script. The functions are highlighted in brown. Refer to the table above the script for additional detail about the small task performed by each function.

  If you've done the previous function tutorials, most of the functions in the script and the tasks they perform will already be familiar to you.

• Run the script

  Once you are familiar with the script and the functions it contains, copy and paste the script into Analytics and run it to see how the script interactivity works.

How to run the example script in Analytics

1. Open an Analytics project that contains one or more tables with date fields.

   Sample Project.ACL has several tables with date fields.

2. Create a new, empty script:
   1. In the Navigator, right-click a folder or the top-level project entry and select New > Script.
   2. Copy and paste the entirety of the script below into the new script in the Script Editor.
   3. Save the project.
3. Click Run to run the script.
4. Follow the dialog box prompts to select a table and a date field, specify start and end dates, and select a numeric subtotal field.

Example script: filter and group records

The example script does two main things:

• filters the records in a table by date, using dates that you specify
• groups by month the records included in the filter

Don't worry if you can't understand all the script syntax. The main point is to see the various Analytics functions in action in a script.

A COMMENT before each piece of script syntax explains in simple terms what the syntax is doing.

The functions used in the example script

The purpose of each function used in the example script is described below.

In the script, the functions are highlighted brown.

Function in script	Purpose
DATE( )	Converts the MIN1 and MAX1 variables from the Datetime to the Character data type. The Character data type is required in order to display the contents of the variables in a text string in a dialog box. MIN1 and MAX1 are system variables automatically created by the STATISTICS command. They contain the oldest and the most recent dates in the date field you select.
ALLTRIM( )	Cleans up extra spaces around the oldest and the most recent dates when they are displayed in the dialog box.
CTOD( )	Converts the v_start_date and v_end_date variables from the Character data type to the Datetime data type. The Datetime data type is required for subtracting or comparing dates.
CTOD( )	Converts the v_start_date and v_end_date variables from the Character data type to the Datetime data type so that they are consistent with the v_date_field variable. All BETWEEN function parameters must be the same data type.
BETWEEN( )	Filters the date field based on the start and end dates you specified.
MONTH( )	Extracts the month portion from every date in the date field as a number.
CMOY( )	Extracts the month portion from every date in the date field as a character value.

Example script: filter records by date, and group filtered records by month

COMMENT
This script allows you to apply a date filter to any Analytics table with a date field, and then group by month the records included by the filter.
END

COMMENT Prompts you to select a table in the Analytics project.
ACCEPT "Select a table with a date field:" FIELDS "xf" TO v_table_name

COMMENT Opens the selected table.
OPEN %v_table_name%

COMMENT Prompts you to select a date field from the table.
ACCEPT "Select a date field:" FIELDS "D" TO v_date_field

COMMENT Identifies the oldest and the most recent dates in the selected date field.
STATISTICS ON %v_date_field%

COMMENT Assigns the oldest and the most recent dates to variables. The variables are used to display the existing date range in the dialog box where you specify the start and end dates for the date filter. It's easier to specify filter dates if you know what the existing date range is.
ASSIGN v_min_date = ALLTRIM(DATE(MIN1, "YYYYMMDD"))
ASSIGN v_max_date = ALLTRIM(DATE(MAX1, "YYYYMMDD"))

COMMENT Prompts you to specify the start and end dates for the date filter.
DIALOG (DIALOG TITLE "User Dialog" WIDTH 484 HEIGHT 153 ) (BUTTONSET TITLE "&OK;&Cancel" AT 370 12 DEFAULT 1 ) (TEXT TITLE "Specify a start date:" AT 12 16 ) (EDIT TO "v_start_date" AT 156 12 DEFAULT "YYYYMMDD" ) (TEXT TITLE "Specify an end date:" AT 12 52 ) (EDIT TO "v_end_date" AT 156 48 DEFAULT "YYYYMMDD" ) (TEXT TITLE "Date range in table:" AT 12 88 ) (TEXT TITLE "%v_min_date%   to  %v_max_date%" AT 156 88 )

COMMENT Displays a warning if the user-specified date filter spans more than 1 year.
IF CTOD(v_end_date) - CTOD(v_start_date) > 365 OR CTOD(v_start_date) - CTOD(v_end_date) > 365 DIALOG (DIALOG TITLE "User Dialog" WIDTH 469 HEIGHT 100 ) (BUTTONSET TITLE "&OK;&Cancel" AT 348 8 DEFAULT 1 ) (TEXT TITLE "Date range exceeds 1 year. Monthly groupings may include records from more than 1 year." AT 12 28 WIDTH 326 HEIGHT 33 ) (TEXT TITLE "Caution" AT 12 8 )

COMMENT Displays a warning if the user-specified start date is after the end date.
IF CTOD(v_start_date) > CTOD(v_end_date) DIALOG (DIALOG TITLE "User Dialog" WIDTH 469 HEIGHT 100 ) (BUTTONSET TITLE "&OK;&Cancel" AT 348 8 DEFAULT 1 ) (TEXT TITLE "Start date is after end date. Records between the two dates are included." AT 12 28 WIDTH 326 HEIGHT 33 ) (TEXT TITLE "Caution" AT 12 8 )

COMMENT Applies the date filter to the table and field you selected.
SET FILTER TO BETWEEN(%v_date_field%, CTOD(%v_start_date%), CTOD(%v_end_date%))

COMMENT Prompts you to select a subtotal field.
ACCEPT "Select a numeric field to subtotal for each month:" FIELDS "N" TO v_subtotal_field

COMMENT Groups the table by month, and outputs the results to a new table.
SUMMARIZE ON MONTH(%v_date_field%) SUBTOTAL %v_subtotal_field% OTHER CMOY(%v_date_field%, 9) TO "%v_table_name%_by_month.FIL" OPEN PRESORT

Where to next?

If you've completed all the tutorials in How to use functions and Advanced use of functions, congratulations! You now have a solid grounding in how Analytics functions work throughout Analytics.

Here are some suggestions for continuing to increase your expertise with functions:

• Continue to explore
  • Check out Top 30 Analytics functions for a list of the most frequently used Analytics functions, with accompanying examples.
  • Search and filter using Analytics functions provides numerous examples of using Analytics functions to perform powerful and effective searching and filtering of data in tables.
  • Browse through the entire set of Analytics Functions. Familiarize yourself at a high level with all the different things that functions can do.
• Don't forget about functions

  When you're presented with a data analysis challenge in your Analytics work, ask yourself, "Could a function help me out? Or several functions in combination?"

  With data analysis using Analytics commands, a large part of the challenge can be preparing the data for analysis. Functions, either singly, or in combination, are often critical in the preparation.