Skip to main content

I’m stumped…

I sell tickets to shows.

I do these shows in different cities.

Every day, I receive a report from each city which tells me total tickets sold and total $ value of those tickets.

So, my sales table (if grouped by city) looks like this:

CITY 1
11/1/19 325 Tickets $4,675
11/2/19 330 Tickets $5,150
11/3/19 345 Tickets $5,325

CITY 2
11/1/19 600 Tickets $7,575
11/2/19 650 Tickets $8,420
11/3/19 700 Tickets $9,380

CITY 3
11/1/19 100 Tickets $1,250
11/2/19 125 Tickets $1,650
11/3/19 150 Tickets $1,850

And so on…

My sales team needs to know things like:

How many tickets did we sell in City 1 yesterday?
How many tickets are we averaging daily in City 2?

And such…

In a spreadsheet, it would be easy, I would just create a formula that subtracts yesterday’s total from today’s total and my answer would be “Tickets sold today”.

Obviously, I can’t do that in airtable (or at least I don’t know how) because formulas only work within the same record.

I’ve thought about creating a zap that shoots today’s total to a spreadsheet which performs the calculation and then sends the result back to airtable but that seems cumbersome and inefficient to me. Maybe it’s the best way…

Thoughts?

Make table Reports with fields: ID, date, city, #, sum
And table Cities (name of the cities).

In Reports: make a record for each day for each city, so 9 records in your example. Link field Cities to the table Cities. You should be able to do all the calculations you want like this.

Make table Reports with fields: ID, date, city, #, sum
And table Cities (name of the cities).

In Reports: make a record for each day for each city, so 9 records in your example. Link field Cities to the table Cities. You should be able to do all the calculations you want like this.


That’s how I have it set up, but I still can’t figure out how to subtract yesterday’s sales from today’s sales and return total tickets sold for a day…

Ah, aggregates. Depends on how many days you want to look back. Only 3 I would do it like this:

In Reports an extra calculated field Days ago (0,1 or 2) then a column today: if daysago=0 then Sales else “”. For yesterday and day before same formulas but then with 1 and 2.
Now you can use lookups in Cities to get the Sales in a separate column. And calculate the sales per day in new columns.

Still, a workaround…

Ah, aggregates. Depends on how many days you want to look back. Only 3 I would do it like this:

In Reports an extra calculated field Days ago (0,1 or 2) then a column today: if daysago=0 then Sales else “”. For yesterday and day before same formulas but then with 1 and 2.
Now you can use lookups in Cities to get the Sales in a separate column. And calculate the sales per day in new columns.

Still, a workaround…


I’ll have a go… I was simplifying for structural understanding.

I currently have about 120 shows on sale in around 80 different markets.

The on-sale period for each event (between tickets going on sale and the show happening) can vary from anywhere between 3 months to as much as a year.

The trick is to be able to gauge how each market responds (or fails to respond) to advertising and marketing efforts. So my marketers need to be able to see "well, we were doing 20 tickets a day with no advertising and I just launched a $10,000 TV schedule and I only jumped to 30 tickets a day while ads were running so our dollars are probably better invested elsewhere…

Stuff like that…

Yes, so too much for manually adding date columns.
However you could use my method to create a new view Entry where user types Total and in the next field the daily sales are calculated for the user as a ‘suggestion’ to type in in the field after that.
But considering its a lot of data, I would use a google sheet to put in the reports and use Zapier to add the correct records into Airtable.

Welcome to the community, @Chris_Ritter! :grinning_face_with_big_eyes: Because Airtable is a database, with records not knowing of each others’ existence, doing cross-record calculations in the same table requires some interesting gymnastics. If you want to pursue that option, check out this thread for ideas:

Terms & ConditionsAccessibility statement