Skip to content

Instantly share code, notes, and snippets.

@bethbrains

bethbrains/Donor Streak Formula

Created March 29, 2017 20:38
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save bethbrains/bbcc4c45f9b731e27a7bdd70e2f7c3e3 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save bethbrains/bbcc4c45f9b731e27a7bdd70e2f7c3e3 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Donor Streak Formula
/*Already donated this year*/
if(year(npo02__LastCloseDate__c)=year(today()),1+
if(contains(Years_Donated__c,text(year(today())-1)),1+
if(contains(Years_Donated__c,text(year(today())-2)),1+
if(contains(Years_Donated__c,text(year(today())-3)),1+
if(contains(Years_Donated__c,text(year(today())-4)),1+
if(contains(Years_Donated__c,text(year(today())-5)),1+
if(contains(Years_Donated__c,text(year(today())-6)),1+
if(contains(Years_Donated__c,text(year(today())-7)),1+
if(contains(Years_Donated__c,text(year(today())-8)),1+
if(contains(Years_Donated__c,text(year(today())-9)),1+
if(contains(Years_Donated__c,text(year(today())-10)),1+
if(contains(Years_Donated__c,text(year(today())-11)),1+
if(contains(Years_Donated__c,text(year(today())-12)),1+
if(contains(Years_Donated__c,text(year(today())-13)),1,0),0),0),0),0),0),0),0),0),0),0),0),0),
/*donated last year but not yet this year*/
if(year(npo02__LastCloseDate__c)=year(today())-1,1+
if(contains(Years_Donated__c,text(year(today())-2)),1+
if(contains(Years_Donated__c,text(year(today())-3)),1+
if(contains(Years_Donated__c,text(year(today())-4)),1+
if(contains(Years_Donated__c,text(year(today())-5)),1+
if(contains(Years_Donated__c,text(year(today())-6)),1+
if(contains(Years_Donated__c,text(year(today())-7)),1+
if(contains(Years_Donated__c,text(year(today())-8)),1+
if(contains(Years_Donated__c,text(year(today())-9)),1+
if(contains(Years_Donated__c,text(year(today())-10)),1+
if(contains(Years_Donated__c,text(year(today())-11)),1+
if(contains(Years_Donated__c,text(year(today())-12)),1+
if(contains(Years_Donated__c,text(year(today())-13)),1,0),0),0),0),0),0),0),0),0),0),0),0),0))
@tet3
Copy link

tet3 commented Mar 30, 2017

What if you make the first IF() be >= This Year - 1, and then the second be = This Year. So it would be 1 if they've given last year but not yet this, and 2 if they gave this and last. And then go on to - 2, - 3, etc. Wouldn't that let you collapse it into one set of IFs?

@tim-npdg
Copy link

tim-npdg commented Jan 8, 2026

Tweaked the code to get 20 years of consecutive giving (or 21 if they gave this year). If someone else uses this, just make sure to replace "Years_Donoated__c" with the name of your custom field that contains the list of years that they donated.

if(contains(Years_Donated__c,text(year(today()))) || contains(Years_Donated__c,text(year(today())-1)),
 if(contains(Years_Donated__c,text(year(today()))), 1, 0) +
  if(contains(Years_Donated__c,text(year(today())-1)),1+ 
   if(contains(Years_Donated__c,text(year(today())-2)),1+ 
    if(contains(Years_Donated__c,text(year(today())-3)),1+ 
     if(contains(Years_Donated__c,text(year(today())-4)),1+ 
      if(contains(Years_Donated__c,text(year(today())-5)),1+ 
       if(contains(Years_Donated__c,text(year(today())-6)),1+ 
        if(contains(Years_Donated__c,text(year(today())-7)),1+ 
         if(contains(Years_Donated__c,text(year(today())-8)),1+ 
          if(contains(Years_Donated__c,text(year(today())-9)),1+ 
           if(contains(Years_Donated__c,text(year(today())-10)),1+ 
            if(contains(Years_Donated__c,text(year(today())-11)),1+ 
             if(contains(Years_Donated__c,text(year(today())-12)),1+ 
              if(contains(Years_Donated__c,text(year(today())-13)),1+ 
               if(contains(Years_Donated__c,text(year(today())-14)),1+ 
                if(contains(Years_Donated__c,text(year(today())-15)),1+ 
                 if(contains(Years_Donated__c,text(year(today())-16)),1+ 
                  if(contains(Years_Donated__c,text(year(today())-17)),1+ 
                   if(contains(Years_Donated__c,text(year(today())-18)),1+ 
                    if(contains(Years_Donated__c,text(year(today())-19)),1+ 
                     if(contains(Years_Donated__c,text(year(today())-20)),1,0)
                    ,0)
                   ,0)
                  ,0)
                 ,0)
                ,0)
               ,0)
              ,0)
             ,0)
            ,0)
           ,0)
          ,0)
         ,0)
        ,0)
       ,0)
      ,0)
     ,0)
    ,0)
   ,0)
  ,0)
,0)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment