Graph Annual Data for Years with Zero Activity
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.