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=.76UNION 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=.76UNION 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.

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.

## No comments:

## Post a Comment