Solved

Determining Basic Trends

Posted on 2011-02-11
6
499 Views
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.
0
Comment
Question by:FNDAdmin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 5

Expert Comment

by:jaiminpsoni
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...
0
 

Author Comment

by:FNDAdmin
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.
0
 
LVL 27

Assisted Solution

by:aburr
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 37

Accepted Solution

by:
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.
http://www.shodor.org/unchem/math/lls/index.html
0
 
LVL 27

Assisted Solution

by:CaptainCyril
CaptainCyril earned 167 total points
ID: 34886746
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
 

Author Comment

by:FNDAdmin
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.
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Part One of the two-part Q&A series with MalwareTech.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Six Sigma Control Plans

623 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