FNDAdmin

asked on

# 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.

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.

ASKER

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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.

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...