• Replies 5 replies
  • Subscribers 700 subscribers
  • Views 86 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

Parents
  • $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. 

Reply
  • $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. 

Children