What role does grant ID play here?
Do you want to see the difference between successive disbursements regardless of grant ID or do you want to see the difference for successive disbursement with the same Grant ID?
Assuming that you need this specific to the grant ID, do you need to see these intervals with a layout like your example where disbursements for more than 1 grant are mixed together?
If you sorted your found set by grant ID and then by Date, You can use the GetNthRecord function to access data in the preceding record in your found set.
You can also set up a self join relationship that matches by Grant ID and date so that the first related record is the related record with the most recent date that is less than the date in the current record's.
Thank you so much for your response!
1) Grant ID groups together grants (i.e. a pot of money) out of which individual disbursements are made.
2) I would like to see the time difference in days between successive disbursements with the same Grant ID.
3) Eventually I wish to group the differences by Grant ID and display them in a stacked bar chart. I also wish to calculate averages when the differences are sorted/grouped by disbursement numbers to get an average amount of time between, say disbursement one and two.
4) The layout will thus be a chart or a summary where the differences will be grouped by Grant ID or by country (each grant ID is linked to a country).
Before I try the GetNthRecord, I wish to try the self join. However, when identifying grant ID and date, I only get zeroes for the date difference.
Thank you so much for your help!
The self Join would look like this:
YourTable::GrantID = YourTable 2::GrantID AND
YourTable::Date > YourTable 2::Date
Then specify that Your Table 2 be sorted by Date in descending order as part of the relationship details for this relationship.
Then this calculation will compute the difference in days:
IF ( Not IsEmpty ( YourTable 2::Date ) ; Date - YourTable 2::Date )
without the If function, your first disbursement record for a given grant ID will have a very large number representing the date in the date field.