2 Replies Latest reply on Dec 7, 2009 11:46 AM by comment_1

    Dividing summary field by summary data in a separate table

    abailey3313

      Title

      Dividing summary field by summary data in a separate table

      Post

      I am trying to divide a summary field that calculates a Net Income by a separate summary field from another table, which shows the amount of customers. The basic formula I'm looking to calculate is:

       

      sum.net / Customers

       

      My first attempt was creating a calculation field with that formula, to no avail (field populated with a question mark). I attempted using the GetSummary function, as such: GetSummary( sum.net, Customers ). This time the question mark is gone and the field returns blank. Am I using the incorrect break field in the GetSummary command? I currently have the field set up on a sub-summary section of a report that is already sorted based on other data fields there. My end-result should be a single column on this report reflecting the above equation.

       

      The Customers field is generated in a separate table and comes as a result after several other calculation fields are created in a script.

       

      Another wrinkle: the final report where this column should appear is broken down by Job Numbers. So, for instance, there could be 15 different Job Numbers on this report and the ( sum.net / Customers ) calculation would need to happen individually for each job, taking the sum.net of said job and dividing it by the total number of Customers overall. 

       

      I am using FM 5.5 (unfortunately) on XP.

       

      Any ideas? I'm quite confused...

        • 1. Re: Dividing summary field by summary data in a separate table
          abailey3313
             I also tried Job Numbers as the break field, as well as Job Code (which I previously failed to mention -- Job Code would be a code attached to each client, under which you'd find these Job Numbers. For instance, there may be a Job Code of XX with Job Numbers of XX001, XX002, etc). Neither of these seemed to work.
          • 2. Re: Dividing summary field by summary data in a separate table
            comment_1
              

            To use a sub-summary value in a calculation you must use the GetSummary() function, and the breakfield must be the same field as the one defined for your sub-summary part. In your case, it would be:

             

            GetSummary ( sum.net, Job Number )

             

            Then you can divide the result be another value, e.g.:

             

            GetSummary ( sum.net, Job Number ) / AnotherTable::Customers

             

             

            I didn't get how your tables are related, and what exactly is the Customer field - make sure that the reference does return some valid value, otherwise you'll be dividing by zero and get a question mark as the result.