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


Related Posts
Tech Tip: Transforming Your Data
May 15 We are inundated with data on a daily basis. Here are a few time-saving tips for transforming your raw or jumbled data into useful information.   Adding...
Task Automation Using IDEA
Apr 16 Audit scenarios rarely require an entirely unique process. Having a preferred set of tests ready to go is a great time saver, but that can be further improved i...
Gain Productivity with RPA
Apr 16 Leaders of professional associations are urging auditors to embrace emerging technologies to advance the industry, create audit efficiencies and manage organiza...
BROWSER NOT SUPPORTED

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

×