12 Replies Latest reply on Nov 15, 2014 12:44 AM by erolst

    Showing a "0" when charting values by month (Ideas?)

    jessicar

      I have a chart that works: It shows how many tasks were completed each month (in chronological order). Some months, no tasks were done. By design FileMaker isn't charting these months (rightfully so, as there's nothing to chart.)

       

      Is there are way to work around this and make those months show up on the chart with a "0" value?

       

      I don't think there is, but if anyone knows the answer it will be someone on these boards.

      Thanks in advance!

        • 1. Re: Showing a "0" when charting values by month (Ideas?)
          LyndsayHowarth

          If your scale starts at minus 1... would it show the zero then?

          What about number formatting options to show zero... do they apply to charts?

          ... just thinkin... I have not done a lot of charts in FM.

           

          - Lyndsay

          • 2. Re: Showing a "0" when charting values by month (Ideas?)
            TomHays

            It sounds like you have something like a bar chart with the months along the x-axis and the number of tasks on the y-axis.

            The data you are plotting is something like this...

             

            Jan 5

            Feb 3

            April 2

            May 4

            June 3

            July 1

            September 1

            October 4

            November 5

            December 8

             

            As you can see this has no entries for March and August.

             

            A classic remedy is to have a table consisting of the full set of items you want to display in your report even when there are zero things to report on them.  In your case you would have a TheMonths table with one record for each month.  Do your reporting via this table.

             

            If you wanted to have more flexibility you can use the Virtual List technique to accommodate many such situations without needing to create dedicated tables for each.

             

            If it can work for you, another technique (a kludge) is to create a dummy record that generates a zero in your data.  In your case you are likely counting records so it won't work since a placeholder record would add to the count, but some other types of reporting, e.g. totaling money in each category, can be managed by adding an entry.

             

            -Tom

            • 3. Re: Showing a "0" when charting values by month (Ideas?)
              jessicar

              Thank You but I can't control the scale - FileMaker automates it based on the data.

              • 4. Re: Showing a "0" when charting values by month (Ideas?)
                jessicar

                Yes, my data looks alot like that.

                So you're saying create a Months table and then rebuild the chart to count related records from that table right? It's more work than I was hoping for but so be it. I can give that a go.  Thanks!

                • 5. Re: Showing a "0" when charting values by month (Ideas?)
                  erolst


                  jessicar wrote:

                  So you're saying create a Months table and then rebuild the chart to count related records from that table right?

                   

                  You don't need an extra table – or even records, for that matter. See here:

                  • 6. Re: Showing a "0" when charting values by month (Ideas?)
                    jessicar

                    I'm not understanding your response. Are you saying dump the numbers in here? This is essentially a Month Table just without the records. I want to chart the records otherwise the report is no longer automated but requires manual labor from me. (BTW this isn't charting anything when I open it, and I verified it is sorted. Not sure what's up with that.)

                    • 7. Re: Showing a "0" when charting values by month (Ideas?)
                      erolst

                      jessicar wrote:

                       

                      I'm not understanding your response.

                       

                      No, what I was meaning was that you don't need an extra table and/or extra records, since you can use a script to compile a list of your chart data into a list variable in which the gaps are filled, and use these variables to hold the chart data (using the “Current Record / Return-delimited Data” option).

                       

                      jessicar wrote:

                      (BTW this isn't charting anything when I open it, and I verified it is sorted. Not sure what's up with that.)

                       

                      The file has a script that creates the variables which hold the chart data. I'm sorry to inform you that launching that script will (at present) require manual labour from you.

                      • 8. Re: Showing a "0" when charting values by month (Ideas?)
                        jessicar

                        OK, I'm closer to understanding. Can you please break it down in layman's terms for me? How exactly would I apply it.. Do I copy this script and chart into my current solution? (if you'd rahter email I can provide my address.. js)

                        • 9. Re: Showing a "0" when charting values by month (Ideas?)
                          erolst

                          jessicar wrote:

                           

                          OK, I'm closer to understanding. Can you please break it down in layman's terms for me? How exactly would I apply it.. Do I copy this script and chart into my current solution? (if you'd rahter email I can provide my address.. js)

                           

                          At the moment all this script assumes is that you have a sorted found set of 12 or less records (which the find section manages). Each record represents a month and holds the value you want to chart, as well as a date or month that you can sort by.

                           

                          It then collects the values within a loop (by virtually stepping through the found set), writing a placeholder value (e.g. a blank or a zero) whenever there is no record for the month that the current loop iteration expects.

                           

                          As for the implementation: you could copy the script, then adapt the field reference(s) to your fields. If you have FM Advanced, copy the Custom Function from the file; it holds a list of abbreviated English month names used as X-axis date. Otherwise, create your own list within a $$globalvariable and use it in the chart setup.

                          1 of 1 people found this helpful
                          • 10. Re: Showing a "0" when charting values by month (Ideas?)
                            jessicar

                            I’m still a bit befuddled.. I have hundreds of records to chart, and the values are constantly changing, so getting a table with only 12 or less records is not possible with my data. I might be missing something, but this seems like the same as creating a “Months” table to me, because I have to get it down to 12 records. I think I’m going to try that approach because I can wrap my head around it. I’m trying to understand but I think charting variables might be above my level of understanding. Thank You very much for your assistance!

                            • 11. Re: Showing a "0" when charting values by month (Ideas?)
                              jbrown

                              Im a big fan of using summary tables and ExecuteSQL to show on charts. Summary tables do not change their data and Execute SQL is context free, for the most part.

                               

                              If the count of projects for all previous months never changes, I'd run a script that does the count of projects and then stores that in a summary table. When a script counts 0 projects for March, it will record 0 in the summary table field.

                               

                              You could do that for previous months and then have another chart that shows this current month. If you'd like more information I can let you know.

                               

                              A chart will skip any records thare are not there. Recording 0 for March in a summary table is way different than NOT having a record for march.

                              • 12. Re: Showing a "0" when charting values by month (Ideas?)
                                erolst

                                jessicar wrote:

                                 

                                I’m still a bit befuddled.. I have hundreds of records to chart, and the values are constantly changing, so getting a table with only 12 or less records is not possible with my data. I might be missing something, but this seems like the same as creating a “Months” table to me, because I have to get it down to 12 records.

                                 

                                Well, you never said anything about your data structure. Anyway, what you have to get down to are not necessarily 12 records, but 12 values. Here is another sample file, charting a found set in a table with any number of records (with a date and a value), and using an even shorter script that utilizes SQL.

                                 

                                btw, for periods that are concluded, it may indeed be a good idea to summarize them once and store the results in another table – which is where you can create records to denote missing date ranges.