IDEA Data Analysis has, for many years, offered users the ability to use Gap Detection to test for gaps in date sequences based on dates falling on working or weekend days. This has been one of the more invaluable ways that IDEA helps to identify issues with data completeness and integrity.
That said, the function only works in those situations where the expectation is that there should be at least one record for every date between the minimum or maximum dates within the field being tested. What if users want to test, not for missing consecutive dates, but for specific days? For example, where you may expect a payment run to fall on a Thursday only.
In this example, by using IDEA’s advanced Gap Detection functionality you can then test for any Thursdays that fall between the minimum or maximum dates. As you will see below, you can do this by manipulating IDEA into thinking that any day except for Thursday is a weekend day, and then by excluding the weekend days from the analysis, return only the desired days.
The following instructions are based on Version 10.4.1 of IDEA, but this test can also be achieved in older versions of IDEA 10, as well as versions 9 and 8.
- Browse to the File tab and select Options from the left-hand menu.
- When the IDEA Options dialog appears, select the Weekends buttons from the System tab.
- On the Specify Weekends dialog, tick all of the days, apart from the day or days that you want to test for any missing occurrences.
- Select the ‘OK buttons on both of the open dialogs.
- Now open the file you wish to test and browse to the Analysis tab on the main banner at the top of IDEA and select Gap Detection from the Explore group.
- Select the date field in the open file which will be tested for missing dates.
- Make sure that the Ignore Weekends option is ticked.
- Select whether to create a database, or a result, and name appropriately.
- Select the OK button.
- IDEA will then run the test and return a report detailing any missing days, based on those defined in the previous steps.
- Make sure that the Weekends days are then returned back to their correct state.