Producing a chart of total membership over time given joining an leaving dates

I have records for members of a club. There is one record for each member and it contains fields for when they joined the club and when they left. I would like to create a chart that shows total membership over time. What’s the best way to do this?

I found something similar here:

and

And the answer by @ Asya_Kamsky or
@ steevej would do what you wanted.

I took it apart slightly to re-work it for my understanding, but in effect you want to create a list of days that each member was active, you have their start and end date, so if member 1 was active from 1 Jan 2023 to 5 Jan 2023 you want to get a list of days:

1 Jan 2023
2 Jan 2023
3 Jan 2023
4 Jan 2023
5 Jan 2023

And if member 2 was active from the 3rd to 4th:
3 Jan 2023
4 Jan 2023

Combining this data you get
1 Jan 2023
2 Jan 2023
3 Jan 2023
3 Jan 2023
4 Jan 2023
4 Jan 2023
5 Jan 2023

And grouping by date you get membership per day:
1 Jan 2023 : 1
2 Jan 2023 : 1
3 Jan 2023 : 2
4 Jan 2023 : 2
5 Jan 2023 : 1

The way this is done in the linked example was to use the $range and $map operators to build a list of days to add to each start date to generate a list of days they are active.
If you subtract the start from end, you get time active, which you can then convert to days, you then map over this, adding each to the start date to build an array of days that the user was a member.

If you then $unwind the array and group all data up, you get a collated list of days that a user was active.

You can simplify the above, but I spread things out a bit to let me run it in stages to check each stage.

So we start with:
image

Then calculate the days
image

Now the map and range operators blow the days into an array of days each user is active:
image

The $unwind turns this into a list of dates that a user was active:

Finally we can group this up, counting how many users were active on each date:
image

With a lot of members who are members are active for a long time this could get big quickly, you could re-calculate on a periodic basis and exclude members who have left before the start calculation point to just calculate it weekly or something.

Also I didn’t take into account partial days or limits on the end, so you would need to adjust for your requirements.

You didn’t post actual documents so other things to take away from the other posts are to STORE DATES AS DATES! And when doing operations on dates, remember that you need to use a scalar to the period of interest, be it days, hours, minute, seconds or ms etc.

2 Likes

@John_Sewell thanks for that - great answer and just what I needed.

A couple of pointers for anyone else attempting the same:

  • dateDiff is used by the $range function and so needs to be an integer - use $toInt or $convert
  • The scalar comment for those not following: with the startDate and endDate as date values, the $subtract function gives a result in milliseconds. If you are grouping by day you need to divide by (1000ms x 60s x 60min x 24hrs = 86400000). If you want to group by a different time period, divide by the number of ms. You’ll then need to multiply by the same value when you $map.
  • I used $dateTrunc on my startDate to avoid problems with partial days caused by different start times on the same day (otherwise the values for daysActive might not match and you won’t be able to group them). If grouping by a different time period you’ll need to adjust the $dateTrunc parameters accordingly.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.