1 2 Previous Next 25 Replies Latest reply on Nov 10, 2012 10:24 AM by johnhorner

    Charting monthly sales by year from same field

    Emmanuel

      Title

      Charting monthly sales by year from same field

      Post

           To whom it may interest!

           I am experiencing a hard time trying to chart monthly sales (x-axis) by year ( y-axis: 1 line per year x 4 years). All the data is from "invoice details" file which is a direct export from the accounting system (I mention it since I would expect it to eventually generate the same report automatically in a folder and create a daily-updated analysis). Anyways, the file namely includes:

             
      •           Invoice No
      •      
      •           Invoice Date
      •      
      •           Item No
      •      
      •           Qty
      •      
      •           Unit Price
      •      
      •           Customer No

            

           Also, I have another file, "customer list", which includes:

             
      •           Customer No
      •      
      •           City
      •      
      •           State
      •      
      •           Country

            

           To be more precise, I would like to create 3 checklists of options that would establish the  analysis parameters:

             
      •           Choosing either "unit" or "sales"
      •      
      •           Choose from either "all products", "product line" or "a specific product"
      •      
      •           Choose the geographics based "all country",  "specific country", then related "states" and finally "city"

            

           That is the whole plan. However, I can not even manage to have simply all the sales, by month with year comparaison.

           I have been looking around and I found an "array charting" that has an monstruous script that I would not be able to copy and rearrange. Then, I got on the self-join concept, but there again, I get lost in the extra-fielding and relationshiping. Can someone help out?

           Best regards,

        • 1. Re: Charting monthly sales by year from same field
          philmodjunk

               What Chart Style will you use? Line Chart?, Bar chart? or ???

          • 2. Re: Charting monthly sales by year from same field
            Emmanuel

                 A line chart, is it possible?

            • 3. Re: Charting monthly sales by year from same field
              philmodjunk

                   It's possible. The details for setting up a line chart for this vs. setting up a bar chart differ. Bar charts are simpler, BTW.

                   A line chart needs to use a different field/data series for each line in order to chart multiple lines on the same chart and that complicates this process quite a bit.

                   Are you using FileMaker 11 or 12? ExecuteSQL might make it much easier to produce the needed data series' but it's a new feature in FileMaker 12 so I don't want to go there unless you are using that version.

              • 4. Re: Charting monthly sales by year from same field
                Emmanuel

                     We are using FM 12!! So what is the first step? :)

                      

                • 5. Re: Charting monthly sales by year from same field
                  philmodjunk

                       First step is to set up a table for your report and then use ExecuteSQL to generate the needed data series for your four specified years. With older versions of FileMaker, the only way to make this work that I can think of is to devise an extra table with some date and global field data to sit between your charting table and the data as a kind of "aggregator table". But with ExecuteSQL, we should be able to dispense with that added complication.

                       Create a new table and add a field for specifying a year. I'm assuming that you want a range of years in sequence so if you specify 2009, you could chart data for 2009, 2010, 2011 and 2012 in four separate data series for your line chart. Eventually, you'll want fields for the other chart options as well so you can add those fields while you are at it if you want.

                       I'm going to bookmark this thread and return back with a demo copy. ExecuteSQL still has that "new feature smell" for me so I want to play with it to make sure I get all the details correct before I tell you how to do it. Once I have a working demo, I'll report back here with a description of the details and a downloadable demo file.

                       And one question about charting the unit price: Since you'll have many transactions for a given product for a given month, do you want to chart the average unit price perhaps? I'm assuming that you want total sales for when you chart a sales figure.

                  • 6. Re: Charting monthly sales by year from same field
                    philmodjunk

                         Ok, this was fun....

                         I highly recommend the use of SQL Explorer by SeedCode. I ended up using it in conjunction with the documentation on select statements in the fm12_odbc_jdbc_guide_en.pdf that you can find in FileMaker's help file to build the SQL used in this demo file.

                         You can download a free copy of this tool from: http://www.seedcode.com/downloads

                         In this demo file, I've assumed that you would need the tables typical of most invoicing systems:

                         Customers----<Invoices-----<LineItems>-------Products

                         I added a 5th table, Charts and did not link it to any of the other tables in relationships as ExecuteSQL will define any needed relationships "on the fly" in order to produce a return delimitted list of values to chart in the Line Chart.

                         I defined 3 fields for this demo in Charts: year1 (number), cYear1Series and cYear2Series--both use the same SQL expression but cYear2Series uses Year1-1 as a parameter in the ExecuteSQL function where the first field uses just Year1. I could easily generate two more data series by duplicating one of these fields and then editing the paramter to by Year1-2 and Year1 - 3 repectively.

                         I didn't create those fields as I didn't have time to generate sufficient test data for them to show.

                         This isn't the complete solution, but it's a start for you to take a look at and try to understand how it works. To handle all the options you've requested, You can create more sophisticated SQL expressions that include more joins to other tables and more criteria in the WHERE clause to limit the data in each series. You could also put several ExecuteSQL function calls inside a case function so that the particular SQL used can be user selectable.

                         Here's the demo file for a look see: https://dl.dropbox.com/u/78737945/4YearCompChartDemo.fmp12

                         Note: one important limitation of this demo file is that there must be at least one sales invoice with at least one lineitem record for each month of the year. If not, the sales totals may not correctly align for the correct month. For most businesses, this should not be a problem as they would be out of business if they don't have any sales for a given month.

                    • 7. Re: Charting monthly sales by year from same field
                      Emmanuel

                           Amazing! You are a genius! It works perfectly :)

                           Thank you very much!

                      • 8. Re: Charting monthly sales by year from same field
                        Emmanuel

                             Hi Phil!

                             I am currently deploying what we have discussed and I am blocked getting the "product line" in the sql query. What would be the sql way of asking: give me the sales per years, grouped by months of the product family "bathroom" - which is a value from "product line" field from "product" table. Do we have to put that table in SELECT even though we don't want its value but just using it as a condition?

                        You started with:

                             ExecuteSQL ( "

                              
                             SELECT SUM ( b.sales ) FROM \"Invoices\" a
                              
                             INNER JOIN \"LineItems\" b ON a.\"__pkInvoiceID\" = b.\"_fkInvoiceID\"
                              
                             WHERE a.cYear = ?
                              
                             GROUP By a.cMonth
                              
                             ORDER BY a.cMonth
                              
                              " ; Char ( 9 ) ;  ¶ ; Year1 - 1 )
                              
                              

                        And I am trying out:

                             ExecuteSQL ( "

                             SELECT SUM ( b.Sales) FROM \"Invoices\" a 
                              
                             INNER JOIN \"LineItems\" b ON a.\"__pkInvoiceID\" = b.\"_fkInvoiceID\"
                             INNER JOIN \"Products\" c ON b.\"_fkItemID\" = c.\"_pkItemID\"
                              
                             WHERE a.cYear = ? AND c.productline = ?
                              
                             GROUP BY a.cMonth
                              
                             ORDER BY a.cMonth
                              
                             "; Char (9) ; ¶ ; Year1 - 1 ; "Bathroom" )
                        • 9. Re: Charting monthly sales by year from same field
                          philmodjunk

                               What results do you get when you inspect the values in this field? It looks correct to me, but maybe you are getting the dreaded and frustrating ? that tells you something isn't quite right but with no clue as to exactly what isn't correct?

                          • 10. Re: Charting monthly sales by year from same field
                            Emmanuel

                                 Correct! I have tried the "data viewer" to see if it could help out, but all I can get is a "?" as a result... even in the SQLExplorer I can't get it work! Is there something to do with " ? Is there a way to point out what is wrong?

                            • 11. Re: Charting monthly sales by year from same field
                              philmodjunk

                                   That ? response is the one aspect of ExecuteSQL I hate the most. All you can tell from it is that "something is wrong" and there is not the slightest indication as to what, exactly is going wrong in the expression.

                              • 12. Re: Charting monthly sales by year from same field
                                philmodjunk

                                     I opened the demo file, duplicated the calculatioin field and then successfully edited it as follows:

                                     ExecuteSQL ( "

                                     SELECT

                                     Sum ( b.cost )

                                     FROM \"Invoices\" a
                                     INNER JOIN \"LineItems\" b ON a.\"__pkInvoiceID\" = b.\"_fkInvoiceID\"
                                     INNER JOIN \"Products\" c ON b.\"_fkItemID\" = c.\"__pkProductID\"

                                     WHERE a.cYear = ? And c.ProductName = ?

                                     GROUP By a.cMonth

                                     ORDER BY a.cMonth

                                      " ; Char ( 9 ) ;  ¶ ; Year1 ; "Wrench" )

                                     I then went back and carefully compared to yoru example and found a one letter typo in this line:

                                     INNER JOIN \"Products\" c ON b.\"_fkItemID\" = c.\"_pkItemID\"

                                     Unless you renamed a field, the field name is missing an underscore character as I routinely preface primary key fields with two underscores so that they sort to be ahead of foreign key fields with only one underscore.

                                • 13. Re: Charting monthly sales by year from same field
                                  Emmanuel

                                       Rigth on it! Thank you very much, it helped me big time setting my dashboard with charts!

                                       Without over-asking, I am facing antoher challenge: the dashboard that uses exlusively "ExecuteSQL" - as you taugth me :) - takes 12 minutes (!!!) to load at every changes I make in the radio buttons section that I use to filter the results (Case functions integrated in the charting fields to personalized the search). I tried indexing the main fields that are being searched for but it did not change anything. Is it suppose to be that slow? Can I make it faster? 

                                       If not, can I add some function somewhere to launch the search on a button clic after that the radio button have been chosen? 

                                        

                                       Thank you again

                                        

                                  • 14. Re: Charting monthly sales by year from same field
                                    philmodjunk

                                         ExecuteSQL is new to all of us so I don't have any definitive answer on whether or not that result is typical for what you have set up. Much depends on the number of records in your tables and whether or not all key fields are indexed or not.

                                         If your SQL is using aggregate functions such as SUM or Average, it may be possible to use a new table that "flattens" the data into fewer tables and records--thus reducing the amount of calculations and iterations needed to evaluate your queries.

                                    1 2 Previous Next