3 Replies Latest reply on Feb 4, 2011 11:16 AM by philmodjunk

    Repeating calculation to add the first X related records?

    trialuser1111

      Title

      Repeating calculation to add the first X related records?

      Post

      I am struggling to build a calculation that sums the first X records of a related table (for a chart).  The records in the related table are sorted in descending order, and I want to sum the values of number fields for the top 5, the top 6-10, and the top 11-25.

      How would I go about this?

        • 1. Re: Repeating calculation to add the first X related records?
          philmodjunk

          Any way you can give each group of records a common value? That would be simplest. There are calculations that can use a serial number (if there are no gaps in the order) that can do this. Let me know if this sounds possible and I'll expand on this in a subsequent post.

          If not, perhaps by using evaluate and List functions.

          Let ( L = LeftValues ( List ( RelatedTable::ValueField ) ; 5 );
                  Evaluate ( Substitute ( left ( L ; length (L) - 1 ) ; ¶ ; " + " ) )
                )

          Note: left ( L ; length (L) - 1 ) strips off a trailing return produced by using LeftValues.

          Modifying the above expression by using MiddleValues instead of LeftValues will return your other sub totals.

          • 2. Re: Repeating calculation to add the first X related records?
            trialuser1111

            The values of the fields I'm looking to sum are likely to be unique (but not guaranteed to be).  Other fields of the records I'm looking to sum are common, however.  How would I go about implementing your first method?

            Thanks

            • 3. Re: Repeating calculation to add the first X related records?
              philmodjunk

              What value in what field would be in common for the first 5 records and no other records in the set you want to chart?

              Do they have, or is it possible to number them with serial numbers in  a an indexed field so that they are numbered in order starting with 1?

              Ceiling ( SerialNumberField / 5 )

              For example will return a 1 for the first 5 records, 2 for records 6 - 10, 3 for 11 - 15....

              With some such value to correctly identify each group of records, you can bring up these records on a layout based in the related table, sort them by this field with a common value and define a summary field to compute the total of the field you want to chart. With that approach, you specify "use data from found set", and "Show data points for groups of records when sorted". For your chart axis, you specify the summary field instead of the original data field.