Hi friends,
Long time lurker, first time poster.
I'm still relatively new to Analytics and we are trying to tweak this wonderful donor retention formula to show $ contributed instead of # constituents.
My old school brain is thinking the hot fix is lists but wondering if there's a more elegant solution to this?community.tessituranetwork.com/.../finding-donor-retention-repeat-donors-in-analytics
CASE WHEN MIN( [Calendar Year] ) = 2018 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Calendar Year = 2017] ) )WHEN MIN( [Calendar Year] ) = 2019 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Calendar Year = 2018] ) )WHEN MIN( [Calendar Year] ) = 2020 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]), [Calendar Year = 2019] ) )WHEN MIN( [Calendar Year] ) = 2021 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Calendar Year = 2020] ) )WHEN MIN( [Calendar Year] ) = 2022 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]), [Calendar Year = 2021] ) )WHEN MIN( [Calendar Year] ) = 2023 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Calendar Year = 2022] ) )ELSE 0 ENDJono
Hi Jono,
just update each THEN to a version of this...
SUM( [Constituent ID] , ([Total Amount]) , [Calendar Year = ####] ) )
Hi Chris,This did the trick! Looks like I just had to get my mind around where the group by was occurring.Many thanks,Jono