3 Replies Latest reply on Jul 14, 2014 4:45 PM by alquimby

    Can't get 15% of the Sum

    maddie

      In a report I'm working on, I have two subsummary parts. The first subsummary is by the contact's name, which shows what they've earned in total. The second subsummary is sorted by date, which displays what they earned for that day. Then all the individual records below that to show the seperate dollar amounts if they conducted more than one purchase. My client wants to see 15% of what a contact has earned when running the report, both in the contact name subsummary and by the date subsummary.

       

      I am working with Contact Sum, which is a summary field that is the total of Contact Ttl. Contact Ttl simply adds up their profits and sales if the status is paid.

       

      The calculation field's context is Contact Sum * .15

       

      This should work, yet the field is repeating 1486.05 across all names and dates no matter who they are or how much they earned. After a bit of mathmatics I found out that the 1486.05 is the grand total of what everyone earned together. This is not what I want it needs to be seperated by name and date.

        • 1. Re: Can't get 15% of the Sum
          mikebeargie

          Sounds like you might need to use GetSummary() instead since your summary is not breaking.

           

          Something like: GetSummary(Contact Sum ; Contact Name) * .15

          • 2. Re: Can't get 15% of the Sum
            Malcolm

            The magic of summary fields breaks down when they are used in calculations.  If you put your summary field and your calc field side by side and produce a summary report you’ll see that the summary field displays the correct values for each summary part. The calc field displays the correct value for the entire found set. The calc field has to be regarded as a global field, it produces the same result for each record and each summary part. Frustrating isn’t it?

             

            You are really trying to obtain a weighted average, which is supported by summary fields. If you add a number field , e.g., “commission”, and populate it with the  appropriate amount then you can produce a weighted average.

             

            Malcolm

            • 3. Re: Can't get 15% of the Sum
              alquimby

              Maddie,

               

                   In each line item you could have a calc that multiplies Earnings by .15. Then create a summary field that totals this calc. Use this new summary field in both the reports that sort by name and date.

               

              Al Quimby