2 Replies Latest reply on Feb 24, 2016 1:55 PM by jared944

    Calculate yearly difference in income by month

    jared944

      Hello everyone,

       

      I am looking to create a filemaker database that will analyze the monthly income of a business. Specifically, I want to compare months and calculate the net additional income or loss the business has sustained that month. If there is a net increase of a certain amount a%3100bout projected than I want to issue a bonus to my employees.

       

      I will have a field that has an amount and a field in the same record that has a date. Lets take January for example:

       

      January 15 2013          $67,100

      January 15 2014          $70,200     Net Increase      $3,100

      January 15 2015          $69,200     Ned Decrease    $1,000

       

      The only way I imagine I can do this is with a script that looks at the previous year and runs a calculation to put a value in the increase/decrease field. Any ideas would be appreciated.

       

      Thanks!

       

      Jared

        • 1. Re: Calculate yearly difference in income by month
          jbrown

          Evening.

          There's a few ways you can do this, but what I would do is what you allude to, that is, creating a table that holds the summary information of the monthly income.

          I'm assuming you have a table that stores the month, year, income for the business. At the end of the month, you calculate the total earnings (or have it through an un-stored calc).

           

          I'd create another table that stores the NetResults. In this table is stored the primary keys of each month from that monthly income table (fk_StartYear and fk_EndYear), the increase/decrease amount, and a field that identifies it as a "Decrease" or "Increase".

           

          This gives you a static summary of the difference between two months. since Jan 2013 and Jan 2014 is in the past and unchangeable (if that is a correct assumption), you could script the creation of the summary record and store the loss/gain amount as a number field.

          Potentially you're saving yourself a lot of time when the record is stored in a static fashion; any reports you run don't have to be based on unstored calculated fields.

           

          you COULD do the same thing and have unstored calcs between the years table, but then you are dealing with potential unstored-calculation slow down.

           

          I prefer scripting to work out my business logic. That is simply how I choose to do things.

          • 2. Re: Calculate yearly difference in income by month
            jared944

            Thanks! I figured this was the best way but I just needed another opinion.