Determining Basic Trends

Posted on 2011-02-11
Last Modified: 2012-05-11
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.
Question by:FNDAdmin

Expert Comment

ID: 34874316
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...

Author Comment

ID: 34874353
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.
LVL 27

Assisted Solution

aburr earned 166 total points
ID: 34874570
" 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.
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

LVL 37

Accepted Solution

TommySzalapski earned 167 total points
ID: 34875566
"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.
LVL 27

Assisted Solution

CaptainCyril earned 167 total points
ID: 34886746

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.

Author Comment

ID: 34887475
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.

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
splitOdd10 challenge 5 108
Modify a small python script 19 108
Lock or digital lock that are lockable from both inside plus outside 12 55
Math equations 13 50
A short article about problems I had with the new location API and permissions in Marshmallow
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question