Audimation Services has been acquired by Caseware International Learn More.

X
Icon


Blog Image

Tech Tip: Top Records Extraction with Ranking


IDEA Lab was introduced with IDEA 11.1 in Mar-2020. Since its introduction, 13 different plugin/utilities have been introduced. Videos are posted explaining how to use each one

Feb-2021, IDEA Lab posted a new plugin, Top Records Extraction with Ranking. The plugin performs exactly like the Top Records Extraction task; however, the output database also contains a RANKING field that ranks the transactions based on the specified “Top Records for” field and grouping. The dialog box looks similar to the Top Records Extraction dialog box, except you do not have access to the Equation Editor. If using criteria, you must paste the equation into the Criteria field in the dialog box.

Using the SAMPLES project that is automatically installed with IDEA, Detailed Sales table – click FIELDS to select additional fields to include. Click OK, OK.

Results!

IDEA v9/v10
Select TOP RECORDS Extract from the Analysis tab.

Enter number of records per grouping to extract. Select type: Top (or Bottom) records. Select field from drop down for the top or bottom criteria and choose the field(s) to group/sort on. Clicking the FIELDS button to the right allows you to select the other field(s) from the record to include. All are selected by default.

Click OK and the results are the top three sales per SALESREP_NO, but wait – how do you add the ranking?
Append a numeric field I, value 1 and download the script RUNNING_TOTAL from the IDEA Script Vault

https://support.casewareanalytics.com/en/ideascript-vault/running-index-total

Select the key field, then select I as the field to total to create the rank.

Resulting RUNNING_TOTAL is your RANK field.


Best Practices , CaseWare IDEA , Tech Tip



Posted By

By Kris Willison
Kris joined the Professional Services team in January of 2015 as a Solutions Specialist. She has an extensive background in Software and Database Development accumulated from thirty years in IT support with twenty years’ experience in database development, cleanup, audit and migration using Microsoft Access. In her time with Audimation, she has received client praise for her “Top Tier” engagement on Monitor and Scripting projects. Kris enjoys looking at problems from new angles to determine the most efficient means of meeting the clients’ needs. Kris has been breeding/showing purebred Balinese cats since 1972 and Oriental Longhairs since 1996. She also hosts one of the largest online pedigree database sites for Siamese and related breeds with nearly 600 users worldwide.


Related Posts
Tech Tip: Report Reader Menu (Customize)
Jul 21 I had much sympathy for a recent caller to our help desk complaining about the size of the icons on the Report Reader ribbon. I make it a habit of using my 24�...
Tech Tip: Creating Separate Files for Summarizations
Sep 25 Follow these simple steps to create and export separate files based upon your summarization: Open the original file, in this case SAMPLE-CUSTOMERS Fro...
The Next Frontier in Data Analytics
Aug 17 by: Norbert Tschakert, CPA/CFF/CITP, Ph.D.; Julia Kokina, CPA, Ph.D.; Stephen Kozlowski, CPA, CGMA, Ph.D.; and Miklos Vasarhelyi, Ph.D. As technology continu...
BROWSER NOT SUPPORTED

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

×