Skip to main content

Calculate sum according to date and other parameter

  • June 17, 2016
  • 4 replies
  • 112 views
M
Forum|alt.badge.img+2

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

    4 replies

    K
    Forum|alt.badge.img+4

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

    T
    Forum|alt.badge.img+19
    • Tuur
    • Inspiring
    • June 18, 2016

    Check out rollups… :slightly_smiling_face:

    M
    • Matt_Grandy
    • New Participant
    • June 30, 2016

    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!

    T
    Forum|alt.badge.img+19
    • Tuur
    • Inspiring
    • July 3, 2016

    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!


    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.

    Terms & ConditionsAccessibility statement