How to Write Criteria with ADA

Introduction

Multiple utilities in ADA (Audit Data Assistant) allow you to use criteria for your analysis. So what are criteria? Simply put, they are formulas and conditions you specify that select only particular records for analysis.

Some Rules for Using Criteria

  1. Keep in mind that criteria values are case-sensitive.

  2. You must place quotes or double-quotes around any value that isn’t a number, such as words and dates.

  3. You must use double equal signs instead of a single equal sign.

  4. You can use words or symbols for operators. In other words, you may use the operator “and” or its symbol counterpart, the ampersand “&”.

ADA’s custom datetime functions sometimes require a format for interpreting the datetime data. For a complete reference for date and time formats, please see Python’s date and time formats.

Examples

Here are some examples of criteria formulas.

1. Select records where the column ACCURACY is 6.0:

ACCURACY == 6.0

2. Select records where the column STATE is “Kanton Bern”:

STATE == “Kanton Bern”

3. Select records where the column ACCURACY is 6.0 and the column COUNTRY_CODE is “AD”:

ACCURACY == 6.0 & COUNTRY_CODE == “AD”

ACCURACY == 6.0 and COUNTRY_CODE == “AD”

4. Select records where the column ACCURACY is 6.0 or the column COUNTRY_CODE is “AD”:

ACCURACY == 6.0 or COUNTRY_CODE == “AD”

ACCURACY == 6.0 | COUNTRY_CODE == “AD”

5. Select records where the column ACCURACY is 6.0 and the column LOCATION is “Canillo”:

ACCURACY == 6.0 and “Canillo” in LOCATION

6. Select records where the column ACCURACY is 6.0 and the column LOCATION is not “Canillo”:

ACCURACY == 6.0 and “Canillo” not in LOCATION

7. Select records where the column LOCATION is “Canillo”, “Encamp”, or “Ordino”:

LOCATION in(“Canillo”,”Encamp”, “Ordino”)

8. Select records where the column COUNTRY_CODE is not “AD” or “CH”:

COUNTRY_CODE in(“AD”,”CH”)

9. Select records where the column INVOICE_DATE is between “8/15/2015” and “8/10/2017”:

INVOICE_DATE > “8/10/2015” and INVOICE_DATE < “8/10/2017”

Using the Criteria Editor Dialog

The Criteria Editor will help you construct your formulas. The interface is point-and-click, and it is very intuitive and user-friendly:

ADA Criteria Dialog

Button Bar. The buttons at the top of the dialog are useful for inserting various operators into a criteria formula. These include Boolean operators “and”, “or”, “not”, and “in” as well as arithmetic operators. The arithmetic operators include exponent and modulus, which gives you the remainder of a division operation. Finally, the bar has a LIST button. Some custom functions take lists of values, and the list button will give you a list outline with square brackets surrounding comma-separated values.

Editor Box. Below the button bar is the editor box where you can type formulas. If you click a button or double-click a column name or a function name, the corresponding command will be inserted into the editor box. The editor box is also multi-line. This means you can press Enter to break up long formulas into multiple lines, making them much easier to edit.

Check Formula. Just below the editor box you’ll find a Check Formula button. When you click this button, ADA will evaluate your formula and let you know if it’s valid or if it has errors.

Test Criteria. Also below the editor box is an area where you can apply your formula to a specific row in your data. Select the row number you wish to test, and click the Test Criteria button. ADA will evaluate the formula and give an answer in the box below. As you can see in the screenshot, the answer box indicates False, which means the value for INVOICE_AMOUNT in row 1 is not greater than 50.

Data Grid. At the bottom left of the dialog, the data grid contains a list of the columns in your data as well as their types. To use a column, double-click it to insert it into the editor box.

Functions. At the bottom right of the dialog is a comprehensive list of custom functions for analyzing data in criteria formulas. Getting information about these functions is simple. Hover your mouse pointer over the name of a function to see pop-up for the function. To use a function, just double-click it to insert it into the editor box.

Questions

If you have questions about ADA software or you would like to know about purchasing custom ADA analytics, wonderful! Please call us at (864) 625 – 2524, and we’ll be happy to help.