How to Include Two Sets of Rules into One Data Quality Report

With the new 2014 HUD Data Standards in effect now, everyone is picking up the pieces from their old reports having been broken with the upgrade, and may be wondering how to continue to use their same reports but include the new set of rules within them. HUD has stated that all clients in a program stay as of October 1st should have the new rules apply and that clients that exited prior to that date should have the old rules apply. This means that basically data quality checking has taken on one very large and new dimension: differentiating between program stays that would be included under the 2014 Data Standards and those that would not be.

In our Data Quality reports, I have added a variable that helps us build out our already existing variables to use two sets of rules for the same data element. I named mine NewDataOrOldData? and here's the code:

1=If(ToDate([Clients].[Entry Exit Entry Date];"MM/dd/yyyy") >= ToDate("10/01/2014";
2"MM/dd/yyyy") Or 
3  ToDate([Clients].[Entry Exit Exit Date];"MM/dd/yyyy") >= ToDate("10/01/2014";"MM/dd/yyyy") Or 
4  IsNull([Clients].[Entry Exit Exit Date]); "New"; "Old")

It basically says if the Entry Date or Exit Date is greater than or equal to 10/1/2014, or there is no Exit Date, then consider it "New" data, otherwise consider it "Old".

Then once you have created this variable, refer to it in all of the relevant other variables looking for errors. For instance, I used it in this way for the variable meant to find missing Names:

 1=If([NewDataOrOldData?] = "New"; 
 2  If([Client First Name] ="Anonymous" Or 
 3  [Client Name Data Quality] InList("Client Doesn't Know (HUD)";"Client doesn't know (HUD)";
 4  "Client Refused (HUD)";
 5  "Client refused (HUD)"); 
 6  "Don't Know/Refused";   
 7    If(IsNull([Client Name Data Quality]) Or 
 8    [Client Name Data Quality] = "Data not collected (HUD)" Or 
 9    ((IsNull([Client First Name]) Or IsNull([Client Last Name])) And 
10    Not([Client Name Data Quality] InList("Client Doesn't Know (HUD)";"Client doesn't know (HUD)";
11    "Client Refused (HUD)";
12    "Client refused (HUD)"))); 
13    "Missing")); 
14      If([NewDataOrOldData?]="Old"; 
15        If([Client First Name]="Anonymous";
16        "Don't Know/Refused")))

This basically says If the data is new, check to see if the first name is "Anonymous" or the Name DQ is either Don't Know or Refused, and if it is then label it "Don't Know Refused", otherwise if the Name DQ, First Name, or Last Name is null and the Name DQ isn't Don't Know/Refused, then call it Missing. Otherwise, if it's "Old" data, then use the old rules (which were pretty minimal!)

Anyway, I think this is a great way to keep your old Data Quality reports useful and up to date. Hope this helps someone out there! :)

See y'all at the Bowman conference this week hopefully!