2 Replies Latest reply on Feb 2, 2014 7:13 PM by macwizard

    Line Chart for running totals (FMPAdv 12)

    macwizard

      I wish to create a line chart to display cumulative sales (running total) amounts at monthly intervals for the current year to date. I have a SalesSummary field and an InvoiceMonthNumber field that work nicely in a bar graph showing month by month sales totals, but now I wish to display how those monthly totals add up cumulatively in a line chart. Months are to be laid out on the X axis and sales on the Y axis.

       

      If the sales are generally equal each month, the line will be relatively straight, rising at perhaps a 45 degree angle, but if monthly sales increase steadily through the year, the line will be closer to an exponential curve. Ultimately, I wish to display the current year and the previous 3 years for comparison.

       

      Is there a simple way to create a calculation field that will total only January's sales if the month is Jan, but Jan and Feb sales together if the month is Feb, etc.?

       

      I am happy to use ExecuteSQL if that is the best approach, but I am a beginner with SQL. Any help would be greatly appreciated.

       

      Henry

        • 1. Re: Line Chart for running totals (FMPAdv 12)
          user19752

          It is better to save SalesSummary field  and an InvoiceMonthNumber as a record per month.

          Since ExecuteSQL is very slow. ex. I tested date range 1 month

          SELECT SUM(num) WHERE date<=? AND date>=?

          finding about 3,000 from 360,000 records takes about 4sec.

          You need 3years then 4x36sec may be need in this case.

          1 of 1 people found this helpful
          • 2. Re: Line Chart for running totals (FMPAdv 12)
            macwizard

            Thank you for your suggestion. I assume I would:

             

            1) create a script to find the records where the InvoiceMonthNumber is = or&lt; n

            2) then sort by date and go to last record and

            3) set the Sales running total as a variable, looping through all the months to set successive repetitions of the variable.

            4) finally, it would generate a single record per month in a new table populating the SalesSummary from the multiple repetitions of the variable. That will certainly be easy to graph.