I'm not 100% sure from your description what data you have available, but if you have month and year available on each sales record, one way this can typically be done is to have a separate table that lists month and year, then set up a relationship between that table and the raw sales data. Then, use a calculation that summarizes the sales data for the related data by month and year. For example:
SalesByMonth = Sum ( Sales::Volume )
Then, you can chart SalesByMonth from the Months table.
I have attached an image which shows such a feature within a single sales-data table. I have tried to obscure data which makes it recognisable so a lot of the categories or Types (subsummary) are missing... and I've blurred the actual numbers...
The date periods (global fields) are generated by entering one and the rest are auto-populated according to a pattern the user selects from a popup with options for months, quarters and years.
Each column is a relationship by category and time period.
Works well... but I have not tried charting it. I shouldn't imagine it would be too much of a problem...
Thanks for this. I will certainly give it a go.
I was going to try to method I had writien a case statement to give each year a number and then through a self join I could separate the data and chart it that way. I was just having trouble with my casr statement, its just not doing what I am expecting it to do.
Case(Date ≥ 1/10/2009 and Date ≤ 30/9/2010;1;
Date ≥ 1/10/2010 and Date ≤ 30/9/2011;2;
Date ≥ 1/10/2011 and Date ≤ 30/9/2012;3;
This is what I had come up with, but it does not give me what I need.
Dates in FileMaker are stored as numbers, so you'll need to parse your text-based dates into actual date values using the GetAsDate ( ) or Date ( ) functions. For example:
Case ( DateField >= Date ( 10 ; 1 ; 2009 ) and DateField <= Date ( 9 ; 30 ; 2010 ) ; 1 ;
DateField >= Date ( 10 ; 1 ; 2010 ) and DateField <= Date ( 9 ; 30 ; 2011 ) ; 2 ;
DateField >= Date ( 10 ; 1 ; 2011 ) and DateField <= Date ( 9 ; 30 ; 2012 ) ; 3 )
Also, watch out for that trailing semicolon on the end of your function. It's probably going to break your syntax.
Oh - just a suggestion, but if you have a need to do this sort of thing on a regular basis (make determinations based on a fiscal year), it might be worth building a custom function that does it for you. That way, you can feed the CF a date and year and have it return a 1 or 0 based on whether the date falls within that year. Might cut down on the possibility of errors.
I would be interested to know how you achieved this. What I had been trying to do with my data was to create table occurances of my main data table and basically have a table occurance for each year then creating a data set for each year and then charting it but it does not seem to be working and I'm not sure why. I am not able to create the correct set of records I am looking for.