IDEA Users Working Remotely: We’re here to help! Please contact our IDEA Help Desk to ensure there is minimal disruption to your daily workflow.

X
Icon


Blog Image

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.

Equation Editor with Description of @FieldStatistics Screenshot

Assigned Numbers for Field Statistics Lookup Screenshot


CaseWare IDEA , Tech Tip



Posted By

By IDEA Help Desk Team
The IDEA Help Desk is staffed by CIDA-certified data analytics experts. Our team has one goal - to help you get the most from IDEA. In addition to providing one-on-one support for using any IDEA feature or function, they also provide Tech Tips to keep your skills sharp.


Related Posts
Analyzing the General Ledger
Jun 20 The general ledger is the core of the financial reporting system, so it’s an ideal place to start an audit engagement. Analyzing 100% of the general ledger da...
New Perspectives (AHIA) Article on Data Analysis Challenges
Apr 19   Executive Summary   When it comes to using data analysis in place of manual audit processes, the benefits clearly outweigh the challenges. From...
Retail
Jan 01 Retailers have many of the standard ledgers, purchasing and payroll can be key concerns. However, the main business areas are sales and inventories. Retailers o...
BROWSER NOT SUPPORTED

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

×