Filtering with blank date fields

Sometimes data is incomplete or optional and therefore fields in Analytics tables may be blank. When an expression in Analytics compares a blank date field, 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:

Product_Category	Base_Margin	Order_Date	Order_ID	Priority	Quantity	Tax_Rate	Category
Small Business	0.04		3	Low	6	0.8	Office Supplies
Consumer	0.07		293	High	49	0.58	Office Supplies
Consumer	0.01	07/23/2012	293	High	27	0.39	Office Supplies
Corporate	0.08	10/15/2010	483	High	30	0.58	Technology
Consumer	0.08	08/28/2010	515	Not Specified	19	0.5	Office Supplies
Consumer	0.05	08/28/2010	515	Not Specified	21	0.37	Furniture
Corporate	0.03	06/17/2011	613	High	12	0.38	Office Supplies
Corporate	0.09	06/17/2011	613	High	22	0	Office Supplies
Corporate	0.07	03/24/2012	643	High	21	0	Office Supplies
Home Office	0.07	02/26/2009	678	Low	44	0.38	Office Supplies

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:

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 fields that you want to exclude:

Product_Category	Base_Margin	Order_Date	Order_ID	Priority	Quantity	Tax_Rate	Category
Small Business	0.04		3	Low	6	0.8	Office Supplies
Consumer	0.07		293	High	49	0.58	Office Supplies
Corporate	0.08	10/15/2010	483	High	30	0.58	Technology
Consumer	0.08	08/28/2010	515	Not Specified	19	0.5	Office Supplies
Consumer	0.05	08/28/2010	515	Not Specified	21	0.37	Furniture
Home Office	0.07	02/26/2009	678	Low	44	0.38	Office Supplies

Checking for blanks while filtering

Using functions, you can exclude blank data fields 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 fields 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

Second filter results

Because you excluded blank values before testing whether the order was placed before 2011, this filter's results do not include canceled orders like the first one's did:

Product_Category	Base_Margin	Order_Date	Order_ID	Priority	Quantity	Tax_Rate	Category
Corporate	0.08	10/15/2010	483	High	30	0.58	Technology
Consumer	0.08	08/28/2010	515	Not Specified	19	0.5	Office Supplies
Consumer	0.05	08/28/2010	515	Not Specified	21	0.37	Furniture
Home Office	0.07	02/26/2009	678	Low	44	0.38	Office Supplies