4 Replies Latest reply on Jan 29, 2014 12:56 PM by m.mcdonell

    Chart number of invoices by month

    m.mcdonell

      I have a relational layout and want to show (in the Dashboard Table) the number of invoices (from the Invoice Table) generated per month for the current year. This chart would update as invoices are added and are based upon the Invoice Date.

      2nd question: How would I use the same information as above to display 'by current month' a chart showing the number of invoices by sales person?

       

      Thank you in advance,

      Michael

        • 1. Re: Chart number of invoices by month
          jbrown

          Good afternoon.

          To generate a chart that updates constantly, you can do a few things. All of these would involve the ExecuteSQL function.

           

          Here's what I would do: I would create a separate summary table that records the sum total of invoices per month. it would have a few fields:  InvoiceCount, Month(number), MonthName, Year, I would get this to update once a month, on the last day to summarize your invoices table. On the chart, I would write the following: (Note the chart, in this set up can be on pretty much any layout)

          In the x-axis part of the chart generator, I'd write "ExecuteSQL(" SELECT MonthName FROM InvoiceSummary WHERE Year =? ORDER BY Month";"";"";2014)

          In the y-axis part of the chart generator, I'd write "ExecuteSQL("SELECT InvoiceCount FROM Invoice Summary WHERE Year = ? ORDER BY Month";"";"";2014)

           

          In the DataSource, be sure to select "Current Record (delimited data). This is important. This lets the chart know it will be getting a list of numbers to put on the chart rather than a field from many records or even from the current record.

           

           

          If you need this updated daily, skip the Invoice Summary table. In your invoices, table, I'd create a MonthNumber field that records the month number to keep all invoices grouped together into months and a year field that records the year. . Instead write "ExecuteSQL("SELECT COUNT(Invoice) FROM InvoiceTable WHERE Year = ? GROUP BY MonthNumber";"";"";2014).  This would be for the y-axis. For the x-axis, I'd ask the SQL statement to get the DISTINCT Months from the InvoiceTable, order by the months.

           

           

          To answer your 2nd question, simply recreate the graphs but instead ask the SQL statement to select count the invoices that are in this given month:  SELECT COUNT(Invoices) FROM InvoiceTable WHERE MonthNumber = ?";"";"" Month(Get(CurrentDate)).

          • 2. Re: Chart number of invoices by month
            beverly

            Michael, I always create an auto-enter field in any table using dates and needing to be charted or grouped for reports. This field is TEXT and has the calc:

                 Year(mydatefield) & "_" & Right("00" & Month(mydatefield) ; 2 )

             

            You can probably skip the "_" but I find it easier to read. This is also a good field for sorting, as alphanumerically it is correct. You can search on this field and I've used it for filtering a portal of records.

             

            You can use this field in the charts and you can always calculate the "format/display" of the label to be something more human (JAN 2014, for example) instead of the 2014_01 text.

             

            Once you have this field, you can use it with the salesperson summary as well for reports and charting.

             

            HTH.

            Beverly

            • 3. Re: Chart number of invoices by month
              m.mcdonell

              I have modified the invoice template that came with FP 13 Advanced and the Dashboard used allows for much of the invoice and product to be calculated as you work. I am preferring a method along those lines so that users will not have to call any information. It also provides close to real time updates at a glance.

              I will try your second suggestion and also look further into how the charts are done on this template.
              Thank you

              • 4. Re: Chart number of invoices by month
                m.mcdonell

                Thanks Beverly, I do have an auto create field for the date and a modification date as well. I am unsure how to take this information and turn it into a chart showing the count of each salesperson separately on a monthly chart.

                Example: The month of January had 24 sales:

                Brian = 9

                Ted = 3

                Don = 12

                (chart could be pie or flow)