Create a new table, Charts, with 12 records--one for each month of the year. Add a pair of global number fields, gYear1 and gYear2. Put a number field, Month with the numbers 1-12 in it to identify the months.
In your original table, define cYear as Year ( YourdateFIeldHere ) and cMonth as Month ( YourDateFIeldHere ) unless you already have calculation fields that return these values. If you do, use them in place of where I'll use them in the following relationships:
Use the duplicate button (two green plus signs) to make a duplicate table occurrence of your original table in Manage | Database | Relationships.
Set up these relationships:
OriginalTable::cYear = Charts::gYear1 AND
OriginalTable::cMonth = Charts::Month
OriginalTable 2::cYear = Charts::gYear2 AND
OriginalTable 2::cMonth = Charts::Month
In Chart Setup on a layout based on Charts, you can use Sum ( OriginalTable::YourValueField ) for the first y-series and sum ( OriginalTable 2::yourValueField ) for the second series. You can set up a text field for month names for the x-axis labels.
And note that by changing the values of gYear1 and gYear2 you can chart monthly totals from different years just by editing those two fields.
Thank you very much!
It worked really well.
I assume that if I want to include more years, I have to duplicate the original table again and include another gYear in the Chart table, is that right?
Phil, this is a follow up question. I was unsure how to "define" cYear in my database using
your example above. Can you clarify? Is "cYear" simply a variable in a graph calculation?