ACL Certified Data Analyst

The Basics of Datetime Fields

With the release of ACL Analytics 10.5, time components were added to datetime fields, meaning that entire time stamps (such as YYYY-MM-DD hh:mm:ss) can now be analyzed! While this allows for much more granular and powerful analysis, sometimes we may only want to work with a certain component of the field, such as the month. In this newsletter, we will take a look at how to deconstruct the datetime stamp and how to analyze these components to track for trends overtime or things at a macro level.

My datetime field data is in a MM/DD/YYYY hh:mm:ss format but I only want to find out the total number of transactions for the month of May. What can I do to achieve that?

You can start by parsing the month values from the datetime field using the MONTH() function. Similarly, you can parse the day, year, hour, minute, and second from the datetime field by using the DAY(), YEAR(), HOUR(), MINUTE (), and SECOND() functions respectively.

These functions will extract the components as a numeric value into an unconditional computed field.

For example:

Datetime field
03/18/2011 16:32:45 03 18 2011 16 32 45

To create the unconditional computed field, you would define it in the Table Layout dialog box:

  1. Click on Edit on the menu bar and select Table Layout or Ctrl + I (short key), the Table Layout window will open
  2. Click on the  button to Add a New Expression
  3. Give the computed field a name (for example, c_txnmonth_number)
  4. Click on f(x) to build your expression
  5. Select MONTH(date/datetime) function on the right in the Expression Builder and replace the date/datetime with the proper datetime field name. Click OK.
  6. Last step, click on the checkmark to save the new unconditional computed field.

Instead of creating the field manually, you can also parse the month by using this simple script: DEFINE FIELD computed field name COMPUTED MONTH(datetime field)

You can repeat the above steps to parse the day, year, hour, minute and second from the datetime field using their respective functions.

Next step is to convert your computed fields into character versions by using the STRING() function. This function will extract the number as a character type into a computed field, This will come in handy for future analysis since certain commands will only work on character fields.

Sometimes, it is helpful to know the name of the month or the actual day of the week that the transaction took place. You can also extract the components from the datetime field by using the CMOY() and CDOW() functions. When you are using these two functions, you should use 9 as your length parameter since the longest month & day names are nine bytes long.

What if I want to do analysis based on fiscal year and quarter of when the transactions took place in?

Since the fiscal year and quarter depend on the organization, you will need to generate conditional computed fields to calculate the fiscal year and quarter that the transactions took place in.

For example:

Assume a fiscal year start date of July 1 and a fiscal year end date of June 30

Datetime Field
Fiscal Year
Fiscal Quarter
07/18/2014 16:32:45 07 2014 FY2015 1st Qtr
03/16/2014 11:21:23 03 2014 FY2014 3rd Qtr

You will need to use the BETWEEN() function to create a conditional computed field to calculate the transaction fiscal year and quarter. To create the conditional computed field, you would also define it in the Table Layout dialog box:

  1. Click on Edit on the menu bar and select Table Layout or Ctrl + I (short key), the Table Layout window will open
  2. Click on the  button to Add a New Expression
  3. Give the computed field a name (for example, c_txnfiscalyear)
  4. Put “Unknown” as the Default Value
  5. Click on the + sign to start building the conditions:
    • To calculate the fiscal year:
      1. The fiscal year date range is July 1st through June 30th, so the min and max value of your first condition will be 1 (January) and 6 (June) and your value will be “FY” + the transaction year (2014).
      2. Your second condition will have a min value of 7 (July) since this is the start of a new fiscal year, and a max value of 12 (December) since the calendar year rolls forward in January. The resulting value will be ‘FY” + the transaction year +1
        BETWEEN(month number field, 1,6) “FY” + STRING(YEAR(datetime field),4)
        BETWEEN(month number field, 7,12) “FY” + STRING(YEAR(datetime field)+1,4)
    • To calculate the fiscal quarter:
      •  You will use each quarter’s start month as the min parameter and the end month as the max parameter
        BETWEEN(month number field, 7,9) “1st Qtr”
        BETWEEN(month number field, 10, 12) “2nd Qtr”
        BETWEEN(month number field, 1, 3) “3rd Qtr”
        BETWEEN(month number field, 4, 6) “4th Qtr”
  6. Last step, click on the checkmark to save the new conditional computed fields.

Now that you have separated the different components of the datetime field, you are ready to do some basic analysis on them!

You can use:

CLASSIFY Determine the total count of transactions and total transactional amounts in the fiscal year and/or each month
SUMMARIZE Determine the total count of transactions and total transaction amounts in the quarters of each fiscal year and/or the months of each fiscal year
CROSSTAB Generate a detailed summary of transactions by month and day of the week

Interested in practicing on parsing, converting and analyzing the datatime fields? Jump into the Basics of Datetime Fields online course in ACL Academy to get started today!

Frequently Asked Questions

Here are some Frequently Asked Questions about ACL Analytics:

What types of field can I define in ACL Analytics?

  • There are two types of fields:
    • Physical data fields – fields that correspond to physical data in the data source
    • Computed fields – fields that contain expressions that are evaluated by ACL in order to display a computed value. They do not refer directly to the physical data in the data source. There are also two types of computed fields:
      • Unconditional – the same expression applies to every record of the physical field
      • Conditional – different expressions apply to the physical field based on each condition

How many data types are there in ACL Analytics?

  • ACL data types are grouped into four data categories:
    Character A series of one or more characters. 32,767 bytes Single quotation marks, or double quotation marks
    • ‘John Doe’
    • “John Doe”
    Numeric Numeric values contain digits from 0 to 9 and, optionally, a negative sign and a decimal point. 22 digits No qualifier
    • 100
    • -5
    • 5.01
    • 22222.1232
    Datetime A date, datetime, or time value expressed in a supported format.
    • Minimum = 1900-01-01
    • Maximum = 9999-12-31
    • Backquotes
    • Leading ‘t’, or a single blank space, for time values
    • `20160101`
    • `141231`
    • `t2359`
    • `20141231T235959`
    • `20141231 235959`
    Logical The simplest data type. Logical data expresses a truth value of either true or false.

    Comparison operators such as ‘=’, ‘>’, and ‘<‘ return logical values.

    • T
    • F
    No qualifier ASSIGN v_truth = 5 > 4 evaluates to T
  • The ACL operations that you can perform on a field with a specific data type, and how the field is displayed, are determined by the data category.
  • If you use a field with the wrong data type in an operation, ACL will display an error.

What is a script?

  • A script is a list of commands that run in ACL Analytics. Scripts are useful for performing a series of tasks automatically so that you do not need to run each command manually through the user interface. Any ACL command can be contained in a script.
  • Haven’t scripted before? Follow this short tutorial to build your first ACL script today!

Is there a list of useful functions that I can reference to?

  • We have a list of top 30 ACL functions that can help you prepare, parse, convert, and harmonize data in your scripts. Bookmark this page today!

Tip of the Month

I have just finished parsing and analyzing my data from 2014. I now need to analyze the same set of data fields for year 2015 and 2016. I don’t want to waste time creating the same computed fields because it is very time-consuming. Is there an easier way to achieve my goal?

Of course! You can create a script to do just that. Not a coder or scripter? Not to worry, scripting in ACL Analytics is as easy as 1-2-3 (copy-paste-revise). Complete the following steps to create your script:

  • Copy your computed field definitions from the log (remember that the log records almost every analysis step performed in ACL Analytics).
  • Paste the computed field definitions into a new script.
  • Revise the script so that the fields will be defined in your subsequent tables for the following years.
  • Then review and run your script!

Watch the video in Episode 3 of the Basics of Datetime Fields online course in ACL Academy for a demo of how to create your script to analyze the data from subsequent years.

Learn more best practices and how to make the most out of your ACL technology at ACL Connections, September 24-27 in Nashville, TN. This event features four days of training and offers 21.5 CPE credits. Hurry! Last day of registration will end on September 1, 2017! Access the full event details here >>

ACDA Bootcamp Series News

In preparation for our annual user conference, ACL Connections, we will not be having a webinar in September.

Did you miss previous editions of the newsletter series? Don’t worry, just visit here to check out the past editions.

Subscribe to receive the Bootcamp Series sent directly to your inbox!