13 Replies Latest reply on May 22, 2014 12:42 PM by MichaelBennewitz

    Create a bar graph for monthly sales

    KennySolway

      Title

      Create a bar graph for monthly sales

      Post

      Hello,

      I would like to know how to create a bar graph/chart that allows me to view my annual sales broken down by month across a bar graph.  In other words, I'd like to create a report that allows me to easily see what my sales are in each month.

       

      Right now I have a standard table with my revenue field name as 'Price' and the date for when the revenue occurs field name is 'Delivery Date'.

       

      Your assistance in advance is much appreciated.

        • 1. Re: Create a bar graph for monthly sales
          philmodjunk

          Know how to produce a summary report with a monthly total for each month?

          Your bar chart will have similarities to such a report. You'll need a summary field, I'll call it sSalesTotal, that computes the total of your Price field. You'll need to be able to sort your records so that they are grouped by month. Here's a method I recommend for that:

          Define cMonth as Delivery Date - Day ( DeliveryDate ) + 1 and specify "date" as the return type. This computes the date of the first day of the month for every record.

          Now set up your chart to graph the sTotalSales Field and use either cMonth or Delivery Date in a calculation to label your X-axis. Make sure that you specify these options:

          Use Data From Current Found Set
          Show Data Points for Groups of Records when sorted.

          Then you do a find for the records you want to chart and sort them by cMonth in order to group them by month, sorted in chronological order.

          • 2. Re: Create a bar graph for monthly sales
            KennySolway

            Hi,

             

            Thanks for your response above.  As I'm very novice to customization, are you able to simplify the steps outlined above?  I understand in theory what you've explained, but I don't know how to recreate what you've explained. 

            Is this possible please?

            • 3. Re: Create a bar graph for monthly sales
              philmodjunk

              Which part do you need help with? Defining cMonth or in setting up the chart? Let me know exactly where you first get lost and I'll break it down into more specific steps from that point.

              • 4. Re: Create a bar graph for monthly sales
                KennySolway

                I don't suppose I can send you my file to work on can I?

                If so, please advise how to do this.

                If not, Assume I don't know anything other than how to set up the table which contains the data.

                Your note above assumes I know how to set up a summary report, which I don't.  

                Are you able to outline it step by step? Also, to ensure we're clear on my desired outcome report, please see the graph on this link http://www.realtown.com/LiveInLosGatos/blog/los-gatos-homes/blossom-hill-manor-real-estate-market where it says 2 - Average sales price per month for this east Los Gatos neighborhood:

                • 5. Re: Create a bar graph for monthly sales
                  philmodjunk

                  The chart on that web page shows what I understood you to need.

                  Open Manage | Database | Fields and select the table in which you set up these fields in the drop down if it is not already selected.

                  Create the field cMonth, select Calculation as its field type and enter the calculation that I posted earlier:

                  Delivery Date - Day ( DeliveryDate ) + 1

                  In the bottom left corner of the Specify Calculation dialog where you enter this expression is a drop down. Select "Date" from it. Now click OK.

                  This calculation should return the date of the first day of the same month as the date in DeliveryDate.

                  Get this working and report back when you do. (Or what problems you encountered if you cannot get it to work.)

                  • 6. Re: Create a bar graph for monthly sales
                    KennySolway

                    This is great.  So far so good.  I appreciate the step by step and simple nature of the instructions.  Ready for next step.

                    • 7. Re: Create a bar graph for monthly sales
                      philmodjunk

                      Add sTotalSales to the same table.

                      Select "summary" as the field type. Select the "total of" option and click the listed field in this table where you record individual sales.

                      Now add a new Chart Object to your layout. (The layout must specify the same table as where we have defined these two new fields.)

                      In the Chart Setup Dialog box that appears, select Bar Chart as the chart type.

                      Enter what you want for the chart title.

                      For the X-Axis, click the button with the black triangle on it to the right and select "specify calculation".

                      Enter this calculation:

                      Left ( MonthName ( YourTable::DeliveryDate ) ; 3 ) & " " & Year ( YourTable::DeliveryDate )

                      Since I don't know the name of the table, I've used YourTable. Put your name in place of this text. (Better, just add the fields from the list of fields shown in the list at top by double clicking them.) This produces the three letter month name abbreviation followed by a space and then the year to use to label each bar in your chart.

                      For the Y-Axis, click the button to the right and select "specify field". Select sTotalSales.

                      At the bottom of the dialog box, select these two options:

                      1. Use Data From Current Found Set
                      2. Show Data Points for Groups of Records when sorted.

                      Click OK to dismiss this dialog.

                      After making any needed adjustments to your chart object's size and location, return to browse mode.

                      Perform a find for the records you want to include in this chart. (This could be a specific range of sales dates such as those for the year 2011.)

                      Then Sort your records by selecting sort from the Records menu. Specify cMonth as the sort field. This step is need to group your records by month and in the expected "calendar" order of January through February.

                      • 8. Re: Create a bar graph for monthly sales
                        KennySolway

                        how do i add a new chart object and to what layout?

                         

                        is there a messenger service so we can chat in real time, otherwise, this could take all day?

                        • 9. Re: Create a bar graph for monthly sales
                          philmodjunk

                          You have nearly everything you need to do this.

                          Select the layotu where you want this chart. It should specify the same table as that where the fields we have added were defined. You can use new layout from the layouts menu to create a brand new layout and specify this same table if you want. (Layout Setup... specifies the table for a given layout in the Show Records From drop down.

                          To add a chart object, enter layout mode, click the chart tool at the top of the screen in the status area and then draw a rectangle on your layout (in the body layout part). This will pop up the chart setup dialog I described in my last post.

                          • 10. Re: Create a bar graph for monthly sales
                            KennySolway

                            Thank you.  I think I have it working now, however is it possible to create a static report such that you just press a button and that report shows up?  As opposed to having to manually find then sort each time?

                             

                            Also, for the bar graph, is there a way to show the dollar value, in this case, sales total at the top of the bar?  For example, if in September of 2011 sales were $14,596, could you have the value of $14,596 appear at the top of the bar for the month of September?

                            • 11. Re: Create a bar graph for monthly sales
                              philmodjunk

                              Finds and sorts can be scripted so that you click a button and the script does the find and sort for you as well as changing to the report layout.

                              Charts in FileMaker are pretty limited. We didn't have any charting capability built in until the latest version was released. You cannot add the dollar total to the top of the bars, but a tool tip with the total (not formatted as currency) should appear when you hover the mouse over the bar. (Not what you wanted, but that's all FileMaker can offer you here.)

                              The alternative is to use a third party tool for for generating the chart. There are plug ins. I think there's s google widgit that will allow you to use a webviewer to chart your data and you can export the data to an excel spreadsheet so that you can use its charting tools (don't know if it offers this feature or not).

                              • 12. Re: Create a bar graph for monthly sales
                                KennySolway

                                Thanks for all your help.

                                • 13. Re: Create a bar graph for monthly sales
                                  MichaelBennewitz

                                       Hey Thanks for the breakdown, PhilModJunk,

                                       This really helped me finally get something useful in a chart.

                                       Now I've got to figure out how to do this as a dashboard item with preselected or selectable year or year range...