7 Replies Latest reply on Dec 19, 2012 1:08 PM by BeachedWhale

    Subtotaling Calculations

    BeachedWhale

      Title

      Subtotaling Calculations

      Post

           I've grouped the data by name. There are multiple entries under the same name and they have a calculation field that calculates price with some formula. I want to create a subtotal of the prices under the same name. I tried creating  a field that is a summary total of the calculation field, but when I do add trailing subtotal, it says the subtotal is just the last price for that group. What am I doing wrong?

           I'm new to filemaker so let me know if there are other things I should mention so that people can better respond with a solution.

           Thanks!

        • 1. Re: Subtotaling Calculations
          Abhaya

               Hi

               I'm giving a simple example here and hope you will get your answer from there.Bcoz i'm little bit confused  on your post.

               Suppose you have multiple product having its own price. If you want to show these products by their Product Type having total price(by product type as well as whole product ) then please follow these steps.

               1) Create a layout(in LIST mode) based on product and put all fields in its BODY parts.

               2) Add one subsummary part based on the product type and put it above the body part.

               3) Create a summary field(Total product price) that is the total of that product price field.

               4) Add another subsummary part based on the product type and place it below the body part and there place this "Total product price" field.

               It will sum of all the product's price, subsummary by the product type.

               5) Add one grand summary part below the body part and place the "Total product price" on it.

               It will sum of all the product price on product table.

               Thanks

          • 2. Re: Subtotaling Calculations
            BeachedWhale

                  

            I think that was what I tried, I can outline my attempt exactly what I did.

            1. Created list layout with required fields.

            2. Added trailing group by Product type.

            > Everything looks right up to here and I have to subtotal and grandtotal two things: Quantity Ordered and Total paid

            3. I added a Trailing Subtotal and a Trailing Grand Summary for Quantity Ordered and it totaled correctly.

            > Here is the problem

            4. I added a Trailing Subtotal for Total paid and it lists the last price in that group as the subtotal (see attached image).

            Let me know if this clarifies what the problem is!

            Much appreciated.

            • 3. Re: Subtotaling Calculations
              Abhaya

                   Hi

                   I think you have done the same thing but check it again from the above points.

                   1) Make a subsummary by PRODUCT TYPE and placed it on above and below the body part both are sorted by the TYPE.

                   2) keep two TotalSummary field TotalQuantity and TotalCost on the subsummary below the body part.

                   3) Keep two TotalSummary field on the Leading or trailing GrandSummary part to show the total of each record.

                   And the most important is make sure the layout is sorted by its PRODUCT TYPE field.T o do so go to browse mode,click on SORT on the Statusbar and their sort it by the PRODUCT TYPE field.

                   Hope now it will work for you.

                   Please see the attached image and find the details. TotalCost in red color is the grandtotal.

                    

              • 4. Re: Subtotaling Calculations
                philmodjunk

                     Note that cOrderItem is from a related table. This can affect how summary fields evaluate.

                • 5. Re: Subtotaling Calculations
                  BeachedWhale

                       Hi 

                       Sorry, I'm really new to this, but how do you create a subsummary? I checked like you told me to, and it is sorted by Product Type.

                       Also right now I have multiples of product type, I want to get the subtotals of those product types and then the grand total of these subtotals. Is there a way for me to display just that as well? I wanna delete the duplicates of the same product type and only display their subtotals and the grandtotal.

                       Thanks

                  • 6. Re: Subtotaling Calculations
                    philmodjunk

                         First, use a list view layout instead of a table view like you have shown.

                         Then, when in layout mode, use part set up to add a sub summary layout part. When you do, specify "when sorted by" your product type field.

                         Put your product type field and the summary field in this sub summary layout part.

                         If you then sort your records by product type, you should see your records grouped by product type and one sub summary layout part for each product type group with the sub total in this layout part.

                         To get a grand total and the bottom of your report, place the same summary field in a trailing grand summary layout part.

                    • 7. Re: Subtotaling Calculations
                      BeachedWhale

                           Thanks! That worked perfectly, the format is exactly how I wanted :)