• Replies 5 replies
  • Subscribers 700 subscribers
  • Views 85 views
  • Users 0 members are here
Options
Related

Gave >=$5k but nothing in the last 5 years

Anne M Robichaux
$organization

I tried this as an extraction and used contribution date >=1/29/2019 as a suppression, but that didn't kick people out, so now I'm trying in Analytics. I'm trying to find people who have given $5k or more (cumulatively), but who haven't given in the last 5 years. 

This is getting me people who gave $5k or more prior to 2019, but it isn't excluding those who have given since then. I know I need another filter somewhere, but I'm drawing a blank. Has anyone set something like this up?

Thanks!
Anne

  • $organization

    Hi Anne,

    Probably the best way to approach this initially is with a Flag value such as this. Something that returns a 1 if the constituent meets the criteria you want, and a 0 if they don't. They we filter on that value where it equals 1.

    IF (
       ( [Total Amount] , [Contribution Date from Earliest Date to 1/29/2019] ) >= 5000
       AND ( 
          ISNULL( (     [Total Amount] , [Contribution Date from 1/29/2019 to Latest Date] ) )
          OR ( [Total Amount] , [Contribution Date from 1/29/2019 to Latest Date] ) = 0
      )
   , 1 , 0
   )

    We could make that a little simpler if instead you add a filter on using the Amount field (not a filter from your Total Amount value) and setting it to exclude 0.

    Then the formula could look like this because there won't be anyone with a $0 contribution amount in the past 5 years... only the absence of contributions...

    IF (
       ( [Total Amount] , [Contribution Date from Earliest Date to 1/29/2019] ) >= 5000
       AND ISNULL( ( [Total Amount] , [Contribution Date from 1/29/2019 to Latest Date] ) )
    , 1 , 0
   )

    Then filter the value to only include 1. If you like at this point, you can disable the Flag value from being visible in the widget, and the filter will continue to apply. 

  • $organization in reply to Chris Wallingford

    Hi Chris, I got the formula working, but it is showing me all constituents with a 1 or a 0:

    IF (
    ([Total Amount], [Contribution Date from Earliest Date to 1/30/2019]) >= 5000
    AND (
    ISNULL (([Total Amount], [Contribution Date from 1/29/2019 to Latest Date]))
    OR ([Total Amount], [Contribution Date from 1/30/2019 to Latest Date])=0)
    , 1, 0

    I can't get the 0s to hide.

  • $organization in reply to Anne M Robichaux

    Hover over the Formula and click the funnel to filter, and set it to Values = 1 only.

  • $organization in reply to Chris Wallingford

    Got it! Thank you! I knew there was a missing filter.

  • $organization in reply to Anne M Robichaux

    I really liked this concept and put a little spin on it. So instead of taking the total amount, which would give their cumulative giving more than 5 years ago, I tried using the Max Amount so their largest single gift in that time was above $5,000.
    Then I modified the output, so instead of 1 or 0, I have it giving me what that Max amount was or Null to make the field blank.

    IF ( ( [Max Amount] , [Contribution Date from Earliest to 1/31/2019] ) >= 5000
    AND ( ISNULL( ( [Total Amount] , [Contribution Date from 1/31/2019 to Latest] ) )
    OR ( [Total Amount] , [Contribution Date from from 1/31/2019 to Latest] ) = 0),
    ( [Max Amount] , [Contribution Date from Earliest to 1/31/2019] ), NULL)

    Thanks for this idea, Anne! Happy Analyzing!