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.
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?
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.
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.
If this is a summary field from a related table, replace the reference to the field with a calculation:
table::summaryField + 0
I didn't realize you were counting sales. My fault. I didn't read carefully
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.
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.
Chart_WithZero.fmp12.zip 71.8 K