2 Replies Latest reply on Dec 1, 2013 7:42 PM by RaeCrothers

    Cumulative total for the month



      Cumulative total for the month


           I'm a freelancer and like to keep track of how much I make in a day and how much I have left to earn that month to meet my goals.

           I am working on creating a time tracking data base with one record per day. Each record has a field that calculates how much I earned that day compared to how much I want to earn in a day with the formula of 'Earned today minus daily goal amount'.

           I would like another field on each record that would amount to 'Earned this month minus monthly goal amount.' I'm just not sure how to calculate 'earned this month.'

           I've looked at  the summary option for a field, but it doesn't give me the options I need:

           -recognize the start and end of the month and pull the records in that range

           -add up the total of the 'Earned today' fields in that range to come up with a 'Earned this month' figure

           -subtract the monthly goal amount from the 'Earned this month' figure.

           I know FMP is not a spreadsheet and I'm wondering if I'm asking too much of it?



        • 1. Re: Cumulative total for the month

               There are multiple ways to get that monthly sub total. One way is to define a self join relationship that matches one record to all records of the same month and year.

               Here's a simple calculation to give you a value that can be used as the match field in such a relationship:

               Define cMonth as: YourDateField - Day ( YourDateField ) + 1

               Select Date as the result type.

               Make a new Tutorial: What are Table Occurrences? of your table and link it by cMonth:

               YourTable::cMonth = YourTable 2::cMonth

               If you refer to your summary field via this expression: YourTable 2::summaryFieldName, you'll get the monthly subtotal. You can also use:

               Sum ( YourTable 2::Field ) to get the monthly total for Field over all the records of the same month.

               Ps: cMonth calculates the date for the first of the month for all dates of the same month and year.

          • 2. Re: Cumulative total for the month

                 Thank you! That worked perfectly!

                 I used Sum ( YourTable 2::Field )-Monthly goal to get the figure that I wanted after following the other steps. I made dummy records for two different months. The monthly goal field tracked the first month correctly and then reset to 0 at the start of the second month.

                 I'm starting to think there is nothing FMP can't do!