3 Replies Latest reply: May 15, 2014 9:56 AM by Data Kruncher RSS

    Summary Count and Total issue

    Bradl Vogl

      I need to determine labor time for assemblies.

      We have various processes involved.

      We collect times at each process station.

      A job for 100 "XYZ" assemblies may take 10 minutes total at process 1 and 10 minutes total time at process 2. This would be a total of 20 minutes to produce 100 assemblies.

      There are 3 fields that make each job unique.

      Assembly #, Qty and S/O (sales order #).

      All records that are the same to these 3 fields would be totalled for time and the Qty field would remain constant.

      The example below would result in the 1st 2 records being the same with quantity, S/O and Assembly #, therefore....unit time would be the total of both times divided by 100.

      The last 3 records would be a total of 2 jobs. 2 records combined and 1 record a separate job.

      What I need is:

      If Assembly, Qty and S/O are the same then total time and divide by the untotalled quantity.

      I have only achieved getting total time but also total quantity......how can I keep the quantity constant?

       

      Any help would be greatly appreciated.

       

      Thanks, in advance, for any ideas,

       

      Brad Vogl

       

       

      [font="courier"] 

      Assembly     Qty     Process                   Start                     End     Minutes     Unit Time     S/O .       

      695027172     100     WIRE PREP 1     2/26/2007 3:52:19 PM     2/26/2007 3:53:52 PM     1.55     0.016     124795       

      695027172     100     WIRE PREP 2     2/26/2007 3:52:31 PM     2/26/2007 3:53:56 PM     1.42     0.014     124795       

      CABGR-236-002     100     ASSEMBLY 5     2/26/2007 3:53:04 PM     2/26/2007 3:55:08 PM     2.07     0.021     127139       

      CABGR-236-002     13     WIRE PREP 3     2/26/2007 3:52:48 PM     2/26/2007 3:55:29 PM     2.68     0.206     127139       

      CABGR-359-001     1     PINNING 3     2/26/2007 3:51:50 PM     2/26/2007 3:54:03 PM     2.22     2.220     128038       

      CABGR-359-001     1     PINNING 4     2/26/2007 3:52:05 PM     2/26/2007 3:54:38 PM     2.55     2.550     128038       

      CABGR-362-001     100     ASSEMBLY 4     2/26/2007 3:53:20 PM     2/26/2007 3:55:03 PM     1.72     0.017     128038       

      CABGR-362-001     1     PINNING 1     2/26/2007 3:51:21 PM     2/26/2007 3:56:24 PM     5.05     5.050     128038       

      CABGR-362-001     1     PINNING 2     2/26/2007 3:51:35 PM     2/26/2007 3:56:29 PM     4.90     4.900     128038     

      /font[/quote]

        • Summary Count and Total issue
          Data Kruncher

          OK Brad, let's give this a whirl.

           

          As the only mechanism which Monarch offers to totals over multiple records is a summary, that's what we need to design.

           

          Further, we need to summarize every unique occurence of the combination of Assembly #, Qty and S/O. Let's build a calculated field so that we can do that. We'll name the field Assembly_SalesOrder_Qty with the formula:

          [font="courier"]Assembly"_"trim(str())"_"trim(str(Qty))  /font[/quote]Now create a summary which doesn't display a total line.

           

          Make Assembly_SalesOrder_Qty the key field, and add the Unit Time to the meaure list.

           

          Double click the Unit Time measure item to bring up the editor. Change the title to Assembly Time, and on the Calculation tab click the Edit button to change the SUM() formula to read  [font="courier"]SUM()/AVG(Qty)[/font][/quote]and set it to display 5 decimal places.

           

          This gives you:

          [font="courier"]Assembly_SalesOrder_Qty       Assembly Time

          695027172_124795_100             0.00030

          CABGR-236-002_127139_100     0.00021

          CABGR-236-002_127139_13             0.01585

          CABGR-359-001_128038_1             4.77000

          CABGR-362-001_128038_1             9.95000

          CABGR-362-001_128038_100     0.00017

            /font[/quote]Since the Qty doesn't change within a "key" group, using the Avg function should always be equal to the Qty value for that key group.

           

          Does this give you the results you'd expect?

           

          Kruncher

          • Summary Count and Total issue
            Bradl Vogl

            Data Kruncher,

             

                  That did the trick.

                  Thanks for your help!!

             

             

            Brad Vogl

            • Summary Count and Total issue
              Data Kruncher

              Good news!

               

              Thanks for the update Brad.