Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Determining Basic Trends

Posted on 2011-02-11
Medium Priority
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 664 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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 37

Accepted Solution

TommySzalapski earned 668 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 668 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Aerodynamic noise is the cause of the majority of the noise produced by helicopters. The inordinate amount of noise helicopters produce is a major problem in the both a military and civilian setting. To remedy this problem the use of an aerogel coat…
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 …
Screencast - Getting to Know the Pipeline

581 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