3 Replies Latest reply on Oct 15, 2014 9:51 AM by philmodjunk

    How do I calculate this? Can't seem to get it to work

    JuliaOlsen

      Title

      How do I calculate this? Can't seem to get it to work

      Post

       

      I need to calculate how much $$ is being spent in each of 3 categories every month to be able to track whether or not we are in budget. I keep records of transactions in a table called Monthly Accounting. One table.  Each record has an ID, creation date, category for payment - I have Employee Salaries (A), Student Payments through Bursar's Office (B) , and Student Payments through Outreach Program (C) - and amount (I enter these as negative numbers since they are subtractions from the initial amount for each month.

      So I want to know each month the total of A, the total of B and the total of C. I will need to sum each of these later into a report by fiscal year. 

      I am able to get totals for ALL subtractions.

      I am able to select out the records and get sub-summaries using portals. 

      I can't see what I am doing wrong.  When I try to calculate with either "case" or "if" I get the total amount as if I have not selected records though case or  if.  For example: If ( Category = "Student Support | Bursar" ; Sum ( Amount) )

      What am I doing wrong?

       

        • 1. Re: How do I calculate this? Can't seem to get it to work
          philmodjunk

          If ( Category = "Student Support | Bursar" ; Sum ( Amount) )

          Makes no sense. Since there are no tables names, Category, Amount and this calculation would seem to be defined in the same table (Monthly Accounting?) and thus the calculation would return nothing or the value of that record's Amount field. (You might want to look up Sum in FileMaker help to note what syntax should be used to get a sum.

          Selectively summing values can be done through several different approaches:

          If you have FileMaker 12 or 13, you can use the ExecuteSQL function: FMP 12 Tip: Summary Recaps (Portal Subtotals)

          If you don't have one of those versions or you don't wish to use SQL, there are other options: Sum_Calculation based on condition

          • 2. Re: How do I calculate this? Can't seem to get it to work
            JuliaOlsen

            Thanks for the feedback, Phil.  I hope answering this question yet again helps build your wealth - yes I have read the other posts on this form and this questions seems to pop up a number of times , so it seems to not be a trivial question!  Deceptively simple to sum based on values in another column.  

            My two tables are connected by the Expense ID match field. I have attached the relationship map.

            I am very frustrated at this point.  Have 2 tables.  One for data, one for calculations.  What I posted previously was an attempt to calculate within the data table.  Have gone back to calculations table ("Expense Framework") which only holds a match field, fiscal year, fiscal month, and calculations.  Data table holds match field, record id, category, an explanation field, and the amount of the transaction.

            No matter what I do, I get the total sum of the amount rather than selecting out by category.  I need income for month, expenses for month based on value in "Category". Then will subtract  from beginning of month (this is correctly carried forward from prior record) to get end of month total (and this calculates correctly).

            I am sure it's something simple that I am totally overlooking, but I just don't see it....

             

             

            • 3. Re: How do I calculate this? Can't seem to get it to work
              philmodjunk

              The methods I've already posted links to can be used to selectively sum your data. Here's a more detailed description of just one of those methods:

              Define a Summary field in Monthly Expenses Data to compute the total of Amount.

              Put a one row portal to Monthly Expenses Data on a layout based on Expense Framework. Define this portal filter expression:

              Monthly Expenses Data::Category = "Student Support | Bursar"

              Put the new summary field inside the single row of this portal.

              It will now show the total that you want.

              You can now add additional one row portals and just set up each with a different portal filter expression.

              But if you want a report listing all expense categories with a single row for each category and the total of amount for each such category, I would use a summary report layout based on Monthly Expenses Data. A sub summary layout part "when sorted by Category" can be set up and both the category field and the same summary field I described using with the filtered portals can be placed inside this layout part. You can remove the body layout part to limit the output to one row of data per category. You can perform finds, constrain found sets and use Go To Related Records to control what records are included in such a report so long as you always keep the found set of records sorted by category.

              Here's a tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial