7 Replies Latest reply on Jun 8, 2017 7:30 PM by jbrown

    Chart Points With Zero Results


      Been messing with charts, still struggling past some of the more basic functions this has. Not sure if it is just limited by the way it works or if I am just not getting it.


      Here is an example:

      Doing a chart on #sold items per month. X-Axis has the dates Jan - Dec of whatever year I choose. Y-Axis has number of sales. Problem I have is when a specific month has no sold items it just doesn't show up so it skips the month. I would like that month to say zero.


      I tried doing this based off same table (date, summary_count_sales,date_labels etc). I also tried doing this by creating a table that has all the dates in them, for other reasons but I was hoping when using this it would still list them. Now make sense since it will still only show them based off related info existing.


      So my question is, is there a way to force a zero result in the graph where there is no data such as my example above, no sales for say the month of Feb? Instead of skipping that month, I would like it to read as Zero vs skipping the month altogether.

        • 1. Re: Chart Points With Zero Results


          Charts take a bit of getting used to.

          The short answer is no: you can't force a zero. You have to have a record or a delimited row for the 0 amount. Since it is possible to sell 0 of something in any month, you have to have that 0 in the data list.


          I recommend that you Create a summary table that adds up the sales for each month (one record per month per year). That includes 0. sum up all the sales for a month, write a record, and then use that list in your chart.

          • 2. Re: Chart Points With Zero Results

            Makes sense but when I am calculating the number of sales it does it with a summary field of count so it is based off the existence of it. Is there a work around to that or am I stuck with that?

            • 3. Re: Chart Points With Zero Results

              the only workaround i can think of is that you create at least one record for every month that is a 0 amount.

              That's a hack way and introduces records that don't mean anything ( a sales record for March in the amount of 0 is fake), but it would catch in the summary.


              I personally never use a summary field to put into a chart and then sort by that summary field. There's too much to do. I think charts should be based on static data. In the example i described above, a summary table holds the past sales data. Last year's data won't change, so it can be stored and then gathered via a relationship or ExecuteSQL or some other method.

              • 4. Re: Chart Points With Zero Results

                I appreciate that info however that won't work. Since this is counting number of sales, even if I input $0 in the amount it still counts it as a sale so it would read one sale for that month. The summary field is just counting the records regardless of what an item sold for so plugging in a record at 0 will show inaccurate information of one sale.


                I suppose in this case the FMP charts are limited unfortunately won't work for what I need unless there are any other suggestions or I am doing something wrong.


                **** EDIT ****


                Hold on, I didn't try the method you mentioned, will try that out and see if it works. Not sure how I missed that.

                • 5. Re: Chart Points With Zero Results

                  If this is a summary field from a related table, replace the reference to the field with a calculation:


                  table::summaryField + 0

                  • 6. Re: Chart Points With Zero Results

                    I didn't realize you were counting sales. My fault. I didn't read carefully

                    I'm not really sure that FileMaker's charts are the limiting factor; i work a lot with JavaScript charts, and they need the same thing: a list of data to be plotted, including a data point that is 0.


                    Since summary fields count existing records, it won't return 0 for any months that have no sales.


                    I guess your only option here is to skip using the summary field and instead create the data set for the chart. Here you'd go through and count how many sales in each month in a script, collect that data and chart it. Let me work up a demo of this.


                    • 7. Re: Chart Points With Zero Results

                      This is how i'd do it. I'd capture the data into a list into two fields and use those fields to be in the chart.


                      I like using scripting to control how the data gets collected. In this case I'm just looping thru.