4 Replies Latest reply on Aug 7, 2013 6:22 AM by JeromeSimons

    Calculating the difference between two successive records

    JeromeSimons

      Title

      Calculating the difference between two successive records

      Post

           Dear All,

           I wish to introduce a new field that calculates the difference in days between two dates. The table would hold disbursements which are identified by a grant ID and a disbursement number (1st 2nd etc.).

           I have seen the excellent example Calculation between 2 records in same table. that talks about how to calculate values within one field but could not figure out how to use the table occurences and sorts in a manner that would actually display the difference between two successive records. From what I have, I get the number of days elapsed since the earliest date as can be seen in the screenshot. I would, however, need the number of days elapsed since each successive disbursement.

           I would greatly appreciate your help.

           Best,

           Jerome

      screen.jpg

        • 1. Re: Calculating the difference between two successive records
          philmodjunk

               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.

          • 2. Re: Calculating the difference between two successive records
            JeromeSimons

                 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!

            • 3. Re: Calculating the difference between two successive records
              philmodjunk

                   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.

              • 4. Re: Calculating the difference between two successive records
                JeromeSimons

                     Thank you so much for your help! That worked like a charm. smiley