5 Replies Latest reply on Nov 29, 2013 11:30 AM by philmodjunk

    Cumulative totals

    FilipePimentel

      Title

      Cumulative totals

      Post

           Hello FM Gurus!!!!

           Needs some help: 

           I am creating a monthly report to see the expenses and revenues. No issues there. 

           However the Financial Controller wants to add a column in the report to show for each line of expenses and revenue the cumulative (total for this month + total for last month).

           Basically I would have for February for example: 

           Items              Amount (current month)   Cumulative (amount from last month)              Total

           Salaries         1000                                      1000 (in January salary was 1000)                  2000 (sum of Amount + Cumulative)

            

           Then the Total should then be carried over to next month in the Cumulative line for march

           Any help would be very much appreciated

            

           F

        • 1. Re: Cumulative totals
          philmodjunk

               Take a look at summary fields. There's an option for "total of" summary fields that compute a running total--which would seem to be what you need here.

          • 2. Re: Cumulative totals
            FilipePimentel

                 Hello PhilModJunk, 

                 I maybe doing something wrong, but the summary fields are not doing for me. 

                 What I am doing is an accounting sistem. Montlhy the Financial Controller runs 2 reports - revenue and expenses. They are sorted by item. As the items are a value list, they will be consistent. 

                 So, let's say we start from 1st Jan-2013. Previous month (December 12) is zero amount - we will not have an amount - it will be as if the company just started on 1st of jan. 

                 At the end of Jan, after entering all data, the FC will run a report. and she wants to appear just like that: 

            January- 2013

                 item                                Amount             PreviousMonth Amount                Total (sum of Amount and PreviouMonth amount - running total

                 Salaries                         2000                                  0                                                        200

                 Then in February we pull another report: 

            February 2013

                 item                                Amount             PreviousMonth Amount                Total (sum of Amount and PreviouMonth amount - running total

                 Salaries                         4000                                 2000                                                       6000

                      Then March: 

            March 2013

                      item                                Amount             PreviousMonth Amount                Total (sum of Amount and PreviouMonth amount - running total

                      Salaries                         3000                                 6000                                                       9000

                  

                 I maybe doing something wrong...

                 The previousmonth field should be a calculation, I imagine to pull the data from the Total field, but I am not getting it right, somehow...

                 Thanks

                 F

                             

                  

            • 3. Re: Cumulative totals
              philmodjunk

                   For reporting purposes "PreviousMonth Amount" seems redundant as that balance is listed on the row above.

                   From the example shown, you can define Total as a running total summary field that computes the total of amount and you'll get the amounts shown in your example data.

                   The PreviousMonth Amount field will not be needed to produce that running balance of your account. If you must have that amount on your report, it can be defined as a calculation: Total - Amount.

              • 4. Re: Cumulative totals
                FilipePimentel

                     Phil,

                     Thanks for your help. But I am not getting the final result that the FC is looking for...

                     Following your steps, I do get the running total, and the items are summarized correctly. However when I have the final report, say for march, I see each line one different item, which is what I want, but then the amount values and total are being added up after each line. 

                     Iterns                    Amount            Total (running)

                     Salaries              5000                  5000

                     Petrol                  1000                  6000

                     Rent                     3000                 9000

                     Bonus                  1500               10500

                      

                     But this is not what we are looking for.

                     What I am seeing is that salarie is 5k, then petrol is 1k, which is added up to salaries making it 6k, then rent is another 3k, whichs then make 9k and so on and so forth.  

                     What the FC is looking for is when she pulls the report for march, she will see on the amount line for salaries for the month of march. The total should be the total spent on salaries year-to-date.

                     Hope you can help

                     F

                      

                • 5. Re: Cumulative totals
                  philmodjunk

                       Keep your summary field but remove the "running total option" or create a new summary field if you have other uses that need the running total option.

                       Define a self join relationship like this: (substitute your names for mine)

                       Transactions::Item = Transactions|Year::Item AND
                       Transactions::cYear = Transactions}Year::cYear

                       Define cYear as a calculation field: Year ( TransactionDate )

                       Transactions and Transactions|Year would be two Tutorial: What are Table Occurrences? with the same data source table.

                       Then, on your Transactions layout, you can add this summary field from Transactions|Year to your layout to show the YTD total for that item.

                       In FileMaker 12, a calculation could also be defined that uses ExecuteSQL to compute the same total without having to add a table occurrence to your relationships graph.