• Replies 7 replies
  • Subscribers 962 subscribers
  • Views 185 views
  • Users 0 members are here
Related

Pivot Table; Holds and Order Status

Amy Welsh
$organization

Hi All, 

I'm currently working in an Excel document to track sales / inventory for a company I'm consulting for and want to turn it into an Analytics widget to reduce workload. The aim of the Excel was to capture the status of all seats; the company was getting sales reports where they were technically 'sold out' but had only 1/3 of the house as ticketed orders. I had to then use other reports to find the status of their remaining inventory. 

Current process; using four (4) reports (PT Category, Hold Availability, Performance Base Price & Availability + custom Sales Report), I record the following: 

  • Venue Capacity
  • Holds - COVID (Seats Held Off Sale due to COVID)
  • Holds - All Other Holds (Wheelchairs / Technical / House Seats etc)
  • Sellable Inventory (Venue Capacity minus COVID Holds)
  • Tickets Sold - Count 
  • Tickets Sold - Gross or Net Value
  • Reserved / Unpaid Tickets - Count
  • Reserved / Unpaid Tickets - Gross or Net Value
  • Complimentary Tickets - Count
  • Available Tickets

I've attempted to create the widget in Analytics, but when I'm encountering errors in some values like hold code filtering, available seats remaining and unpaid ticket count. Its probably formula based so any help would be greatly appreciated. 

NB: Some of the values across the examples won't match; the season has been rescheduled due to COVID so the dates are shifting. 

Cheers, 

Amy 

ORIGINAL SPREADSHEET

ANALYTICS WIDGET

Parents
  • $organization

    These are my thoughts in case anyone wants to correct me.  I'm the hollow dots.

    I've sent the dash back with some working formulas in there

    Slight smile

    • Holds - COVID (Seats Held Off Sale due to COVID) 
      • Try ( [Total Seat Count] ,  [Seat Hold Code]  )
    • Holds - All Other Holds (Wheelchairs / Technical / House Seats etc)
      • Same again ( [Total Seat Count] ,  [Seat Hold Code]  )
    • Sellable Inventory (Venue Capacity minus COVID Holds)
      • As above but filter out the zero value holds due to Covid seat kills and Technical/Houseseats/comp development etc.  NB we have Dev in 2 types of holds; sellable and comp for better reporting. It’s not slavish but it gives a better prediction
    • Tickets Sold - Gross or Net Value (not in dash)
      • Are you filtering by Price Layer or Price Layer Category?
    • Reserved / Unpaid Tickets – Count
      • Try this … ( [Total Ticket Count] , [Ticket Unpaid Amount1] ) and filter the unpaid amt by >0 seems to work for me
    • Reserved / Unpaid Tickets - Gross or Net Value
      • Tess standard is [Total Ticket Value]-[Total Ticket Paid Amount]
    • Available Tickets
      • [Total Seat Unsold Count] but if that’s too high then you might need to add some seat hold code filters as per the above about holds
  • $organization in reply to Heath Wilder

    Not sure if this is correct but I used Seat Status for the following:

    Holds - filtered Available and Held to exclude any reserved or ticketed seats
    ( [Total Seat Count] ,  [Seat Hold Code] , [Seat Status] )

    Reserved Count - filtered on Reserved Unpaid
    ( [Total Ticket Count] , [Seat Status] )

Reply Children
No Data