Hey Everyone! Working on a dashboard to track new, lapse, and long lapse donors. I tried the following formula to no avail (I get an error message). Any Idea?
COUNT([Constituent ID],[Campaign Fiscal Year]) -
count([Constituent ID], IF(MIN([Contribution Count],[Campaign Fiscal Year1])=1, MAX([Constituent ID]), NULL))Where the first Campaign Fiscal Year is for anything 2023 and forward. Campaign Fiscal Year on the second line is anything previous to 2022. I thought this would show The number of constituents who have donated in 20223 (or to future campaigns) minus the constituents who have at least one donation prior to 2023. Thanks in advance for any advice,
Hi Michael,
The syntax error is a missing ")" at the end of the second statement.
You've sort of the right idea here, but we need a slightly different approach for the correct number. Let's look at the unique count of distinct constituents with ANY contribution minus the unique count of constituents with a PAST contribution.
COUNT([Constituent ID]) - (COUNT([Constituent ID]),[Campaign Fiscal Year < 2023])
With the original post logic of this year's donors minus prior year's donors, a donor from a past year who hasn't contributed this year, is substracted from another donor who contributed for the first time this year. I contributed last year but not this year. You contributed this year but not last year. We net zero in the output.
Using some Venn math, if we know we have 10000 unique donors ever, and 9000 unique donors prior to this year, then we have 1000 unique donors who are new this year. Some previous posts with related content:
(+) Retention rate formula - Reporting & Analytics - Forums - Tessitura Network
(+) Booking churn Subscribers in Analytics? - Reporting & Analytics - Forums - Tessitura Network
Hi Chris, Thank you for simplifying this for me! I was definitely overthinking this. I also overlooked the fact I was doing "count" and not just subtracting the const. IDs from a list. Again, thank you!
T.C. is correct. I updated the formula in my last post to reflect the correction.
Hello formula experts! I am trying to follow along but am receiving a sytax error...can someone take a look at my formula below and provide any insight to correct it? Thanks in advance!
Try putting parentheses around [Total Campaign Fiscal Year] --> ([Total Campaign Fiscal Year])
Thank you!