Jun 05, 2023 08:48 AM
We have a database of our donors. We assign an ID to each donation, and it is then pulled in to the donor. On this table I would like to show how many people donated in a given month. Some people donate more than once in a month because of a campaign, so it's not enough to group by donation dates. Do you have any ideas?
Jun 05, 2023 10:02 AM - edited Jun 05, 2023 10:03 AM
Hi @Komm11111111,
MTD # of donations/ donor can be achieved with a count field and sum of donations / donor can be achieved with a roll-up field.
Ideally, you would have at least two linked tables: Donations, Donors
Then, after you've linked donations to donor, you could add a Count field in the Donors table with the condition of including donations made in the past month:
If you want to sum MTD donations / donor, you could do similarly with a Roll-up field on the donation amount field with the same condition above (for MTD) and your aggregation formula as SUM(values)
Hope that helps!
-Stephen
Jun 06, 2023 08:18 AM
I don't think that's exactly my problem, but I'll try to put it another way:
In March, there are 10 regular (every month referrals) donors who make 10 referrals
In April, we have 11 regular donors who are also donating to another campaign, so we get 15 referrals from them
May also has 10 regular donors, but 13 referrals
I would like to see a statement where I can see that in March we have 10 regular donors, in April 11, in May 10, regardless of how many times they refer in a given month.
I would like to see more than two years of data backwards on a monthly basis so that we can compare our work.
Jun 06, 2023 08:58 AM
Here's what I would try:
- Create a new grid view in your donations table
- add a new formula field that extracts the range you want to filter by from your donation date, like month and year:
- group by this formula field
- group a second time by donor
Now you can see the counts in the group container as # of donations / month. Adjust the function over the donation amount (defaults to sum) to see total donation amount / month.
Jun 07, 2023 02:51 AM
The problem with this formula is that, again, it only returns the date of the last transfer. I would also like to see the details of the donor in the previous month's statistics.
Jun 07, 2023 05:25 AM
Is this the donations table or the donors table? I would create it in the table that has 1 record per donation.
If you're interested, you can share the base with me via PM and I'd be happy to take a look and offer a suggestion for this.
Thanks,
-Stephen