Designing base to allow tracking of spending by entities over time

[Ryan_Sager]

Hi - I’m trying to track spending for various entities by date, to allow the creation of a chart of spending by month.

Our database has a list of Advertisers. And each of those Advertisers is linked to many Advertisements. Each Advertisement has already been assigned a dollar value and each record also has a date.

How would I create a chart for each advertiser for what they’ve spent in a given date range — ideally by month and/or quarter?

I’ve been able to create a cumulative sum of ad spending per Advertiser, but I’m stumped on breaking it down by month.

Any help much appreciated! Thanks!

[TheTimeSavingCo]

Hey Ryan, hmm, could you check this out?

In this, we use the chart’s “Bucket values by” function to bucket by month (or quarter if you’d like), and also make the chart display the records from a specific view.

We then add filters to that view to only show records for a specific advertiser, resulting in a chart that shows a single advertiser’s spend by month

[Ryan_Sager]

Thanks for your response. I can see how we’d do it in the extension, just to look at it ourselves in Airtable.

What we ultimately need, though, is for this to be pulled into a frontend (Softr) for each individual Advertiser (there are 3600 Advertisers).

So, basically I need an X and a Y axis for Softr. Unfortunately, as of now, Softr doesn’t bucket the way the Airtable extension does. I’d need to create the bucketed X axis (by month) to exist somewhere in Airtable.

[TheTimeSavingCo]

Ah, yeah, that’s a whole different beast. I’ve updated the original base to handle that

We’ll have to make a new table that has one record per advertiser per month, and then get Softr to read from that

I’ve accomplished this via the Text to Paste field and pasting that value into a linked field, thereby compiling the records per month. You could probably achieve this with automations too though; I just favour this method personally

To view the formulas, you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

I’m also available to be hired to set it up in your base for you as well