Membership Overlap Report

Hi Elizabeth. 

I'm assuming that your rows and break by are Membership Organisation. 

What's your Value? sum([Membership  Count])

Count Unique Current Membership is my current value. 

[# of unique Current Membership Level]

Hi Elizabeth,

Crossover reporting is possible in Analytics. I want to start with a simpler case: of the people that have a membership in our general membership program, what other memberships to they hold... In my case, I have 345 in that program SR Membership.

The Constituent ID Ranking filter here says, limit the widget to the top 10000 constituents ranked by their Total Membership Count of SR Memberships.

Alternatively, the Constituent ID Attribute filter here says, limit the widget to any and all constituents that have a SR Membership.

{
 "attributes": [
 {
 "dim": "[MEMBERSHIP LEVEL.Membership Organization]",
 "filter": {
 "members": [
 "SR Membership (AD)"
 ]
 }
 }
 ],
 "custom": true
}

Moving to the full crossover scenario, we would need a value per membership organization. Each filtered value would basically be a count of constituents filtered by one membership organization. The formula would need to be grouped by Constituent ID, limiting the scope of the value to constituents who have a membership in the given membership organization of the row. The inner formula runs against each constituent, and for each constituent returns the Max Constituent ID in the selected membership organization. So it would return either the Constituent ID or NULL for each constituent. The outer aggregation can then take a unique count of all of the returned Constituent IDs for the membership organization.

COUNT([Constituent ID],
 (MAX([Constituent ID]),[Membership Organization1])
 )

Chris Wallingford This was so helpful, thank you!

Now the only problem I'm running into is I wanted to be able to click into each group but it's returning everyone from that area. For example, if I were to click into the SR Donor/Membership Levels and Film Center Membership overlap I'd get the entire 265 SR Donors/Members instead of just the 4 I was hoping to see. Anyway to fix that?

Hi Elizabeth,

Good news and maybe some bad news at the end. Good news first... thank you for asking this. Using Multi-Pass Aggregation in the way that I did above, the grouping on [Constituent ID] would override the _Constituents jump to grouping on Constituent ID, and include everyone from the source row rather than just the constituents in the row and column from which you jumped. I dug a little deeper and was able to find a formula that worked around this. It seems that if we use this formula instead...

( [# of unique Constituent ID] , [Constituent ID] )

...where the latter [Constituent ID] field uses the "Attribute" filter from above, we get both the same result in our cross-over counts, and the jump to is successfully filtered by both the row and the column.

Note there are a total of 6 Film Center members, and 4 of them are also SR Membership... the results on the _Constituents jump to align:

I am excited about this, so thanks again for asking. However, there's still the potential bad news. This just came up here, and I thought for sure that in v15, the filter would not transfer to the _Constituents dashboard. I just tested though, and it worked. Hopefully that will reliably work for you as well, but if not, we can consider that defect I reference in the linked post to be resolved in v16.

Thanks for the positive start to my day today.

I read Nathaniel's post and then this one and it reads like a thriller. Great job. V16 here we come

Chris Wallingford this is so helpful, thank you! When I put this advanced filter in I'm getting a syntax error. I imagine I'm missing something from your screenshot. Would you mind copying and pasting that into our chat here?

Hi Elizabeth.

It's this one from earlier up in this thread.

{
 "attributes": [
 {
 "dim": "[MEMBERSHIP LEVEL.Membership Organization]",
 "filter": {
 "members": [
 "SR Membership (AD)"
 ]
 }
 }
 ],
 "custom": true
}

Chris Wallingford Success! Thank you again!

Chris Wallingford Sorry to keep looping back to this. One last question. I'm getting the click through to work, the only issue now is that the number on the table is based on active membership and the jump to lists are people with an active constituency. Is there a way to pull that list off of something other than the attribute tab? This is a weird moment where the corresponding constituency is handled manually so there's some bad data.

Hi Elizabeth,

I don't quite follow in what way the list of constituents on the jump to _Constituents dashboard is different from the constituents in the source crossover widget. Are you seeing a constituency filter on the _Constituents dashboard that you don't expect? Is there a different count of constituents in the crossover than in the resulting jump to? Is it the constituencies in Tessitura that are out of date?

We're seeing a different count of constituents in the crossover than in the jump to. I believe the reason is that the cross over is based on Active Membership and the Jump To is based on Constituency and those are managed separately in our database. 

Example, Friends of Euro Paintings and Wrightsman Fellow cross over count is 5 but 8 constituents show in the Jump To.

Where are you finding evidence of the jump to being Constituency based? My expectation is the jump to be using the same logic/filters as the source dashboard.

Are there by chance 8 constituents in the Wrightsemans Fellows column of the Wrightsemans Fellows row (it's out of the screenshot)?

Would you check the jump to dashboard after clicking through, that the [Constituent ID] attribute filter is what you expect, referencing Friends of European Paintings?

In the 8 constituents the only thing relating them to both Wrighstman and Euro Paintings is the active constituency. The 3 extra have nothing else consistent between them.

There are 37 constituents who fall under the Wrightsmans crossover.

I've attached a screenshot and the advanced filter I'm using below.

{
"attributes": [
{
"dim": "[MEMBERSHIP LEVEL.Membership Organization]",
"filter": {
"members": [
"Friends of European Paintings"
]
}
}
],
"custom": true
}

I believe I see what's happening now, Elizabeth.

It's that Current Record filter, in the context of the source dashboard, it impacts, as you no doubt want it to, the Constituent ID advanced attribute filter. Such that, for the row of constituents with a Wrightsmans current record, the count of members is filtered to constituents with a Euro Paintings current record. When we jump through, the Constituent ID filter is moved up to the same level as the Current Record filter and we lose that hierarchy of Current Record impacting the Constituent ID attribute filter. We end up with constituents having a Wrightsmans current record, who also have a Euro Paintings whether current or not. 

We need a way to filter the values/formulas in the source widget to current records only, even though it's redundant at that point, so that the jump to filter in the way we expect. I've tried a number of approaches to reach a better recommendation for you, and I think I've found one. It will mean rebuilding the values in the widget.

Wherever you have the Current Record filter now, it can stay there, at the dashboard or widget level. For the formulas, we'll need to change from the advanced attribute, to a ranking type. Set the Top value to 100, and then edit the formula to

( [Total Membership Count] , [Membership Organization=Euro Paintings] , [Current Record=Current Record] )

Clicking through to the jump to with that will transfer the constituents with a current membership in the organization of the row you clicked (as it did before), and cross filter constituents with a current membership in the organization of the column you clicked.