Better Business Through Data Analysis & Monitoring My Account

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)+@Str(DAY, 2, 0)+@Str(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 helpdesk@audimation.com

Print

Number of views (1507)/Comments (0)

Brad Newman

Brad Newman

Other posts by Brad Newman
Contact author

Please login or register to post comments.

Name:
Email:
Subject:
Message:
x
Jeremy Clopton Joins Integration Partnership Program to Help Professionals State & Sustain Their Use of Data Analytics
18 April 2018

Jeremy Clopton Joins Integration Partnership Program to Help Professionals State & Sustain Their Use of Data Analytics

Data analytics & fraud detection expert Jeremy Clopton joins the Audimation Integration Partnership program to help organizations leverage data analytics.

Read More

Think Like a Thief to Catch One
11 April 2018

Think Like a Thief to Catch One

Apply Logic to Detect Peculiar Behavior

When working with rows & columns of data to spot suspicious behaviors, fraud & errors can look very similar to one another. Tips on sniffing out fraud.

Read More

5 Elements of a Successful Data Analytics Program
12 March 2018

5 Elements of a Successful Data Analytics Program

Building a successful, sustainable data analytics program takes effort…and a few other elements.

Read More

The Power of Field Statistics
7 March 2018

The Power of Field Statistics

IDEA Tech Tip

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?

Read More

Using Data Analytics to Prepare for Fieldwork
20 February 2018

Using Data Analytics to Prepare for Fieldwork

Automating High-Risk Transactions with IDEA

See how a global corporation used IDEA to develop an automated process to prepare auditors for on-site work.

Read More

People. Processes. Tools.
5 February 2018

People. Processes. Tools.

Successful Integration of Data Analytics

Expert IDEA users share their experiences and advice for building and sustaining a successful data analytics program.

Read More

Planning & Scoping Your Audit with Data Visualization
1 February 2018

Planning & Scoping Your Audit with Data Visualization

Data-Driven Insights Save Hours of Effort

Here are some ways IDEA can help you plan and scope an audit using the data visualization features within IDEA.

Read More

Classroom Collaboration at University of Missouri - St. Louis
30 January 2018

Classroom Collaboration at University of Missouri - St. Louis

Student Learn to Develop Analytic Tests

Masters students attending the University of Missouri - St. Louis share their results using data analytics to assess risk, test 100% of the data population.

Read More

Do One Thing Every Day that Scares You
30 January 2018

Do One Thing Every Day that Scares You

Virginia Tech Students Gain Confidence Using Data Analytics

Learn how Associate Professor of Practice Nadia Rogers, CPA, introduced students to IDEA, including types of audit tests that can be performed within the software and various documentation aspects of an audit engagement.

Read More

New Features in IDEA 10.3
29 January 2018

New Features in IDEA 10.3

IDEA now runs faster and offers more flexibility than ever before

The latest version of IDEA introduced a host of new features and improvements.  IDEA now runs faster and offers more flexibility than ever before.

Read More

5 Ways Data Analytics Saves Time
26 January 2018

5 Ways Data Analytics Saves Time

Turn Hours of Work into Minutes

5 time-saving ways data analytics can help you shrink your audit time, while improving the quality of your work.

Read More

Tired of Wrangling Cumbersome Client Data?
24 January 2018

Tired of Wrangling Cumbersome Client Data?

10 Ways Our Importing Services Make it Easy

10 reasons to try our data importing services, which save you time, effort and undue frustration. 

Read More

A Journey in Using Data Analytics
24 January 2018

A Journey in Using Data Analytics

Testimonial from Brian Cullum

Read how Brian Cullum used IDEA to help identify a multi-million-dollar fraud based on the overstatement of the borrower’s lending base.

Read More

Planning & Scoping Your Audit
15 January 2018

Planning & Scoping Your Audit

With Data Visualization

Data visualization is changing the way auditors are communicating with their audiences. Easy planning, find risk areas and prioritize your scope of work.

Read More

RSS