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

# Summary Count and Total issue

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,

[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

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&quot;_&quot;trim(str())&quot;_&quot;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

Data Kruncher,

That did the trick.