Hi there,
I've been chewing on this longer that I'll admit and thought I should finally ask for some help.In Analytics I am trying to make a pivot table that shows the month to date count and sum amount of gifts for this fiscal year and last fiscal year, split by gift amount range. I would like to use this for reporting on Community Giving and for Memberships. In my mind it would look something like this:
Sorry the screenshot is so shrimpy. Along the very top is "FY23 Month to date" and "FY22 Month to date". The next row is count and amount for each of those fiscal years. And along the left is the spread of gift ranges: $1-99, $100-499, etc. If you can read the numbers on here, you might notice they're kind of funny. The thing that's messing it up the most is that giving range on the left, it's not doing what I hoped it was doing. I had hoped it would relate to the gifts received, for example: In FY23 this month we received 21 gifts that were between $0 and $99, 50 that were between $100 and $499... and so on. Instead what it's doing is showing me how many gifts I received in FY23 this month, and how many of the donors have a lifetime giving amount between $0-99, lifetime giving amount of $100-499 and so on.
I've tried to get at this several ways but nothing really suits, and the closest I can get would need to be manually updated each month. I would rather make something that is self-managing. Can anyone help point me in the right direction?Jenny
Hi Jenny,
I hope you're well. I don't think we can get 100% to where you are trying to go without customization, but we can get closer than you are now without.
If you were okay having the contribution size buckets as columns in the pivot widget, or it might look better as a column chart, then we can use a bucketing formula... slide 16 of this presentation.
... or ...
And then a second widget with the count of contributions in that "size bucket" rather than the total amount attributable to those contributions.
Thanks, Chris!
I feel like I revisit your video annually, haha! Similar to one of my other posts you replied to, I think I'm just missing the mark. Maybe I'm using the wrong flavor of Contribution Count or Amount?
I have (SUM([Contribution Count], [Amount])) in Values (Contribution Count has All Items, and Amount Values are >100) but I'm getting the message of there being an unknown error querying the elasticube. The only Category I'm using for this is Contribution Fiscal Year for the past 3 years. No dashboard filters.
I'm at a loss.
Change this... (SUM([Contribution Count], [Amount]))
(SUM([Contribution Count], [Amount]))
to this... (SUM([Contribution Count]), [Amount])
(SUM([Contribution Count]), [Amount])
Huzzah! Thank you, I really really appreciate it! It seem so simple now of course, I got all tangled up in the parens. This will be infinitely useful.
Awesome, Jenny!