Determining Basic Trends

This should be a very common mathematical need, but I'm having trouble finding a solution.

I have an MS-SQL database with a 1:M relationship between people and donations (relevant columns would be date, DI#, total).  Obviously any given person can have any number of donations- typically less than 20 or 25.  I need to determine whether any given person is trending up, down or basically staying the same in giving.  I don't mind if they gave less in the middle, I'm looking for overall trend or trajectory.  

Ideally I would like to be able to compare trends, so someone who is substantially increasing their giving has a higher score than someone who is slowly creeping up, but I can live with just an increasing, stable or decreasing return value.  

Due to where in my code this sits, I am hoping to be able to do it without iteration.  I do have a table already containing mean/average, first, last, largest, and count of donations, and can add more summaries if need be.

Is there a way I can compare various summaries to determine a trend, for example, a formula like if mean > median and largest-average>median, then it is increasing?  I haven't found anything yet.

Thank you in advance.
FNDAdminAsked:
Who is Participating?
 
TommySzalapskiConnect With a Mentor Commented:
"This should be a very common mathematical need"

That is absolutely correct. The 'right' way to do it would be to find the formula that predicts every point with 100% accuracy. Since that obviously can't happen (and since you are really only concerned with trend) then the standard method is to basically fit a line to the data. This is what aburr is suggesting. (Forgive the following explanation if it's too simplistic). Think of plotting all your points for one person on a graph. Now stick a line in there that gets the closest to every point. (So we are basically assuming they are following a linear trend and then estimating it).

This is called linear regression and is used a great deal. The simplest way to do that is the least squares method.

That will give you the best guess at the trend.
Here is a decent explanation of how to find the slope of the line.
http://www.shodor.org/unchem/math/lls/index.html
0
 
jaiminpsoniCommented:
Is it possible for you to store the quarterly average for last quarter?

you can compare this average with the average of donation in current quarter, it will give you some good trend which focuses on the recent past. (i.e. previous quarter)

This is just a suggestion.... This is open ended question and there will be many more approaches availble to solve this...
0
 
FNDAdminAuthor Commented:
I could create the average by decade or something in my summary table and essentially compare moving averages, but I'm not sure that would really work out.  So far it is looking like I might accomplish this by if first gift<average<last gift I can see if they are trending - though it "breaks" if they are trending up by their hiccup is the first or last gift.  a large gift early in their history also breaks it.  It also looks like average compared to median gives me an idea how steeply they are trending.  I'm guessing someone knows a true rule though.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
aburrConnect With a Mentor Commented:
" I need to determine whether any given person is trending up, down or basically staying the same in giving.  I don't mind if they gave less in the middle, I'm looking for overall trend or trajectory.  "
If you do not care what they do in the middle just take the difference between first and last.
You, of course, know that this will not give you the information you want.
-
"I'm guessing someone knows a true rule though."
The "true" rule does not exist without careful definition.
You have indicated a number of circumstances which "break" various rules.  
-
"It also looks like average compared to median gives me an idea how steeply they are trending."     no
-
A suggestion is to calculate the least squares best linear fit to the individual. The slope of the line will give you the information for which you are looking.
0
 
CaptainCyrilConnect With a Mentor Founder, Software Engineer, Data ScientistCommented:
Regression
http://www.okstate.edu/ag/agedcm4h/academic/aged5980a/5980/newpage24.htm

The slope of the line will give you the trend.

Correlation or R-Squared can give you the accuracy of the regression slope.

The higher the correlation the more you can trust the values.
0
 
FNDAdminAuthor Commented:
Thank you for all your suggestions.  It appears Linera Regression woudl give me what I am looking for, but unfortunately the formulas involved are too complicated to include reasonably in the non-iterative SQL script I need to calculate this from.  I have opted for a crude comparison of recent donations that is far from 100%, but shoudl beat the 80/20 rule at least.  Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.