Forum Discussion
Need Help Updating Query for New Error Rate Thresholds
Hi everyone,
Several months ago, I received a solution for calculating thresholds based on production and error rates, and it has worked flawlessly. Here is the solution:
Going forward, our organization will be evaluating and adjusting the allowable error rates based on data analysis. I would like to update the query to maintain existing error rates for past data while applying new thresholds to errors within the applicable date range. Below is the same data used previously, with an additional column for new acceptable error rates.
Production
| Cost Ctr | Location | Date | Volume |
1 | Chicago | 1/1/24 | 300 |
| 2 | Tokyo | 1/1/24 | 500 |
| 3 | New York | 1/1/24 | 200 |
| 1 | Chicago | 1/1/24 | 450 |
| 2 | Tokyo | 1/1/24 | 550 |
| 3 | New York | 1/1/24 | 175 |
| 1 | Chicago | 1/1/24 | 250 |
| 2 | Tokyo | 1/1/24 | 500 |
| 3 | New York | 1/1/24 | 300 |
Errors
| Cost Ctr | Date | Category | Part |
| 1 | 1/1/24 | Broken | Wheel |
| 1 | 1/1/24 | Incorrect Part | Gear |
| 2 | 1/1/24 | Missing | Paint |
| 2 | 1/1/24 | Broken | Wheel |
| 3 | 1/1/24 | Broken | Glass |
| 3 | 1/1/24 | Missing | Handle |
| 1 | 1/2/24 | Incorrect Part | Motor |
| 1 | 1/2/24 | Incorrect Part | Gear |
| 2 | 1/2/24 | Broken | Wheel |
| 3 | 1/2/24 | Incorrect Part | Motor |
Thresholds
Error | Part | Threshold (per 100 units) (Jan 1 - Jun 30 | Threshold (per 100 units) (Jul 1 - Dec 31 |
| Broken | Glass | 0.2 | 0.25 |
| Broken | Motor | 0.3 | 0.35 |
| Broken | Wheel | 0.1 | 0.15 |
| Incorrect Part | Gear | 0.05 | 0.10 |
| Incorrect Part | Motor | 0.6 | 0.5 |
| Missing | Handle | 0.5 | 0.5 |
| Missing | Paint | 0.25 | 0.25 |
| Missing | Wheel | 0.3 | 0.3 |
Can anyone help update the query to reflect these changes?
Thank you!
1 Reply
Hi scoutmedic
I've understand your needs, but it's still not clear to me what's your expected result.
The first screenshot you provided was too blurry for me to see.
Please provide your expected result, It would be helpful to find out the solution.
Thanks for your cooperation!
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
