2 Replies Latest reply on Mar 8, 2010 10:02 AM by jcalcara

    Calculation using dates and sales data



      Calculation using dates and sales data


      I have a database with inventory items in one table, and sales data in another table. In the inventory table, which I have linked to sales, and can see the sales data in one of my layouts, I want to create a calculation field that will give me sales totals for each month individually. So, it will look at sales data, look for the given month, and extract the total sales for that month. Then I also need to create a field that will calculate year-to-date for the item.


      I have tried various if statements (based on examples found searching the internet), but have had no luck getting anything to work.


      I am new to FileMaker (although a very experienced user), but have pretty much mastered Bento. My new databases were imported from Bento.

      I am using FileMaker Pro 10, latest version, on a Mac running Snow Leopard.


      Here is a screen shot showing the related sales data, and the month fields on the right that I want to automatically fill with the correct info.






        • 1. Re: Calculation using dates and sales data

          You can have a SUMMARY field on the field which contains sales.  Do a find on the date range to pull the months.  If you do a GETSUMMARY field that does it at the record level, it will be on every record for the given month. 


          You could also do a table of summarized sales.  Each record in your sales table would become a child record to this table.  One record in this new table could be sales by week that you summarize for a month using a sub-summary part. Or you could just have one record per month that has a calculation field to sum on the records for the month.  There are several options dependent on how your data is laid out in the existing table and how you want to do reports.

          • 2. Re: Calculation using dates and sales data

            Interesting.... thanks so much, I will see what I can do.

            I take it there is not simple way to come up with a calculation field that would be able to parse this data from the sales table?