Filtering blank date values

Sometimes data is incomplete or optional and therefore fields in Analytics tables may contain empty or blank values. When an expression in Analytics encounters a blank date value, the blank date is treated as the minimum system date value 1900-01-01. Whenever you write an expression that compares dates, you must account for this behavior.

The sales order table

You are working with the following table of sales orders and you want to filter it so that you can analyze all orders that were submitted before the year 2011. If a record has no order date, then it represents a canceled order and you do not want to include it in your filtered records:

Category Order_Date Order_ID Quantity
Office Supplies   3 6
Office Supplies   293 49
Office Supplies 07/23/2012 293 27
Technology 10/15/2010 483 30
Office Supplies 08/28/2010 515 19
Furniture 08/28/2010 515 21
Office Supplies 06/17/2011 613 12
Office Supplies 06/17/2011 613 22
Office Supplies 03/24/2012 643 21
Office Supplies 02/26/2009 678 44

Using a simple filter

When you first attempt to filter the table, you use the following simple expression to exclude any orders from 2011 and later:

Tip

Notice the backquotes ` that surround the literal date value. You must always surround literal datetime values with this qualifier. For more information, see Data types. 

COMMENT filters out records with an order date of Jan 1 2011 or later
SET FILTER TO Order_Date < `20110101`

First filter results

Because Analytics treats blank date values as 1900-01-01, and January 1, 1900 is earlier than January 1, 2011, your results include the records with blank Order_Date values that you want to exclude:

Category Order_Date Order_ID Quantity
Office Supplies   3 6
Office Supplies   293 49
Technology 10/15/2010 483 30
Office Supplies 08/28/2010 515 19
Furniture 08/28/2010 515 21
Office Supplies 02/26/2009 678 44

Checking for blanks while filtering

Using functions, you can exclude blank date values before you filter out records from 2011 or later.

The ISBLANK( ) function returns true if a text value is blank, so with some manipulation of the Order_Date field, you can exclude blank values:

COMMENT excludes blank dates and order dates from 2011 and later
SET FILTER TO NOT ISBLANK(DATETIME(Order_Date)) AND Order_Date < `20110101`

When this expression evaluates, the functions run from inside out and several things happen:

  1. The DATETIME( ) function converts the Order_Date date value to a text value (`20100828` becomes "20100828").
  2. The ISBLANK( ) function checks if the text value is blank and evaluates to either true or false.
  3. The NOT operator flips the logical value returned from ISBLANK( ) so that:
    • If the Order_Date is blank (true), then the value is flipped to false and the filter excludes the record
    • If the Order_Date is not blank (false), then the value is flipped to true and the filter checks if the date is earlier than 2011 and includes all records that have an Order_Date value before January 1, 2011

Tip

Only those records where the sub-expressions evaluate to true on both sides of the AND operator are included. If either of the sub-expressions evaluate to false, the record is excluded.

Second filter results

The second filter excludes blank date values before testing whether orders were placed before 2011, so the results do not include the canceled orders that were included by the first filter.

Category Order_Date Order_ID Quantity
Technology 10/15/2010 483 30
Office Supplies 08/28/2010 515 19
Furniture 08/28/2010 515 21
Office Supplies 02/26/2009 678 44