I have a data set where individual samples failures from particular lots are recorded as individual rows. Each failure has partilar columns for lot#, severity, sample size, and month. I would like to add a column for total samples tested per month. I cannot simply sum the samples sizes because there are sometimes multiple rows entered per lot. Instead, I must average the sample sizes per lot and then sum those averages. I was thinking of using the cross tab and the multi-field tool but I am having trouble with the correct expression to use. Can someone offer some advice? below is an example of what I am looking to do.
This is what i currently have:
Lot | Sample Size | Severity | Test Month |
A1 | 400 | A | Jan |
A1 | 400 | B | Jan |
A1 | 200 | N/A | Jan |
A2 | 200 | N/A | Jan |
A3 | 200 | B | Feb |
A3 | 200 | B | Feb |
A3 | 200 | B | Feb |
A4 | 200 | B | Feb |
A5 | 200 | N/A | March |
A6 | 200 | B | March |
A6 | 200 | B | March |
A7 | 200 | N/A | March |
This is what I look like to have:
Lot | Sample Size | Severity | Test Month | Total Samples per Month |
A1 | 400 | A | Jan | 600 |
A1 | 400 | B | Jan | 600 |
A1 | 400 | N/A | Jan | 600 |
A2 | 200 | N/A | Jan | 600 |
A3 | 200 | B | Feb | 400 |
A3 | 200 | B | Feb | 400 |
A3 | 200 | B | Feb | 400 |
A4 | 200 | B | Feb | 400 |
A5 | 200 | N/A | March | 600 |
A6 | 200 | B | March | 600 |
A6 | 200 | B | March | 600 |
A7 | 200 | N/A | March | 600 |
Solved! Go to Solution.
This is perfect! Thank you!