8 Replies Latest reply on Jan 17, 2012 10:18 AM by MattLeach

    Calculating a Graph

    MattLeach

      I've been asked to setup a graph in our system to show the total amount of expected income for each month. Up to this point i have very minimal experience with FileMaker's graphing capabilities so i'm a bit new to this part.

       

      Each record in our database contains 2 fields:

      MonthlyAmt = The amount the client pays each month

      SWPD2 = The date that the client is paid up through (this date will always be the end of a month)

       

      To suffice my boss's need for this right away, i was able to export the information and quickly create a graph in excel as i'm very fluent in excel and knew i could get it done quickly.

      I now need to make this graph real-time, so the only solution would be a graph within our database.

       

      What would be the best way to approach this? I'm assuming i would need a summary field that calculates the total amount based on the paid through date?

       

      Thanks

        • 1. Re: Calculating a Graph
          comment

          MattLeach wrote:

           

          Each record in our database contains 2 fields:

          MonthlyAmt = The amount the client pays each month

          SWPD2 = The date that the client is paid up through (this date will always be the end of a month)

           

          How does one know when did the client start paying?

          • 2. Re: Calculating a Graph
            MattLeach

            There really is no "Start Paying Date". We have a start date for when the record was created.

             

            The way it works is when the client purchases our software, they are required to pre-pay a year. After that initial year, they are not required to keep up with paying their subscription. They could pay the first year, let it lapse and then start paying again a few months later either yearly, quarterly or monthly.

             

            I realize that this isn't exactly the greatest way to handle it, but this is how they've been doing it and will not change no matter how hard i try lol.

             

            Based on what they are looking for, i do not believe the start date is relevant. For example:

             

            Lets say i have 3 clients that pay $100 a month

            Client 1 paid through 1/31/2012

            Client 2 paid through 2/29/2012

            Client 3 paid through 3/31/2012

             

            Using these figures and what i'm being asked, the outcome of how much can be expected each month would be:

            January - $300

            February - $200

            March - $100

             

            I realize this isnt exactly the best practice for how to handle this, i've tried to discuss this with them but they insist this is what they want and there is no changing it.

             

            Thanks

            • 3. Re: Calculating a Graph
              comment

              There is an assumption here that all clients whose paid-through date is later than the month being considered are supposed to pay in that month. For example, your Client 3 is assumed to have a starting date on or before January 2012.

               

              Anyway, the real issue here is that you want to chart data that you don't have - namely the sums to be paid each month. You cannot use a summary field to get these numbers (at least not all of them at the same time), because you don't have enough records for that.

               

              There are several possible ways you could work around this - I'll outline the one that is the simplest (and the most primitive one, too): for each month you want to chart, find the applicable records (i.e. records whose paid-through date is equal to or greater that the end of that month) and place the name of the month and the summary value into variables. At the end you should have 2 variables, each containing a return-separated list:

               

              $months = "January¶February¶March"

              $amounts = "300¶200¶100"

               

              Use these two variables as the X and Y axes of your chart, respectively. Set the chart to use data from current record.

              • 4. Re: Calculating a Graph
                MattLeach

                Your assumption(s) are correct. All clients in the example started paying prior to 2012.

                 

                The thing im having the hardest time comprehending is how to go about calculating the total for each month.

                 

                You stated that i did not have enough records for a summary field. Those were just an example, in the real data im using there are over 4000 records.

                • 5. Re: Calculating a Graph
                  comment

                  MattLeach wrote:

                   

                  You stated that i did not have enough records for a summary field. Those were just an example, in the real data im using there are over 4000 records.

                   

                  Ideally, you would have an individual record for each payment. Then you could find alll payments in January, February and in March and summarize them by month. Using the above example, you would have 6 records in your found set. However, you have only 3 records - and you want the one record for Client 3 to contribute to 3 different sub-summaries. That's not possible. So what I suggest is that you do a find for each month in turn, and save the total in a variable. You can get the total from a summary field (total of MonthlyAmt).

                  • 6. Re: Calculating a Graph
                    MattLeach

                    Thank you for all your suggestions, i will give this a go.

                    • 7. Re: Calculating a Graph
                      Stephen Huston

                      Hi Matt,

                       

                      You can also get the PDF "Introduction to FileMaker 11 Charting" via the FM Knowledgebase, search on "charting info" and the first Featured Answer immediately below the search bar area (above the results!) is a link to download that PDF.

                       

                      It' worth reading to avoid some easy mistakes when learning FM charts.

                       

                      Stephen Huston

                      • 8. Re: Calculating a Graph
                        MattLeach

                        Will do, thanks Stephen.