Formula for displaying current ticket sales

[Mike_Driscoll]

I’m looking to calculate the amount of tickets sold and the revenue the event generates and have it update as more tickets sell. Here’s a 56 second video laying everything out.

Table 1: Eventbrite Ticket Sales
Table 2: Master Event Log

[Pablo_DV]

Hi Mike,

Do you have some kind of event ID that Eventbrite also references or do you need to rely on Venue + Date to identify events?

Assuming the later, here you have the formulas for Current Ticket Sales and Eventbrite Revenue. You might need to adjust them slightly depending on your schema for them to work.

[Eventbrite Ticket Sales].filter(
  Venue=thisRow.Venue AND [Event Date]=thisRow.[Event Date]
).count()

[Eventbrite Ticket Sales].filter(
  Venue=thisRow.Venue AND [Event Date]=thisRow.[Event Date]
).[Amount Paid].sum()

Basically you are taking all the tickets sales that have the same venue and date as that of the current event, then counting them in the first formula or summing the amount paid in the second.

Hope this helps,

Pablo

[Mike_Driscoll]

@Pablo_DV

[Eventbrite Ticket Sales].filter(
  Venue=thisRow.Venue AND [Event Date]=thisRow.[Event Date]
).count()

Thanks for taking a look at this.

I put the formula in but it’s returning 0 for every event. Same thing with the revenue.

[Pablo_DV]

Here you are comparing a row vs a text (pay attention to the icon within the chip). In order to fix this you have two options:
(assuming there is a table called Venues with a text field named Name)

• Create a relation column in Eventbrite Ticket Sales (recommended)
  • Rename Venue to Venue Name
  • Create a Venue relation column. If you don’t have a Venue ID or something like that, then you need to filter based on the name, with the following formula: Venues.filter(Name=thisRow.[Venue Name])
  • Apply the formulas from my previous post again
• Refer to the Venue name in the formula
  • Adapt the formulas from my previous post to refer to the Venue name, so you are now comparing a text to a text Venue=thisRow.Venue.Name
  • This is simpler, but not as robust as the previous option, which will open the door to more advanced data handling.

Hope this helps,

Pablo

[Mike_Driscoll]

@Pablo_DV

[Eventbrite Ticket Sales].filter(
  Venue=thisRow.Venue AND [Event Date]=thisRow.[Event Date]
).[Amount Paid].sum()

Now I’m getting totals.

[Pablo_DV]

The color of the chips is also useful to build proper filters, it indicates to which table the variable belongs to.

In your first formula Eventbrite Ticket Sales is green and all the items within the filter() are purple. If you click on the chips you will see they all belong to Master Event Log.

Basically what you are doing is saying: “please give me all the items of Eventbrite Ticket Sales where the current Event Venue is equal to itself”. Obviously that will return all the items.

You want it to return the Sales where the Venue of the sale is equal to the Venue of the event, 2 different tables and therefore the chips should also have different colors.

TL;DR: The formula is correct, you just have to make sure the 1st and 3rd chips within the filter() are green (belong to Eventbrite Ticket Sales)

Click on the first Venue chip, start typing it again and pay attention to the suggestions. Select the Venue item that is green instead of purple.

Hope this makes sense to you,

Pablo

[Mike_Driscoll]

@Pablo_DV

[Eventbrite Ticket Sales].filter(
  Venue=thisRow.Venue AND [Event Date]=thisRow.[Event Date]
).[Amount Paid].sum()

I think I’m close but still not getting the outcome. Thanks in advance Pablo.

[Pablo_DV]

Here you are trying to compare a chip that can represent multiple rows vs a chip with only one row (again, pay attention to the icons).

You have two options:

• If you are sure EV Related Column should only contain one item, then add .first() at the end of its formula.
• Otherwise, use the formula Contains() instead of the = sign: [EV Related Column].contains(thisRow.Venue)

If it still doesn’t work, please create a copy, remove sensitive information and share it in play mode, so that I can check what’s wrong.

[Mike_Driscoll]

Login - Coda

[Pablo_DV]

You need to let anyone with the link edit it or publish it in play mode.

[Mike_Driscoll]

I just changed the settings.

[Pablo_DV]

Still not correct make sure that it is in play-mode and allow copies just in case.

[Mike_Driscoll]

Geez, I swear I’m not this dumb. I think I just fixed it?

[Pablo_DV]

There are multiple issues:

• Missplaced bracket in the formula, here’s the correct one

[Eventbrite Ticket Sales].filter(
  [EV Related Column].Contains(thisRow.Venue) AND
  [EV Event Date]=thisRow.[Event Date]
).count()

• EV Related Column in the Eventbrite table is a text instead of a relation and anyway it doesn’t point to a real ‘Venue’ table, or that table is not properly maintained, since there are duplicated values.
• Amount paid is a text instead of number/currency
• etc. etc.

In general the data seems to be quite messy, the column types are not consistent and the general structure not so well thought through so you would really need to spend quite some time tidying up before being able to extract any meaningful insights.

Good luck!

Pablo