3 Replies Latest reply on Aug 18, 2009 9:51 AM by philmodjunk

    Cost Calculation

    Catherine

      Title

      Cost Calculation

      Post

      I'm a calculation field rookie.

      Any advice how to create a formula (in a caluclationf field) that takes the number value of the 'total cost' field' in order to show monthly expenditure (ie by adding each cost field in a given month)

      Thanks!

        • 1. Re: Cost Calculation
          philmodjunk
            

          A lot depends on how you've set up your table(s)

           

          Let's assume you have multiple records for each month with "Cost" the name of either a number field or a calcualtion field that returns a number.

          You will also need a date field which we'll call "DateField"

           

          Define a summary field "TotalCost" and set it up as the "total of" the Cost field.

          Create a calculation field that will return the same date for each record with the same year, month: Date (Month (DateField); 1 ; Year(DateField)). Call it "MonthYear".

           

          Create a summary report layout with a sub-summary part when sorted by "MonthYear".

          Create a trailing Grand Summary part.

           

          Place the TotalCost field into the Sub-Summary and Grand Summary parts.

           

          Sort your records by MonthYear.

           

          You'll get a report where Monthly cost is returned in the Sub-Summary part and the total cost is shown in the Trailing Grand Summary.

          • 2. Re: Cost Calculation
            Catherine
              

            I followed these instructions until the part about creating a summary layout. Does this mean creating an entirely new layout? I didnt see summary layout in one of the pre-configured options.

            Could you please clarify this point?

            Thanks- much appreciated!

            • 3. Re: Cost Calculation
              philmodjunk
                

              A summary report is a list/report with grouped data. You can create your layout using those options, but I find it easier just to create a list report and then add the sub-summary and grand summary parts after the basic layout is created.

               

              Starting from an existing list/report type layout:

               

              Enter layout mode

              Select Layouts | Part Setup... and click the Create button.

              You'll see a list of layout parts you can create. Select the sub summary option and choose the options that will work for you. (Sort by your MonthYear field, print below or print above).

              You can use this same method to create a trailing grand summary part.

               

              Note: Sub-summary parts are invisible to filemaker version earlier than FMP 10 unless you are previewing/printing the report. Sub-summary parts are invisible in all versions/all modes unless the found set of records are sorted in an order that refers to the "sort by" field specified for the sub-summary part.