FileMaker charts are still in their infancy being a brand new feature with the latest release. It is a common experience to hit limitations with what you can do with them.
If you can structure a utility table wher you have 12 records, one for each month of the year and global fields for each year, you can define a series of relationships (think queries in Access) that each match by a different year and thus you can define calculations that compute totals for each specified month and year--with each different yearly total for January found in the first record, the yearly totals for February found in the second table and so on.
I agree that's a cumbersome approach, but it does work. (And you can define the calculations for this in the chart set up rather than in fields in the table if you want to.)
mmmhhh ... that sounds scary ... lots of relationships need to be managed ! What I am doing now is similar but believe a bit simpler. I have created one single utility table for all months and years. I could then populate the total fields per month and year, either by (hopefully) a simple script or by calculation fields (not sure how this works ... apparently I can summarize with GetSummary data into one field ... but then can I do a find using date ranges to filter the records that I need, before calling GetSummary ?).
As far as the charts are concerned, it seems that the easiest will be to buy a plug-in. So far I found FusionCharts and FusionMaps which seems to be a good combination of functionality and low cost.
To compare 5 years of totals you'd need 5 relationships only. That doesn't sound to me like an unmanageable number of relationships to use for this purpose.
The reason I've suggested this is that it's a way to link to different sets of record by month and year in separate fields (or at least separate calculations) for each year for that month so that the user simply selects a year or range of years one time and the 12 'month' records all update to point to the needed records for summary purposes.
That sounds interesting. The way I understand your suggestion is to create a summary table per year, 12 records ... 1 per month. Or do you mean one year-"neutral" utility table with only summary data.
Quote: you can define a series of relationships (think queries in Access) that each match by a different year and thus you can define calculations that compute totals for each specified month and year
Not sure I understand how do the relationships between tables and global variables would work. Can you elaborate please ? Maybe with a simple example ?
Yep, one "year neutral" set of 12 records.
You can use unstored fields such as a global field in relationships much like any other field as long as you respect the fact that the resulting relationship only works from the table with the unstored key to the table that does not have an unstored key.
Thus you can have a "year" global field such as gYear1 that is just a number field. You can create a series of calculation fields cYear2, cYear3... defined as gYear1 + 1, gYear1 + 2 and so forth.
A number field, monthNumber, can hold a number from 1 to 12 for the 12 records.
Now you can define a series of unstored key fields for your date based relationships:
cDateKey1: Date ( MonthNumber, 1, gYear1)
cDateKey2: Date ( MonthNumber, 1, cYear2)
and so forth.
This type of relationship can then be defined for the first "year":
UtilityTable::cDateKey1 = MainTable::cPurchaseDateMonth
(cPurchaseDateMonth is a calculation field to return the first of the month from the purchase date.)
You'd continue the pattern for each additional month and year you'd want to compare in your chart.
Now you can define calculations with aggregate functions such as Sum, count, average... etc. in your chart's axis definition to get totals for charting purposes.
on your first post of this thread you state:
"If you can structure a utility table wher you have 12 records, one for each month of the year and global fields for each year, you can define a series of relationships (think queries in Access) that each match by a different year and thus you can define calculations that compute totals for each specified month and year--with each different yearly total for January found in the first record, the yearly totals for February found in the second table and so on"
Can you please elaborate on the "series of relationships ... by ... year". I am now able to summarize by month and year correctly the data and even chart it. My question is now how to I break it by year ... and by month in the same data set.
Lesson learned by me so far: Use the table mode to generate the table that you require for your chart. Once that's done ... generate the chart !
Define these number fields with global storage: gYear1, gYear2, gYear3, gYear4....
gYear 1 as global number field and cYear2 as gYear1 + 1, cYear2 as gYear1 + 2 ....
Define matching calculation fields to combine a month with the year:
cMonth1: Date ( Month ; 1 ; gYear )
cMonth2: Date ( Month ; 1 ; gYear2 )
and so forth
Define a series of relationships:
ChartTable::cMonth1 = DataTableM1::cMonth
ChartTable::cMonth2 = DatatableM2::cMonth
and so forth
DataTableM1 and DataTableM2 are both table occurrences of the same data source table. cMonth is defined as: DateField - Day(Datefield) + 1 which computes all dates to be the first of the month for whatever month that date falls in to match back to the other cMonth fields on the other side of these relationships.
Sometimes a better thought comes later, I'm thinking this may work better with ChartTable records with a single year field, a single cMonth calculation field and just one relationship by cMonth to the data field. You could perfrom finds on the ChartTable to pull up the series of records you want for the chart and you could sort them first by month, then by year to cluster them by year as you've specified in the example in your first post.
I went a different route ...
I created a calculated field for the sales by year. I.e. Sales2012: if (cYear = 2012, Sales, 0) and same for Sales 2011 etc. I then create summary fields for the same and use those summary fields as data series in my graph where I have cMonth on the x-axis. Since the calculations are unstored I figure I don't loose too much in storage efficiency.
Fredrik - sorry to reopen a dormant thread but I'm trying to do exactly what you've just suggested.
Could you break what you've done down into more intermediate level steps without wasting too much of your time!
Thanks very much in advance!
1) Make a separate table for the summary. It has a remote Key in common with my main sales database (in our case that is the close time of the ticket. It is unique in our POS system).
2) I set up a relationship between the two tables so that I could reference the sales from the main table rather than import a copy, but that isn't necessary.
3) You need a month field in the reporting table (at least I wasn't able to use a remote field for the xlabel). I use the year field from the main sales database through the relation.
4) Set up one calculated field per year in your reporting table:
Sales2011 = If (DinerWareSales::cYear = 2011; DinerWareSales::NetSales ; 0)
5) Set up a summary field (total) for each SalesYear field
6) Create the graph with the xaxis:
MonthName ( Date ( MonthlySales::Month ; 1 ; 2010 ) )
The calculation is to get the month name. You could do it in other ways too (Choose for example) but I felt this was pretty easy. It would be tempted to just use MonthName(CloseTime), but that doesn't seem to work.
7) Set up one y-series for each summaryYear field.
8) To display the graph you need to sort the data on month then year. I have a script for that on "LayoutEnter".
9) Finally, to update the display table I delete all and then import my sales records (CloseTime).
See if that works for you.