Hello Group Members,
We routinely have to compute the difference in two dates in a database as a measure for operational effectiveness or to perform bucket analysis (aging) or to calculate interest on delays and more.
The MS-Excel way to arrive at the difference in two dates is to perform a Date 1 - Date 2 to arrive at the difference in dates in a new field in Excel.
In IDEA we do it differently using an @Function.
We append a virtual numeric field with the function @age(Date1, Date2) where Date 1 is the Later Date and Date 2 is the Earlier Date.
So to calculate the difference in Collection Date and Bill Date for interest levy on delays we do this by appending a virtual numeric field titled 'Collection Lag' with the criteria @Age(Collection Date, Bill Date)
Just as a side - bar topic for discussion if we need to convert this difference in days to say difference in month/s we can tweak the criteria above from @Age(Collection Date, Bill Date) to @Age(Collection Date, Bill Date)/30
In the same example being discussed if you need to apply the criteria - @Age(Collection Date, Bill Date) through a Direct Extraction to filter out records where say Collection Date is prior to Bill Date which is an abnormal state the criteria in the Direct Extraction will be @Age(Collection Date, Bill Date) < 0 to capture all negative differences.
Please note this criteria - @Age(Collection Date, Bill Date) < 0 will be applied only through an extraction and not a field append to get a list of negative differences.
Regards
Group Admin Team