1 Reply Latest reply on Mar 15, 2014 10:30 AM by philmodjunk

    Graph Annual Data for Years with Zero Activity

    Mac89

      Title

      Graph Annual Data for Years with Zero Activity

      Your post

           I'm hoping this is one of those things where there is a really obvious solution that I just haven't thought of yet. Thanks in advance for any ideas.

           I'm trying to graph the number of sales by year in a line and bar graph. I was able to easily do this using the basic method of switching to Table View, adding a Trailing Group by my Sales Field and then setting the Trailing Subtotal to Count. Then I select Chart by Sales and my chart appears. This works beautifully when there is activity in every year. However, if there is a year with zero sales it skips that year because there are no entries with that year for it to read from the table. So if I had sales in 2002, 2003, 2005 and 2006. The chart will not show there were zero sales in 2004.

           I did come up with a labor intensive workaround by creating a calculation field using "If(Year = "2012";1;0)" and then a summary field which figures out the total of the calculation field. This works but requires two fields to be created for every year, is much less flexible and I can only get it to work as a bar graph and not a line.

           Anyone have a better way of creating bar/line graphs when you need to show periods with zero activity? This is especially important with line graphs showing activity over time since the removal of zero periods from the line graph give the wrong impression.

            

        • 1. Re: Graph Annual Data for Years with Zero Activity
          philmodjunk

               You've posted a question about FileMaker Pro in the section for questions about FileMaker Server. (See tabs at to of screen).

               I would set up a new table with one record for each year. Link it to your sales table by year. Define a calculation field in this new table that uses Count to count the related records (the number of sales record for that year). You can then use these two fields in the new table from a layout based on that new table to produce your chart.