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.

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.

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.

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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