• Replies 17 replies
  • Subscribers 961 subscribers
  • Views 536 views
  • Users 0 members are here
Related

Membership Overlap Report

Elizabeth Warren
$organization

Hi all!

I'm creating a membership overlap report. As a museum we offer a few different kinds of memberships and it's not uncommon for people to have multiple active memberships at a time. My colleague was able to get this report in Tableau and I was hoping to recreate it in Tessitura Analytics but I'm getting stuck. As of right now Analytics is only giving me totals for each membership and not the overlapping numbers as seen in the Tableau example. Any thoughts?

Tableau Membership Overlap Example.xlsx

Parents
  • $organization

    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])
     )
Reply
  • $organization

    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])
     )
Children
No Data