Tech Tip: Magic of Field Statistics

IDEA’s Field Statistics can save you a wealth of time. It should be the first thing you go to after importing a file. Why?

Every control total is available in one view. And because everything is in one view, you can quickly spot anomalies such as negative quantities on inventory records, $0.00 amounts on disbursed checks, or accounts receivable invoices with a due date in the year 3015. Even better, you can extract the questionable records by clicking on the hyperlink and selecting “Save.” No need to write an extraction equation.

But where’s the magic? It’s in the Equation Editor. You can call any value listed in Field Statistics into an IDEA equation. For example, perhaps you wish to review purchase card transaction amounts that are over the company’s average amount. Without @FieldStatistics, you could extract the data with an equation that might look like this:

AMOUNT >= 3773

Assuming 3773 is the average transaction amount in your file.

If you used @FieldStatistics, your equation would look something like this:

AMOUNT >= @FieldStatistics("TRANSACTION_AMOUNT", 11)

The difference is that the second equation is reusable and “sharable” because it uses a parameter that fluctuates with each file instead of a hardcoded value. You save time by doing work once instead of doing the same work repetitively.

How does it work? @FieldStatistics is a numeric function. It uses three look-up tables (arrays); one for numeric statistics, one for date field statistics, and one for time field statistics.

Syntax: @FieldStatistics(“FieldName”, Statistic)

The “FieldName” is the field from which you want the statistic. Commonly, that will be the same field you are comparing to the statistic, but the function will allow statistics from other fields to be called into your equation if you wish. For example, if you want the average invoice amount, you would choose the invoice amount field from your data. The field name must be encapsulated in double-quotes in this function. If the field name is properly encapsulated, it should display in purple. Un-encapsulated field names display in blue. If you forget the double-quotes, you will get an “Invalid parameter” error message.

The Statistic is the number in the lookup table (array) assigned to that particular field statistic. In the example above, the average value is the 11th statistic in the lookup table. If you open the Field Statistics view, you can count down the list and you will find Average Value is the 11th statistic.

An easier way to find the lookup table number for a statistic is to open the Equation Editor and use the Assigned Numbers for Field Statistics lookup. Click the hyperlink, scroll to find the desired statistic, then enter that statistic’s number in the equation.