Estimating change in rank, and how to cleanly calculate that change

[alcimedes]

I have an odd problem, and I've searched through the forums and I haven't been able to find this problem addressed previously.

 

I have a database of people who have donated money to our non-profit.

 

Based on past donation history, we send out requests for a varying amount of money. (This is where I was shown the beauty of the Case command.)

 

What I'm trying to calculate is the following. If a donor gave the amount that we requested, what would their change in rank be relative to the current ranking of donors.

 

I had *no* idea how to do this nicely, so I went with a messy but working solution.

 

I have a table with global values of the average donation at each percentage point. (a donor in the 40th percentile donates on average $23 per month. Say the 50th percentile is $28 per month.)

 

If a donor were at $23 per month and added another $5 per month, it would put them in the 50th percentile. ($28 per month) Then I just take (Total Records / 100) * (predicted percentile - current percentile) and that's approx. the number of places they'd move.

 

So the math for the above with 24,500 total records would be (24,500 / 100) * (50-40) for a change of +2450 places.

 

 

So my question is, is there a cleaner way to accomplish the above? Right now this means I have a table with 100 global values that get re-calculated each time new data is imported.

[alcimedes]

Anyone?

 

Is there anything else I can explain that might help?