Counting multiple records as one - extension

[Komm11111111]

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?

[Stephen_Orr1]

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

[Komm11111111]

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.

[Stephen_Orr1]

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: 

IF({Donation Date}, DATETIME_FORMAT({Donation Date}, 'MMM YYYY'), '')

- 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.

[Komm11111111]

 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.

[Stephen_Orr1]

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