10 Replies Latest reply on May 7, 2014 12:25 PM by beverly

    Invoice graph

      Hi all !


      I have this problem explained in FileMaker Training Series for FileMaker 12 7-39


      Be careful about holes in data series

      When creating line or area charts to show a trend of data over time, be careful that there are no time periods missing from your chart, or else you may misinterpret the results. For instance, if you are charting sales volume by month and you had no sales at all for April, your chart will likely display March and May next to each other. Holes can be more easily filled if you are scripting to create a delimited list than if you need to rely on the completeness of the found set or a related set.


      Anyone has an example of "scripting to create a delimited list of months"


      Thank you.

        • 1. Re: Invoice graph

          I bet this gem from Kevin Frank will help:




          I think this para will answer your question:

          === quote ===

          Stated more generally: when producing multiple charts for comparison purposes, we want to make sure the labels on the x axis are the same for every chart, and if there are any “gaps” in the data, we want to make sure they appear in the correct location.

          === /quote ===



          1 of 1 people found this helpful
          • 2. Re: Invoice graph

            I always use global variables for storing graph data generated like this, something like this works for me:



               Set Variable [ $i ; $i + 1 ]

              Set Variable [ $$Months ; if ( $i = 1 ; MonthName ( $i & "/1/2000" ) ; $$Months & ¶ & MonthName ( $i & "/1/2000" ) ) ]

               Set Variable [ $total ; ExecuteSQL("SELECT SUM(InvoiceTotal) FROM Invoices WHERE MonthNumber = ?" ; "" ; "" ; $i )  ]

              Set Variable [ $$Totals ; if ( $i = 1 ; $total ; $$Totals & ¶ & $total )  ]

              Exit Loop If [ $i = 12 ]

            End Loop


            I would make a bar or line chart, make $$Months your X-axis data source (current record, delimited), and then $$Totals your Y-axis source. Of course you'd have to adjust the calcs to meet your needs, but this is a basic example.


            Hope this helps a bit.

            • 3. Re: Invoice graph

              Mike –

              Mike Beargie wrote:


              Set Variable [ $$Months ; if ( $i = 1 ; MonthName ( $i & "/1/2000" ) ; $$Months & ¶ & MonthName ( $i & "/1/2000" ) ) ]


              Until FMI grants us real arrays and associated niceties like myArray.push(), L()IYF …


              Set Variable [ $$Months ; List ( $$Months ; MonthName ( $i & "/1/2000" ) ) ]


              and similarly, Set Variable [ $$Totals ; List ( $$Totals ; $total ) ]

              • 4. Re: Invoice graph

                *sigh*, I always forget to use List() to make lists. I need to get that tattooed or something. Thanks for the reminder as always!

                • 5. Re: Invoice graph

                  erolst wrote:


                  Until FMI grants us real arrays and associated niceties like myArray.push(), L()IYF …


                  That's an interesting standard for what makes a "real" array; I pity the primitives who were writing operating systems in lowly C without "real" arrays!

                  • 6. Re: Invoice graph

                    I didn't want to set a standard for a "real" array; I just would like to have a container type that's a bit more sophisticated – e.g. protects CRs within cells.


                    And yes, I pity those poor souls too …

                    • 7. Re: Invoice graph

                      Yeah! I got lazy of always doing:

                           $myVar = $myVar & "zyx" & ¶


                      now it's:

                           $myVar = List ( $myVar ; "zyx" )


                      LOVING IT!!

                      • 8. Re: Invoice graph

                        Old habits die hard. And unfortunately if you don’t “grow up” in a development environment that has developers with good coding standards you can pick up some bad ones yourself!


                        Another edge on FileMaker’s “double-edge” sword is the ability to calculate things in no less than three different ways. It was a few years in before I even started using Let()!


                        Ah well, a good developer is ALWAYS learning.

                        • 9. Re: Invoice graph

                          I get around this problem by creating a summary table intended for use on the graph. Every week, I summarize data for each advisor group in a school. If someone has NO data for the week (unlikely) it will still create that user's record and will put in 0. Every week, each advisor gets their own record whether or not they have any data to report.

                          THis does mess up the graph (you see the line steeply decline), but at least that advisor as some VALUE in the field for that week.


                          I use ExecuteSQL in all my graphs and do some other work to compare data series dynamically, so the summary table saves me from having to ask the ExecuteSQL statement to Aggregate the data and then draw it. By drawing from the summary table, I ensure that every person on the graph has the same weeks which to compare.

                          • 10. Re: Invoice graph

                            mayhaps not "arrays" as we have some quasi-ways to do these. But perhaps STRUCTURES, because these can be variable "arrays". Imagine being able to pass a  structure in ScriptParameter. Woooo hooo, now all of a sudden you can pass multiple values *easily*...