Hi everyone! This feels like something that already exists that I just can't seem to find - we want to see the number of donors who are repeat donors, specifically looking year to year.
IE. number of 2023 donors who gave in 2022; number of 2023 donors who gave in 2022 AND 2021; number of 2023 donors who gave in 2022 AND 2021 AND 2020; and so on and so forth
I'm a little familiar with the bucketing technique, but it doesn't seem to want to cooperate in the Contributions cube. Way down the line it would be cool to find out who those people are, but for the time being we're just looking at numbers.
Thoughts?
Correct Gabrielle,
Would it be a good "idea" to add Household or Group Constituent ID into Analytics, which would be the same for the Household and Primary Affiliates?
Thanks for the speedy response. Sounds like that would be really helpful for what I'm usually trying to do!
Edit: the Idea is here for anyone who wants to upvote!
https://community.tessituranetwork.com/ideas/i/ideas/add-household-and-or-group-constituent-id-to-analytics
Of Course!I tried to create Nathanael's formula from above
but it says that 'IF' doesn't accept 2 parameters.
I have also tried this one
But the numbers it returned are definitely not correct.
One of yours just worked (I swear I tried this one yesterday), but I can't jump to the constituents to spot check.
I've just been jumping around trying to get things to work and see how these formulas are different, just to learn more about these less straightforward metrics.
In the first one, if you reformat it, to use multiple lines, like the second one, it'll be clearer where those parentheses are messing with us again...
SUM ( [Constituent ID] , IF ( ( [Total Amount] , [Fiscal Year] ) > 0 AND ( [Total Amount] , [Fiscal Year] ) > 0 , 1 , 0 ))
Yours is missing a "(" after the IF. There should be 2 there. And at the end there is need of another ")". Reading this formula, it implies a retention calculation, and that the two [Fiscal Year] filters are set to this year and last year. This reads as: for each constituent that has an amount contributed this year that's greater than zero, and an amount contributed last year that's greater than zero, then add "1" to the overall SUM() of this value.
The second formula looks like it's intended to find new contributors within or excluding selected funds, but has a mismatch of using COUNT() with an IF() that returns a "1" to it. This formula looks like it will always return 1 or 2 as the result rather than the number of new contributors. To fix it, change the COUNT() to SUM(). Also be sure that there is a filter on the [Amount] field (probably easiest as a Widget Filter) set to include only Values > 0. Otherwise, a $0 contribution from a prior year will count as a contribution in that prior year, and exclude the constituent from being "new".
The third one is more difficult to interpret out of context. It implies a count of new contributors based on subtracting the count of contributors in one or more prior [Campaign Fiscal Year] (presumably all FY included in the widget except for the current one) from the count of all contributors in the widget (including the current FY). This seems like a value intended for an Indicator widget, but then the image of the Pivot output is trying to render that number within a couple of FY fields, which could result in some unexpected results.
I am a COMPLETE noivce and have a really basic question. I am trying to change from Calendar Year to Fiscal Year using your code above.
CASE WHEN MIN( [Total Campaign Fiscal Year] ) = 2018 Then Count( [Constituent ID] , ( MAX ([Constituent ID]) ,
[Total Campaign Fiscal Year = 2017] ) ) Else 0 end
So far just doing one year, will add multiples when I get one working.
I get this error
Function Syntax Error
Error in mearsured value definition. A dimension (Measures) is used more than once in a single Measured value definition.
Any suggestions?
THanks
In your formula, right click on [total campaign fiscal year] and choose type to all items. That should change it so it says [Campaign Fiscal year] sans the word "total". See if that fixes it for you.
Thank you! That did the trick!
My version of the code is now running
CASE WHEN MIN([Campaign Fiscal Year] ) = 2018 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Fiscal Year1 = 2017] ) )WHEN MIN([Campaign Fiscal Year] ) = 2019 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Fiscal Year2 = 2018] ) )WHEN MIN([Campaign Fiscal Year] ) = 2020 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]), [Fiscal Year3 = 2019] ) )WHEN MIN([Campaign Fiscal Year] ) = 2021 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Fiscal Year4 =2020] ) )WHEN MIN([Campaign Fiscal Year] ) = 2022 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]), [Fiscal Year5 =2021] ) )WHEN MIN([Campaign Fiscal Year] ) = 2023 THEN COUNT( [Constituent ID] , (MAX([Constituent ID]) , [Fiscal Year6 =2022] ) )ELSE 0 END
When I try to drill into the # Renewed from last year group it opens a window that has a filter called Fiscal Year1 = 2017 regardless of which year's row I am drilling into. What have I done wrong? I would provide a screenshot but for some reason I can't paste an image into this window.
Running this code
When I try to drill into the group renewed from last year it opens a window with a filter showing Fiscal Year1 = 2017. This filter shows for every year in the table. What have I done wrong? I would paste a screen shot but this window won't let me paste an image.
I am running this code
When I try to drill into the data in the column for #Renewed from last year it pops a window that shows a filter with Fiscal Year1 = 2017 regardless of the row of data that I have selected. I would show a screen shot but can't paste an image in this window for some reason.
What have I done wrong?