3 Replies Latest reply on Mar 4, 2014 3:18 AM by Kaorin

    Calculation of Total under specific criteria per month

    Kaorin

      Title

      Calculation of Total under specific criteria per month

      Post

           Hello, 

           I wonder someone can let me know how to do the calculation of following "without" using summary fields / part:

           I'd like to get the TOTAL amount spent each month under Calculation field, and so far I have 

           - AMOUNT SPENT field (number)       and

           - MONTH (text) field  , Jan / Feb / Mar ...... Dec

           Is there anyway of using a calculation field and set criteria / function / formula so that I don't have to keep "sorting" to get the instant Amount Spent on each month under one Table?

           Say I'm trying to get the Total Amount Spent for March without using Summary fields & part. in a different Table as an Annual Report.

           It's ironic - I'm trying to set the Brows Mode of my Filemaker to look like Excel spreadsheet on this occasion! Please don't tell me to go back using Spreadsheet.

           I use FM 12 for Mac.

           Thank you.

            

            

            

            

        • 1. Re: Calculation of Total under specific criteria per month
          philmodjunk

               And what happens if you have data for Jan 2014 and data for Jan 2015? Won't those be two separate totals?

               are the amount and month fields the only fields in this table? If not, how is that table set up?

               And how do you want this to look on your screen?

               I'm not as yet convinced that you should do this without summary fields as you can use a single summary field to show multiple monthly sub totals on the same report.

          • 2. Re: Calculation of Total under specific criteria per month
            Kaps_1

                 Can't you set up a relationship based on dates ? So assume your current table is called expenditure and has two fields called expenditure_date and expenditure_amount . Have a second table called total expenditure with three fields called start of month  , end of month and total expenditure . The relationship could be 

                  

                 expenditure:: expenditure date >= total expenditure :: start of month AND 

                 expenditure::expenditure date <= total expenditure :: end of month

                  

                  

                 Then total expenditure::total expenditure = sum(expenditure amount).   Thanks kaps

            • 3. Re: Calculation of Total under specific criteria per month
              Kaorin

                   Yes, that works! - Thank you, Kaps.