Need Help With Tabulate Summary Statistics

[#1]

I am transferring my companies quality metrics to JMP, and I am having a hard time with something that seems simple enough. My JMP newbness shows...

I have a "tabulate" table that looks something like this:

                                                 January                   February                  March ...

Promise qty                                   10                              12                          15

Customer defects                        -1                                  0                           -2

Net pcs to process                       9                                 12                          13

(My company) defects                 0                                  -2                           -3

Yield Loss (%)

The data comes from a master table with data by order (the table above is obviously fake and I am working with larger quantities) . Everything is working fine with the quantities filtered by the month columns. The problem I am having is the yield loss % and filtering that by month. Since there are multiple rows on the master table, I cannot figure out how to get the % Loss for the month's numbers. In excel, for the yield loss, the formula would simply be "=(My comany) defects/Net pcs to process". The values for Yield Loss (%) in the above I'd be looking for are 0%, -16%, -23%.

I do not wish to have the mean or sum of the % yield loss. In other words, if my master table has 2 lines in the same month:

       Month               Order        Prom Qty             (my company) defects                Yield loss (%)

      February            Order A            4                                    -1                                       -25%

      February            Order B            8                                    -1                                       -13%

I do not want the yield loss calculation to be the mean (-19%), or the sum (-38%), rather the aggregate value filtered by the month, -3/12 (-16%). How do I do this?

I hope this question doesn't seem too stupid- I am very familiar with excel and trying to better learn JMP, and this one is giving me fits. Any help is appreciated. I am not very good with scripting and would prefer an explination.

Thanks!

[#2]

It is a little hard to decipher exactly what your original table looks like (you might attach a fake example small table if you want a more precise response), but I think these comments might help.  Before you tabulate, you might create a new column.  The new column can use the Statistical functions Column Sum (defects, by Month) divided by Column Sum (Qty, by Month).  Once the new column is showing the correct percentages, then use it in your tabulate command - probably using the mean if the tabulate is by month (the mean should work since each month will have only one value since the new column formula is using the by Month).

If this doesn't make sense and you want a solved example, upload a sample fake worksheet that looks like your original table.

[#3]

Hi Dale,

Thanks a lot for the reply. I am still having some trouble with getting the formula results I want. Attached is a sample table, the tabulate is saved as a script if you wouldn't mind taking a look.

Thank you,
Aaron

[#4 · Accepted solution]

Attached is what I am suggesting.  I've created a new column (defect rate) and provided a new tabulation.  Let me know if this is what you are looking for.

[#5]

Hi Dale,

I needed the rate from different columns but I was able to look at your formula and this gave me exactly what I needed. I wasn't able to sort by column and month, until now. Thank you very much.

Aaron