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.
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
powerN  challenge 3 47
Linear algebra 3 52
What is Python programming? 3 66
Homework Help 5 49
Lithium-ion batteries area cornerstone of today's portable electronic devices, and even though they are relied upon heavily, their chemistry and origin are not of common knowledge. This article is about a device on which every smartphone, laptop, an…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now