10 Replies Latest reply on Jun 2, 2014 11:29 AM by philmodjunk

    Using Summary Field in Calculation to Produce Averages

    productionQC

      Title

      Using Summary Field in Calculation to Produce Averages

      Post

           Hello-

           I am having a total brain fart and can not figure out how to produce an average of totals... 

           I have a Summary Field that totals all line items on work orders, thus allowing me to produce sub summary reports and graphs that show the total parts spent on each vehicle.  What I want to do is use that Summary Field total  to calculate the average total per vehicle.  Thus, giving me the ability to graph all vehicles to see if they fall above or below this average.  So if I create another summary field using average, it won't let me select my other summary field used to total.  If I select the line item field, then I get an average of all line items, not by vehicle.

           What is the best approach to first total parts by vehicle, then calculate an average of those totals....

           Thanks

            

        • 1. Re: Using Summary Field in Calculation to Produce Averages
          philmodjunk

               When you set up a calculation that refers to a summary field, the value returned is the grand total, not a sub total like you find in a sub summary layout part. To access such a sub total, use the GetSummary function. This function has two parameters, the summary field from which you want the sub total and a "break" field. This break field is the same field you'd specify as the "when sorted by" field for a sub summary layout part.

          • 2. Re: Using Summary Field in Calculation to Produce Averages
            productionQC

                 Hi Phil-

                 thanks for the reply.  I have tried that and the field appears blank.  I checked to make sure the records are sorted using the same as the break field.  I am going to put up my layout so you can see what I am trying to do.  I used GetSummary ( TotalAllPartsUsed ; WORKORDER::Truck )....

                  

                  

            • 3. Re: Using Summary Field in Calculation to Produce Averages
              philmodjunk

                   The break field and summary field must be defined in the same table.

              • 4. Re: Using Summary Field in Calculation to Produce Averages
                productionQC

                     Hi Phil- 

                     So just so I understand - when creating a sub summary report it is ok to use a break field on a different table but not when using a GetSummary in a calculation?

                • 5. Re: Using Summary Field in Calculation to Produce Averages
                  productionQC

                       Ok, so I have made a Truck Field in Workorder_Parts_Data to lookup the truck number from the table Workorder.  I have now used GetSummary using both fields from the same table (Workorder_Parts_Data).  It's is blank ....

                  • 6. Re: Using Summary Field in Calculation to Produce Averages
                    philmodjunk

                         Are the records sorted to group them like you would for sub summary layout parts? Are you sorting them on your new "truck field"?

                    • 7. Re: Using Summary Field in Calculation to Produce Averages
                      productionQC

                           Hi Phil-

                           got it working, I had a conflicting script.  So my question now is...  As I scroll through the records, it shows the total for each vehicle.  So if there are five records for a truck in Workorder_Parts_Data, for those five it shows the constant summary and then when the truck number changes it shows to total for that truck for however many records.  So how to I now calculate the average of those records.  I tried using it in a summary field but the math is not correct.  I thought it was because it was dividing by the number of records, rather than trucks in the sort, but I can't match the result manually.  Any thoughts how I should approach this.

                      I want to get a result that shows the average of the totals derived from the GetSummary.  Thus, we can see if one truck is way above the average cost of repairs, create a baseline.

                      • 8. Re: Using Summary Field in Calculation to Produce Averages
                        philmodjunk

                             So if you have 5 trucks, you want the total cost for all 5 trucks divided by 5 and not the total of all repairs divided by the number of repairs correct?

                             And might you have a truck (say it's new) with no repairs at all logged? How would that affect your average? Include or exclude it?

                             But you have multiple work orders for each truck. Do you have a table of Trucks with one record for each Truck?

                        • 9. Re: Using Summary Field in Calculation to Produce Averages
                          productionQC

                               Hi Phil-   That's right...

                               I have 50 trucks (Fleet table with one record per truck).  Each truck has several work orders (Workorders table) and each work order has several line items (Workorder_Parts_Data table).  So I want the total of the line items, which we have accomplished with the GetSummary in the Workorder_Parts_Data table.  Thus, if a truck has 10 work orders against it, I want the average of those 10 work orders, based on the total of the line items of each work order.  As for new or any trucks with no records I wan't to exclude those.

                          • 10. Re: Using Summary Field in Calculation to Produce Averages
                            philmodjunk

                            Thus, if a truck has 10 work orders against it, I want the average of those 10 work orders, based on the total of the line items of each work order.

                            I'm not sure that's really useful to know, but this should be possible from a simple "average" summary field in your work order table. Just put it inside a sub summary layout part "when sorted by" a Truck Id field that identifies the truck and sort your work order records by Truck.

                            I would think a total cost of all repairs divided by all trucks of the same category to get an average repair cost per truck would be more useful as you could then check the total repair cost for a given truck to identify those with significantly better or worse repair costs. (This is just a rough description. To get valid data, you'd need to factor in some kind of time period over which you are averaging your costs.)