In the manufacturing world in which I live, we worry about things like First Pass Yield (FPY), something I will show how I calculate in T-SQL in a future post. In general, if you don’t know, FPY is the percentage of widget that makes it through the manufacturing process the first time, without rework or retest. I work in electronics manufacturing, so rework can, and has been, a big deal, resulting in low FPY.
While building an SSRS report that included daily FPY results, and I wanted to put some indicator of a trend, so the daily result would have some context – are we doing better or worse than we have been. So I wrote a stored procedure to determine trend, a very simplistic thing from some information I found online about how to calculate trends.
The formula I used involved some higher math that I don’t truly understand, but was finally able to convert to T-SQL to the point where it looked like this:
select
((@n * sum(x*y)) - (sum(x)*sum(y)))/
((@n * sum(Power(x,2)))-Power(sum(x),2)) as r
from @sample
Here, @n is the count of the rows in the table variable @sample, x is the week number, and y is the FPY average for that week. I did a select on our FPY data to gather the average FPY per week for 12 weeks, but the example given below will only cover five, for simplicity’s sake.
So, to wit:
/***************************************
Determine trend direction for FPY samples
***************************************/
DECLARE @Sample TABLE (x FLOAT, y FLOAT)
DECLARE @n int
-- some sample data
INSERT @Sample
Select x=1, y=.98
UNION ALL
Select x=2, y=.96
UNION ALL
Select x=3, y=.88
UNION ALL
Select x=4, y=.81
UNION ALL
Select x=5, y=.76
select @n=count(*) from @sample --this is just for convenience, you can substitute count(*) for @n in the code
select -- the big formula
((@n * sum(x*y)) - (sum(x)*sum(y)))/
((@n * sum(Power(x,2)))-Power(Sum(x),2)) AS r
from @sample
Utilizing the given data, the result ® works out to -.059, which should be the slope value of a trend line, and it makes sense, as the numbers are obviously going down. (In the below graphs, the data is the thick green line, the trend the thin black line.)
If the numbers are reversed like so:
INSERT @Sample
Select x=1, y=.76
UNION ALL
Select x=2, y=.81
UNION ALL
Select x=3, y=.88
UNION ALL
Select x=4, y=.96
UNION ALL
Select x=5, y=.98
The result works out to .059, an upward trend, again obvious from the numbers given.
A more random order, like so:
INSERT @Sample
Select x=1, y=.76
UNION ALL
Select x=2, y=.88
UNION ALL
Select x=3, y=.96
UNION ALL
Select x=4, y=.81
UNION ALL
Select x=5, y=.98
Gives a result of .037, a smaller upward trend, but still positive, still upward.
From this code, then, I could formulate my reports to place either an upward pointing or downward pointing arrow next to my bar chart (for each manufacturing area) in my report, based on the positive or negative value of the trend for the time period, and the group of managers could see at a glance both what yesterday’s FPY was from the bars on the chart, and which way each manufacturing area was trending.
By the way, if there’s a better way of doing this, please let me know, I am tring to give back to the community that has taught me so much, but I am more than happy to learn more from that self same community, thank you so much.
