IDEA Tech Tip: Finding the Minimum and Maximum of a Date Range

August 22, 2017

Q: I want to write an IDEA script or a custom function to find the minimum and maximum value for a date range by using a criterion, such as vendor. So, if I have this file:

Vendor

Date

111

03-Mar-17

111

28-Feb-17

111

01-Jan-17

112

07-Mar-17

112

01-Mar-17

112

28-Nov-16

112

20-Nov-16

 

I would like it to become the following with the Vendor and the Latest and Earliest date.

Vendor

Earliest Date

Latest Date

111

01-Jan-17

03-Mar-17

112

20-Nov-16

07-Mar-17

 

A: This is a great question! You don't really need a script or custom function to perform this task. It can be done using IDEA's built-in functionality that then can be automated if you want to create a script. Using your example, I brought the info into IDEA.

The next step is to sort the file in order by Vendor and then by Date. I did this by creating a new file using the Sort function.

This gave me a new file where the Vendor and Date are in order:

I then did two summaries, each one slightly different. For the first summary, I want to get the Vendor and the Earliest date. I summarized by Vendor, and for the option where I can select additional fields I selected the Date field. At the bottom I also selected "Use fields from first occurrence". This will give me a file with a unique Vendor per line, in this case the Earliest date.

I would then rename the Date field to Earliest Date.

Now I would perform the exact same summary, but in this case I would select "Use fields from last occurrence" and rename the date field to Latest Date.

The final step is joining the two summaries together. You can take either file as the primary and secondary (because they are both the same). For fields in the primary I selected the Vendor and the Date field, and in the secondary I only selected the Date field (or whatever it has been renamed to). The match would be on Vendor and the matching order would be Matches only (but All Records in primary file would also work).

You now have your file by vendor with the earliest and latest date. As these are all IDEA steps they can be easily created into a script.

– Brian Element, IDEA certified instructor and Financial Advisor at Public Works and Government Services Canada

For more tips or to ask your own IDEA question, visit www.ideascripting.com.  

 

About Alain Soublière:

Alain Soublière has many years of experience working with computer audit software. He worked in a senior management role as the IDEA Product Manager for many years before becoming Director of Product strategy for CaseWare Analytics and more recently the Chief Product Strategist.   

Connect:    Alain Soublière

« Back to Blog

Add new comment

Plain text

  • No HTML tags allowed.