• Replies 4 replies
  • Subscribers 702 subscribers
  • Views 147 views
  • Users 0 members are here
Options
Related

If X and Y then count, If X and Z then count, If X and anything else then count etc.

Madeline Dummerth (Past Member)
$organization

I've been asked to show of constituents with tickets to X how many also attended Y. Also how many people have tickets to X that first came to us for Z. AND how many people have tickets to X that have been to anything other than Y and Z. I'm pretty sure I need some IF statements so I started just trying to figure out the first if X and Y and I keep producing a syntax error. Does anyone know how to get this to work?

SUM([Constituent ID] , IF (
[Max Production Season ID] = 3363 AND [Max Production Season ID] = 3380) , 1 , NULL)))

Parents
  • $organization

    Hi Madeline,

    ...of constituents with tickets to X how many also attended Y.

    Yes, you have the right idea I think, just recommending a tweak to your formula that's more like

    SUM( [Constituent ID] , IF (
    ( [Total Ticket Count] , [Production Season ID = 3363] ) > 0 AND ( [Total Ticket Count] , [Production Season ID = 3380] ) > 0 , 1 , NULL)))

    Also how many people have tickets to X that first came to us for Z.

    There's a Constituent field for First Performance Name, so for this you could filter on Production Season = X and First Performance Name = Z

    AND how many people have tickets to X that have been to anything other than Y and Z. 

    SUM( [Constituent ID] , IF (
    ( [Total Ticket Count] , [Production Season ID = X] ) > 0 AND ( [Total Ticket Count] , [Production Season ID <> X or Y or Z] ) > 0 , 1 , NULL)))

  • $organization in reply to Chris Wallingford

    Thanks, Chris - this was helpful! When I put these together into a pivot table it's telling me that I don't have anyone that had X & Y but if I pull a list in Tessitura it gives me 5 constituents. I'm pulling the list based on ticket history which should be in Analytics at this point. What might be causing this to not compute?

  • $organization in reply to Madeline Dummerth (Past Member)

    Hi Madeline,

    I would create a pivot table widget in a fresh, on-off dashboard, filtered on those 5 constituent IDs and those 2 production seasons, and with Constituent ID and Production Season both on rows, see if you see what you'd expect. If the combination of constituent and production do appear, is it possible their "ticket count" is not > 0? (E.g. there isn't a "Ticket Price" price layer category on those tickets, in which case we could switch from Total Ticket Count to something like COUNT([Order ID]) or something.) Confirm too, we're not talking about constituents who might have returned or released their reservations. 

    Otherwise, I think we move to a support ticket and have someone to take a closer look.

Reply
  • $organization in reply to Madeline Dummerth (Past Member)

    Hi Madeline,

    I would create a pivot table widget in a fresh, on-off dashboard, filtered on those 5 constituent IDs and those 2 production seasons, and with Constituent ID and Production Season both on rows, see if you see what you'd expect. If the combination of constituent and production do appear, is it possible their "ticket count" is not > 0? (E.g. there isn't a "Ticket Price" price layer category on those tickets, in which case we could switch from Total Ticket Count to something like COUNT([Order ID]) or something.) Confirm too, we're not talking about constituents who might have returned or released their reservations. 

    Otherwise, I think we move to a support ticket and have someone to take a closer look.

Children