X
Icon

The 21st Minute


Blog


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 hard coded 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.

Equation Editor with Description of @FieldStatistics Screenshot

Assigned Numbers for Field Statistics Lookup Screenshot


CaseWare IDEA , Tech Tip



Posted By

By Team Audimation


Related Posts
Tech Tip: Duplicate Identification
Aug 28 Raw data is often incomplete, messy and inconsistent. One of the first steps in the data preparation process is the detection and removal of duplicate records. ...
Wayfair Woes One Year Later
Jul 24 The rise in online shopping has sharply impacted state sales tax revenue. Rather than increasing the tax rate, states are setting parameters to define nexus, wh...
Importing Date Fields
Jul 24 There are times when you import a date field and it just does not work out as expected. Here are ways to address the problem before or after the import.  ...
BROWSER NOT SUPPORTED

This website has been designed for modern browsers. Please update. Update my browser now

×