Compare Fiscal Year data on a single chart

[James Watt]

I own a small retail business.  I track our daily revenue results and then summarize the weekly and monthly results.  I'd like to compare the monthly results to previous fiscal years on the same line graph.  I've searched for how to do this and it seems that there is no (fairly) easy way to do this in Filemaker!  This seems odd since this is the kind of thing I would have thought a database could do, and the demand is certainly there if the number of people asking are any indication.

Basically I'm looking for the x-axis to contain months (Jan-Dec or first fiscal year month to last, whatever) and have several lines showing the data (revenue, COGS, expenses, etc.) showing the various fiscal years.  I've done this in Excel and it took very little time but I have to copy/paste the summaries (usually re-type them) from FileMaker to Excel and there's always a chance of a problem.  Besides, it means I have to use two tools to do what one tool should do.

Any ideas on how to do this?

Thanks.

James.

[comment]

Could you explain this:

5 hours ago, James Watt said:

have several lines showing the data (revenue, COGS, expenses, etc.) showing the various fiscal years.

I cannot figure out if you want a separate line for each year, or for each type of data (revenue, COGS, expenses, etc.). Or would there be a separate chart for each type of data? Or something else completely?

Edited December 29, 2015 by comment

[LaRetta]

The request appears far too cluttered to appear in a single chart.  Can you attach a screen shot of what you want it to look like, James?

And welcome to FMForums!   

[James Watt]

15 hours ago, comment said:

Could you explain this:

I cannot figure out if you want a separate line for each year, or for each type of data (revenue, COGS, expenses, etc.). Or would there be a separate chart for each type of data? Or something else completely?

Yeah, that was probably a little unclear, sorry.  I'd like to graph each one of those items on a separate chart.  So what I'd like to see is, for example, gross revenue per month for each of a number of fiscal years.  So January to December along the x axis, gross revenue along the y axis and a line for each fiscal year (5 years, 5 lines, each showing the monthly revenue).  I'm not at home right now but will generate a chart showing what I mean.

Thanks!

James.

[comment]

10 minutes ago, James Watt said:

I'd like to graph each one of those items on a separate chart.

Okay, then. You are right that there is no easy way to do this in Filemaker. In fact, there are several factors that combine to make this difficult:

• The first complication is that a chart must have a fixed, pre-determined number of data series (i.e. lines in a line chart). This means you cannot re-use a chart designed to compare 4 years to compare 3 years or 5 years.
• The second complication is that the series must be clearly designated - but when you sort your records by month, Filemaker does not make it easy to mark the first 12 groups as the 1st series, the next 12 groups as the 2nd series, and so on.
• The third complication - which may or may not apply in your situation - that each data series must have a value for each data point; if there happens to be a month with no records, the remaining values will be skewed to the left, unless you have a mechanism in place to insert a zero value where the missing group is.

How to approach this, then? There are two possible ways:

1. Run a script using the FastSummaries technique to generate 5 variables (one for each year to be compared) of 12 values each (one for each month). Set the chart to use delimited data from these variables. This is relatively simple to implement, if you can be sure that the third complication mentioned above cannot occur - otherwise the scripting can get quite complex;
2. Use a related table of "slots" to summarize the records through a relationship based on matching the month. Then set the chart to use data from the slot records. This has the advantage of having a value for each slot, whether data for that month exists or not. However, summarizing records through a relationship ignores the found set - so this method cannot be used (at least not easily) to chart only records that meet some additional criteria.

I am enclosing a demo file showing the second approach.

 

 

ComparisonChartSlots.fp7

[bcooney]

James, this webinar might be of interest. 

 

http://www.filemakeracademy.com/october-20th-tips-for-building-dashboards-in-filemaker-14/

[James Watt]

Thanks all!  That demo file looks like what I'd like to do (and looks pretty slick), I'll have a deeper look at it.  I'll also look at the webinar.

I can make sure there are always records for each month, even they're 0, so the third complication won't apply.

Thanks for the help!

[LaRetta]

8 hours ago, comment said:

I am enclosing a demo file showing the second approach.

Nice example!!