Audimation Services has been acquired by Caseware International Learn More.

X
Icon


Blog Image

Using Nested @Functions()

IDEA Tech Tip


One of the most powerful tools in IDEA is the ability to create equations, save them as “.eqx” files, and share them with colleagues. By utilizing the list of @functions in the Equation Editor, you can filter data via Criteria or append new fields to your database. Learning to combine the many different functions in creative ways can open a wealth of new capabilities that you may not have known your IDEA software is capable of.
 

Syntax

 
All of the functions used in the IDEA Equation Editor start by typing the “@” symbol followed by the function name and a set of parentheses which contain the parameters that must be entered for the function to work. The parameters are different for every function. Fortunately, the equation editor provides you with a detailed description of each function, including the parameter definitions and examples of use.

To view the description for an @Function, open the Equation Editor and select the one you are interested in. The description will appear in a window on the right.

@Ctod function converts dates stored with a Character field type to a Date field type in IDEA

The first parameter is usually the value being evaluated. In the screenshot above, the @Ctod() function asks for a string. This could be a Character field or could be just written text.
 

Nesting

 
You can use an @Function in place of a string or parameter inside another @Function. This is commonly called “nesting.” Nesting is very useful when the string’s or parameter’s value would change from record to record.

When building a nested IDEA equation, it may be helpful to think of it as being very similar to an algebraic equation since they use many of the same rules. For example, the “Order of Operations” rule in algebra dictates that operations within parentheses occur before those outside. This is true when writing IDEA equations as well.
 

Example:

 
Let’s say you have three Numeric fields called MONTH, DAY, and YEAR. You’ll need to concatenate those fields into a single Date field if you want to do any sort of aging or other date related analytics. It’s important to note that Numeric fields cannot be concatenated, so the data in the three numeric fields must first be converted to character data, then concatenated. Once concatenated, the resulting value would need to be converted to a date data type. All these steps can be performed with one nested equation similar to this:

@Ctod(@Str(MONTH, 2, 0)+@Str(DAY, 2, 0)+@Str(YEAR, 4, 0), “MMDDYYYY”)

As noted above, the syntax for the @Ctod function is @Ctod(String, Mask). This part of the equation, @Str(MONTH, 2, 0)[email protected](DAY, 2, 0)[email protected](YEAR, 4, 0), serves two purposes. It converts the numeric values in the Month, Day and Year fields to character data type and is used as the String in the @Ctod function. This part of the equation, “MMDDYYYY”, is the Mask. The equation only works because the operations inside the inner most parentheses were completed first.

We hope this will be very helpful to your team. And, as always, if you have questions about this Tech Tip or other IDEA questions, please give our help desk a call at:

888-641-2800 Option 4
Or email us at [email protected]


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
No Image
Mar 21 More than ever, auditors are taking on the role of programmers; writing scripts to run automated analysis and tests on a regular basis for the organization. Luc...
Time Delay on PDF Files
Jun 07 Have you ever gotten frustrated when working with building a template on extremely large PDF files because of the time involved for IDEA to open these files? Th...
Tech Tip: Identifying Duplicates and Removing Reversals – Revisited
Jun 16 In the Aug-2019 newsletter we reviewed this process using IDEA v9 or v10. IDEA 11.2 released 13-Oct-2020 updates the analytic, Duplicate Key Exclusion: You c...
BROWSER NOT SUPPORTED

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

×