8

My client needs to know:

  • How many members did we have on date x
  • Who were the members on date x

Using "Find memberships" it appears that, though the UI allows it, combining the membership status filter with any of the date filters will return misleading/wrong results. It seems that the query there is using whatever value is in the membership status field at the time you run the report to evaluate against the filter. (Is there a way to tell what query CiviCRM is running?)

How can I best deliver this historical information to my client.

The site in question is running CiviCRM 4.4.14 with Drupal 7.36.

Improve this question

6 Answers 6

Reset to default
8

Since the status is based upon the start and end date and changes with the cron job, can you just leave the status off and select the date range. Anyone who had a start date before the date in question and an end date after that date should have been a member on that date.

So... Select custom date range for start.
For From leave blank (the beginning of time).
For To: put the date in question.

Select custom date range for end.
For From put the date in question plus one day.
For To: leave blank.

Improve this answer
2
  • I wonder if this needs slightly finessing. "Anyone who had a start date before the date in question and an end date after that date should have been a member on that date" sounds right, but if someone had one membership that ended before date X and another that started after then I think they would still be counted. To avoid this the search should be done on Memberships not on Contacts. Thoughts?
    – petednz - fuzion ♦
    Commented Jul 28, 2020 at 0:02
  • Also if your membership has a Fixed Start Date, do you need to base your search on Member Since Date rather than Member Start Date, since someone might join in july 1, their Start Date would be April 1, and if you are trying to identify members as of June 30 they would be included incorrectly?
    – petednz - fuzion ♦
    Commented Jul 28, 2020 at 0:06
3

Paul-Tahoe's answer is the correct one for your use case. If you need point-in-time queries generally, consider putting Civi data in a data warehouse that supports OLAP cubes. Parvez Saleh at Veda has implemented this and can probably speak to best practices.

Improve this answer
1

In Drupal you can use Views and Aggregation to spit out a count of Memberships on any particular date, pretty much as per Paul's description, you set the Join Date to be 'before' and the End Date to be 'after'

Improve this answer
1

The nz.co.fuzion.historicmembershipdata extension offers this feature

Improve this answer
2
  • Is that extension still working with PHP8 and Smarty updates, etc.?
    – Graham
    Commented Oct 24 at 8:27
  • honestly don't know
    – petednz - fuzion ♦
    Commented Oct 25 at 9:17
1

And another solution to check out using the CiviCRM Data Processor Extension

Improve this answer
1

I opened a proposal to add a new feature for SearchKit to handle a generic "entity active period" that I think covers the scope of this question. See SearchKit: Add ability to search and filter by active periods

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.