Finding Non-NULL NULL Dates
As we saw in ”Why do 5% of My Services Occur on January 1, 1960?”, NULL dates are frequently passed to claims extracts as 0 dates causing issues with trending and length of stay. To identify this issue, summarize your data by year and month as shown in the DPS Non-NULL NULL Dates dataset.
The results are not always as dramatic as displayed below, but the example illustrates how to identify possible problems. Significant event counts of dollar amounts prior to the time period of the analysis generally indicate that you have found the issue. It is easy enough to reset 0 values to nulls for key date fields. Keep in mind that there are a variety of dates that 0 can be translated into and not all are so far back in time as 1900 or 1960.
In general, it is good practice to look at all of your date fields with counts (or another unit of measurement like dollars or member months) by year and month. Look for outliers prior to the general analytic time period. Dates of birth can be especially tricky since most actually do precede the analytic time period. However, a chart showing counts will help identify outliers from the general trend.
To see the data click here.