Calculate sum according to date and other parameter


#1

Hello together!

first, thanks for the wonderful app I just found!

I set up several tables which essentially track sales. There are three different product categories, for each of them I set up a table names with the category ID. The three tables are identically, basically consisting of a unique product ID, date of sale and amount for the product (calculated using a formula from other fields).

What I am now trying to figure out is a way to calculate the total amount of all sales by month and by product category. Ideally I could somehow show the total income for all product categories in a given month or for a given month classified by product category.

Is it possible with Airtables or does anyone have a good idea for a 3rd party app that does that?

Thanks!
Striatum


#2

On the desktop version, if you create a view that filters your records, there is an autosum at the bottom of each column.


#3

Check out rollups… :slight_smile:


#4

I’ve been trying to do something similar to the OP. Rollups seemed promising, but it looks like you can only perform roll-ups on linked fields, which essentially tokenizes the values in the field. And once a field is linked/tokenized, it doesn’t behave as a date or formula field anymore.

So in my case, ideally I’d have a single date field (that I manually input), an Expense Amt ($) field, and then be able to roll up the sum of the Expense Amt based on the month of the date field. But, as soon as I do the roll-up, the date field gets tokenized, and then acts like a category/single select field, instead of a date field. Same happens if I try to create a Month formula field to derive the month from the date field.

Any ideas would be greatly appreciated!


#5

You could make a ‘Month’ table and enter the expense & a link to the month in your details table (instead of using a formula). Now in the ‘Month’ table you could do a rollup.

Not ideal, but a sum of the cash flow non the less.