Jul 13, 2022 11:39 AM
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!
Jul 14, 2022 03:43 AM
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
Jul 14, 2022 09:44 AM
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.
Jul 14, 2022 11:11 PM
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