4 Replies Latest reply on Jun 12, 2014 3:05 AM by NathanVeitch

    Using sub summary totals in calculations

    NathanVeitch

      Title

      Using sub summary totals in calculations

      Post

      Hi There,

      I need a little advice on playing around with sub summary totals. I am trying to display an income statement. I have been able to get the sub summaries working where it shows me the totals for the different sections, eg Revenue, Expense, Interest, Tax and Dividends. Now I need to work the report so that the totals shown would be: 


          Total of Revenue - made up of all the revenue amounts
          Total of Expense - made up of all the expense amounts
      Income before tax and interest (Total of Revenue - Total of Expense)
          Total of Interest - made up of all the interest amounts
      Income before Tax ( Income before tax and interest + Total of interest)
          Total of Tax - made up of all the tax amounts
      Income after Tax ( Income before tax - Total of Tax)
          Total of Dividends - made up of all the dividends amounts
      Retained Income ( Income after Tax - Total of Dividends)

      Im thinking of creating a new field to base the sub summary for the income before/after sections, but im stuck on how to work the calculation. The Total of sub summaries are working at the moment.

        • 1. Re: Using sub summary totals in calculations
          philmodjunk

               depending on how you sort your records  and where the GetSummary function may be able to provide the needed subtotals that correspond with sub totals shown in your sub summary parts.

               In other cases, you may want to use executeSQL to compute the needed sub totals--if you are using FileMaker 12 or newer.

          • 2. Re: Using sub summary totals in calculations
            NathanVeitch

                 Hi Phil,

                 I have tried the GetSummary() option, but I am unsure of how to get the summaries working, cause of the different calculations required, eg revenue - expenses, then the next being revenue - expenses + interest and so on. 

                 I was looking at the ExecuteSQL but I am not sure of how to use it. I tried ExecuteSQL ( "select sum(Amount) where Income_Statement_Group1 = Revenue" ; "" ; "" ; "" ) just to see if I could get the total for the revenue but I only get a ?. 

                 I do have to ask thou, if I get the SQL statement right, would I then have to have a case statement looking something like this:

                 case(

                 Income_Statement_Group1 = Expenses ; ExecuteSQL ( "select sum(Amount) where Income_Statement_Group1 = Revenue" ; "" ; "" ; "" ) -  ExecuteSQL ( "select sum(Amount) where Income_Statement_Group1 = Expenses" ; "" ; "" ; "" ) 

                 Income_Statement_Group1 = Interest ; ExecuteSQL ( "select sum(Amount) where Income_Statement_Group1 = Revenue" ; "" ; "" ; "" ) -  ExecuteSQL ( "select sum(Amount) where Income_Statement_Group1 = Expenses" ; "" ; "" ; "" ) + ExecuteSQL ( "select sum(Amount) where Income_Statement_Group1 = Interest" ; "" ; "" ; "" )

                 Income_Statement_Group1 = Taxation ; Interest ; ExecuteSQL ( "select sum(Amount) where Income_Statement_Group1 = Revenue" ; "" ; "" ; "" ) -  ExecuteSQL ( "select sum(Amount) where Income_Statement_Group1 = Expenses" ; "" ; "" ; "" ) + ExecuteSQL ( "select sum(Amount) where Income_Statement_Group1 = Interest" ; "" ; "" ; "" ) - ExecuteSQL ( "select sum(Amount) where Income_Statement_Group1 = Taxation" ; "" ; "" ; "" )

                 Income_Statement_Group1 = Revenue ; "" )

                 The reason i am looking at it like this is because I have a sub summary below set for Income Statement Group 1 so it will show the totals below the required sections.  

            • 3. Re: Using sub summary totals in calculations
              philmodjunk
                   

                        I tried ExecuteSQL ( "select sum(Amount) where Income_Statement_Group1 = Revenue"

                   Try:  ExecuteSQL ( "SELECT sum(Amount) FROM YourTableNameHere WHERE Income_Statement_Group1 = 'Revenue' " )

                   Note the single quotes around the literal text "Revenue". (But you don't have to put the SQL key words in all caps, that's just a standard format often used in SQL queries.)

                   And be careful, your report may be based on a found set of records while this query will produce a total based on all "revenue" records in your entire table. You may need to add additional terms to your WHERE clause in order to get the correct sub total.

                   You could set up a case function like that or you could define a series of fields, each with a different SQL query and stack them on top of each other in your sub summary part where you need the subtotal.

              • 4. Re: Using sub summary totals in calculations
                NathanVeitch

                     Hi Phil,

                     Once again, thank-you for the help. :)