Hello, I'm having a little difficulty progressing some work whereby I'm trying to show the median number of consecutive years our new donors give for, broken down by each year in which they started giving.
For example, of the three donors who gave for the first time in 2000:
- Jill went on to give in 2001 and 2002. She therefore gave for 3 consecutive years
- Jane went on to give in 2001, 2002, 2003, 2004, she didn't give in 2005 or 2006, but gave again in 2007 and 2008. She therefore gave for 5 consecutive years - we discount 2007 and 2008 in this scenario.
- John C went on to give in 2001 but stopped giving thereafter. He therefore gave for 2 consecutive years
Based on the above, the median number of first-time donor consecutive years giving for 2000 was 3 years. What I'm interested to see is how this changes over time - have first-time donors been giving for more or fewer years consecutively since our fundraising campaign started?
To achieve this I began by adding Constituent ID and Gift Date into Rows, grouping the date by year. I then created a calculation ("Determine consecutive giving") to determine whether or not each donor had given for consecutive years:
if SIZE() == 1 then 0
elseif first() == 0 and DATEDIFF("year", MAX([Gift Date]), LOOKUP(min([Gift Date]),1)) > 1 then 0
elseif DATEDIFF("year", LOOKUP(min([Gift Date]),-1), MAX([Gift Date])) > 1 then 0
elseif PREVIOUS_VALUE(1) == 0 then 0
else 1 end
As you can see, in order to mark the donor with a 1 against the year in question the following criteria has to be met:
- The donor has to have gifts in more than one year
- The difference between the current donation year and the next cannot be greater than 1 year
- The difference between the previous donation year and the current donation year cannot be greater than one
- The previous year should have met all of the above criteria too (this ensures that if their consecutive giving is interrupted at any point then any future consecutive giving will be discounted)
This is computed along the Gift Date for each Constituent ID. I then use this calculation to count the total number of years each donor gives for consecutively from their first donation ("Consecutive years giving from first gift"):
if LOOKUP([Determine consecutive giving],first()) == 0 then 0
else window_sum([Determine consecutive giving])
This is also computed along the Gift Date for each Constituent ID. It produced the following result:
This is where I can't get any further. Firstly, how do I produce a median value for each year's consecutive giving as described above? And secondly, how do I visualise that?
Any help would be greatly appreciated.