• Replies 3 replies
  • Subscribers 701 subscribers
  • Views 107 views
  • Users 0 members are here
Options
Related

tracking sales to "extra curricular" events

Lesley Chaney
$organization

Hello all,

I'm trying to create a dashboard to track sales to an "add-on experience" that we are selling through Tessitura. I need to show that patrons have tickets to the concert and the experience on the same date, and that they have the same number of tickets to each, but I'm stuck on how to do that. Any guidance is welcome!

Thanks so much - it was really nice to see/meet some of you next week.

Best,

Lesley

  • $organization

    Does something like this work, Lesley?

    For each constituent, and then for each of their performance dates, if the total tickets to the concert equals the total tickets to the experience, then count that constituent.

    SUM ( [Constituent ID] , 
       SUM ( [Days in Performance Date] ,
          IF ( 
             ( [Total Ticket Count] , [Production Season = Concert] )
             = ( [Total Ticket Count] , [Production Season = Experience] )
          , 1
          , NULL
          )
       )
    )
  • $organization
    Hi Chris,
     
    Thanks for your reply! I’m not sure I understand the formula you sent, but here is a VERY basic clip of what I’m trying to accomplish.
     
    I’m looking for results like the top two rows – patrons who have the experience AND the concert:
    • The first row is good, because they bought the same number of experience and concert tickets.
    • The second row is bad because they did not buy the same number of experience and concert tickets.
    • The remainder of the rows that do not have the experience should not be returned in the results.
     
     
    Be well,
    Lesley
     
  • $organization in reply to Lesley Chaney

    Do you need to see the dates or dates and codes to which they hold those tickets, or just a flag that says, this constituent good, this constituent bad. If the latter, then just start with Constituent ID on rows, and removing everything else including perf date and code from columns.

    Then filter the widget to just Production Season = Experience so that we only get constituents that have experience. This will remove all the "don't want".

    Then a value like this...

       MIN ( [Days in Performance Date] ,
          IF ( 
             ( [Total Ticket Count] , [Production Season = Concert] )
             = ( [Total Ticket Count] , [Production Season = Experience] )
          , 1
          , 0
          )
       )

    The alignment of using Production Season as a filter on the widget, and as a filter inside the formula, allows the formula to break out of the widget filter to look for each constituent's ticket count to Concert.

    For each date on which the constituent holds Experience tickets, if the count of Concert tickets is the same, return a 1, otherwise a 0. If the minimum value returned from that is a 1, then all dates with Experience tickets also have an equal number of Concert tickets for that constituent. If the minimum value returned for the constituent is a 0, then at least one date on which they hold Experience tickets does not hold an equal number of Concert tickets.

    One quirky thing to try... sometimes raw values like 1 and 0 in a formula don't play nice, and may need to be replaced with SUM(1) and SUM(0) to trick the value into taking those raw numbers as aggregations.