Membership Comparison - Number of Members Each Year

Hi Erica,

I have a couple of thoughts to get you started. First is looking at this be calendar year, which if you'll bear with me, is sort of an intro into how to set up a widget like this.

Here is a widget looking at Purchase Dates, with it's Calendar Month Sort field across the bottom and Calendar Year in the Break by. Line charts that use a Break by, like the one above has Calendar Year, can only have one Value in the widget, like Total Membership Count in the widget above. To rework this into a Fiscal Year view requires sorting the Calendar Months by their Fiscal Period in order to get them in the right order. Click on the a>z icon to sort by this field.

Disregard for a moment that my Calendar Months are still in Calendar order - my sample Tessitura Fiscal Year is set the same as a Calendar Year. What's important is that Min Purchase Date Fiscal Period value, which is sorted ascending, and for the moment shown in green in the widget above. That will order the month names according to your Fiscal Year regardless of which month of the Calendar Year is first in your Fiscal Year configuration. In this case I've switch from Calendar Month Sort to Calendar Month (w/o that month number Sort).

Line charts in Analytics can only use a Break by when there is a single Value configured. Because we need a sort value to fiscally order the calendar months, and we want to count memberships, which is two values, we can't use a Break by. Instead we'll use Filtered Values and create a value per fiscal year. Here's how I set up 2018.

Finally, to make that sort value (nearly) invisible, change it's color to white and rename it to just a period (".") and the widget legend won't highlight the fact there's a dedicated sort value formula in the widget.

Hopefully this will get you going. 

Best,
Chris

PS. Here's the sample dashboard MembershipTrends.dash and how to import.

I'm new to Analytics too and was looking for this - thanks Chris! We're also wanted to keep track of how many Members we had at each level year after year. Is there a way to add this as a widget as well?

Hi Emily,

Very glad the above was helpful. For YoY, are you thinking a year is the year in which their membership started, their membership expired, or their membership was at all active? Another way, if I have a membership that started on 1-Jun-2020 and expires on 30-Apr-2021, would you want to see me counted in a 2020 membership started bucket, in a 2021 membership expiration bucket, or in both a 2020 and a 2021 active members bucket?

I will tag in here as I have been trying to create something similar to what Emily has asked about and only been moderately successful- for PCT this could be the Membership Campaign it was assigned to (which is seperate from the Campaign the contributions might be booked to. Where it become a little extra complicated is that we might have a gift in December 2019, the membership wouldn't start until Jan 2020, but we would consider the membership part of our 2019 renewals. I currently work with inexact numbers and remind everyone we are looking at trend analysis and use a combination of membership and contribution widgets. 

Ideally, we are looking to report number of active memberships by month, for example how many of each level were active at all in October 2019. But to start, YoY where their membership was active at all.

Hi Emily,

Tessitura Analytics doesn't have what I'll call a "Membership Active On Date" where you can use that field to filter to memberships that had an initiation date before and an expiration date after the selected date or date range. So in lieu of that, we can filter on initiation dates before and an expiration dates after the period we want to report on. In this first case, YoY.

We'll need a value per period (year), with a filtered value formula as shown in the "2020" example above. This is the total count of memberships, filtered to those with a membership expiration date after Jan 1, 2020, and filtered to those with an initiation date before Dec 31, 2020. I then duplicated the value from its Options menu, and edited the formula's filters for the other 2 years. 

We can easily render this as a Column chart by changing the cart type on the Pivot widget.

When I've seen this done for a Monthly analysis, the same process is followed, but with a value formula per month, and expiration/initiation dates set to the before the end / after the start of that month, all within a given fiscal year.

Hi all,
I, too, am new to Analytics and am also having trouble with this same issue. When I’ve tried using the formula suggested ([Total Member Count], [Expiration Date After 1/1/2020], [Initiation Date Before 12/31/2020]), I’m still having trouble and end up with an inaccurate count. Can anyone share a sample dash of this if you have gotten it to work? Or what exactly is the code for the Days to Date filter. Thank you! 

Hi Sean,

To what are you comparing to determine inaccurate counts? If between Analytics and your comparison you can find an out of place membership, look at the initiation and expiration dates and see if they're what you'd expect the filter to catch.

Here is the detail from the Value formula filters in the Pivot table above:

HI Chris Wallingford -- I'm trying to set up a widget like the one above for number of memberships year over year as a column chart and I'm following your instructions above but I'm stuck. I'm trying to add the value for the year, but when I type in the formula in your image I get an error message and I can't seem to figure out how to set it up using the editor. Do you still have this widget? If so, would it be possible to get a copy of it? Thanks!

Hi Anne,

This is the one updated with the YOY in Level ... MembershipTrends.dash

Thank you!

I'm afraid I'm totally stumped. I'm getting different counts in different widgets and when I pull lists of members. For example, when I pull a list of active and pending Merieult members, I get 308, but my Level widget is showing 356. Is it possible that one is counting constituents and the other is counting memberships? (i.e. it is including duplicate constituents) And my Members by Level this FY and last widget is totally off. The dashboard you sent seems to be working but it is set up by calendar year rather than FY so I can't be totally sure since everything else is set up for FY. Thank you!

3302.Anne'sMembershipDashboard.dash

Hi Anne,

You can absolutely change the dates on that widget in the value filters to align with your fiscal years instead of calendar years.

Given that the values are counts of memberships with expiration after the start of the year AND initiation prior to the end of the year, I would very much expect, once adjusted to FY, for the values in this widget to NOT match any of the other widgets that are based on the FY of the Purchase Date. 

It is difficult in a forum thread to dig into the details of the different widgets' filters and your list criteria. I suggest a Support ticket to address matching values. I can say that the Level widget is 4 FYs of memberships based on the Dashboard filter for Purchase Fiscal Current Year Offset in 0, -1, -2, and -3, and is filtered to exclude memberships with a Current Status in Deactivated or Inactive, while the Members by Level This FY and Last widget, is limited to only this and last FY, but not includes Deactivated and Inactive memberships.

Could I have a copy of the dash file to try this out? We would also like to calulate number of members for a given year

Chris Wallingford - The link to the sample dash doesn't seem to work - it says I do not have permissions to view it.  Can you please repost it?